October 20, 2011 at 3:33 am
it takes 58 sec to execute,
nd i am directly executing them into SSMS
October 20, 2011 at 3:36 am
Do you have results from the three test queries?
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
October 20, 2011 at 3:49 am
No there is no results...only messages indicating number of rows effected..
if we execute select * from #orderlines
then rows effected will display
October 20, 2011 at 3:52 am
adlakha.22 (10/20/2011)
No there is no results...only messages indicating number of rows effected..if we execute select * from #orderlines
then rows effected will display
Yes, that's exactly as expected. How long did each query take? Can you post the plans please.
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
October 20, 2011 at 4:16 am
adlakha.22 (10/20/2011)
@Chrisquery which u hv posted, took 58 sec to execte
and select qry executed in 3 sec(Select * from #Orderlines).
Not quite right - let's try again. Here are three test queries. I need to know how long each one takes to run, then I need the execution plan for all three run together.
-- Test query 1
DECLARE @ordernumber VARCHAR(20) -- WHATEVER DATATYPE IT IS
SELECT @ordernumber = ordernumber
FROM orderlines ol
INNER JOIN #prodline pl ON pl.prodline = ol.prodline
AND oetype = 'R'
and cono = '10'
and enterdt >= '1/1/2008 12:00:00 AM'
and enterdt < '10/25/2008'
and transtype not in ('qu','RA','BR','CR','ST')
WHERE whse IN ('100','101','102','103','104','105','106','107','108','120','121','122','151')
-- Test query 2
DECLARE @ordernumber VARCHAR(20) -- WHATEVER DATATYPE IT IS
SELECT @ordernumber = ordernumber
FROM orderlines ol
INNER JOIN #prodline pl ON pl.prodline = ol.prodline
AND oetype = 'R'
and cono = '10'
and enterdt >= '1/1/2008 12:00:00 AM'
and enterdt < '10/25/2008'
and transtype not in ('qu','RA','BR','CR','ST')
WHERE whse IN ('100','101','102','103','104','105','106','107','108','120','121','122','151')
GROUP BY ordernumber, ol.prodline, enterdt, operid, slsrepin
-- Test query 3
DECLARE @ordernumber VARCHAR(20) -- WHATEVER DATATYPE IT IS
SELECT @ordernumber = ordernumber
FROM orderlines ol
INNER JOIN #prodline pl ON pl.prodline = ol.prodline
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
October 20, 2011 at 5:21 am
So each one of the three takes around 40 to 50 seconds?
Last test: add this index, then run each query again, noting the time each query takes to run:
CREATE NONCLUSTERED INDEX [IX_TestIndex]
ON [dbo].[orderlines] ([prodline],[cono],[oetype],[transtype],[whse],[enterdt])
INCLUDE ([ordernumber])
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
October 20, 2011 at 5:42 am
1st qry takes-1 sec
2nd - 40-50 sec
3rd-3 sec
here is execution plan for combined
October 20, 2011 at 6:08 am
Well it's clear that a huge covering index would improve the performance of this query but the cost of maintaining it would be considerable. Here's a compromise.
CREATE NONCLUSTERED INDEX [IX_Testing] ON [dbo].[orderlines]
([prodline], [ordernumber],[enterdt],[oetype],[cono],[transtype],[operid],[slsrepin],[whse])
What I recommend you do is remove all the other testing indexes from the table, then create this one.
Then run the three test queries. What we're looking for is an improvement in the performance of the second one. If there is a significant performance boost, then run the main orderlines query and take a timing and EP.
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
October 20, 2011 at 6:11 am
0.02$ that the filter returns too much data to consider a bookmark lookup ;-).
I'll say it again... all or nothing.
I applaud the effort tho 🙂
October 20, 2011 at 6:18 am
Quite possibly Remi, hence the test - but it could be worth it. I'd expect some improvement.
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
October 20, 2011 at 6:25 am
ChrisM@Work (10/20/2011)
Quite possibly Remi, hence the test - but it could be worth it. I'd expect some improvement.
It can't.
It's either 99% faster or it's still a clustered index scan. And with 700K rows returned that table must have over 25M - 140M rows (just a guess) to maybe make the bookmark lookup worth it.
If it helps it's to get better estimate... almost 4:1 underestimating this... not good!
October 20, 2011 at 7:11 am
what should i do? 🙁
October 20, 2011 at 7:15 am
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.
Viewing 15 posts - 61 through 75 (of 110 total)
You must be logged in to reply to this topic. Login to reply