Query with parameters much slower than normal query
Query with parameters much slower than normal query
I experience a very bad behaviour.
I have a TMSQuery with parameters like
select * from table
where user = :UserID (of course much complex query with more parameters)
simply doing
msquery1.parambyname('UserID') := 14
msquery1.open;
takes 30 seconds
by doing
select * from table
where user = 14
it takes 3 seconds.
Why is this possible?
I have a TMSQuery with parameters like
select * from table
where user = :UserID (of course much complex query with more parameters)
simply doing
msquery1.parambyname('UserID') := 14
msquery1.open;
takes 30 seconds
by doing
select * from table
where user = 14
it takes 3 seconds.
Why is this possible?
Hello
I tried both queries (with parameter and without) and they have the same performance. Probably it depends on the table structure. Please send me SQL script to create your table and the exact query that is executed so slowly. Also I need to know how many records are there in the table that is used in your query.
I tried both queries (with parameter and without) and they have the same performance. Probably it depends on the table structure. Please send me SQL script to create your table and the exact query that is executed so slowly. Also I need to know how many records are there in the table that is used in your query.
We have investigated the problem. This behaviour is connected with the specificity of SQL Server work and not connected with SDAC. We have tested the same example with ADO and other third-party components, and the performance difference when executing the query with and without parameters is the same.
Parameters usage should improve the performance when you use one SQL query with different parameters values many times. In this case SQL Server caches the prepared query for the first time and uses it afterwards. But if you use one small SQL query with parameters, server spends much time to analyse parameters, and the amount of data transmitted between client and server increases.
May be I pinpointed the problem.
I corss checekd and SDAC is not the cause, but the problem is DISTINCT.
By removing distinct the queries are almnost the same
While distinct for some reasons makes the performance worse in case of parametrized query.
Can you confirm this?
(you can just remove distinct from my example and you'll notice it)
I corss checekd and SDAC is not the cause, but the problem is DISTINCT.
By removing distinct the queries are almnost the same
While distinct for some reasons makes the performance worse in case of parametrized query.
Can you confirm this?
(you can just remove distinct from my example and you'll notice it)