Mind...Blown
-
For the life of me, I can't explain this. This isn't intended to be a programming question, more a "nope, don't believe it, even if I'm seeing it" thing. We host our main app's data in SQL Azure, and we have information in a given table w/ part of the primary key as a datetime (has month-end mutual fund data). One particular function seemed to be taking too long, so we started investigating this afternoon. If I run a given statement w/ asofdate = '11/30/2013, query takes 20 seconds. Seems slow. If I run the same statement, changing only asofdate = '10/31/2013', query takes 0.1 seconds. Odd. No significant # of diff recs between those two dates (26,488 for Nov vs. 26,382 for Oct). So now I'm thinking it's either the way those 11/30 recs were physically stored, or something special about that particular date, until... If I run the same statement w/ asofdate >= '11/30/2013' (most recent date in db right now), it again takes 0.1 seconds. Cannot replicate anything close to this in our on-site SQL Server dev dbs. How the elephant can asofdate >= '11/30/2013' ever be faster than = '11/30/2013'?
-
For the life of me, I can't explain this. This isn't intended to be a programming question, more a "nope, don't believe it, even if I'm seeing it" thing. We host our main app's data in SQL Azure, and we have information in a given table w/ part of the primary key as a datetime (has month-end mutual fund data). One particular function seemed to be taking too long, so we started investigating this afternoon. If I run a given statement w/ asofdate = '11/30/2013, query takes 20 seconds. Seems slow. If I run the same statement, changing only asofdate = '10/31/2013', query takes 0.1 seconds. Odd. No significant # of diff recs between those two dates (26,488 for Nov vs. 26,382 for Oct). So now I'm thinking it's either the way those 11/30 recs were physically stored, or something special about that particular date, until... If I run the same statement w/ asofdate >= '11/30/2013' (most recent date in db right now), it again takes 0.1 seconds. Cannot replicate anything close to this in our on-site SQL Server dev dbs. How the elephant can asofdate >= '11/30/2013' ever be faster than = '11/30/2013'?
I don't know anything about Azure however if there is some sort of partitioning of data based on dates then this could be quite possible. Alternatively the database statistics may need refreshing - that can have a big impact if you are talking about tens of millions or hundreds of millions of rows.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I don't know anything about Azure however if there is some sort of partitioning of data based on dates then this could be quite possible. Alternatively the database statistics may need refreshing - that can have a big impact if you are talking about tens of millions or hundreds of millions of rows.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Yeah, I definitely thought about partitioning as the cause, but wouldn't I see the same results for equal/greater-than-or-equal to the same date?
-
For the life of me, I can't explain this. This isn't intended to be a programming question, more a "nope, don't believe it, even if I'm seeing it" thing. We host our main app's data in SQL Azure, and we have information in a given table w/ part of the primary key as a datetime (has month-end mutual fund data). One particular function seemed to be taking too long, so we started investigating this afternoon. If I run a given statement w/ asofdate = '11/30/2013, query takes 20 seconds. Seems slow. If I run the same statement, changing only asofdate = '10/31/2013', query takes 0.1 seconds. Odd. No significant # of diff recs between those two dates (26,488 for Nov vs. 26,382 for Oct). So now I'm thinking it's either the way those 11/30 recs were physically stored, or something special about that particular date, until... If I run the same statement w/ asofdate >= '11/30/2013' (most recent date in db right now), it again takes 0.1 seconds. Cannot replicate anything close to this in our on-site SQL Server dev dbs. How the elephant can asofdate >= '11/30/2013' ever be faster than = '11/30/2013'?
IndifferentDisdain wrote:
11/30/2013
There's your problem - using US-format dates! ;P
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
For the life of me, I can't explain this. This isn't intended to be a programming question, more a "nope, don't believe it, even if I'm seeing it" thing. We host our main app's data in SQL Azure, and we have information in a given table w/ part of the primary key as a datetime (has month-end mutual fund data). One particular function seemed to be taking too long, so we started investigating this afternoon. If I run a given statement w/ asofdate = '11/30/2013, query takes 20 seconds. Seems slow. If I run the same statement, changing only asofdate = '10/31/2013', query takes 0.1 seconds. Odd. No significant # of diff recs between those two dates (26,488 for Nov vs. 26,382 for Oct). So now I'm thinking it's either the way those 11/30 recs were physically stored, or something special about that particular date, until... If I run the same statement w/ asofdate >= '11/30/2013' (most recent date in db right now), it again takes 0.1 seconds. Cannot replicate anything close to this in our on-site SQL Server dev dbs. How the elephant can asofdate >= '11/30/2013' ever be faster than = '11/30/2013'?
-
Yeah, I definitely thought about partitioning as the cause, but wouldn't I see the same results for equal/greater-than-or-equal to the same date?
I could make lots of guesses all of which would probably be wrong, one of which might go something like this - what if the database engine prioritises queries that return a higher volume of data(see what I mean?) First thing I would do is rebuild the indexes and re-run database statistics.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Is asofdate the only parameter in the query? SQL my be picking different execution plans, which result in the different times.
It's not, and it's also joining another table (the table in question is the child, and it's being linked to the parent), but I've even tried eliminating different parameters, and that didn't make a difference. However, removing the join, i.e. making the query as simple as possible, did result in a positive difference. So, that does lead me to think about the execution plan, but the data is nearly the same in our dev dbs as production, so that would seem strange that a different execution plan would be generated, though that may be the case.
-
IndifferentDisdain wrote:
11/30/2013
There's your problem - using US-format dates! ;P
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Ha, yes, however all of our hosting, devs and customers are U.S-based, so that does seem the natural route to go :)
-
For the life of me, I can't explain this. This isn't intended to be a programming question, more a "nope, don't believe it, even if I'm seeing it" thing. We host our main app's data in SQL Azure, and we have information in a given table w/ part of the primary key as a datetime (has month-end mutual fund data). One particular function seemed to be taking too long, so we started investigating this afternoon. If I run a given statement w/ asofdate = '11/30/2013, query takes 20 seconds. Seems slow. If I run the same statement, changing only asofdate = '10/31/2013', query takes 0.1 seconds. Odd. No significant # of diff recs between those two dates (26,488 for Nov vs. 26,382 for Oct). So now I'm thinking it's either the way those 11/30 recs were physically stored, or something special about that particular date, until... If I run the same statement w/ asofdate >= '11/30/2013' (most recent date in db right now), it again takes 0.1 seconds. Cannot replicate anything close to this in our on-site SQL Server dev dbs. How the elephant can asofdate >= '11/30/2013' ever be faster than = '11/30/2013'?
-
IndifferentDisdain wrote:
How the elephant can asofdate >= '11/30/2012' ever be faster than = '11/30/2013'?
Did you really mean 11/30/2012? /ravi
My new year resolution: 2048 x 1536 Home | Articles | My .NET bits | Freeware ravib(at)ravib(dot)com
Yep, thanks; edited.
-
IndifferentDisdain wrote:
How the elephant can asofdate >= '11/30/2012' ever be faster than = '11/30/2013'?
Did you really mean 11/30/2012? /ravi
My new year resolution: 2048 x 1536 Home | Articles | My .NET bits | Freeware ravib(at)ravib(dot)com
Meant 11/30/2013 for both; corrected. Thanks.
-
Meant 11/30/2013 for both; corrected. Thanks.
OK. I know I'm not answering your question, but shouldn't the date part of your primary key be in the format "
yyyy/mm/dd"? Otherwise, your lexical comparison will be wrong since "12/01/2001" will be greater than "11/01/2012". /raviMy new year resolution: 2048 x 1536 Home | Articles | My .NET bits | Freeware ravib(at)ravib(dot)com
-
OK. I know I'm not answering your question, but shouldn't the date part of your primary key be in the format "
yyyy/mm/dd"? Otherwise, your lexical comparison will be wrong since "12/01/2001" will be greater than "11/01/2012". /raviMy new year resolution: 2048 x 1536 Home | Articles | My .NET bits | Freeware ravib(at)ravib(dot)com
No, my dates are formatted as 'mm/dd/yyyy' in my IDE (TOAD for SQL Server).
-
No, my dates are formatted as 'mm/dd/yyyy' in my IDE (TOAD for SQL Server).
-
For the life of me, I can't explain this. This isn't intended to be a programming question, more a "nope, don't believe it, even if I'm seeing it" thing. We host our main app's data in SQL Azure, and we have information in a given table w/ part of the primary key as a datetime (has month-end mutual fund data). One particular function seemed to be taking too long, so we started investigating this afternoon. If I run a given statement w/ asofdate = '11/30/2013, query takes 20 seconds. Seems slow. If I run the same statement, changing only asofdate = '10/31/2013', query takes 0.1 seconds. Odd. No significant # of diff recs between those two dates (26,488 for Nov vs. 26,382 for Oct). So now I'm thinking it's either the way those 11/30 recs were physically stored, or something special about that particular date, until... If I run the same statement w/ asofdate >= '11/30/2013' (most recent date in db right now), it again takes 0.1 seconds. Cannot replicate anything close to this in our on-site SQL Server dev dbs. How the elephant can asofdate >= '11/30/2013' ever be faster than = '11/30/2013'?
Well >= can easily be faster than = because, assuming some sort of binary tree search, as soon as a branch is encountered at the value, everything 'to the right' needs to be included as they are all >=, but with = each node needs to be checked. Similarly, it may be that 31-10-2013 records are all more easily found because of the tree structure of the indexes (remember its not the way the data is stored but the way the indexes are stored that will make a difference) If this sort of thing were happening on my local DB I'd be look ing at execution plans, re-building indexes and ensuring suitable indexes exist. On SQL Azure I don't know what's available.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
It's not, and it's also joining another table (the table in question is the child, and it's being linked to the parent), but I've even tried eliminating different parameters, and that didn't make a difference. However, removing the join, i.e. making the query as simple as possible, did result in a positive difference. So, that does lead me to think about the execution plan, but the data is nearly the same in our dev dbs as production, so that would seem strange that a different execution plan would be generated, though that may be the case.
Is there a
not indirective in the join? If so change it to anot existsas anot incan slow things down tremendously.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Well >= can easily be faster than = because, assuming some sort of binary tree search, as soon as a branch is encountered at the value, everything 'to the right' needs to be included as they are all >=, but with = each node needs to be checked. Similarly, it may be that 31-10-2013 records are all more easily found because of the tree structure of the indexes (remember its not the way the data is stored but the way the indexes are stored that will make a difference) If this sort of thing were happening on my local DB I'd be look ing at execution plans, re-building indexes and ensuring suitable indexes exist. On SQL Azure I don't know what's available.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
_Maxxx_ wrote:
Well >= can easily be faster than = because, assuming some sort of binary tree search, as soon as a branch is encountered at the value, everything 'to the right' needs to be included as they are all >=, but with = each node needs to be checked.
Good answer!!
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
