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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Multiple UNION ALL queries cause SSMS to...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Maxer

Posted Friday, February 11, 2011 3:10 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, September 21, 2012 9:22 AM
Points: 250, Visits: 815

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

The Dixie Flatline

Posted Friday, February 11, 2011 3:31 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 3,790, Visits: 5,548

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

Maxer

Posted Friday, February 11, 2011 3:37 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, September 21, 2012 9:22 AM
Points: 250, Visits: 815

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

Khades

Posted Friday, February 11, 2011 3:48 PM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, January 19, 2013 3:28 PM
Points: 87, Visits: 279

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

mister.magoo

Posted Friday, February 11, 2011 4:45 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 1,308, Visits: 3,899

Do you have any add-ins installed in SSMS - like maybe a code completion tool?

MM

Post #1062893

Maxer

Posted Friday, February 11, 2011 5:00 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, September 21, 2012 9:22 AM
Points: 250, Visits: 815

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

mister.magoo

Posted Friday, February 11, 2011 5:02 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 1,308, Visits: 3,899

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

Post #1062899

UMG Developer

Posted Friday, February 18, 2011 5:36 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148

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 »

Permissions