Dynamically union selects in a Stored Procedure

[lawlerpat]

This is difficult to put into words. I have a very long Stored Procedure (SP) for a collection of reports. The SP has 6 unioned selects in it and also has 6 optional parameters. By passing yes or no into each of those 6 parameters I can have the SP return data from only the unions that I want. This works great except when I pass in a NO parameter for one of the selects that select does not return any records(by design) but it is still executed and this is causing a big performance hit.
So, how can I acheive the same results w/o the all the selects being executed. The sample below probably explains the issue a little better.
**********************
In the below example I would really like to have the dog select not even be executed but it is.
***********************
Declare @cat_flag varchar(10)
Declare @dog_flag varchar(10)
set @cat_flag = 'yes'
set @dog_flag = 'no'
Begin
select 'dog'
where @dog_flag ='yes'

Union All

select 'cat'
where @cat_flag = 'yes'
end
**************************
Thanks
Patrick

 

[JayKusch]

OK ... Here is a crack at it.

In your stored procedure you would have to create and load a variable for each select that may be called. Somewhat like ...

DECLARE @SQL_Command VarChar(7000)
DECLARE @Union Bit

DECLARE @DogPart VarChar(500)
DECLARE @CatPart VarChar(500)
DECLARE @BirdPart VarChar(500)

SET @UnionInserted= 0 -- To know when to add a Union to Code

SET @DogPart = 'select ' + '''' + 'dog' + 'where ' + @dog_flag + ' = ' + '''' + 'yes' + ''''

SET @CatPart = 'select ' + '''' + 'cat' + 'where ' + @cat_flag + ' = ' + '''' + 'yes' + ''''

SET @CatPart = 'select ' + '''' + 'bird' + 'where ' + @bird_flag + ' = ' + '''' + 'yes' + ''''

... Finish the rest of the animals in that fashion.

Now its a matter of using a bunch of IF statements, a Nested-IF statement or a CASE statement to build your query.

something like ...

SET @SQL_Command = 'BEGIN '

IF @Dog_Flag = 'yes'
THEN
BEGIN
@SQL_Command = @DogPart
SET @Union = 1
END

IF @Union = 1
THEN @SQL_Command = @SQL_Command = ' UNION ALL '

IF @Cat_Flag = 'yes'
THEN
BEGIN
@SQL_Command = @CatPart
SET @Union = 1
END

IF @Union = 1
THEN @SQL_Command = @SQL_Command = ' UNION ALL '

IF @Bird_Flag = 'yes'
THEN
BEGIN
@SQL_Command = @BirdPart
SET @Union = 1
END

-- FILL IN REST OF ANIMALS LIKE ABOVE. Last animal WILL ------ NOT have a IF @UNION test after it!!!

SET @SQL_Command = ' END '

PRINT @SQL_Command -- Let you see how it looks after build

EXEC (SQL_Command)

The tricky part is getting the UNION in place especially if the first couple of flags, ie dog and cat, are set to NO.
Hope this gets you a bit closer.





Thanks

J. Kusch

 

[lawlerpat]

Thanks Jay.
That is an option that I had considered however you have layed it out a bit more cleanly than I have thus far. I don't really like this option just b/c of how big the query is and how messey it gets buildging strings that way.

I have considered another option using a temp table but don't know how temp tables work with regards to performance.
Consider this
*******************************
Begin
Declare @cat_flag varchar(10)
Declare @dog_flag varchar(10)
set @cat_flag = 'yes'
set @dog_flag = 'yes'

Create table #temp (field1 varchar(10))

Begin
If @dog_flag = 'yes'
Insert into #temp
select 'dog'
If @cat_flag = 'yes'
insert into #temp
select 'cat'
end
select * from #temp
drop table #temp
end
************************
Faster? or is there an inherant problem with temporary tables?

Thanks again.

 

[donutman]

Patrick,
Maybe I don't understand the question? Wouldn't it be easy to create a temporary table and do "insert into" for only the appropriate selects? The union operation is implicitly creating a temporary table too. So I don't think there should be a performance difference.
-Karl

 

[lawlerpat]

Thanks, I think you did answer the question.
If the Union does implicictly create a temp table then by me explicitly creating one I should be able to only execute the "insert into" statements that I want based on the parameters that I have passed in, which should be faster thant the original method? do you agree?

Thanks again
Patrick

 

[JayKusch]

Either way ... I see you having to build that query dynamically based on yes no criteria. Maybe a way to use temp tables would be to create a table w/ a VarChar(8000) column size and write the syntax for every permutation that could happen. SO the first record would be w/out Dog but w/ CAT, Bird, Lizard .... Next one would be w/ Dog, Bird, Lizard BUT NOT Cat. Either way you are going to have ALOTT of cutting and pasting and creating that large query any way you slice it. Just remember if you have just 4 items, dog..cat..lizard..bird, you are going to have 16 possible combinations.

SO ... IMHO I like the IF senerio better that a temp table way in that if I have to add or delete an item from the list, I simply add or remove it from the script instead of multiple records in a table

Thanks

J. Kusch

 

[donutman]

We must have been thinking the same thing at the same time! If the temp table doesn't solve your problem, then post your actual code. Maybe, it's something else.
Actually, Patrick, I don't understand why you're getting a performance hit when some of the parameters are 'No'.
-Karl

 

[lawlerpat]

Karl,
re: performance hit with the 'NO' parameters.
Two reasons why I think I am getting them.
1) the execution plan shows a significant cost for those parts of the unioned select where parameter= NO
2) In real time reporting/execution it seems that the SP is taking the same amount of time where all parameters are yes or no.

I would rather not use a temp table and have to maintain more lines of code (already 75 fields in the recordset) but if it is faster I will.

Any more thoughts?
Thanks

 

[donutman]

Just one. First see if there's a way to tweak the actual code for the "@dog_flag ='no'" so that there is no performance hit. I wonder if that same performance hit occurs if the SQL statements are run directly rather than as a dynamic string? For sure you don't get the advantages of a pre-compiled SP, but maybe the hit goes beyond that.
-Karl