Pre-Test Delima

[Karl Blessing]

There is a little delima, I have a big set of items, which when chosen goes to next page, returns the data for this, now what a customer wants, is that, if there is no data for the item, then dont have a hyperlink going to the next page.

while this sounds like a pretty easy problem. The delima is, I could do this, but even telling SQL Server(Via ADO) not to return recordsets, and to set the number of items per page to zero, it would still take a significant ammount of time, to just tell if there is data or not for a query.

is there a way to tell if there is data for a certain query, but not spend any time, trying to get that data back. (Especially if it has data, then you'd have to wait almost 40 second at the most for it to return wheter or not it has any) Karl Blessing aka kb244{fastHACK}

 

[Karl Blessing]

oh also, it'll have to be something that'll work with SQL Server 6.5, 7.0 and Oracle. Karl Blessing aka kb244{fastHACK}

 

[tlbroadbent]

I don't know about Oracle but in SQL 6.5, 7.0 and 2000 you could do something like the following:

Select RetCnt=count(*) From MyTbl Where ....

Check the value of RetCnt. If > 0 then records exist.

NOTE: This will work best if the criteria in the where clause includes indexed column(s).

Another possibility that may work faster:

if exists (select * From MyTbl Where ....)
Select RetCd=1
Else
Select RetCd=0

RetCd=1 if any records exist and RetCd=0 if no records exist. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Karl Blessing]

RS.MaxRecords = 1
RS.Open "select * from " & Request.Form("Cmd&quot, Conn, 3,1
RSCount.Open "select Count(*) as RecNo from " & Request.Form("Cmd&quot, Conn, 3, 1


those are the three I use in another project, for the field listings (giving their data types and such) And the count. Though I'd have to do them seperating to determine just how fast select Count(*) ... is on a recordset that could at some cases have 100,000 records (even though queried might return a couple hundred, it still seems to have to process the whole thing, the indexes were rebuilt on the 6.5 box last friday)

Karl Blessing aka kb244{fastHACK}

 

[tlbroadbent]

You are correct. Select Count(*) will traverse all the data. It will work best when no records exist and the criteria references an indexed column.

The 2nd recommendation (If exists) will work best when records exist because it doesn't have to go through all the records. As soon as one record is found, SQL returns true.

If you expect to find data most of the time use the If Exists query. Terry

http://members.home.net/tlbroadbent/index.htm