union all slowing down stored procedure

Question

union all slowing down stored procedure

DaveK2014

Right there with Babe

Points: 788
More actions
March 3, 2014 at 6:57 pm

#396359

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.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

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.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

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.

CKX SSCrazy Points: 2519 More actions · Answer 3

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.

DaveK2014 Right there with Babe Points: 788 More actions · Answer 4

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. 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

CKX SSCrazy Points: 2519 More actions · Answer 6

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DaveK2014 Right there with Babe Points: 788 More actions · Answer 8

how do i make sure all the statistics are up up to date?

DaveK2014 Right there with Babe Points: 788 More actions · Answer 9

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

DaveK2014 (3/4/2014)
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.

iTVF = inline table value function.

When you have a scalar function like that it can be a real performance killer. Not saying this is the only thing here.

You might want to take a look at this spackle article. http://www.sqlservercentral.com/articles/T-SQL/91724/[/url]

Updating your statistics will also help. http://technet.microsoft.com/en-us/library/ms187348.aspx

Make sure you read that article and especially the piece about "When to update statistics".

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DaveK2014 Right there with Babe Points: 788 More actions · Answer 11

thanks Sean I will take a look at those.

I have removed the UDF call as a test to see and still slow just as an FYI.

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

DaveK2014 (3/4/2014)
thanks Sean I will take a look at those.
I have removed the UDF call as a test to see and still slow just as an FYI.

Not surprising. You have a few things in there that will help performance and that is only 1 of them. 😉

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DaveK2014 Right there with Babe Points: 788 More actions · Answer 13

I found the parts that are messing it up, just now need to find out how to fix them. will post once I find it.

BobMcClellan SSC-Addicted Points: 400 More actions · Answer 14

Is something like this an option?

Create table #t1 ( Col1 int )

Insert into #t1 ( Col1 )

Select SomeColumn from FirstTable

Insert into #t1 ( Col1 )

Select SomeColumn from NextTable

select * from #t1

drop table #t1