February 11, 2011 at 3:10 pm
Has anyone noticed this?
I have a relatively straight forward set of UNION ALL queries.
I have a CTE that is being reused in every query and then about six more CTEs after that.
I take the first CTE join it to the 2nd CTE UNION ALL, then join the 1st CTE to the 3rd CTE, UNION ALL...
This repeats:
CTE1 to 4
1 to 5
1 to 6
UNION ALL joining them all up.
Anyway, SSMS seems to really slow down when I scroll through the TSQL, copy/paste, etc...
Has anyone else run into this sort of issue before?
February 11, 2011 at 3:31 pm
It might be easier to follow if we could see the actual code, but....
Remember that a CTE does not produce a temporary table. It is just a symbolic representation of a query (like a view). Every time you reference the CTE, it runs the CTE query all over again, or possibly does a lazy spool. If I need to reference a result from a cte multiple times, I usually instantiate it by writing it out to a temporary table (and possibly indexing it) before proceeding to the next step.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 11, 2011 at 3:37 pm
In this case CTE1 is
just a month/quarter table.
Quarter | Month
1 |1
1|2
1|3
2|4
2|5
...
4|10
4|11
4|12
then the other CTEs are just used later to join against that.
Though I suppose I could just create a quarter month table to use.
February 11, 2011 at 3:48 pm
Don't say Join and union all at the same line, it confuses as to wether you are joining or UNIONing these CTEs.
But just to clarify, are you saying that SCROLLING through these slows down your SSMS? Or are the Queries running too slow?
February 11, 2011 at 4:45 pm
Do you have any add-ins installed in SSMS - like maybe a code completion tool?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 11, 2011 at 5:00 pm
I am joining two CTEs and then UNION ALL and repeating.
So
CTE1 JOIN CTE2
UNION ALL
CTE1 JOIN CTE3
UNION ALL
CTE1 JOIN CTE4
etc..
The slowness is when I physically scroll through the TSQL text inside SSMS.
I think I have RedGate SQL Search and prompt, I'll try turning those off and checking again.
February 11, 2011 at 5:02 pm
I don't use the redgate ones but have had issues with other add-ins and biggish union queries... although they were quickly resolved by the authors
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 18, 2011 at 5:36 pm
SQL Prompt 5.0.1 has problems with CTEs that cause SSMS to be really slow. They say that it will be fixed in 5.1, or you can contact support to get a non-fully tested version that fixes it. (They have yet to send me the newer version.)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply