I want to write a query that finds out whether records from a date exist in a table. At the moment I am using

SELECT [Processing Date] FROM [Data] WHERE [Processing Date] = @ContextDate

It works but I was hoping there was a quicker query that would stop as soon as the first ocurrence of Contextdate was found

colmkav <colmjkav (AT) yahoo (DOT) co.uk> wrote in
news:3eab4f12-2cfc-4c2e-84b6-fceca77b818a (AT) googlegroups (DOT) com:

Make sure that the date column is indexed.That will improve processing
time more that any other technique.


--
Bob Q.
PA is y I've altered my address.

colmkav wrote:
Is this a saved query? Or a passthrough? The "@ContextDate" makes it look
like a T-SQL statement rather than a JetSQL one. Hmm, can a VBA variable
name begin with @? I'll need to check that later.


In addition to the indexing suggestion, using EXISTS will cause the query to
return a True result upon the first encounter with a record that meets the
requirements (an EXISTS subquery is one of the only times selstar (select *)
is acceptable in production code - since an EXISTS subquery returns a
true/false result instead of an actual record, the wildcard has no impact on
performance):

In VBA code:
if exists (select * from data WHERE [Processing Date] = @ContextDate) then
...

or, in a query:
select ...
where exists (select * from data WHERE [Processing Date] = @ContextDate)

On Thursday, July 12, 2012 11:14:51 AM UTC+2, colmkav wrote:
Thanks for the replies. The making it an index certainly works much faster (1/8of the original time!). My only problem (short term anyway) is I might not be allowed to change the live table as its used by other users.

On the exists query what is the actual SQL that should be used? I tried

select [Processing Date] FROM data
where exists (select * from data WHERE [Processing Date] = @ContextDate) ;

But this is actually slower (and also other dates.

colmkav wrote:
Ummm, why would you use EXISTS when filtering data from the same table? Just
put the criterion in the WHERE clause.

select [Processing Date] FROM data
WHERE [Processing Date] = @ContextDate

Yes, we're back to where you started. In what context are you using this
query? VBA code? You say you want to find " ... out whether records from a
date exist in a table ." ... in order to do what? Decide whether to run some
code or not? Decide whether to show or hide a control in a form or report?
What's the context?

Anyways. use EXISTS when you wish to filter the results from a table based
on values in a different table without using a JOIN.

select [Processing Date] FROM data
where exists (select * from SomeOtherTable WHERE [SomeDate] =@ContextDate)

A join might not work well because the other table might have multiple rows
that meet the criteria and you only want the single row to be returned from
the first table.

Bob Barrows wrote:

Anyways, use an EXISTS subquery in a query when you wish to filter the
results from a table based on values in a different table without using a
JOIN.

EXISTS can also be used in VBA code, as I described in my first post.

On Thursday, July 12, 2012 3:14:51 AM UTC-6, colmkav wrote:
Have you considered querying just the count of rows from your chosen Contextdate ?

SELECT Count(*) AS num_rows FROM [Data] WHERE [Processing Date] = @ContextDate

With an index on [Processing Date], the db engine wouldn't even need to look in the table because the index contains all the information it needs. It should be fast.