Have you had the experience where a Quick Query in Dynamics SL takes too long to return results for you? You thought what you asked for was a simple query, but it’s taking too long to give you what you need. We used to have the opposite issue with Dynamics GP, where Smartlists (Quick Query equivalent with Dynamics GP) would only return 1000 records regardless of how many records needed to be in the results to get an exhaustive answer to our query.
In Dynamics SL, you need to introduce Paging to correct this issue with the performance of your query.
Paging is a function that allows for the Quick Query screen not to return huge amounts of data all at once. It is like a Bing or Google search in that you only get one page at a time. The paging buttons return additional records as you click through the pages. When you turn on this option, your Quick Query that would otherwise return 100,000 rows will now only return 1,000 at a time, thereby increasing the responsiveness of the tool. Many of the Quick Queries that come out of the box are not filtered such that a user can select their own filtering and save as “filtered views.”
An example would be the “Batches” view in GL. This view returns all batches in the batch table by default. A filtered view can be saved to show only “Unposted” batches or only one period to post or only data that was entered into a particular module. Paging is basically an efficiency and performance option.
Below is the response that one of our technical people gave to one of our consultants that was facing this challenge.
“We don’t necessarily just want to limit the rows returned (5,000 is apparently the new default), but we also need to enable paging (in my opinion), which is a separate option to set in the Solomon.INI file. There is both a Solomon.INI file on the server install and each client workstation install. Many times, if you want the same setting for everyone, it can be configured in the server version of the Solomon.INI file, so that it does not have to be set on each workstation. I will test out the paging and row count options by setting in the shared file (my developer workstation actually has its own full install, so I can test without interfering with anyone else). I will let you know. This item was/is on my list on the production upgrade, but we can get it configured for the Subject Matter Expert testing too.”
The setting to turn on “Paging” in the Solomon INI file is:
[QuickQuery]
Paging=Yes
MaxSQLRows=1000
Once it is turned on, new buttons appear in the header of the “Quick Query Viewer” to allow for scrolling between pages. Each page will contain the max row count specified (for example, 1,000 rows per page).
Service Pack 1 of Dynamics SL 2011 allows you to set the maximum number of rows returned to something greater than 5,000. The setting is on the workstation and is as follows:
Change the SQL statement in the Quick Query screen to return a default maximum of
5000 rows. A solomon.ini entry has been added to change the number of rows returned. The new entry is:
[QuickQuery]
MaxSQLRows=5000 (where MaxSQLRows should be a value from 10 to 999999)
I hope this is helpful to those of you that have had issues waiting for the results of your Quick Queries.
