August 26, 2009 at 2:34 pm
****UPDATE: I have removed all string concatenation so this is a straight CTE Recursive Function
****I have also added a new Execution plan.
I have added the sample output and dummy data that can be used. When you run the stored proc you can use
EXEC GetCategoryList @LotNumber=30447
Import "ItTransactionThatDontWork LotNumber 29238.xls" and execute "EXEC GetCategoryList @LotNumber=29238" to see it spin.
Is there anyway to improve this query. I ran query analyzer on it and applied the indexes but it didn't matter.
When there is a small recordset < 100 it runs under a couple seconds. When that recordset gets to be about 300 it takes over 10 minutes. I have attached the execution plan and the table definitions for reference.
Any help would be greatly appreciated.
Thanks in advance.
WITH DirectReports (UniqueID, CurrentProcessStepID, NextProcessStepID, [Name], Level)
AS
(
-- Anchor member definition
SELECT tp.CurrentProcessStepID as UniqueID, tp.CurrentProcessStepID, tp.NextProcessStepID, Name, 0 AS Level
FROM itTransactionProcess AS tp LEFT OUTER JOIN
dbo.itProcessSteps ON tp.CurrentProcessStepID = dbo.itProcessSteps.ProcessStepID
WHERE CurrentProcessStepID = 1 and LotNumber = @LotNumber
UNION ALL
-- Recursive member definition
SELECT tp.CurrentProcessStepID + (d.uniqueid * DatePart(ss, getDate())) as uniqueid, tp.CurrentProcessStepID, tp.NextProcessStepID, dbo.itProcessSteps.Name, d.UniqueID
FROM itTransactionProcess AS tp INNER JOIN
dbo.itProcessSteps ON tp.CurrentProcessStepID = dbo.itProcessSteps.ProcessStepID
INNER JOIN DirectReports AS d
ON tp.CurrentProcessStepID = d.NextProcessStepID and tp.LotNumber = @LotNumber
)
-- Statement that executes the CTE
SELECT DISTINCT UniqueID, CurrentProcessStepID, Name, CASE WHEN Level = 0 then null else level end as level
FROM DirectReports
order by level, currentProcessStepID ;
August 26, 2009 at 2:46 pm
this does not help ... please read http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 26, 2009 at 3:06 pm
Sorry about that I have added the supporting documentation. If there is anything else you need from me please let me know. My clients is freaking out.
Thanks,
August 26, 2009 at 3:25 pm
Michael,
Here's a link on how to post your problem so that we can quickly help you. We need to see table definitions (I cannot open your .rar file), sample data (in easily consumable form - that you prepare) and an example of the result set you need to see based on that sample data.
http://www.sqlservercentral.com/articles/Best+Practices/61537/"> http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 26, 2009 at 3:37 pm
Recursive Function
That's probably your problem with performance, right there.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 26, 2009 at 3:39 pm
I have added the sample output and dummy data that can be used. When you run the stored proc you can use
EXEC GetCategoryList @LotNumber=30447
I have also changed the rar file to zip for the table structures.
After importing ItTransactionThatDontWork LotNumber 29238.xls
User can use to get the no response.
EXEC GetCategoryList @LotNumber=29238
August 26, 2009 at 4:36 pm
I appologize in advance for posting an incomplete post, but I have to get out of the office for the day and I didn't want to leave you hanging.
Your problem is that you are referencing the CTE inside of the CTE code. Look at your execution plan, or better yet, SET IOSTATISTICS ON when you run the code and you'll see that there's large amount of reads associated with a work table. Not good.
Here's a start at re-working your CTE, again - sorry it's not complete. It should get you working in the right direction or get someone else to step in and help (even w/o a CTE).
;WITH cteTest(CurrentProcessingStepID, NextProcessingStepID)
AS (
SELECT tp.CurrentProcessStepID,
tp.NextProcessStepID
FROM itTransactionProcess AS tp
WHERE LotNumber = @LotNumber
)
SELECT DISTINCT
t1.*
FROM cteTest t1
LEFT JOIN cteTest t2
ON t1.NextProcessingStepID = t2.CurrentProcessingStepID
ORDER BY t1.CurrentProcessingStepID
August 26, 2009 at 10:43 pm
Thanks for your help. Everything works but one thing. I need the Level to show the results from attached results.xls file. I have your query and mine in a zip file Old Vs New Queries Results.zip.
The Attached jpg is a result of how the data need to be displayed to the user. the Tree view control requires a Unique id and the level so it can indent correctly.
Thanks for all your help. I really appreciate it.
August 27, 2009 at 10:44 am
After thinking about this more last night is it even possible to get the level and UniqueID to function as they do in the correct results above.
I'm having issues seeing how this is possible.
Thanks for all of you that are trying to help me with this.
Michael
August 27, 2009 at 10:51 am
August 27, 2009 at 10:53 am
It's the primary key from my tree view in the jpg above.
This has to be unique but can be any value.
August 27, 2009 at 10:57 am
And what about Level?
So in your correct results, why would you want to see CurrentProcessingStepID 8 our of numeric order?
August 27, 2009 at 11:09 am
The level indicates that how far down the process it is.
So for example
Recieving Level null
Sort Level 1
Tear Down Level 1
Shipping Level 2
Recycling Level 1
The numbers don't necessarily have to be incrementing but they do have to increase when their indent level increases.
I believe in my original query I was taking the UniqueID of the parent row and using that as the level since the Unique field was alwasy increasing.
August 27, 2009 at 11:21 am
What about that Processing Step ID 8 question?
I guess what I'm getting at is, based off of the result sets in your spreadsheets, why is Step 8 our of numeric order from the others?
Both result sets have the same data, just out of order so if you can give a written description of how you would apply an ordering algorithm, we should be able to incorporate it into our code and get you on your way.
August 27, 2009 at 11:26 am
The current process step really has no function in the sort except in the following situation.
Recieving
Sort
Teardown
Recycling
Test & Diagnostics
TearDown
Recycling
To keep subgroups in the same order if that process step is reached from other multiple processes.
This is used in a production area with multiple steps and things can be sent in any item direction at any time depending on the quality of what was received.
The main thing I need sorted is the Level ID which will show the indenting of the tree view.
Thanks for all your help.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply