Query Optimization using Indexes

Question

Query Optimization using Indexes

Viewing 15 posts - 76 through 90 (of 110 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

adlakha.22 (10/20/2011)
what should i do? 🙁

Run the tests, post the results.

If there's no significant gain from my last suggestion, a gain which you're happy with, then Remi will take over with the covering index option. It will enable the orderlines part of your query to run in maybe 5 to 10 seconds - at a price...and will only take him about 5 minutes to do. Offer him money, it might take less :hehe:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Ninja's_RGR'us (10/20/2011)
adlakha.22 (10/20/2011)
what should i do? 🙁
Keep working with Chris... as I said I'm not doing 8+ hours of free tuning work here for a single issue. Apparently Chris is fine with that :-D.

The important thing to keep in mind here is that running all these tests, reverting to the plan, tweaking and testing again and again and again - is all fundamental to writing queries. Getting this far here has taken 5 days. A sql programmer will cover all of this ground for every production query they write - in no time at all.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mishal Ten Centuries Points: 1103 More actions · Answer 3

@ninja

I know in b/w 8 hours u r usually....see the posts read them...

its lot of work to do...its very time consuming to read all the posts..

I can understand

Thanks

For giving ur valuable suggestion

its good if u will suggest some thing that will help me

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

ChrisM@Work (10/20/2011)
adlakha.22 (10/20/2011)
what should i do? 🙁
Run the tests, post the results.
If there's no significant gain from my last suggestion, a gain which you're happy with, then Remi will take over with the covering index option. It will enable the orderlines part of your query to run in maybe 5 to 10 seconds - at a price...and will only take him about 5 minutes to do. Offer him money, it might take less :hehe:

My WHOLE point here is that you have a very viable possible solution and a test env. Instead of giving that index a shot and seeing the real cost in cheap HD space & ram you choose to spent about 1 day each on the issue. Costing possibly enough to buy a whole new server or 100X more ram than needed than what it would take to put that index into.

P.S. MOST system are 95% to 99.99% reads. So unless you are seeing 100(0)s+ of inserts / second you can probably lean more towards heavy(ier) indexing.

/ life lesson for those interested 😉

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

adlakha.22 (10/20/2011)
@ninja
I know in b/w 8 hours u r usually....see the posts read them...
its lot of work to do...its very time consuming to read all the posts..
I can understand
Thanks
For giving ur valuable suggestion
its good if u will suggest some thing that will help me

I don't work here... I just invest way more time than most users 😉

Mishal Ten Centuries Points: 1103 More actions · Answer 6

@chris-2

could u please suggest me further...

I think ur last suggestion might work...seems lot of improvement..

I m waiting for ur response

Mishal Ten Centuries Points: 1103 More actions · Answer 7

@ninja

But from now i m not investing my time in reading and replying to ur post...

😉

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us (10/20/2011)
adlakha.22 (10/20/2011)
@ninja
I know in b/w 8 hours u r usually....see the posts read them...
its lot of work to do...its very time consuming to read all the posts..
I can understand
Thanks
For giving ur valuable suggestion
its good if u will suggest some thing that will help me
I don't work here... I just invest way more time than most users 😉

He can't help without seeing the new plans and how long it took.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

adlakha.22 (10/20/2011)
@ninja
But from now i m not investing my time in reading and replying to ur post...
😉

19K+ points. ±5 threads started so far. 3 of them still unanswered to this day. :hehe:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Ninja's_RGR'us (10/20/2011)
Ninja's_RGR'us (10/20/2011)
adlakha.22 (10/20/2011)
@ninja
I know in b/w 8 hours u r usually....see the posts read them...
its lot of work to do...its very time consuming to read all the posts..
I can understand
Thanks
For giving ur valuable suggestion
its good if u will suggest some thing that will help me
I don't work here... I just invest way more time than most users 😉
He can't help without seeing the new plans and how long it took.

Precisely.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Ninja's_RGR'us (10/20/2011)
adlakha.22 (10/20/2011)
@ninja
But from now i m not investing my time in reading and replying to ur post...
😉
19K+ points. ±5 threads started so far. 3 of them still unanswered to this day. :hehe:

"3 of them still unanswered to this day" Really?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

ChrisM@Work (10/20/2011)
Ninja's_RGR'us (10/20/2011)
adlakha.22 (10/20/2011)
@ninja
But from now i m not investing my time in reading and replying to ur post...
😉
19K+ points. ±5 threads started so far. 3 of them still unanswered to this day. :hehe:
"3 of them still unanswered to this day" Really?

2 of them are bugs. So unanswered as unresolved untill PSS comes back to me.

I got a couple more but I'll live!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Blimey! I've only ever found one bug in SQL Server, that was 5 years ago.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

ChrisM@Work (10/20/2011)
Blimey! I've only ever found one bug in SQL Server, that was 5 years ago.

It's not like it's a achievement I was striving for :hehe:.

Mishal Ten Centuries Points: 1103 More actions · Answer 15

Hi Chris,

I was trying to use Suggestion given in your last post,

Could u plz guide me further...how to move on that Qry..