Quickly Determine if a query returns records 1

[MBorofsky]

I need to be able to run a set of queries and determine if each one returns at least one record. There are so many that executing using a DCount or a recordset.recordcount is extremely slow. Is there any way to quickly determine if a query returns at least one record?

 

[Tranman]

MBorofsky,
I have a routine that checks a list of tables for at least 1 row. It's a little different because you may have joins that slow the return significantly, but what I did is wrote a set of queries that say, "Select Top 1 * from....". These are all linked Oracle tables, some with millions of rows, but my response is blindingly fast.

The "Top 1" thing says when you get a row built, display it and stop looking. Of course, you would not need to display the data, just return the top row into a recordset and check the recordset's EOF property. You could even use the same recordset for all of your queries.

If you took your set of queries and either fiddled Top 1 temporarily into the SQL, or made a copy of the query with Top 1 and ran that, my guess is that you could rip through a list of queries pretty fast.

If you're not familiar with the way to fiddle with a query's underlying SQL, check out the querydefs collection and the querydef object.

Tranman

 

[Golom]

No. "TOP 1" doesn't stop after retrieving one record. The "TOP 1" can be determined only after all records are retrieved and the ORDER BY clause processed to figure out which record is the TOP 1.

There is an EXISTS clause as in

Select * From tbl 

Where EXISTS ( ... your query here ...)

and that one does stop when one record is returned.

 

[Tranman]

Of course, if there is no "order by", it does stop after the first one...which is how mine work. You're right--I should have mentioned that.

Tranman

 

[PaulF]

why not open the query as a recordset, check to see if the .recordcount> 0

Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("YourQueryName")
If rst.RecordCount > 0 then
'Do This
Else
'Do This
End IF
Set rst = Nothing
Set db = Nothing

PaulF

 

[MBorofsky]

Just for reference here is a copy of one of the query's SQL:

SELECT tblAllegations.Allegation_ID AS 1, tblAllegations.Referral_ID AS 2, tblAllegations.FIPS AS 3, Count(1) AS 4
FROM tblAllegations
GROUP BY tblAllegations.Allegation_ID, tblAllegations.Referral_ID, tblAllegations.FIPS
HAVING (((Count(1))>1));

This query is in fact looking for duplicate key violations. The client will be importing their own data. We wanted to let them import no matter how shotty the dataset so I decided to create a set of reports in enforcement's sted.


PaulF:

That is what I am doing current. It is far too slow when you are running many many queries for extremely large datasets. I was hoping for a solution that would stop after it saw at least one record existed. This is 'of course' minus the 'Set db = CurrentDB' b/c this will eventually be a compiled into a .MDE and that statement does not translate for Microsoft is stupid reasons. By the way I had a hell of a time figuring out why my MDE files wouldn't compile. For personal reference you can actually use CurrentDB directly though it would certainly be cleaner as a var. I prefer the reduced code length myself but then I also prefer comment free code for the same reason - VB Code is just so damn transparent that it just turns into clutter.

TranMan:
Thank you for your interesting input. I was not aware such a statement existed. My queries do not have an order by statement but the run time is still identical. It may be because I am using those group by's or that Oracle is designed for larger datasets than Access. I will however keep this statement in mind for future problems.

Golom:
I was also not aware of this statement though it seems to have the same runtime as well. I assumed that in the above example I would use:

SELECT * FROM tblAllegations WHERE EXISTS ( SQL CODE ABOVE )

I'm alittle confused as to why it would stop at one record though. It would appear to me that the statement would select any record that also appears in the SQL Code.

 

[tonyflavell]

Hi there

"DLookup" will test for a record in a query, so I use:-

If IsNull(DLookup("MemberID", "qMembersRecordset1")) Then MsgBox "There are no member records to display!", , "Data Error"
Else
...do things
End If

Just an idea

Regards

Tony

 

[MBorofsky]

That also has the same run time. Each of these solutions seems to calculate the entire query before picking whichever record/s it picks. Thanks anyway though.

 

[ide]

MsgBox "In query2 is a record = " & fnHaveRecord("SELECT * FROM tblDBParam ")

Private Function fnHaveRecord(strQry As String) As Boolean
Dim rstTemp As DAO.Recordset
fnHaveRecord = False
Set rstTemp = CurrentDb.OpenRecordset(strQry)
While Not rstTemp.EOF
fnHaveRecord = True
Exit Function
Wend
End Function

ide

 

[MBorofsky]

How would that be functionally different from PaulF's solution?

 

[Tranman]

MB,
Yeah, under the hood, when you "group by", the db engine has to sort all of the rows beforehand. That's why it takes just as long to return the first row as all rows.

I just noticed in your follow-up message, that the underlying database is Oracle. Since you've got the "big guns" available, why not try solving the problem on the server side with an "before insert" trigger? You could throw the "problem" rows into another table. If the table was empty, you know you have no problems.

Another possibility would be to create a materialized view that reflects the output of your query, and just query that from Access.

BTW, that "Top 1" thing does not exist in Oracle--it's a Jet SQL thing. In Oracle, you have to use the "where rownum = 1" or "where rownum < 10" or whatever, to accomplish that.

Just some thoughts.
Tranman

 

[MBorofsky]

Tranman,

I am using Access. My reference to Oracle was b/c you had mentioned Oracle. My thinking was that perhaps TOP 1 had the behavior you described, stopping after it finds the first recordset, only occured in Oracle.

I eventually tested the TOP 1 statement on one of the queries that did not have any group by's and the runtime was still identical.

 

[tonyflavell]

Hi there again

How about adding an automatically incrementing [RowNo] field to the relevant queries then use "DLookup" to get a value where [RowNo]=1, if it's an error then no records, if OK then at least 1 record and goto next query!

Just an idea

Regards

Tony