If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
Different results in SQL and Access
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. |
#13
|
|||
|
|||
Different results in SQL and Access
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 |
#14
|
|||
|
|||
Different results in SQL and Access
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" |
#15
|
|||
|
|||
Different results in SQL and Access
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" |
#16
|
|||
|
|||
Different results in SQL and Access
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" |
#17
|
|||
|
|||
Different results in SQL and Access
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" |
#18
|
|||
|
|||
Different results in SQL and Access
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" |
|
Thread Tools | |
Display Modes | |
|
|