I have a query which selects some records from a large table. When I use a Datamodule, TDAMemtable, Remote service etc etc, make my selection with the wherebuilder and open the dataset I see a RPC:Completed event in the SQL Profiler. The duration is 90 seconds (everytime)!! I also have a dynamic query class to do a quick select on a table. When I put the exact same query in my query class and open this dataset I see a SQL:BatchCompleted event in the SQL Profiler. The duration is 4 seconds!! The query class uses the same (ADO) connection to the same MSSQLServer as the DataAbstract data table and creates a dataset with
Connection.NewDataset(aSQL,‘RUNTIME_DATASET’);
The only difference I see is the duration and the event class in the SQL Profiler.
NB. The duration is the time SQL Server needed to complete the query, it has nothing to do with processing the records on the client or something like that.
How is this possible?
Can I Change the type of event DataAbstract generates to the SQL Server? Or the kind of query?
You can’t change the type of event.
When you use WhereBuilder all the constants are passed as parameters on the server for security reasons. And MSSQL server executes query with parameters as system stored procedure sp_execsql. It takes more time to execute because it formes execution plan before the first execution. But next time it should be faster in the case of the same instruction.
When you execute something like SQLGetData then full direct query are passed to the server, so you see SQL:BatchCompleted in Profiler.
So if you need to execute queries faster - use the second variant. Or implement custom service method that will generate SQL statement from your DynamicWhere