SQLTeam.com | Weblogs | Forums

Compare queries

On what basis should I compare performance of queries?

Query 1:

select * from dbo.Table1 where [T1_col1] like '%string%'

Execution Plan

Query 2:

select Table1.* from Table1 left join db2.dbo.Temp on Table1.num=db2.dbo.Temp.num where db2.dbo.Temp.[col1]='string'

Execution Plan

Query 3:

select * from dbo.Table1 where num in (select num from db2.dbo.Temp where [col1]='string')

Execution Plan

Query 1 is my actual query that executes thousands of time in a day. It being non sargable , I want to convert it into a sargable query (creating child table and querying it), hence query 2 and 3. The problem now is I am not sure how to compare the performance of these queries.
The tracer, client statistics and statistics IO values I have given are not constant (for example queries will have varying CPU time of 0 and 12ms each time I execute the query).All three methods gave different results.
Time and IO statistics = ON I get following results

Query 1:

Scan count 1, logical reads 1987, physical reads 2, read-ahead reads 390, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 12.4 ms, elapsed time = 213 ms.

Query 2:

Scan count 0, logical reads 1882, physical reads 1, read-ahead reads 265, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Scan count 1, logical reads 4, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3.2 ms, elapsed time = 175.2 ms.

Query 3:

Scan count 0, logical reads 1892, physical reads 1, read-ahead reads 265, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Scan count 1, logical reads 4, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3 ms, elapsed time = 166.6 ms.

Tracer result:

TFvg1

Client Statistics:
iDls2

So according to these 3 methods I'm not able to derive a conclusion as to which of my queries would perform better.

All the queries were run on a database have 50000 rows of data.

Any solution for this? Feel free to ask for any other details if required.

Before you can even begin to compare queries for performance - you need to have queries that return the same results. None of the above queries are the same and therefore it is meaningless to compare their performance.

You would probably be better off using full-text search instead of like for these types of queries.

1 Like