&SQL and ORDER BY ID DESC

Question

Question

Nicki Vallentgoed · Oct 7, 2019

#SQL #Caché

I know &SQL returns only one result but is

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

and

&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

the same in terms of processing required?

Discussion (7)3

Log in or sign up to continue

Alexander Koblov · Oct 7, 2019

Relative cost is only used to compare different plans for the same query. Relative cost is not useful for comparing two different queries.

Can you post screenshot of both queries with corresponding plans?

0 0

Robert Cemper · Oct 7, 2019

with

&SQL(SELECT MAX(ID) FROM Cinema.Film)

you eliminate the need of ORDER BY ... and save time

0 0

score 0 · Answer 1 · 2019-10-07T04:28:48-04:00

Alexander Koblov · Oct 7, 2019

You should look into query plans for these queries.

Generally, they can be different.

0 0

score 0 · Answer 2 · 2019-10-07T05:24:48-04:00

The query plan is the same in this instance, however the relative cost is vastly different.

Without TOP 1 = 72497300 (which should give you some idea of the table size)

With TOP 1 = 11

score 0 · Answer 3 · 2019-10-08T05:34:59-04:00

Tomas Vaverka · Oct 8, 2019

If ID is numeric...

0 0

score 0 · Answer 4 · 2019-10-07T08:47:27-04:00

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

similar to the query

&SQL(SELECT TOP ALL ID FROM Cinema.Film ORDER BY ID DESC)

Therefore, it is natural that the query plans

&SQL(SELECT TOP ALL ID FROM Cinema.Film ORDER BY ID DESC)
and
&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

are so significantly different.

score 0 · Answer 5 · 2019-10-07T11:33:57-04:00

While using &SQL without a CURSOR will always only execute once and return one row, the SQL optimization is still dependent on the specific query - so while the cost of the non-top query is much higher, what really matters here is cost to first row. For the example query, that will be minimal, but could be much higher in a more complicated case.