Current filter:
                                You should refresh the page.
                                  • Hi,

                                    We are using WcfServerModeSource. When we trace the queries, we see that aspxgridview is calling the datacource query twice as "SELECT TOP 1.." and after that it calls the query as "SELECT TOP 20 .." This behaviour creates performance issues on big datasets because of executing the query 3 times at database.  Are there any option to avoid calling TOP 1 queries? I have tried the options blow but the result is same;
                                    AutoGenerateColumns = false;

                                • Ilya (DevExpress Support) 12.06.2018

                                  Hi Murat,
                                  Is the "SELECT TOP 1 COUNT(*) ..." SQL query executed first on your database? If so, it is expected behavior, as this query is used to fetch the total row count. However, in general, this query shouldn't decrease the performance. At this step, I suggest you profile the query directly on your database (at least, review the query execution plan) to determine why the query executed slowly in your specific scenario.

                                  If you are observing a different query in the database log, please share it along with your WcfServerModeSource settings for review.

                                • Murat T 12.06.2018

                                  Hi Ilya,

                                  It is not the "COUNT(*) " SQL query. Before these 3 query an other count query is being executed (i did not mention this one before).

                                  I am pasting all queries below in execution order;

                                  QUERY 1: SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT_BIG(1) AS [A1] FROM (SELECT [MyTable].[Id] AS [Id], [MyTable].[TransType] AS [TransType], .......Other Fields FROM [dbo].[MyTable] AS [MyTable]) AS [Extent1] WHERE (5 <> [Extent1].[TransType]) AND ...other conditions ) AS [GroupBy1] QUERY 2: SELECT TOP (1) [Extent1].[Id] AS [Id], [Extent1].[TransType] AS [TransType], ......Other Fields FROM (SELECT [MyTable].[Id] AS [Id], [MyTable].[TransType] AS [TransType], .......Other Fields FROM [dbo].[MyTable] AS [MyTable]) AS [Extent1] WHERE (5 <> [Extent1].[TransType]) AND ...other conditions ORDER BY [Extent1].[AccountRef] ASC, [Extent1].[AccountTitle] ASC, [Extent1].[CreateDate] ASC ... other fields used in data columns.. QUERY 3: SELECT TOP (1) [Extent1].[Id] AS [Id], [Extent1].[TransType] AS [TransType], ......Other Fields FROM (SELECT [MyTable].[Id] AS [Id], [MyTable].[TransType] AS [TransType], .....Other Fields FROM [dbo].[MyTable] AS [MyTable]) AS [Extent1] WHERE (5 <> [Extent1].[TransType]) AND ...other conditions ORDER BY [Extent1].[AccountRef] ASC, [Extent1].[AccountTitle] ASC, [Extent1].[CreateDate] ASC ... other fields used in data columns.. QUERY 4: SELECT TOP (20) [Extent1].[Id] AS [Id], [Extent1].[TransType] AS [TransType], ......Other Fields FROM (SELECT [MyTable].[Id] AS [Id], [MyTable].[TransType] AS [TransType], ......Other Fields FROM [dbo].[MyTable] AS [MyTable]) AS [Extent1] WHERE (5 <> [Extent1].[TransType]) AND ...other conditions ORDER BY [Extent1].[CreateDate] DESC, [Extent1].[Id] DESC, [Extent1].[AccountRef] ASC, [Extent1].[AccountTitle] ASC, ... other fields used in data columns..

                                  * QUERY 1 is the query to get the count of the list. This query is not a problem, it runs fast and also i understand that is required.

                                  * QUERY 2 and QUERY 3 is totally identical. These two query are also very similar to QUERY 4 (Except the TOP count and the ORDER. clause. The QUERY 4 is ordered according to the SortIndex and SortOrder of the gridview). I think it should not be neccessary to call QUERY 2 and 3.  It increases the query execution time by 3 times and it creates performance issues if the database is big.

                                • Murat T 12.06.2018

                                  I have also intercepted the WCF Data service. These 3 calls are being sent to the WCF data service;

                                  ...DataService.svc/MyTable()?$top=1&$inlinecount=allpages&$filter=( (TransType ne 5) and ...other conditions )}

                                  ...DataService.svc/MyTable()?$top=1&$filter=( (TransType ne 5) and ...other conditions )}

                                  ...DataService.svc/MyTable()?$top=20&$orderby=CreateDate desc,Id desc&$filter=( (TransType ne 5) and ...other conditions )}

                                • Ilya (DevExpress Support) 12.07.2018

                                  Thank you for the provided information. Please give me additional time to discuss it with my team.

                                1 Solution

                                Creation Date Importance Sort by

                                Thank you for your patience.

                                WcfServerModeSource doesn't generate SQL queries directly. Instead, it constructs a query against DataServiceQuery<T> that is translated to a WCF service request on the client. When a WCF service receives the request, it generates corresponding SQL queries and executes them in a database. There is no option to affect the SQL query generation logic at the server mode source level.

                                Meanwhile, I've examined your requests and got the following results.

                                1. As you correctly noted, the first WCF query fetches the total row count. However, this information is fetched by the $inlinecount command, which cannot be sent in a separate query. Therefore, it is included in a simple "select top 1" query. As a result, two SQL queries (QUERY 1 and QUERY 2) are generated and executed on the SQL server. However, only the results produced by the first query are actually used.

                                2. The second WCF query is intended to resolve the entity key type. It is very similar to the first one, except for the absence of the $inlinecount operator. On the service, it is translated to QUERY 3, and is executed only at once when the source is initialized.

                                3. The third WCF query is translated to QUERY 4 and fetches the first portion of rows.

                                As far as I can see, all queries are expected and used for WcfServerModeSource to correctly operate. I'm afraid I cannot suggest any other solution, except for optimizing your SQL database so the "select top 1" queries are executed faster against your table.

                                • Ilya (DevExpress Support) 12.10.2018

                                  Hi again,
                                  I found that we optimized the entity key determining logic (p.2) in version 16.2.7 in the context of T495653: GridView - WcfServerModeSource - A redundant request is sent to the WCF service. I suggest you test your app in this version to check if the third SQL query is removed from the trace in your scenario.

                                • Murat T 12.10.2018

                                  Hi Ilya,

                                  Thanks for your detailed answer.

                                  Meanwhile, I did some optimizations;

                                  * I have changed the "?$top=1&$inlinecount=allpages" WCF query with "?$top=0&$inlinecount=allpages". In this case, it executes a SELECT TOP 0 query on DB, and it is much faster than TOP 1 query. (By the way, we already did many optimization  on SQL server but as we are working on a very big datasets, we don't  want to execute any unnecessary select query)

                                  * I have implemented a caching mechanism for the second ("?$top=1") WCF query. Now it is only being called once in application life time. But, if the second WCF query is being called to resolve entity key type,  a better solution could be to provide a new property like "EntityKeyType". We can set the type of the entity key using this property. If this property is set, AspxGridview would not need to make the “?$top=1” WCF query call. Isn't it posible to add a property like this?

                                  I understand that an optimization is done on version 16.2.7, but In this solution also there is one ?$top=1” WCF query call and I am trying to avoid that.

                                • Ilya (DevExpress Support) 12.11.2018

                                  We don't have immediate plans to change the internal WcfServerModeSource query building logic. However, I will check our implementation to see if it is possible to prepare a custom solution for your scenario. Please give me some time.

                                • Ilya (DevExpress Support) 12.12.2018

                                  I'm afraid there is no straightforward way to change the WcfServerModeSource behavior, as the corresponding internal classes cannot be overridden in custom code.

                                  However, having reviewed the fix in T495653, we determined that the server mode source still sends unnecessary queries in certain scenarios. Thus, we've prepared a new hotfix and included it in the next minor releases, 16.2 - 18.2. In the fix, we've also implemented a property that forces WcfServerModeSource to send the $count query instead of $inlinecount. This OData query is translated into a single "select count" SQL query instead of "select top 1" and "select top 1 count" ones. As a result, with the new option, WcfServerModeSource will send only two queries in order to fetch the first portion of rows: "select count" and "select top N".

                                  Refer to T495653: WcfServerModeSource/ODataServerModeSource - A redundant request is sent to the WCF service to learn more about the new option and request the hotfix for your version.