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 12»»

union all slowing down stored procedure Expand / Collapse
Author
Message
Posted Monday, March 3, 2014 6:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 16, 2014 1:59 PM
Points: 34, Visits: 48
I have been trying to figure this out and have had no luck,

We have a stored procedure that is just a select fields from location and a couple where clauses. Then we do a union all with another part same sort of thing.(can post tomorrow while back at the office)

This is the problem

If we run each section independent they take 1 second and 6 seconds.

If we run them togther without the union all they both return results in 7 seconds.

If we run with the union all over 7 min.

We have no order by filters. Anyone have a thought? Like I said I can post the actual queries tomorrow.
Post #1547141
Posted Tuesday, March 4, 2014 12:30 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:43 PM
Points: 709, Visits: 4,514
If you can avoid using UNION, I probably would. (Maybe SQL Server acts different than it's stepbrother Access, where union query performance was appalling, but I don't know.)

Are you querying two different tables and then unioning the results together, like this...

SELECT col1, col2, col3
FROM table1
WHERE col1>10
UNION ALL
SELECT col2, col3, col4
FROM table2
WHERE col3>10

If so, you could be stuck. If you're querying the same table, you may be able to convert the filters into one OR clause.
Post #1547177
Posted Tuesday, March 4, 2014 12:47 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 632, Visits: 1,152
Please post the actual execution plan as a .sqlplan file attachment.

Furthermore, If your queries have something like this :
select Col1 
from Table1
UNION
select Col2
from Table2

Replace the 'UNION' with 'UNION ALL',

select Col1 
from Table1
UNION ALL
select Col2
from Table2

Why is UNION ALL faster? Because UNION must do a sort to remove the duplicates. If you do not need to remove duplicates then UNION ALL is the better option, however UNION does have a purpose and should be used when appropriate.
Post #1547182
Posted Tuesday, March 4, 2014 3:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 69, Visits: 457
From the title of the post, it would appear the OP is already using UNION ALL which I wouldn't expect to cause a major slow down unlike it's sibling UNION.
I guess best thing would be to post the execution plan...no point guessing.
Post #1547228
Posted Tuesday, March 4, 2014 7:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 16, 2014 1:59 PM
Points: 34, Visits: 48
Here is the sqlplan and a PDF of the actual store procedure. like I said running each piece separate is fast and running them together without the union all is fast.

If thats the wrong execution plan let me know first time every getting it and no clue what it means. :)


  Post Attachments 
testing.sqlplan (9 views, 368.07 KB)
select.pdf (10 views, 133.27 KB)
Post #1547349
Posted Tuesday, March 4, 2014 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
DaveK2014 (3/4/2014)
Here is the sqlplan and a PDF of the actual store procedure. like I said running each piece separate is fast and running them together without the union all is fast.

If thats the wrong execution plan let me know first time every getting it and no clue what it means. :)


Can you post the query inside a code box instead of as a pdf? The formatting is completely unusable from the pdf.

You can find the IFCode Shortcuts over on the left when posting.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1547381
Posted Tuesday, March 4, 2014 8:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 69, Visits: 457
Just taking a quick glance at the plan, it appears that you have, in a number of instances, significant differences between the estimated and actual rows.
I would start by ensuring that statistics are up to date on all the relevant tables and see what effect that might have on plan generation.
Post #1547382
Posted Tuesday, March 4, 2014 8:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
Also you have a scalar function in there.

left(dbo.udfBuyers(tbClient.ID), 30)as Buyers

This can be a very serious performance killer. Not sure what that function does but turning that function into an iTVF would help quite a bit.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1547385
Posted Tuesday, March 4, 2014 8:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 16, 2014 1:59 PM
Points: 34, Visits: 48
how do i make sure all the statistics are up up to date?
Post #1547391
Posted Tuesday, March 4, 2014 9:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 16, 2014 1:59 PM
Points: 34, Visits: 48
the udf is designed to get us the buyers names from the system and then we can only take the first 30 or the system goes boom. Some of these people have names longer then my arm so we have to scale it back. what is the iTVF never heard of it.

Why would they run fine independent vs with the union though? Sorry just learning sql so trying to wrap my head around it.
Post #1547393
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse