Background
I was asked to determine which of two queries would be better for our solution. The rough KPI for this is how fast I can display the information on a user's screen (assume all users have the same ping). Both queries return the same data but are built very differently. After enabling client statistics and running 10 trials for each query I started comparing their average Time Statistics.
To keep things simple I'll say that query A has a lower "Wait time on server replies" but has a higher "Total execution time" because of a much higher "Client processing time".
Now I'm trying to understand these results so that I can make a more informed decision as to which of these two queries I should be recommending.
Question
How much variance should one expect when switching from one client (SSMS) to another (client used in product)? Is it very small (+/- 5%), or can the change in client change the "Client processing time" by factors of 2 or more (100% +)?
I ask because I would like to know if I should be using "Total execution time" to compare the efficiency of the two queries (b/c "Client processing time" and "Wait time on server replies" have little variance or no variance when running the same query in our product) or if I should compare these two metrics ("Client processing time" and "Wait time on server replies") independently of each other due to high variance when switching clients (ie Client processing time can fluctuate by a factor of 2 or more and will dominate the "Total execution time" when put into production)?
Dummy Example
| Query | Client processing time | Wait time on server replies | Total execution time |
|---|---|---|---|
| A | 7000 | 1000 | 8000 |
| B | 3500 | 2000 | 5500 |
If I assume that these values won't differ greatly when I run them outside of SSMS then I should go with Query B (Lower Total Execution Time).
If instead the variance for "Client processing time" varies greatly, then I would need to run experiments to determine what the "Client processing time" ratio is between SSMS and our tool before deciding which would be more efficient.
Definitions to terms as I understand them
Wait time on server replies: is usually related to the time spent waiting for the server to process the query and send back the response. This can include operations like data retrieval, joins, and aggregations performed by the server. Operators like Index Scan, Clustered Index Scan, Table Scan, and Hash Match (for joins) are common indicators of server-side processing.
Client processing time: typically includes the time spent on client-side operations such as parsing, aggregating, sorting, and rendering the result set. These operations are usually represented by operators like Sort, Compute Scalar, Filter, and Remote Query (if applicable).
How much variance should one expect when switching from one client (SSMS) to another (client used in product)? Is it very small (+/- 5%), or can the change in client change the "Client processing time" by factors of 2 or more (100% +)?
Drilling into the tech here, your question is actually the following:
"If I run a query from SSMS, and then run it from other client code, will the server use a similar execution plan?"
The answer is usually yes. You can almost certainly assume the answer is yes for the purpose of optimizing a query like the one you mention. Do your optimization work and measurements in SSMS and the results will be valid in your application.
But the answer can be no in a couple of circumstances, let's call them edge cases. If the query is in a stored procedure, compiling the stored procedure can prepare the statement and store that execution plan along with the rest of the executable statement. Similarly, if your application code prepares the statement and hangs on to the prepared execution plan for a long time, you might hit the edge case.
The edge case causes you problems if the data in the table changes so much between statement-preparation and statement execution, that the saved execution plan becomes ridiculously slow. If that happens, recompile your stored procedure or restart your long-running application program. This is most likely to cause problems in tables that start out almost empty and grow very large. Execution plans for almost-empty tables are sometimes very naive.