While developing an application (WEF 7) I was connecting to a development database that only held a few thousand records per table. Now that the application is complete, and moved into a more 'live' environment, the database that it connects to has upwards of 20k records per table. I'm experiencing some noticeable slowdowns compared to what I had seen while developing.
So, I was looking for ways to reduce this sluggishness and increase the performance. I've added the various loading indicator builders to the page, to alert the user that something is loading and not just hung up, but also wanted to look into the SQL calls to make sure they are only calling and returning small 'subsets' of results, instead of all 20k per fetch.
I've mainly been using the SQL Call builder, with the 'Paged XML DataRetriever' set for 'Transform Result' along with a split paging builder being used in the consumer. Does this setting actually modify the query that is performed on the database to include some sort of 'limit' or does it just manipulate the results that are returned from the database? (Turning on SQL logging does not show any modification to the query, but it might be modifying after the log was generated)
I also noticed that there are some "Performance Hints" options for the SQL Statement Builder. Would using this separate builder for the actual statement improve the performance? And how are these settings effecting the actual query that is performed on the database?
Any advice/hints/tips would be appreciated!