Compare queries

Question

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:

Client Statistics:

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.

Answer 1

Those numbers are so small, you will not get any real information.

Are you having a problem, or just anticipating a problem?

Even with the query running "thousands of times a day", unless you have billions of rows it will not be significantly different. Having enough RAM to cache the table will be the most benefit to your query performance.

Answer 2

Look at the difference in CPU time. It's significant.

I think I see what the problem is. That is, why the difference in duration seems to be so small. That problem is spelt S-S-M-S.

Try inserting the result into temp tables instead. Or go into Tools->Options->Query Results and check "Discard query results". To wit, rendering the results in SSMS takes time, and this can distort performance of queries that execute in the millisecond range.

Or even better run it from your actual application, so that you can weigh in how the application behaves.

Then again, you rewrite is reducing the CPU with a factor of 4, so that's not bad.