March 7, 2011 at 2:03 pm
Rog Saber (3/7/2011)
No, I am just a developer w/ dbo. Maybe that makes a difference?
Yeah, you can't see what's happening in another spid unless you've got higher level access.
Have you spoken with your in-house DBA about this issue? Did he give you any of his/her findings? In particular, the difference in benchmarking at different times of the day. The rest is design work.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 7, 2011 at 2:21 pm
No, unfortunately, we really don't have an in-house DBA - hence my trying to get some insight here. We have a person that 'runs' things for us but they really offer no insight other than doing what we ask thenm to do. So, we also don't have any tools - they are mainly used as a control measure.
March 7, 2011 at 2:36 pm
Rog Saber (3/7/2011)
No, unfortunately, we really don't have an in-house DBA - hence my trying to get some insight here. We have a person that 'runs' things for us but they really offer no insight other than doing what we ask thenm to do. So, we also don't have any tools - they are mainly used as a control measure.
Ugh... alright, either they are going to need to be directly involved, you're going to need higher level access, or we're going to throw overboard finding out what the difference is between day and night runs. Sorry. You just can't effectively trace those things from a dbo level. It's too much of an 'if you find this, check that' kind of scenario.
Taking a look at your indexes... I don't see a clustered index, just the nonclustered. Am I just having a blind moment or is there no clustered index on this table?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 7, 2011 at 2:39 pm
You're right, they are all non-clustered.
March 7, 2011 at 3:11 pm
Alright, a little review of your multiple indexes show most of them have the same leading edge (trip_status), and then different incarnations of secondary seeking columns. Sadly, none of them really work for your intent.
I'm curious about your data density. Can you run this for me?
DBCC SHOW_STATISTICS ("RT_TRIP", RT_TRIP_IDX)
And then paste back here what's in the second return (it starts with the column all density). I'm curious about the selectivity that's available. I'm wondering how selective the max(dt) is against the database.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 7, 2011 at 3:14 pm
0.0022522524car_init
7.579777E-0610car_init, car_no
1.014066E-0618car_init, car_no, ship_date_time
March 7, 2011 at 3:42 pm
Thanks, so you're averaging about 8 rows of dates per combinations, ~12% of data... yeah, not enough to force the seek and lookup.
Hm, ~500 Car_inits...
Yeah, my apologies, but there's not a lot you can directly do here without some major reworking of the data. My personal opinion would be this :
1) Include Lutz's recommendation and remove the UNION. You're already doing a scan, you might as well only do it once, just use the AND/OR where clause to handle it... Which I note in a later iteration you've already done. Woot. You also might want to use JOIN ON/WHERE so it's clearer what the joins are and what's data restrictors. It's just easier to read and makes no difference to the system.
2) Using Jeff's recommendation, leverage the existing index that we just looked at (which you're already doing), and try both using it inline as well as inserting the results into a #tmp table and joining against the #tmp in your de-unioned query. See which one runs faster when you've got some stability in the times it takes to run either one of them. Remember, the first run doesn't count, as you've got to fill the cache. Do 5 or 10 and average the times.
3) Inspect the core architecture and see about adding in a clustered index to this table, particularly if you can get it make it the same fields as RT_TRIP_IDX... just invert the column order so ShipDateTime is first, so you're always appending to the end of the table and not splitting the poor thing all over the place. (If you do this, remove RT_TRIP_IDX) Of note for this recommendation: This will help this query. It will probably annoy every other one that doesn't rely on ShipDateTime.
4) Inspect the multiple indexes using the same first column and review if the multiple secondary variations are necessary. This will require a code review. It won't help for this query, but it will help overall for insert/update/delete to this table.
You're pulling about 12-13% of this table out. There's not a lot you're going to be able to do about this forcing a scan without some major overhaul of the core design. If you can manipulate the clustered index, you can probably find some significant speed using a CROSS APPLY SELECT TOP 1 instead of a MAX() comparison to find the last date, but I'm still experimenting with that and can't give you conclusive evidence on the speed improvement.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 7, 2011 at 8:52 pm
From my earlier post:
"However, just running the primary select statment just then took me 1:17 to return 14,765 rows. But, again, am I barking up the wrong tree if I am at the mercy of how slow the server is? If I run this query at night (no users on the DB) it takes less than 10 seconds."
I just ran the primary select statement again - while no one is on the DB. It took 3 seconds. My whole proc only took 6 seconds.
Maybe I'm just banging my head against a wall as no matter how fast I make the query it will still run slow when lots of users are on the DB?
March 7, 2011 at 9:49 pm
Rog Saber (3/7/2011)
From my earlier post:"However, just running the primary select statment just then took me 1:17 to return 14,765 rows. But, again, am I barking up the wrong tree if I am at the mercy of how slow the server is? If I run this query at night (no users on the DB) it takes less than 10 seconds."
I just ran the primary select statement again - while no one is on the DB. It took 3 seconds. My whole proc only took 6 seconds.
Maybe I'm just banging my head against a wall as no matter how fast I make the query it will still run slow when lots of users are on the DB?
I did catch that, but my apologies for not being more clear. The less you touch the data, the less locks, less memory, less CPU you need per pass, the better performance you'll see during the poorly performing times.
The idea of optimizing isn't only for the best of performances, but also during the weakest times. The less your query has to do, the less it'll have to fight with other structures for the resources.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply