Help on Performance issue on Recursive CTE

Question

Post reply

Help on Performance issue on Recursive CTE

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

Thanks for the plan, but that seems to be an estimated plan, not an actual plan?

Can you produce a plan from an actual execution please?

And also include SET STATISTICS_TIME ON; before your call to the stored proc and copy the contents of the messages tab in the results pane so we can see things such as plan compilation time.

Thanks

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

haiao2000 Hall of Fame Points: 3734 More actions · Answer 2

mister.magoo (10/11/2012)

Thanks for the plan, but that seems to be an estimated plan, not an actual plan?

Can you produce a plan from an actual execution please?

And also include SET STATISTICS_TIME ON; before your call to the stored proc and copy the contents of the messages tab in the results pane so we can see things such as plan compilation time.

Thanks

MM,

Oop! I didnt realize they are different, that is how little I am when it comes to SQL 🙂 attached is the actual one.

hold on let me SET STATISTICS_TIME ON first.

Thank You!

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

And the statistics???

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

haiao2000 Hall of Fame Points: 3734 More actions · Answer 4

haiao2000

Hall of Fame

Points: 3734

October 11, 2012 at 10:12 am

#1548249

mister.magoo (10/11/2012)

And the statistics???

MM,

I updated last post with attachments now.

Thanks

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

That actual execution plan was for a run that only pulled top level items, no children at all, so we can't tell much from that.

Can you run it again for something that will reach at least level 3?

and included the SET STATISTICS TIME ON please 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

haiao2000 Hall of Fame Points: 3734 More actions · Answer 6

mister.magoo (10/11/2012)

And the statistics???

MM,

Sorry here it is. This one has 4 levels. With 34,689 records return in 53 seconds. The table has 10,831,321 records

Updated: uploaded screenshot of segment that takes 64% of the time.



DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 27 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 36 ms.

 SQL Server Execution Times:

   CPU time = 16 ms,  elapsed time = 2 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 1 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 49 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 16 ms,  elapsed time = 616 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 296 ms,  elapsed time = 5863 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 1575 ms,  elapsed time = 28654 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 764 ms,  elapsed time = 17281 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 47 ms,  elapsed time = 398 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 2855 ms,  elapsed time = 53217 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 7

Ok, that looks interesting.

You can see that each insert is taking a very long time.

Can you try creating a permanent table somewhere other than tempdb for the results - instead of #results ?

I am just wondering if you have issues with IO on your tempdb as it should not take so long to insert into #results.

Using a permanent table in another database will help to test that theory.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

haiao2000 Hall of Fame Points: 3734 More actions · Answer 8

I see, I suspect that my laptop runs slower than a normal one, so I will test on another machine later. I am going to create a permanentable senarios now.

Thanks!

mister.magoo (10/11/2012)

Ok, that looks interesting.

You can see that each insert is taking a very long time.

Can you try creating a permanent table somewhere other than tempdb for the results - instead of #results ?

I am just wondering if you have issues with IO on your tempdb as it should not take so long to insert into #results.

Using a permanent table in another database will help to test that theory.

haiao2000 Hall of Fame Points: 3734 More actions · Answer 9

MM

Attached is new plan running on permanent table sitting on a separated database (in the same laptop).

I will run off and run it on another box and see if anything different

mister.magoo (10/11/2012)

Ok, that looks interesting.

You can see that each insert is taking a very long time.

Can you try creating a permanent table somewhere other than tempdb for the results - instead of #results ?

I am just wondering if you have issues with IO on your tempdb as it should not take so long to insert into #results.

Using a permanent table in another database will help to test that theory.



SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 SQL Server Execution Times:

   CPU time = 31 ms,  elapsed time = 23 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 31 ms, elapsed time = 203 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 81 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 78 ms,  elapsed time = 640 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 436 ms,  elapsed time = 5629 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 1653 ms,  elapsed time = 28684 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 1077 ms,  elapsed time = 17893 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 314 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 3448 ms,  elapsed time = 53733 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 10

Hmmm, that still looks terrible, doesn't it...

Let's hope your laptop is just very slow to write to disk!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

haiao2000 Hall of Fame Points: 3734 More actions · Answer 11

MM,

IT runs even slower in another box. See attachment

Looks like it used the wrong index key. there is a clustered index that combined( ParentID, ChildId, and 2 other fields)

While there is non clustered index for ParentID only. why it does it use clustered key?

SQL Server parse and compile time:



   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 SQL Server Execution Times:

   CPU time = 1061 ms,  elapsed time = 1062 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 156 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 44 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 57 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 442 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 172 ms,  elapsed time = 7458 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 622 ms,  elapsed time = 39130 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 407 ms,  elapsed time = 19958 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 15 ms,  elapsed time = 500 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 1388 ms,  elapsed time = 68065 ms.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 12

so can you try a simple select from your table for a parent that is at level 2 or 3?

use variables to exclude any network latency e.g.

select @id=id,@parent=ParentID,@child=ChildID

FROM yourTable

where parentID=blah....

This will let us see what is happening for the select, without the insert.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

haiao2000 Hall of Fame Points: 3734 More actions · Answer 13

Here it is.

SQL Server parse and compile time:



   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 SQL Server Execution Times:

   CPU time = 31 ms,  elapsed time = 35 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 60 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

mister.magoo (10/11/2012)

so can you try a simple select from your table for a parent that is at level 2 or 3?

use variables to exclude any network latency e.g.

select @id=id,@parent=ParentID,@child=ChildID

FROM yourTable

where parentID=blah....

This will let us see what is happening for the select, without the insert.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 14

And the same details for that query inserting into tempdb

select id,ParentID,ChildID

INTO #test

FROM yourTable

where parentID=blah....

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

haiao2000 Hall of Fame Points: 3734 More actions · Answer 15

MM,

Here it is

SQL Server parse and compile time:



   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 1 ms, elapsed time = 1 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 16 ms,  elapsed time = 30 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 7 ms.

(25 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:

   CPU time = 15 ms,  elapsed time = 8 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

mister.magoo (10/11/2012)

And the same details for that query inserting into tempdb

select id,ParentID,ChildID

INTO #test

FROM yourTable

where parentID=blah....