BCS result filter on SharePoint or SQL side?
-
Mittwoch, 2. Mai 2012 16:28
Hey,
I have setup a BCS connection from SQL 2008 to SharePoint.
The connection runs but I like to filter the results. Is it for performance reason better to filter it on the SQL side (build a view with a result of filter views) or on SharePoint side (filter in the external content type)?
Thanks for your help
Stefan
Viele Grüße Stefan
Kontakt unter Info@IT-Kiessig.de
Alle Antworten
-
Mittwoch, 2. Mai 2012 18:46
Hi,
You should look into getting the data via a stored procedure, and then send your filtering parameter to it. This will filter the values on the SQL side, only returning what you want, the most performant way.
Take a look at this thread for more info and this SharePoint Overflow question.
Thanks,
Mikael SvensonSearch Enthusiast - SharePoint MVP/WCF4/ASP.NET4
http://techmikael.blogspot.com/
Author of Working with FAST Search Server 2010 for SharePoint -
Mittwoch, 2. Mai 2012 21:20
Hi Stefan,
BCS filters are the fundamental way in which the Business Data Connectivity (BDC) service captures user input (or system input) and provides it to the back-end API invocation. These filters will let you narrow down the amount of records being returned by your back end data source which will not only improve performance, but also make your users lives easier as they have less data to work with and process. You should always define filters (it can be limit/comparison/wildcard filter) so that you can narrow your search and overcome others restrictions regarding a count of rows in the database. Especially when working with high volume of items, there might be cases in which you may have millions of items to pick, bcs picker will show up to 200 items (this is SharePoint restriction).
Please take a look at these articles about filter creation:
- Types of Filters Supported by the Business Data Connectivity Service
- Creating Comparison and Wildcard Filters for BCS in SharePoint Designer 2010
- How to: Define Filters for External Item Picker Controls
Also you may implement paging. Please take a look at these articles:
- How to use paging in BCS ?
- SharePoint 2010 External List Paging – Server Side
- Paging and sorting with BCS
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog- Als Antwort markiert Daniel YangMicrosoft Contingent Staff, Moderator Montag, 14. Mai 2012 02:31
-
Mittwoch, 2. Mai 2012 21:58
from my point of view using a view in sql for bsc is better option if you filter criteria is static / include joins and if you are truncation large data with this static filter as you can enable indexing on the view that will provide better performance. and still if there are some dynamic filters also you can all a stored procedure and query on this index view that will further reduces the result set .
rest all depends upon the requirement and DB structure
-
Donnerstag, 3. Mai 2012 08:02
Thanks for all your helpful information.
I don’t can do anything on the DB structure. It comes from Dynamics CRM.
When I set up a BCS filter, will there also less work on the SQL Server? Or is the last on the SharePoint Application server (so the application server will reduce the result summery).
Thanks for your help.
Stefan
Viele Grüße Stefan
Kontakt unter Info@IT-Kiessig.de -
Donnerstag, 3. Mai 2012 09:26
Hi Stefan,
If you create BCS filter in your External Content Type this filter will be applied on the Database server. So your application server will get filtered data that will be displayed on the SharePoint page.
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog -
Donnerstag, 3. Mai 2012 09:40
Thanks Dmitry
is it possible to run the External Content Type only on's a day? (i have only SharePoint Designer as tool)
Viele Grüße Stefan
Kontakt unter Info@IT-Kiessig.de
- Bearbeitet Stefan3110 Donnerstag, 3. Mai 2012 09:42
- Bearbeitet Stefan3110 Donnerstag, 3. Mai 2012 09:44
-
Donnerstag, 3. Mai 2012 09:43What do you mean? For example ReadList operation in the External Content Type is invoked every time you open or refresh a page with external list on it.
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog -
Donnerstag, 3. Mai 2012 09:58We want only show all CRM Accounts in SharePoint. For this reason i like to run "ReadList" only at night (because its really slow).
Viele Grüße Stefan
Kontakt unter Info@IT-Kiessig.de -
Donnerstag, 3. Mai 2012 10:18In this case I would suggest you to create a custom timer job. Your timer job will fetch the data from your external system daily and put it to the custom list on SharePoint page. Take a look at this article Creating Custom Timer Job in SharePoint 2010
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog -
Donnerstag, 3. Mai 2012 13:37
Thanks for your help.
How can I build a filter with SharePoint Designer: the 5 newest items (the creation date is stored in the "Creation" field).
Thanks for your help
Stefan
Viele Grüße Stefan
Kontakt unter Info@IT-Kiessig.de -
Donnerstag, 3. Mai 2012 15:46 Take a look at this article Creating Comparison and Wildcard Filters for BCS in SharePoint Designer 2010
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog- Als Antwort markiert Daniel YangMicrosoft Contingent Staff, Moderator Montag, 14. Mai 2012 02:31
.png)
.png)
