I don't have access to the source so I can look myself. But am curious as to which is the most efficient way to get if any records exist.?

'SELECT  OID FROM items'

Or

'SELECT Count(OID) as Count FROM items'

OID is a Guid field.

-AJ

John AJ Marknette wrote:

> 'SELECT  OID FROM items'

I usually use that with a "TOP 2" or whatever the syntax is. You don't want to process all of the records, so why look at them?

No idea about speed though.

--

Matthew Jones

John

My bet would be SELECT COUNT(*) FROM table


However, just to build a little on what Matthew says -

SELECT field FROM table

will build a result set. If its sensitive then you're ok but if not then files have to be written so will be slower. It will also (surprisingly enough) be faster on an indexed column.



Roy Lambert

"Matthew Jones" wrote:

I usually use that with a "TOP 2" or whatever the syntax is. You don't want to process all of the records, so why look at them?

TOP n is what I would have used with SQL Server, but Elevate doesn't support it.

-AJ

On 5/24/2018 11:04 AM, John AJ Marknette wrote:
> TOP n is what I would have used with SQL Server, but Elevate doesn't support it.

Would RANGE work ?

Raul

>Raul wrote:

>Would RANGE work ?

Actually yes it would in my case, as all I'm really doing is checking if the table is empty or not. Thanks.

-AJ

FWIW, I could not believe that it didn't have TOP, so checked my SQLBuilder class which I know uses it:

{$IFDEF USE_ELEVATE_DB}
   if m_nTopCount > 0 then
       szSQL := szSQL + ' RANGE 1 TO ' + IntToStr(m_nTopCount);
{$ELSE}
   if m_nTopCount > 0 then
       szSQL := szSQL + ' TOP ' + IntToStr(m_nTopCount);
{$ENDIF}

So it is effectively the same thing.

--

Matthew Jones

AJ,

The fastest way is to make sure that you set the RequestSensitive property to True, and then just issue:

SELECT * FROM MyTable

and check the record count.

The second fastest way would be:

SELECT COUNT(*) FROM MyTable

And by "second fastest", I mean you probably won't be able to tell the difference between the two.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


>And by "second fastest", I mean you probably won't be able to tell the difference between the two.

I can't let that go by unchallenged - how do you know its the second fastest if you can't tell the difference

Roy

On 5/25/2018 12:52 PM, Roy Lambert wrote:
> I can't let that go by unchallenged - how do you know its the second fastest if you can't tell the difference

Kind of curious now as well.

Totally guessing here but

Would "select * ..." not have to load at least 1st record (for current
cursor) and if one has blob/memo in that list would that not result in
another table lookup

I guess count(*) might do same but it could be optimized to not to and
just load from table header the record count

I don't have big enough tables handy - i'm getting 0 sec for both for
data i have handy at this time.

Raul