Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple UNION ALL queries cause SSMS to hang/freeze up/ slow down? Expand / Collapse
Author
Message
Posted Friday, February 11, 2011 3:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:53 AM
Points: 284, Visits: 1,129
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?
Post #1062862
Posted Friday, February 11, 2011 3:31 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #1062871
Posted Friday, February 11, 2011 3:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:53 AM
Points: 284, Visits: 1,129
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.
Post #1062874
Posted Friday, February 11, 2011 3:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:52 PM
Points: 97, Visits: 317
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?
Post #1062880
Posted Friday, February 11, 2011 4:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:32 PM
Points: 1,787, Visits: 5,721
Do you have any add-ins installed in SSMS - like maybe a code completion tool?

MM


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1062893
    Posted Friday, February 11, 2011 5:00 PM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Friday, September 5, 2014 9:53 AM
    Points: 284, Visits: 1,129
    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.
    Post #1062898
    Posted Friday, February 11, 2011 5:02 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: 2 days ago @ 4:32 PM
    Points: 1,787, Visits: 5,721
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1062899
    Posted Friday, February 18, 2011 5:36 PM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Friday, September 5, 2014 2:00 PM
    Points: 2,163, Visits: 2,191
    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.)
    Post #1066718
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse