Different results in SQL and Access

BruceM wrote:
I'm not using Access 2007, and it was my mistake to look too quickly
at a query where I had already filtered records as I had described,
but I had used DateSerial (didn't notice that it killed performance,
though).

You wouldn't have unless
1. It was a large table and
2. you had an index on that date field that could have been used to
optimize the query

Applying a formula to the field in the WHERE clause (such as DateSerial
or DateValue) prevents the use of that index which forces a table scan.
The idea is to create a "sargable" criterion, by constructing it in such
a way that the field value is left undisturbed in order to test the
criterion.

So this is sargable:
datefield Date() - 60

This is not sargable and will prevent the use of an index on datefield:
datefield + 60 Date()

See the difference? The query engine has to cycle through every record,
adding 60 to the value in datefield before comparing it to Date().
Whereas with the sargable criterion, the query engine merely has to
calculate Date()-60, and then use the result to filter the results from
an index.

I still think the reason for the difference in the recordsets was due
to starting at 00:00:01 rather than 00:00:00.
Yes, that is the crux of my theory as well - see Michel Walsh's reply
for a better explanation of what I was trying to get at to explain the
different results. What I did not mention was that the use of :01 might
have caused the results to be wrong for both systems, in addition to
being different.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Thanks for the information (and for introducing me to the term "sargable").
One more thing to keep in mind as my projects expand in size. I see the
difference between applying a function or calculation to an indexed field
value and manipulating the criteria instead to get the same result. I use
indexes according to my understanding of when and why to do so, but I can't
say I have that firm a grasp on the nuances of indexing.
I read Michel Walsh's reply, and can see where the use of 01 could lead to
differences between the two results, as well as inaccurate results in both
situations.

"Bob Barrows [MVP]" wrote in message
...
BruceM wrote:
I'm not using Access 2007, and it was my mistake to look too quickly
at a query where I had already filtered records as I had described,
but I had used DateSerial (didn't notice that it killed performance,
though).

You wouldn't have unless
1. It was a large table and
2. you had an index on that date field that could have been used to
optimize the query

Applying a formula to the field in the WHERE clause (such as DateSerial
or DateValue) prevents the use of that index which forces a table scan.
The idea is to create a "sargable" criterion, by constructing it in such
a way that the field value is left undisturbed in order to test the
criterion.

So this is sargable:
datefield Date() - 60

This is not sargable and will prevent the use of an index on datefield:
datefield + 60 Date()

See the difference? The query engine has to cycle through every record,
adding 60 to the value in datefield before comparing it to Date().
Whereas with the sargable criterion, the query engine merely has to
calculate Date()-60, and then use the result to filter the results from
an index.

I still think the reason for the difference in the recordsets was due
to starting at 00:00:01 rather than 00:00:00.
Yes, that is the crux of my theory as well - see Michel Walsh's reply
for a better explanation of what I was trying to get at to explain the
different results. What I did not mention was that the use of :01 might
have caused the results to be wrong for both systems, in addition to
being different.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

My,oh,my

Quite a discussion regarding the storage and selection of date/time access
vs sql server.

Thanks to you all. - Yes I am using a linked table and Yes there is time
involved.

After reading all your kind suggestions, I think I'll never use Access to
select records from SQL via Date/Time again.

I'm rewriting the app in VB and SQL Server.

Many thanks for your kind considerations
Pat Backowski

Pat Backowski wrote:
My,oh,my

Quite a discussion regarding the storage and selection of date/time
access vs sql server.

Thanks to you all. - Yes I am using a linked table and Yes there is
time involved.

After reading all your kind suggestions, I think I'll never use
Access to select records from SQL via Date/Time again.

Why? My first suggestion should work ...
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hi Bob,

I appreciate your suggestion, but I need to reliably select data by the hour
as well as the day.

In a commercial sense, we've fallen flat on our face and I can't take the
risk of another failure.

So, for various reasons, the sql/vb approach has merits.

Regards,
Pat

"Bob Barrows [MVP]" wrote:

Pat Backowski wrote:
My,oh,my

Quite a discussion regarding the storage and selection of date/time
access vs sql server.

Thanks to you all. - Yes I am using a linked table and Yes there is
time involved.

After reading all your kind suggestions, I think I'll never use
Access to select records from SQL via Date/Time again.

Why? My first suggestion should work ...
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

By "sql/vb", do you mean using a recordset to loop through and select your
data?? gasp

Or do you mean using ADO to execute your sql queries (which would certainly
be acceptable)?

I still don't understand why you are blaming Access for the failure of your
incorrectly-constructed query*. The query I suggested you use should be the
same query you should use in your ADO code.


* and before you say "I'm not blaming Access", the statement "I'll never use
Access again" certainly carries the implication of blame.

Pat Backowski wrote:
Hi Bob,

I appreciate your suggestion, but I need to reliably select data by
the hour as well as the day.

In a commercial sense, we've fallen flat on our face and I can't take
the risk of another failure.

So, for various reasons, the sql/vb approach has merits.

Regards,
Pat

"Bob Barrows [MVP]" wrote:

Pat Backowski wrote:
My,oh,my

Quite a discussion regarding the storage and selection of date/time
access vs sql server.

Thanks to you all. - Yes I am using a linked table and Yes there is
time involved.

After reading all your kind suggestions, I think I'll never use
Access to select records from SQL via Date/Time again.

Why? My first suggestion should work ...
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bob,

When quoting, please use the quote in its entirety:

"After reading all your kind suggestions, I think I'll never use
Access to select records from SQL via Date/Time again. "

I'll certainly continue to use MS-Access for lots of good reasons

Kind Regards,
Pat Backowski



If you are

"Bob Barrows [MVP]" wrote:

By "sql/vb", do you mean using a recordset to loop through and select your
data?? gasp

Or do you mean using ADO to execute your sql queries (which would certainly
be acceptable)?

I still don't understand why you are blaming Access for the failure of your
incorrectly-constructed query*. The query I suggested you use should be the
same query you should use in your ADO code.


* and before you say "I'm not blaming Access", the statement "I'll never use
Access again" certainly carries the implication of blame.

Pat Backowski wrote:
Hi Bob,

I appreciate your suggestion, but I need to reliably select data by
the hour as well as the day.

In a commercial sense, we've fallen flat on our face and I can't take
the risk of another failure.

So, for various reasons, the sql/vb approach has merits.

Regards,
Pat

"Bob Barrows [MVP]" wrote:

Pat Backowski wrote:
My,oh,my

Quite a discussion regarding the storage and selection of date/time
access vs sql server.

Thanks to you all. - Yes I am using a linked table and Yes there is
time involved.

After reading all your kind suggestions, I think I'll never use
Access to select records from SQL via Date/Time again.

Why? My first suggestion should work ...
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Pat Backowski wrote:
Bob,

When quoting, please use the quote in its entirety:

"After reading all your kind suggestions, I think I'll never use
Access to select records from SQL via Date/Time again. "

What difference does the entire quote make? It still implies the same thing
to me: Access is to blame for your failure to select records via date/time,
rather than your incorrectly constructed query.

I'm not trying to be argumentative here. The point I am trying to make is:
poorly constructed queries will return incorrect results regardless of the
method used to execute them.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"