TOP with Order By alternative, performance results look mixed

Question

Post reply

TOP with Order By alternative, performance results look mixed

MMartin1

One Orange Chip

Points: 27799
More actions
June 7, 2023 at 10:54 pm

#4206573
Hi
I have a situation where I am testing two versions of the same query for performance.
Assume we have a table like
```
ID | StringValue | add_date
-----------------------------
10, 'val1', '2023-03-03'
10, 'val2', '2023-04-01'
10, 'val3', '2023-05-01'
10, 'val4', '2023-05-29'
11, 'val5', etc...
...
..
.
```
SET statistics time on;
declare @id int = 10;
Select top 1 stringValue
From dbo.myTable
Where id = @id
Order By add_date Desc
-- I should get Val4
/* Compared to */
Select stringValue
From dbo.myTable
Where id = @id and
add_date =
(
Select max(add_date) as max_add_dt
From myTable
Where id = @id
) -- gives val4 as well
When I see the execution plans, query two has a much lesser cost. But when I see the cpu time with STATISTICS TIME On, ther first query uses less CPU.
Top query -->
For query 2 I have:

I am wondering how it is that the CPU times favor Query 1 ?
Q1 -
Q2 -
Thank you.

----------------------------------------------------

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

How large is your (test) table?

What about indexes?

Please post DDL

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Aggregation versus sorting. A lot less work to sort than aggregate. Also, a join operation versus none.

Also, you can get much more accurate measures if you use Extended Events.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101126 More actions · Answer 3

Your table looks to be clustered first (probably only) on ID. Add add_date after ID in the clus index to help that type of query.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 4

Grant Fritchey wrote:
Aggregation versus sorting. A lot less work to sort than aggregate. Also, a join operation versus none.
Also, you can get much more accurate measures if you use Extended Events.

A TOP with order, MIN or MAX can act more like an equality predicate if the correct indexing is in place. Can be faster if the data is well distributed and table is indexed for it

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

The real problem is that even the "Actual" execution plan is riddled with estimates and the % of Batch junk is the worst. It's pretty easy to create a couple of queries the will show one at 0% of Batch and the other at 100% of batch but, when you use it, the exact opposite is true.

Using % of Batch is usually a good way to find potential issues but it should NEVER be used to make a final performance decision. Even SET STATISTICS can be be misleading. See the following article where the real performance issue was the way it was being measured ( https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle) .

The most accurate methods for measuring are either Extended Events or SQL Profiler. (Both need to be used intelligently or BOOM!))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27799 More actions · Answer 6

Thank you everyone for your good input. The message I am getting is to use Extended Events to measure performance since Batch and even Statistics are not always accurate to say the least.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27799 More actions · Answer 7

Grant Fritchey wrote:

Aggregation versus sorting. A lot less work to sort than aggregate. Also, a join operation versus none.
Also, you can get much more accurate measures if you use Extended Events.

This makes total sense, hence why I was confused with what the Batch showed.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27799 More actions · Answer 8

ScottPletcher wrote:

Your table looks to be clustered first (probably only) on ID. Add add_date after ID in the clus index to help that type of query.

That is correct. If there wasn’t on index on ID I am sure I would get a scan.

----------------------------------------------------