Query Optimization using Indexes

Question

Post reply

Query Optimization using Indexes

Viewing 6 posts - 106 through 110 (of 110 total)

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

Mishal Ten Centuries Points: 1103 More actions · Answer 1

ya sure...u can see

CREATE NONCLUSTERED INDEX [IX_Testing] ON [dbo].[orderlines]

(

[prodline] ASC,

[ordernumber] ASC,

[enterdt] ASC,

[oetype] ASC,

[cono] ASC,

[transtype] ASC,

[operid] ASC,

[slsrepin] ASC,

[whse] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

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

adlakha.22 (10/24/2011)
here is execution plan for covered index

How long did this one take to run? There are a number of interesting changes in the plan as a result.

^{“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

@chris-2

"How long did this one take to run? There are a number of interesting changes in the plan as a result. "

are u talking abt index creation?

or any thing else..may be i did't get u

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

When we tune we only care about the statements. Unless the index creation will always be part of the process (like for a temp table).

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

Ninja's_RGR'us (10/24/2011)
When we tune we only care about the statements. Unless the index creation will always be part of the process (like for a temp table).

Both of the temp tables have clustered indexes created on them in the script, but the time taken will be negligible compared to the query.

^{“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 6

hey guys

I would like to inform u,from now to till monday i'll not able to reply ur post...

but we will back on this performance tuning after monday..(24-10-11)

it will be grt if u guys continue to help me further....

Thanks for ur suggestions:-)