&SQL and ORDER BY ID DESC

Primary tabs

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?

 

 

 

Answers

You should look into query plans for these queries.

Generally, they can be different.

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

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?

&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.

with

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

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

Comments

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.