0
425

Written by

Nicki Vallentgoed
Senior Iris developer
Follow
Question Nicki Vallentgoed · Oct 7, 2019

&SQL and ORDER BY ID DESC

#Caché #SQL

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

Comments

Alexander Koblov · Oct 7, 2019

You should look into query plans for these queries.

Generally, they can be different.

0
Nicki Vallentgoed  Oct 7, 2019 to Alexander Koblov

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

0
Alexander Koblov  Oct 7, 2019 to Nicki Vallentgoed

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
Vitaliy Serdtsev  Oct 7, 2019 to Nicki Vallentgoed

<FONT COLOR="#800080">&SQL(</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">Cinema</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Film </FONT><FONT COLOR="#000080">ORDER BY </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000080">DESC</FONT><FONT COLOR="#800080">)</FONT>

similar to the query

<FONT COLOR="#800080">&SQL(</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000080">TOP ALL </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">Cinema</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Film </FONT><FONT COLOR="#000080">ORDER BY </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000080">DESC</FONT><FONT COLOR="#800080">)</FONT>

Therefore, it is natural that the query plans

<FONT COLOR="#800080">&SQL(</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000080">TOP ALL </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">Cinema</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Film </FONT><FONT COLOR="#000080">ORDER BY </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000080">DESC</FONT><FONT COLOR="#800080">)</FONT> and <FONT COLOR="#800080">&SQL(</FONT><FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#000080">TOP 1 </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">Cinema</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Film </FONT><FONT COLOR="#000080">ORDER BY </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000080">DESC</FONT><FONT COLOR="#800080">)</FONT>

are so significantly different.

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
Matthew Giesmann · Oct 7, 2019

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.
 

0