February 25, 2009 at 6:34 am
You listed three or four different queries earlier in this thread. Try each of them on each index, see what the average run-time and IO are on all of them.
The reason I suggested the sequence of columns I did for the index is because it would help with all of the queries you posted.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 12:21 am
Ok. I will try and get back to you shrotly.
Generally speaking, which scan type(FORWARD or BACKWARD) will work fast? How any why? How it is working internally?
karthik
February 26, 2009 at 1:35 pm
karthikeyan (2/26/2009)
Ok. I will try and get back to you shrotly.Generally speaking, which scan type(FORWARD or BACKWARD) will work fast? How any why? How it is working internally?
I don't think it makes a big difference.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 2, 2009 at 1:54 am
I have tested the remaining queries with both index. Both of them took the same time to complete the execution. only difference is 'FORWARD SCAN' and 'BACKWARD SCAN' method in query plan.
karthik
March 2, 2009 at 6:47 am
karthikeyan (3/2/2009)
I have tested the remaining queries with both index. Both of them took the same time to complete the execution. only difference is 'FORWARD SCAN' and 'BACKWARD SCAN' method in query plan.
Well, if all the queries behave the same on all the indexes, then it doesn't really matter which one you use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 2, 2009 at 2:11 pm
GSquared (3/2/2009)
Well, if all the queries behave the same on all the indexes, then it doesn't really matter which one you use.
It does matter.
I did not see here tests for ALL queries.
Ordering may significantly affect INSERT and UPDATE queries.
And multiple INSERTs against the ORDER will lead to index fragmentation which will slow down SELECTs as well.
_____________
Code for TallyGenerator
March 2, 2009 at 2:20 pm
Sergiy (3/2/2009)
GSquared (3/2/2009)
Well, if all the queries behave the same on all the indexes, then it doesn't really matter which one you use.It does matter.
I did not see here tests for ALL queries.
Ordering may significantly affect INSERT and UPDATE queries.
And multiple INSERTs against the ORDER will lead to index fragmentation which will slow down SELECTs as well.
If the first column in an index isn't sequential for inserts, it will get fragmented rapidly. Even if it, it will generally get fragmented over time. I'm operating on the assumption that basic index maintenance will be both needed and done.
And the OP said there are four queries against the table and that he tested all four on all of the index suggestions and found they were the same performance. I'm taking that statement at face value, even though I find it highly implausible, because he's the one with the database and he can test it, and I can't.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply