| Author |
Topic |
|
Cattrah
Starting Member
13 Posts |
 Posted - 2008-05-30 : 14:06:33
|
| I've written a recursive CTE to update a table's heirarchy data with a lineage that the CTE builds. This works and executes over 80,000 records on my local test environment in about 45 seconds. However when I try to execute it from a "production" environment, it hangs and never completes the update (well at least it's taking longer than 5 minutes and I'm too impatient to wait). The CTE itself still runs fine and returns data if I do a Select *, but not when I run the update. Here's my CTE, any ideas? Is there anything in database settings that would disallow this query update? or field requirements? WITH MyLineage (ID, Lineage, ParentID) AS (SELECT ID, CAST('.'+ convert(nvarchar(max), ID) +'.' AS nVarchar(max)) as Lin, ParentID FROM Equipment WHERE OrgID=2 AND ParentID IS NULLUNION ALLSELECT E.ID, CAST(L.Lineage+ convert(nvarchar(max),E.ID)+'.' AS nVarchar(max)) AS Lin, E.ParentID FROM Equipment E INNER JOIN MyLineage L ON L.ID=E.ParentID WHERE L.ID=E.ParentID)UPDATE E SET E.Lineage=L.Lineage FROM Equipment E INNER JOIN MyLineage L ON E.ID=L.IDIf I try to do the update like this: UPDATE Equipment SET Lineage=(SELECT Lineage FROM MyLineage WHERE MyLineage.ID=Equipment.ID)it will hang in my local test environment as well. It seems like the other sql 2005 server instance is converting the join update into a nested select update, or something. I don't know what's going on, help? |
|
|
Cattrah
Starting Member
13 Posts |
Posted - 2008-05-30 : 14:22:51
|
| Also I've just noticed that the version it works in is Standard, it does NOT work in Enterprise. Would that make a difference? It seems like if it would work in only one it would be Enterprise not Standard. So confused. |
 |
|
|
ibeckett
Starting Member
12 Posts |
Posted - 2008-05-30 : 15:20:17
|
| ./ |
|
|
|
Cattrah
Starting Member
13 Posts |
Posted - 2008-05-30 : 16:46:24
|
I've just done this and the two execution plans are really different in some ways.Here's the plan in the "production" environment where I believe the query will take about 30 minutes to complete. Here's the local execution plan of the same query that takes 45 seconds to run They are mostly the same except the local one has fewer steps and spends most of its time in one Index Spool, while the other seems to spend time all over the place on multiple index spools and the concatenation and a bunch of other steps.Is is possible that something like patches or exact version of sql server 2005 would cause the query to execute so differently? |
|
|
|
ibeckett
Starting Member
12 Posts |
Posted - 2008-05-30 : 22:03:47
|
| ./ |
|
|
|
Cattrah
Starting Member
13 Posts |
Posted - 2008-06-02 : 09:45:34
|
| hmm, thanks for the suggestions. For the first one, each of the following steps takes approximately 30% of the cost: 1st clustered index scan, Filter, 2nd clustered index scan, and concatenation. There are a few other steps that take like 1% cost.For the second one, the first index spool takes 93% of the cost and there are a few other steps that take 3%I find that confusing. As far as the environments, the databases are identical. Hardware is negligibly different. I've backed up one and restored over the other, so all the data is the same (80000 records to process in each). By nature of a recursive CTE, I can't remove the UNION ALL and the only way to update all the records in one bang is with the join as I've done, otherwise I have to use a nested select statement which slows the process way down in both environments (the WHERE is necessary in the nested select otherwise multiple records are returned and that's illegal). I really don't see how it can be possible to optomize the query further.I did have almost 90 something % index fragmentation in the table and I cleaned that up but now doing just the select from the cte seems to take forever. I think in my effort to fix it, I've jacked it up worse :-P This sure is bothersome. Thanks for the thought processes. Any more ideas are welcome. |
|
|
|
Cattrah
Starting Member
13 Posts |
Posted - 2008-06-02 : 12:31:58
|
| Well, after much labor and toil I've found a solution, and it's not nearly so elegant as I would have liked to be but it executes in 6 seconds so I can't complain. What I ended up doing was dumping the results of the CTE into a temporary table with a primary key, then using a merge join to my table I'm updating to update the data from the temp table rather than from the CTE. I tried all kinds of joins and nested statements and everything I could think of to alter the execution plan in a favorable way but nothing would give me the results. So here's my SQL for the record.CREATE TABLE #TempLin (ID int primary key, Lineage varchar(max));WITH MyLineage (ID, Lineage) AS (SELECT ID, CAST('.'+ CAST(ID as varchar) + '.' as varchar) AS Lineage FROM Equipment WHERE OrgID=2 AND ParentID IS NULLUNION ALLSELECT E.ID, CAST(L.Lineage + CAST(E.ID as varchar) + '.' as varchar) AS Lineage FROM Equipment E INNER JOIN MyLineage L ON L.ID=E.ParentID)INSERT INTO #TempLin (ID, Lineage) SELECT ID, Lineage FROM MyLineageUPDATE E SET E.Lineage=W.Lineage FROM Equipment E JOIN #TempLin W ON E.ID=W.ID OPTION (MERGE JOIN)DROP Table #TempLin |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-02 : 12:34:40
|
Which service pack are you on?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
ibeckett
Starting Member
12 Posts |
Posted - 2008-06-02 : 13:39:20
|
| ./ |
|
|
|
Cattrah
Starting Member
13 Posts |
Posted - 2008-06-02 : 16:33:33
|
| Creating my own temp table didn't reduce the number of spools it did, infact it really didn't change much of anything for the execution plan of the CTE. I think the problem was that I couldn't index a CTE but I can on a temp table by assigning it a primary key, then the join is exceptionally faster because it can reference the indexes. Over 80,000 records, it makes a difference. |
|
|
|
|
CTE Update Hangs