SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple UNION ALL queries cause SSMS to hang/freeze up/ slow down?


Multiple UNION ALL queries cause SSMS to hang/freeze up/ slow down?

Author
Message
Maxer
Maxer
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3463 Visits: 1667
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?
The Dixie Flatline
The Dixie Flatline
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21945 Visits: 6911
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
Maxer
Maxer
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3463 Visits: 1667
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.
Khades
Khades
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 390
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?
mister.magoo
mister.magoo
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20164 Visits: 7928
Do you have any add-ins installed in SSMS - like maybe a code completion tool?

MM


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




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Maxer
    Maxer
    Hall of Fame
    Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

    Group: General Forum Members
    Points: 3463 Visits: 1667
    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.
    mister.magoo
    mister.magoo
    SSC-Insane
    SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

    Group: General Forum Members
    Points: 20164 Visits: 7928
    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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • UMG Developer
    UMG Developer
    SSCertifiable
    SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

    Group: General Forum Members
    Points: 5946 Visits: 2204
    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.)
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum







































































































































































    SQLServerCentral


    Search