locked
BCS result filter on SharePoint or SQL side? RRS feed

  • Question

  • 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

    Wednesday, May 2, 2012 4:28 PM

Answers

All replies

  • 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 Svenson


    Search Enthusiast - SharePoint MVP/WCF4/ASP.NET4
    http://techmikael.blogspot.com/
    Author of Working with FAST Search Server 2010 for SharePoint

    Wednesday, May 2, 2012 6:46 PM
  • 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:

    Also you may implement paging. Please take a look at these articles:


    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

    • Marked as answer by star.wars Monday, May 14, 2012 2:31 AM
    Wednesday, May 2, 2012 9:20 PM
  • 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 

    Wednesday, May 2, 2012 9:58 PM
  • 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

    Thursday, May 3, 2012 8:02 AM
  • 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 LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

    Thursday, May 3, 2012 9:26 AM
  • 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



    • Edited by Stefan3110 Thursday, May 3, 2012 9:44 AM
    Thursday, May 3, 2012 9:40 AM
  • What 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 LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

    Thursday, May 3, 2012 9:43 AM
  • We 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

    Thursday, May 3, 2012 9:58 AM
  • In 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 LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

    Thursday, May 3, 2012 10:18 AM
  • 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

    Thursday, May 3, 2012 1:37 PM
  • Take a look at this article Creating Comparison and Wildcard Filters for BCS in SharePoint Designer 2010

    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

    • Marked as answer by star.wars Monday, May 14, 2012 2:31 AM
    Thursday, May 3, 2012 3:46 PM