|
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'?
modified 10-Jan-14 16:07pm.
|
|
|
|
|
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?
|
|
|
|
|
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.
|
|
|
|
|
Is there a not in directive in the join? If so change it to a not exists as a not in can slow things down tremendously.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
IndifferentDisdain wrote: 11/30/2013
There's your problem - using US-format dates! 
"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 
|
|
|
|
|
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
|
|
|
|
|
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".
/ravi
|
|
|
|
|
No, my dates are formatted as 'mm/dd/yyyy' in my IDE (TOAD for SQL Server).
|
|
|
|
|
Ah. Sorry, I misunderstood. So you have a composite primary key?
/ravi
|
|
|
|
|
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!!
|
|
|
|
|
For no discernible reason when I heard the lyric:
"You can go sleep at home tonight. If you can get up and walk away"
I thought of Dalek Dave 
!bVagadishnu
|
|
|
|
|
Is that because is sounds like a CCC?
|
|
|
|
|
Ferd Really wrote: "You can go sleep at home tonight. If you can get up and walk away"
Reminds of;
I go to parties sometimes until four
It's hard to leave when you can't find the door
It's tough to handle this fortune and fame
Everybody's so different I haven't changed
They say I'm lazy but it takes all my time ....
|
|
|
|
|
Nah - if you pass out drunk in Luton, the police don't wake you up: they are just relieved you aren't trying to knife them... 
Never underestimate the power of stupid things in large numbers
--- Serious Sam
|
|
|
|
|
Reminds me of:
Can you stand up?
I do believe it's working
Good
That'll keep you going through the show
Come on
It's time to go
|
|
|
|
|
If you find yourself thinking of DD at any time while not actually reading a post of his on CP, I would suggest committing yourself voluntarily to a local asylum, and remaining there for the foreseeable future!
MVVM # - I did it My Way
___________________________________________
Man, you're a god. - walterhevedeich 26/05/2011
.\\axxx
(That's an 'M')
|
|
|
|
|
The story of a unique London Underground map[^]
The detail in the map is remarkable, informative and gave me a good laugh.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
Started looking at WCF recently - I have a rule of thumb which states "if you have to mess around with config files to get a software system running then look for a different software system".
Unfortunately I had forgtten about that - which is why I started to look at WCF. I think now I will look elsewhere - 6 hours today of messing about with config files, all to no avail.
|
|
|
|
|
Everything you can do via configuration can also be hard coded into you application.
Except why would somebody want to hard code it, if can be configured?
Have you already posted an interesting well framed question in one of the forums or the Q&A?
I've been busy today, so I've not been hanging out on CP much.
Cheers!
"I had the right to remain silent, but I didn't have the ability!"
Ron White, Comedian
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
