August 25, 2013 at 11:48 pm
Dear All,
I have a query that takes 22 seconds.
If I create below index then query finishes in 5 seconds, but I'm told that this type of index can't be used as there are too many columns in INCLUDED and it will consume a lot of space.
Can anybody please guide me on this.
CREATE NONCLUSTERED INDEX [test_idx]
ON [dbo].[work_order] ([buyer_code])
INCLUDE ([job_seeker_id],[start_date],[end_date],[worker_id],[sequence],[work_order_id],[status],[creator_id],[bu_id],[actual_end_date],[site_id],[service_type],[new_requisition_owner_id],[work_order_ref],[primary_cost_center_id],[buyer_ref],[supplier_ref],[equipment_flag])
GO
Table work_order has 131 columns and few million records.
Regards.
August 26, 2013 at 10:42 am
I'm not sure that there is a definitive answer to your question. When I'm tuning and considering adding an index or indexes I make sure I consider the application/database as a whole and not this single query. Some of the things I like to look at are:
What are the queries that commonly access this table?
Does the optimizer recommend the new index for the query (in the query plan and also using the missing index DMO's)?
If the index you are proposing will meet and cover the majority of the queries accessing this table, then I'd definitely consider adding it if there isn't an existing index I can just modify to cover this query and others.
One thing you need to remember about included indexes is that the included columns are only stored at the leaf level not in the root or intermediate pages which only have the key columns. This is why it is better to have an index with a narrow key and have included columns vs adding all the columns to the index.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 26, 2013 at 10:55 am
I'd agree with Jack's answer. you can't answer this. It's a bit of an art, depending on your query patterns.
August 26, 2013 at 11:15 pm
The index was recommended by optimizer.
The table itself is having 14 indexes. Earlier it was more then 25, but we removed some indexes that were no more in use.
The database is more than 1.5 TB and it is not possible to assess, how many queries are going to use this index.
August 27, 2013 at 6:13 am
T.Ashish (8/26/2013)
The index was recommended by optimizer.The table itself is having 14 indexes. Earlier it was more then 25, but we removed some indexes that were no more in use.
The database is more than 1.5 TB and it is not possible to assess, how many queries are going to use this index.
I wouldn't say it is impossible to assess how many or what queries might use an index, especially not because of database size. You can have a very large database that has very few query patterns and a small database that has many different queries.
First you can look in the missing index DMO's and see the user impact of the recommended indexes which gives you some idea of the potential usefulness of the index. You can mine the plan cache to determine what columns are often used in predicates and joins. Finally you could run a server-side trace for a day and find the most common queries.
I wouldn't say that 14 indexes is too many on a table with 131 columns, particularly because I don't know the access patterns.
There are some tools out there that can help with index tuning. Things like Kimberly Tripp's sp_helpindex[/url] or Brent Ozar Unlimited's sp_BlitzIndex[/url]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2013 at 6:26 am
T.Ashish (8/25/2013)
Dear All,I have a query that takes 22 seconds.
If I create below index then query finishes in 5 seconds, but I'm told that this type of index can't be used as there are too many columns in INCLUDED and it will consume a lot of space.
Can anybody please guide me on this.
CREATE NONCLUSTERED INDEX [test_idx]
ON [dbo].[work_order] ([buyer_code])
INCLUDE ([job_seeker_id],[start_date],[end_date],[worker_id],[sequence],[work_order_id],[status],[creator_id],[bu_id],[actual_end_date],[site_id],[service_type],[new_requisition_owner_id],[work_order_ref],[primary_cost_center_id],[buyer_ref],[supplier_ref],[equipment_flag])
GO
Table work_order has 131 columns and few million records.
Regards.
Have you examined the execution plan to determine exactly why the query completes faster with the new index in place, and if further improvement is possible by tweaking the index or the query - or both? If you can post the actual execution plan as a .sqlplan attachment, folks can help you interpret what's happening and establish if it's possible to improve the performance of the query without the prohibitive cost of this wide index. It might be possible for instance to offset the cost of key lookups with better (more restrictive) predicates.
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
August 27, 2013 at 6:44 am
I have attached both execution plans and statistics.
With Index :
(3 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'work_order'. Scan count 7, logical reads 633, physical reads 0, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'worker'. Scan count 4, logical reads 50, physical reads 2, read-ahead reads 17, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'job_posting_visib'. Scan count 13398, logical reads 53697, physical reads 811, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'job_posting'. Scan count 0, logical reads 78037, physical reads 32, read-ahead reads 18073, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'company_name'. Scan count 1, logical reads 283, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'job_seeker'. Scan count 0, logical reads 66154, physical reads 86, read-ahead reads 18093, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bu'. Scan count 1, logical reads 4297, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'cost_center'. Scan count 1, logical reads 9133, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site'. Scan count 1, logical reads 2781, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'cost_center_locale'. Scan count 1, logical reads 211, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bu_locale'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'labor_type'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site_locale'. Scan count 1, logical reads 268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Without Index
(3 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'work_order'. Scan count 7, logical reads 98562, physical reads 61, read-ahead reads 33929, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'worker'. Scan count 4, logical reads 48, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'job_posting_visib'. Scan count 13398, logical reads 53697, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'job_posting'. Scan count 0, logical reads 55122, physical reads 101, read-ahead reads 1485, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'company_name'. Scan count 1, logical reads 283, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'job_seeker'. Scan count 0, logical reads 54462, physical reads 28, read-ahead reads 113, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bu'. Scan count 1, logical reads 4297, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'cost_center'. Scan count 1, logical reads 9133, physical reads 0, read-ahead reads 15, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site'. Scan count 1, logical reads 2781, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'cost_center_locale'. Scan count 1, logical reads 211, physical reads 0, read-ahead reads 50, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bu_locale'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'labor_type'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'site_locale'. Scan count 1, logical reads 268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
August 27, 2013 at 7:26 am
A few comments.
1. This personalisation:
on wo . bu_id = bu . bu_id
with unnecessary spaces either side of object delimiters, makes the code irritating to read and will put folks off from helping you.
2. This function: get_job_seeker_ref. Can you post the code? If it's a UDF, it's likely that it's skewing the plan costs. Try running the query with this branch of the CASE substituted with a constant to get a rough idea of the cost.
3. If you have a reference in the WHERE clause to a left-joined table in the FROMlist, the join is converted to an INNER JOIN. An example of this is table dbo.worker.
The work_order table is referenced twice. I'd guess that a couple of hours spent on a chopped-down version of the query could repay a half decent developer in spades. By chopped-down, I mean something like this:
SELECT wo.*
FROM dbo . work_order wo ( nolock )
INNER join dbo . worker w ( nolock )
on w . worker_id = wo . worker_id
WHERE wo . sequence = (
select max ( we . sequence )
from dbo . work_order we ( nolock )
where we . job_seeker_id = wo . job_seeker_id
and ( ( we . status not in ( @5 , @6 ) or ( we . status = @7 and we . revision_time > = getdate ( ) - 7 ) ) )
and ( w . status is null or w . status ! = @9 or ( w . end_date > = we . start_date and w . end_date < = we . actual_end_date ) ) )
and ( w . status is null or w . status = @10 or ( w . status = @11 and w . end_date > = getdate ( ) - 7 ) ) and ( wo . buyer_code = @13 )
and ( ( jp . coordinator_id = ??????
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
August 27, 2013 at 2:05 pm
Until recently we didn't use the 'included' on our indexes. We started adding fields to existing indexes, we didn't run into a limit but ran into another problem. One of the fields we added, say loc_code, was added to 4 different indexes in the include. We have a process that does an update on the loc_code field. This process started running up to 4 times longer, 45 min. before, up to 4 hours. We figured out that it was updating all of the indexes where this field was included. So we've removed this field from the includes. So be careful what fields you add to includes.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 27, 2013 at 11:12 pm
Hi Chris,
Thanks again for your help.
I could not see any performance difference with/without function. I have pasted the function for you. Meanwhile I'm looking into third point you suggested.
CREATE FUNCTION dbo.get_job_seeker_ref (
@userType int, @suppressSupplierFlag BIT, @roleFlag BIT, @status INT, @isMspCoordinator BIT, @jobSeekerRef VARCHAR(14))
RETURNS TABLE AS
RETURN
SELECT CASE WHEN (@userType = 1 and @isMspCoordinator = 0 and @suppressSupplierFlag = 1 and @roleFlag = 0 and @status IN( 0, 1, 7 , 8, 5, 20, 30, 31, 41, 47, 10, 54, 56, 62, 72, 11, 73, 74, 75, 76, 82, 83, -1, 71))
THEN
('XXXX' + substring(@jobSeekerRef,len(@jobSeekerRef)- 9, 14))
ELSE @jobSeekerRef
END AS job_seeker_ref
August 28, 2013 at 12:21 am
With(NOLOCK)
I hope u know everything about it,
and for the query optimization can you chop down your query into Temp tables as this could lead better execution plan as well,
and i hope your tempDB is optimize before using temptables.
Neeraj Prasad Sharma
Sql Server Tutorials
August 28, 2013 at 1:10 am
T.Ashish (8/27/2013)
Hi Chris,Thanks again for your help.
I could not see any performance difference with/without function. I have pasted the function for you. Meanwhile I'm looking into third point you suggested.
CREATE FUNCTION dbo.get_job_seeker_ref (
@userType int, @suppressSupplierFlag BIT, @roleFlag BIT, @status INT, @isMspCoordinator BIT, @jobSeekerRef VARCHAR(14))
RETURNS TABLE AS
RETURN
SELECT CASE WHEN (@userType = 1 and @isMspCoordinator = 0 and @suppressSupplierFlag = 1 and @roleFlag = 0 and @status IN( 0, 1, 7 , 8, 5, 20, 30, 31, 41, 47, 10, 54, 56, 62, 72, 11, 73, 74, 75, 76, 82, 83, -1, 71))
THEN
('XXXX' + substring(@jobSeekerRef,len(@jobSeekerRef)- 9, 14))
ELSE @jobSeekerRef
END AS job_seeker_ref
This is a simple inline table-valued function and as such will be fast.
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
September 3, 2013 at 1:03 am
T.Ashish (8/25/2013)
Dear All,I have a query that takes 22 seconds.
If I create below index then query finishes in 5 seconds, but I'm told that this type of index can't be used as there are too many columns in INCLUDED and it will consume a lot of space.
Can anybody please guide me on this.
CREATE NONCLUSTERED INDEX [test_idx]
ON [dbo].[work_order] ([buyer_code])
INCLUDE ([job_seeker_id],[start_date],[end_date],[worker_id],[sequence],[work_order_id],[status],[creator_id],[bu_id],[actual_end_date],[site_id],[service_type],[new_requisition_owner_id],[work_order_ref],[primary_cost_center_id],[buyer_ref],[supplier_ref],[equipment_flag])
GO
Table work_order has 131 columns and few million records.
Regards.
Have you got the solution, please always complete thread if you get the solution...
Neeraj Prasad Sharma
Sql Server Tutorials
September 3, 2013 at 3:22 am
Mr. Neeraj,
As you can see it is not closed, we will appreciate if you can come up with some solution. We assure you that this thread will be closed as soon as we receive some good solution from your end.
September 3, 2013 at 6:21 am
Would it be possible for you to post your code?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply