Bizarre SQL statement

I have a SQL UNION (I know I know...they're bad, but trust me, I have to us
e
it) in a stored proc.
Effectively, it liiks like this:
SELECT f1, f2, f3 FROM T1
UNION
SELECT f1, f2, f3 FROM T2
note:
T1 and T2 are not tables, but parameterized query expressions that contain 3
and 4 tables, respectively.
If I just run "SELECT f1, f2, f3 FROM T1" I get 0 results in 2 or 3 seconds
If I just run "SELECT f1, f2, f3 FROM T2" I get 31 results in less than 1
second
If I run:
SELECT f1, f2, f3 FROM T1
UNION
SELECT f1, f2, f3 FROM T2
I wait 28 minutes and nothing happens...
I know that unions deprecate performance, but this is REALLY strange.
Also, this only happens for certian parameters. But its the same SQL that's
running...
What the heck is going on'David Jessee wrote:
> I have a SQL UNION (I know I know...they're bad, but trust me, I have to
use
> it) in a stored proc.
> Effectively, it liiks like this:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> note:
> T1 and T2 are not tables, but parameterized query expressions that contain
3
> and 4 tables, respectively.
> If I just run "SELECT f1, f2, f3 FROM T1" I get 0 results in 2 or 3 second
s
> If I just run "SELECT f1, f2, f3 FROM T2" I get 31 results in less than 1
> second
> If I run:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> I wait 28 minutes and nothing happens...
> I know that unions deprecate performance, but this is REALLY strange.
> Also, this only happens for certian parameters. But its the same SQL that
's
> running...
> What the heck is going on'
try UNION ALL just to see if that helps. Maybe the optimizer in
whatever DB you're using (you didn't say) is trying to do stuff so the
sort is 'faster'|||examnotes <DavidJessee@.discussions.microsoft.com>
wrote in news:F951CFD8-F105-46FB-8D2E-0752C6399B78@.microsoft.com:

> I have a SQL UNION (I know I know...they're bad, but trust me, I have
> to use it) in a stored proc.
> Effectively, it liiks like this:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> note:
> T1 and T2 are not tables, but parameterized query expressions that
> contain 3 and 4 tables, respectively.
Does "T1" and "T2" share some of these tables? If so, are you sure that you
are not experiencing a deadlock? This is not my strongest field, but as far
as I've understood, the two statements that are merged with a union may
actually run in parallell. If some of the values passed to the
parameterized causes a table-lock on a shared table, you could experience
the behavior you describe, or am I completely wrong here?
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||The individual queries are utilizing some of the same tables. However, the
tables are references "WITH (NOLOCK)" so deadlocking shouldn't be an issue,
should it?
"Ole Kristian Bang?s" wrote:

> examnotes <DavidJessee@.discussions.microsoft.com>
> wrote in news:F951CFD8-F105-46FB-8D2E-0752C6399B78@.microsoft.com:
>
> Does "T1" and "T2" share some of these tables? If so, are you sure that yo
u
> are not experiencing a deadlock? This is not my strongest field, but as fa
r
> as I've understood, the two statements that are merged with a union may
> actually run in parallell. If some of the values passed to the
> parameterized causes a table-lock on a shared table, you could experience
> the behavior you describe, or am I completely wrong here?
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||Well, I'm using a SQL Server database (this snippet of code is inside of a
stored procedure).I'll try the UNION ALL to see if it works. Truth to tell,
I'm hoping it doesn't because each of thequeries can potentially return
duplicate records. Rach of them have DISTINCT clauses in them already. For
this query, placing DISTINCT in each of the queries and them UNIONING them
gives better performance than just relying on the UNOIN to remove duplicates
.
(not my data model...but I have to work with it *ack*)
"wolfing1@.gmail.com" wrote:

> David Jessee wrote:
> try UNION ALL just to see if that helps. Maybe the optimizer in
> whatever DB you're using (you didn't say) is trying to do stuff so the
> sort is 'faster'
>|||examnotes <DavidJessee@.discussions.microsoft.com>
wrote in news:D0476075-74A5-4890-A48B-3048292DE2BD@.microsoft.com:

> The individual queries are utilizing some of the same tables.
> However, the tables are references "WITH (NOLOCK)" so deadlocking
> shouldn't be an issue, should it?
I don't remember when, but I seem to remember that Kimberly Tripp had a
TechNet session or so regarding this, where she specified that even with
nolock table locks can occur. But, as I've said, I do not remember in what
situation.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Are you familiar with query and join hints? Take a look at the execution
plan generated by each individual SELECT in the UNION and try coercing the
optimizer to match the execution plan. Remember also that a UNION requires
a sort of the result set from each SELECT in order to eliminate duplicates.
You could try this:
SELECT DISNTINCT f1, f2, f3 FROM
( SELECT f1, f2, f3 FROM T1
UNION ALL
SELECT f1, f2, f3 FROM T2 ) T3
By the way, I hope you're aware of the dangers of using WITH(NOLOCK).
Except in rare circumstances, WITH(NOLOCK) will make your queries return
incorrect results at lightning speed.
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:D0476075-74A5-4890-A48B-3048292DE2BD@.microsoft.com...
> The individual queries are utilizing some of the same tables. However,
> the
> tables are references "WITH (NOLOCK)" so deadlocking shouldn't be an
> issue,
> should it?
> "Ole Kristian Bangs" wrote:
>