Posted by Thomas R. Hummel on 07/11/06 13:41

KoliPoki wrote:
> Erland, thank you for your reply.
>
> That's correct the UNION doesn't work with my query.
> Unfortunately it's very hard to give you all the code as there are 3
> functions and another 12 tables, 2 with hierarchical
> (parent/child/lineage) data (Business units, Job titles).
>
> There are no syntax errors, the problem is that my query never
> returns... like it's stuck in a loop. If I remove the union like:
> -----------------------------------------------
> CREATE VIEW dbo.v_qt_in_type
> AS
> SELECT qi_id, 'Environmental' AS type FROM qt_ins WHERE
> qi_environmental = 1
> -------------------------------------------------
> it works fine and fast, but is obviously incorrect as it only returns
> records of 1 type.
>
>
> So the problem is....
>
> Can any one think of another way to write the below query without the
> UNION
>
> ---------------Query to write in a different way---------------
>
> SELECT qi_id, 'Injury' AS type FROM qt_ins WHERE qi_injury = 1
> UNION all
> SELECT qi_id, 'Environmental' AS type FROM qt_ins WHERE
> qi_environmental = 1
> UNION all
> SELECT qi_id, 'Equipment damage' AS type FROM qt_ins WHERE
> qi_equipment_damage = 1
> UNION all
> SELECT qi_id, 'Vehicle' AS type FROM qt_ins WHERE qi_vehicle = 1
>
> ------------------------END QUERY-------------------------------------
>
> Thanks for your help, and apologies for not being able to provider the
> full context of the issue.

Alexander has given what I think is a pretty good solution to this. To
the original problem though, did you get a query plan for the select
statement that ran for so long? What was the query plan doing?

One thing to keep in mind is that if you are using UDFs, they can often
dramatically impact a querie's performance. It's impossible to know
without your data and the full table, UDF, and view SQL code, but maybe
when you use the UNION with the UDF it has to actually select the
entire dataset, applying the UDF to each row and then performing the
UNION operations before it is able to filter out rows based on your
WHERE clause criteria. I'm afraid that I don't know how smart SQL
Server is when you do a union like this within a view and then select
from the view based on a column that is calculated within the view.
Even if the UDF has nothing to do with it, it may be that when you
select WHERE qi_environmental = 1 then SQL Server can use an index, but
when you have a column hard-coded the way that you do in the unions it
can no longer use that index when you do WHERE type = 'Environmental'.

Anyway, just some stuff to look into for you.

-Tom.

[Back to original message]