June 11, 2014 at 9:47 pm
I Create an Index Like
Create Nonclustered Index In_product_pdid
ON Product(pd_id)
INCLUDE pd_desp; <- Include product description column with Index
GO
My Question is in 60% query i need pd_desp column in my Sql query and by using this i remove additional Key Lookup operator and nested loop join Operator from Query execution plan.
But in 40% query i need not pd_desp column in my Sql query. so on that time this index may degrade some query performance or not.
is there any other way kindly suggest.
June 12, 2014 at 12:59 am
itsgaurav (6/11/2014)
I Create an Index LikeCreate Nonclustered Index In_product_pdid
ON Product(pd_id)
INCLUDE pd_desp; <- Include product description column with Index
GO
My Question is in 60% query i need pd_desp column in my Sql query and by using this i remove additional Key Lookup operator and nested loop join Operator from Query execution plan.
But in 40% query i need not pd_desp column in my Sql query. so on that time this index may degrade some query performance or not.
is there any other way kindly suggest.
One way would be moving the description column into a table of its own, has some additional benefits in terms of flexibility, description reuse, multiple languages and versions. I would normally advice against large free-text type columns to be included in an index but then again, it depends.
😎
June 12, 2014 at 3:10 am
What data type is pd_desp?
To be honest, unless the description tends to be thousands of characters long or a LOB data type, it's probably fine as an include. You definitely don't want a second index without the include, that's a waste of space as it's entirely redundant, plus an extra index which needs to be kept up to date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2014 at 1:50 am
At first thanks for reply
pd_desp datatype is varchar(500).
Then if i not take this column in my sql .
And I include this in my index as mentioned above question.
Then it will reduce performance of my sql or not.
Kindly suggest. Please
June 14, 2014 at 11:24 am
itsgaurav (6/14/2014)
At first thanks for replypd_desp datatype is varchar(500).
Then if i not take this column in my sql .
And I include this in my index as mentioned above question.
Then it will reduce performance of my sql or not.
Kindly suggest. Please
I doubt if it will significantly impact performance if the only value used from the index is pd_id.
June 14, 2014 at 11:58 am
itsgaurav (6/14/2014)
And I include this in my index as mentioned above question.Then it will reduce performance of my sql or not.
...
To be honest, unless the description tends to be thousands of characters long or a LOB data type, it's probably fine as an include. You definitely don't want a second index without the include, that's a waste of space as it's entirely redundant, plus an extra index which needs to be kept up to date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply