if you want to know if COUNT(*)
> 0 then you can use EXISTS
to make the query more efficient. Is there a way I can make a query more efficient when I want to know if COUNT(*)
> 1?
(Needs to be compatible with both SQL Server and Oracle.)
Thanks, Jamie
Edit:
I am trying to improve the performance of some code. There are some lines similar to:
if (SQL('SELECT COUNT(*) FROM table WHERE a = b') > 0) then...
and
if (SQL('SELECT COUNT(*) FROM table WHERE a = b') > 1) then...
The first line is easy enough to switch to an EXISTS
statement, but can I make the second line more efficient? From the comments and my own thoughts I have the following ideas, would any of them be more efficient?
if (SQLRecordCount('SELECT TOP 2 1 FROM table WHERE a = b') > 1) then...
(I can use ROWNUM
for Oracle.)
if (SQL('SELECT 1 FROM table WHERE a = b HAVING COUNT(*) > 1') = 1) then...
The following doesn't doesn't work in SQL Server:
SELECT COUNT(*) FROM (SELECT TOP 2 FROM table WHERE a = b)
But this does with Oracle:
SELECT COUNT(*) FROM (SELECT 1 FROM table WHERE a = b AND ROWNUM < 3)
Thanks for all your help so far.