1

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:

Tracer Result

Client Statistics:

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.

4
  • 2
    First thing to check: are the results the same? Then: where did that third query come from? Dec 21, 2021 at 7:59
  • 3
    The first thing is to define pretty carefully what kind of performance you are looking for. Premature optimization is root for all kinds of evil, as C.A.R. Hoare put it back in the 70's. If you run the query once a day, who cares? If you run it hundred of times per second, it's very different a story.
    – vonPryz
    Dec 21, 2021 at 8:00
  • Are you running the queries against a test SQL Server database with adequate server hardware? Or are the queries run against a database running on a desktop/laptop? If you have a stable environment then you can compare apples of different types, Otherwise you might be comparing apples to oranges.
    – John K. N.
    Dec 22, 2021 at 12:55
  • duplicate on MS Q&A
    – SMor
    Dec 22, 2021 at 14:42

1 Answer 1

0

A couple of good points in the comments regarding being careful with premature optimization, and things to consider comparing such as the results themselves and the execution plan of each query.

But to give some suggestions on what I normally do when the situation calls for a performance analysis, these are the steps in a loose order I follow:

  1. Compare the actual runtimes of each query, both the initial cold run, and then a few hot runs after. At the end of the day, time is generally the reason one wants to tune a query for performance, and if one instance of a query is significantly and consistently faster than another, then a lot of times that can be the clear answer. (Assuming all results are equal between the queries.)

  2. Compare the execution plans of each query. As Erik points out, you may find key differences between execution plans for one instance of a query vs another. Things that can be immediate performance killers which are visible via the execution plan are: cardinality estimate issues, inefficient join operators (e.g. Nested Loops being used on a large unordered dataset where a Hash Join operation would be better), under or over requesting of Memory resources, inefficient index operators (e.g. an index scan when an index seek may be more appropriate), etc.

  3. Compare the Time statistics. If the queries have been equal with all things considered so far, the Time statistics is a helpful resource. While the actual runtimes of each query may be similar, the CPU Times may differ, quite possibly significantly. In the case where the CPU Time was slower, that's more consumption of your server's provisioned resources that could've potentially been used elsewhere to serve other queries and processes. You actually see an example of this in your first query vs your third query, where the Elapsed Time of the first query is only 30% slower but CPU Time is over 300% slower. (Though in actuality, it's only a difference of a few milliseconds, so in this case probably not a concern.)

  4. Compare I/O statistics. Most times I go straight to the Logical Reads metric, which is a measurement of how many 8 KB data pages did the query cause SQL Server to load from disk. Similarly to the reasonings I mentioned in the Time statistics, if a query can result in less data pages being loaded off disk and searched to fulfill the data being served for the query, that's less resources needed in terms of I/O and Memory (to load the data from the pages into) which is a more efficient solution. Even if the queries have the same exact runtime, I'd typically prefer the solution that took less work from my server to get to the finish line.

7
  • Some complement.... Time is not a stable value and it will differ when executing many time the same query because of the concurrent processes, even no user are on the SQL Server instance. So I usually run 11 times the same query and eliminate the firts response time, the smallest and the greatest, and compute the average time. By contrast, IO is a very stable metric, but unfortunably IO cannot be compared to segment when using ColumnStore index... !
    – SQLpro
    Dec 21, 2021 at 17:04
  • @forumresearcher007 Is there any thing you're still unsure of after reading my answer? It's helpful the extra information you provided, and it is evident your first query is non-sargable, hence the significant improvement in CPU Time in the subsequent queries (which my answer discusses). It is also evident by the execution plans that both your second and third query are getting a more efficient index seek operation as opposed to an index scan which your first query is doing (also discussed in my answer). It seems your second and third query are fairly similar enough in performance...
    – J.D.
    Dec 22, 2021 at 13:14
  • ...though sometimes using the IN clause can result in sub-performant execution plans too - but if the runtime is acceptable then no need to worry about it unless it becomes an issue. With only 50,000 rows and thousands of executions per day, it's really not a lot, so I wouldn't be too concerned overall.
    – J.D.
    Dec 22, 2021 at 13:14
  • @J.D. The problem is for my 2nd and 3rd query to work, I need to create a child table, maintain index, add a trigger(so that whenever a new value is inserted in the main table, it also gets inserted into child table) . All of this vs a non sargable query, will my 2nd and 3rd query still outperform the 1st? by how much? is it only by 2% -4% ( as per query cost)? I needed a number to know if it's worth it? Dec 23, 2021 at 5:47
  • @forumresearcher007 Structuring your database properly is a big part of ensuring optimal performance, with relational database systems like SQL Server. So if it makes sense, there's nothing wrong with having a second normalized table that's also maintained. But I always tell people to design their databases to their use cases and object structure first, then optimize second, since you may find there's no need to optimize any further when you design your database accordingly. There is no single number that will ever give you the answer, you have to consider most things and sometimes...
    – J.D.
    Dec 23, 2021 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.