Filters, Sortings, Groupings: SQL command vs. report designer

[Fridli Jacober]

Hello together.

There are two possibilities to accomplish ordering, sorting, filtering and grouping of data when using Telerik Reporting.

First approach is very obvious because probably you have to / had to work like this anyway: Use WHERE, ORDER BY, GROUP BY directly in SQL clause but it's some kind of "hidden".
Second approach is the ".NET way" which means that you apply those conditions on a "data adapter class" like you can do it in the report designer for example which is very handy since you don't need to code anything manually but have handy GUIs and can see it instantly in the designer's view.

Now my question: Which one is better / faster and (dis-) advantages over the other?
Are those "programmatically applied" conditions configured through the GUI evaluated by the application code after the original sql query is executed code or are they "translated" to native sql commands and applied before exuting the query?

First one would have noticeable impact on performance I guess whilest the second one is very handy as described above and you can see on the "top level" and don't have to search for anything "back" in the sql commands.

Thanks and regards!

[Nasko]

Hello Fridli,

The idea behind filtering the retrieved data source records is the same as in the WHERE clause in a SQL statement. However there are two ways to achieve this effect:

• Server side filtering: The most efficient way to save bandwidth is to filter data records before they are retrieved by the DataSource component. This is called filtering on the “server side”. To accomplish this you can use parameterized DataSource component for each DataSource component that support this. The next step is to map each DataSource parameter to Report Parameters using expressions. In this approach no Filters are needed. If however the data source does not support parameters, or you must run stored procedures and cannot modify the query, use report filters.
  
  
• Client side filtering: A filter can be added to the Filters collection on a Report, a DataItem level or in the Report Parameter’s AvailableValues. Filters are applied to the data set after it is retrieved by the DataSource. This method of filtering is called "client side" filtering and should be avoided for big data. Usually the Filter expressions compare fields of the data against report parameters or master data fields. These filters are calculated over the entire set of data and should not use the aggregate functions for the Expressions.

Additional information on the topic can be found in the Filtering Data, Ordering Data, and Grouping Data help articles.

Regards,
Nasko
Telerik

[Fridli Jacober]

Hello and thanks for the info!

 It's more about memory and cpu usage than bandwidth for me.

 So if I understand you right, setting a filter or sort expression on the data source itself (using report parameters) is more or less the same as enter it directly into the query and takes place on the sql server itself?
But using sort, filter and so on "away from the data source" does the operations after the "original data" is received from the data source which is slow and uses a lot of memory...?

Regards

[Nasko]

Hello Fridli,

You are correct. For example, let's say you have 1000 records and filter half of them on the server. You will only need to work with just the rest 500 records on the client side, which will be faster than working with the full set of 1000 records while they are being filtered on the client side.

Regards,
Nasko
Telerik