|
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
|
|
|
|
|
I did start to post an interesting well framed question but decided to have a rant instead
I have spent all day looking at the various solutions to the problem I am having:
The remote server returned an unexpected response: (413) Request Entity Too Large
but I can't get any of them to work.
|
|
|
|
|
RugbyLeague wrote: but decided to have a rant instead
That's way more amusing anyhow, than answering questions or having a technical discussion.
RugbyLeague wrote: The remote server returned an unexpected response: (413) Request Entity Too Large
That looks frighteningly familiar. It's been a while though, it must have been around October 2010 when we were pushing out a POC for a customer. Hmmmmm, ...
Let me poke around for a bit in that mushy mess that is my brain (hey it's almost weekend). May be something useful will pop up.
Regards,
— Manfred
"I had the right to remain silent, but I didn't have the ability!"
Ron White, Comedian
|
|
|
|