SQL Server Performance Forum – Threads Archive

Question: SQL Server Dynamic SQL – Optimize

I have follwing problem: In my application the users have a search tool, where the can select multiple criteria to search documents in a database. The selection will be moved together by a stored procedures using dynamic sql. So, it’s possible, that a query look like this:
SELECT dbo.document.*
FROM dbo.document
WHERE ((field1 = ‘0000192042677506’) OR (field2 = ‘0000192042677506’)) AND
((valdate > ‘01.01.2003’) AND (valdate < ‘01.01.2004’)) The result by executing that query is a timeout (there are a view millions of records). All fields are in one ore more indexes.
If i take that sql statement into the query designer in enterprise manager, the query designer will generate the statement like that: SELECT dbo.document.*
FROM dbo.document
WHERE (valdate > ‘01.01.2003’) AND (field1 = ‘0000192042677506’) AND
(valdate < ‘01.01.2004’) OR
(valdate > ‘01.01.2003’) AND (valdate < ‘01.01.2004’) AND
(field1 = ‘0000192042677506’) The query designer optimize the where clause and the execution of that sql give the results in no time. Is there an system procedure or another possibility to give as input my sql statement an I will receive the optimized query, before I exectue it in my stored procedures. Thank you for your help
stefan SHU

the two are not the same, do you make a mistake in the transcription?
1 ((field1 = ‘0000192042677506’) OR (field2 = ‘0000192042677506’))
2. (field1 = ‘0000192042677506’) (field1 = ‘0000192042677506’) I think OR statements are potentially dangerous.
if you know for a fact that only exclusive row can meet the field1 & 2 conditions, the safest way to do this is
SELECT xx FROM table WHERE field1 = x AND valdate BETWEEN a AND b
UNION ALL
SELECT xx FROM table WHERE field2 = x AND valdate BETWEEN a AND b otherwise, potentially safer than the above is
SELECT xx FROM table WHERE field1 = x AND valdate BETWEEN a AND b
UNION
SELECT xx FROM table WHERE field2 = x AND valdate BETWEEN a AND b
which has to do a sort for distinct rows, also, SELECT * is bad form, always spell out the columns you need, and keep these to a minimum
also, your indexes should be IX_field1 (field1,valdate)
IX_field2(field2,valdate)
instead of separate indexes on each column finally, on your original question, there are techniques to optimize the query, but it depends on the statistics, so it hard to make it generally useful

Check this article on OR clause.
your query has OR hurting performance.
http://www.sql-server-performance.com/pk_or_clause.asp

On the other hand ensure the MSSEARCH service is started and have no issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>