Query taking too long
-
This query seems to be taking longer than it should. It's currently taking close to 30 seconds and should be much faster in my opinon. There is a bookmark lookup that costs 96% of the query. Is there something that I'm missing here?
I've included the query output.
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @dlrcode char(10)
DECLARE@vendor_cross varchar(20)
SELECT @dlrcode ='10201'
SELECT @vendor_cross = '0973390'
SELECT d.item_number, h.order_date, h.order_number, h.invoice_number, d.order_amount
FROM factsql.dbo.sopinh h INNER JOIN factsql.dbo.sopind d ON
d.company_number = h.company_number AND
d.order_number = h.order_number AND
d.invoice_number = h.invoice_number
WHERE h.customer_number = @dlrcode
AND d.item_number = @vendor_cross
AND h.order_priority = 'R'
---- DO WE WANT TO SHOW ALL ORDER DATA (RETURNS AND PURCHASES)
---- IF THE NEXT LINE IS COMMENTED OUT YOU WILL SEE CREDITS AS WELL
AND d.order_amount > 1
ORDER BY h.order_date, h.order_number, h.invoice_number;
(3 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY: ([h].[order_date] ASC, [h].[order_number] ASC, [h].[invoice_number] ASC))
|--Filter(WHERE: ([h].[customer_number]=[@dlrcode] AND [h].[order_priority]='R'))
|--Bookmark Lookup(BOOKMARK: ([Bmk1000]), OBJECT: ([factsql].[dbo].[sopinh] AS [h]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES: ([d].[invoice_number], [d].[order_number], [d].[company_number]) WITH PREFETCH)
|--Filter(WHERE: ([d].[order_amount]>1.000))
| |--Bookmark Lookup(BOOKMARK: ([Bmk1001]), OBJECT: ([factsql].[dbo].[sopind] AS [d]) WITH PREFETCH)
| |--Index Seek(OBJECT: ([factsql].[dbo].[sopind].[IX_sopind_1] AS [d]), SEEK: ([d].[item_number]=[@vendor_cross]) ORDERED FORWARD)
|--Index Seek(OBJECT: ([factsql].[dbo].[sopinh].[IX_sopinh] AS [h]), SEEK: ([h].[company_number]=[d].[company_number] AND [h].[invoice_number]=[d].[invoice_number] AND [h].[order_number]=[d].[order_number]) ORDERED FORWARD)
(8 row(s) affected)
-
Does the column that is being used in the bookmark lookup have an index
-
Well, there's an index seek going on for the sopind.item_number field and the bookmark lookup is on that branch. So, I assume that's what you're asking. These are our sales order history tables with H being the header table and D being the line item detail table. These are large tables, but I'm joining on the keys as well as looking at index fields. It just boggles me that it would take this long.
-
How many rows does this query return?
A.J.
DBA with an attitude -
I'd guess about 8
.How many rows in each table?
Are the stats up to date and was the sp recompiled after that.
Did you check for possible parameter sniffing?
-
What's the fragmentation of the indexes on these tables look like? Run DBCC SHOWCONTIG if the scan density is < 80% I would either run DBCC DBREINDEX or DBCC INDEXDEFRAG on each index that are on these tables.
A.J.
DBA with an attitude -
I'll do my best to answer all questions here.
The query should return 14 rows.
sopinh contains 1,062,065 records
sopind contains 4,952,917 records
I did a DBCC SHOWCONTIG on both tables and the scan density was in the 90's. I did do a DBREINDEX just for fun and that did make a little difference, but not much. My query is still right around 30 seconds and that's unacceptable.
How can I confirm if my stats are up to date and also what do you mean by parameter sniffing?
-
Don't know how to confirm the stats... The reindex should've taken care of it (please correct me if I'm wrong).
-
Just for grins, how fast does it run without the ORDER BY? If it's really fast, use the 14 row result set as a derived table and select from THAT with the ORDER BY.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's the modified query, but it's still taking close to 1 minute to run.
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DECLARE @dlrcode char(10)
DECLARE @vendor_cross varchar(20)
SET @dlrcode = '10201'
SET @vendor_cross = '0973390'
SELECT PS.item_number, PS.order_date, PS.order_number, PS.invoice_number, PS.order_amount
FROM (
SELECT d.item_number, h.order_date, h.order_number, h.invoice_number, d.order_amount
FROM factsql.dbo.sopinh h INNER JOIN factsql.dbo.sopind d ON
d.company_number = h.company_number AND
d.order_number = h.order_number AND
d.invoice_number = h.invoice_number
WHERE h.customer_number = @dlrcode
AND d.item_number = @vendor_cross
AND h.order_priority = 'R'
AND d.order_amount > 1
) AS PS
ORDER BY PS.order_date, PS.order_number, PS.invoice_number;
Here's the plan text as well:
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sort(ORDER BY: ([h].[order_date] ASC, [h].[order_number] ASC, [h].[invoice_number] ASC))
|--Filter(WHERE: ([h].[customer_number]=[@dlrcode] AND [h].[order_priority]='R'))
|--Bookmark Lookup(BOOKMARK: ([Bmk1000]), OBJECT: ([factsql].[dbo].[sopinh] AS [h]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES: ([d].[invoice_number], [d].[order_number], [d].[company_number]) WITH PREFETCH)
|--Filter(WHERE: ([d].[order_amount]>1.000))
| |--Bookmark Lookup(BOOKMARK: ([Bmk1001]), OBJECT: ([factsql].[dbo].[sopind] AS [d]) WITH PREFETCH)
| |--Index Seek(OBJECT: ([factsql].[dbo].[sopind].[IX_sopind_1] AS [d]), SEEK: ([d].[item_number]=[@vendor_cross]) ORDERED FORWARD)
|--Index Seek(OBJECT: ([factsql].[dbo].[sopinh].[IX_sopinh] AS [h]), SEEK: ([h].[company_number]=[d].[company_number] AND [h].[invoice_number]=[d].[invoice_number] AND [h].[order_number]=[d].[order_number]) ORDERED FORWARD)
(8 row(s) affected)
-
It's almost 1am where I am (Australia) so I'm not really gonna look deep into the code, but when I had a similar trouble with bookmark lookups I did one of two things
1. Create a covering index that has as its lead fields the index fields that are currently being used in the index seek (or just add to that index!) - this will avoid the need to do the bookmark lookup
OR
2. Create an indexed view and select from it instead
OR
(ok, three ideas
)3. Try to adjust your indices - if your clustered index isn't really being used at the moment, change the index being used in the query to be your clustered index - again, this will avoid the bookmark lookup as the index & the table are effectively one and the same.
Order of things to try would be 3 (if appropriate), then 1 then 2
-
I know this might be a big no-no for the purists, but an easy thing to do is run the query a couple of times while recording a trace. Then run it through the SQL Index Tuning Wizard which might just propose different index (or change to the current one).
This might put you on the right track wrt creating the appropriate index(es) for that table.
-
since it are all inner joins, what about
SELECT d.item_number, h.order_date, h.order_number, h.invoice_number, d.order_amount
FROM factsql.dbo.sopinh h
INNER JOIN factsql.dbo.sopind d
ON d.company_number = h.company_number
AND d.order_number = h.order_number
AND d.invoice_number = h.invoice_number
AND h.customer_number = @dlrcode
AND d.item_number = @vendor_cross
AND h.order_priority = 'R'
---- DO WE WANT TO SHOW ALL ORDER DATA (RETURNS AND PURCHASES)
---- IF THE NEXT LINE IS COMMENTED OUT YOU WILL SEE CREDITS AS WELL
AND d.order_amount > 1
ORDER BY h.order_date, h.order_number, h.invoice_number;
Do all variable type match? d.item_number = varchar?
h.customer_number = char(10)?
-
Yes, the variable types match. I played around with a few different indexes and it looks like I found a good combination. The biggest problems were the bookmark lookups happening on the sopind and sopinh tables. Those are both large tables and it turns out that their indexes weren't created very well. I used a covering index on the details table and that really helped out. I've gone back over the tables and recreated the indexes and the query is now down to under 1 second. Thanks for all the help.
-
Yup, covering index is usually the easiest way to avoid bookmark lookups - just make sure your inserts & updates are not affected adversely.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply