Need a solution ... urgently

These two queries when executed seperately give results in under 10 secs

A union between these two does not give results even after 20 minutes ...

Any idea why this is happening


SELECT
T001W.NAME1,
t25a5.bezek SKU,
QTY = SUM(CASE MSEG.BWART
WHEN '101' THEN MSEG.ERFMG
WHEN '102' THEN (-1)*MSEG.ERFMG END),
YPLNT.VKGRP
FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a5
WHERE
MSEG.MANDT = MKPF.MANDT
AND MKPF.MANDT = MARA.MANDT
and mkpf.mandt =yplnt.mandt
and mkpf.mandt = t25a5.mandt
AND MKPF.MBLNR = MSEG.MBLNR
AND MARA.MATNR = MSEG.MATNR
AND YPLNT.PPLNT= MSEG.WERKS
AND MSEG.WERKS = T001W.WERKS
and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3)
AND MARA.PRDHA <> ''
AND MKPF.VGART IN ('WR','WF')
AND MKPF.MJAHR=YEAR(@.BUDAT1)
AND MSEG.AUFNR IS NOT NULL
AND MSEG.BWART IN ('101','102')
GROUP BY t25a5.bezek,T001W.NAME1,YPLNT.VKGRP

SELECT
T001W.NAME1,
t25a2.bezek SKU,
QTY = SUM(CASE MSEG.BWART
WHEN '101' THEN MSEG.ERFMG
WHEN '102' THEN (-1)*MSEG.ERFMG END),
YPLNT.VKGRP

FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a2
WHERE
MSEG.MANDT = MKPF.MANDT
AND MKPF.MANDT = MARA.MANDT
and mkpf.mandt =yplnt.mandt
and mkpf.mandt = t25a2.mandt
AND MKPF.MBLNR = MSEG.MBLNR
AND MARA.MATNR = MSEG.MATNR
AND YPLNT.PPLNT=MSEG.WERKS
AND MSEG.WERKS = T001W.WERKS
and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3)
AND MARA.PRDHA <> ''
AND MKPF.MJAHR=YEAR(@.BUDAT1)
AND MKPF.VGART IN ('WR','WF')
AND MSEG.AUFNR IS NOT NULL
AND MSEG.BWART IN ('101','102')
GROUP BY t25a2.bezek,T001W.NAME1,YPLNT.VKGRPYou can use a table variable. Insert the first result set in. Then insert the second result set in. Union tries to eliminate duplications and that takes time. Union All is faster than Union but you may get duplicates.|||Yeah ..thanks for confirming my suspicions ...I was also thinking along the same lines coz there are no duplicates ...

But I am unable to understand why this is happening ... both the result set contain only about 50 rows each ...

20 minutes is too much ...|||SELECT
T001W.NAME1,
t25a5.bezek SKU,
QTY = SUM(CASE MSEG.BWART
WHEN '101' THEN MSEG.ERFMG
WHEN '102' THEN (-1)*MSEG.ERFMG END),
YPLNT.VKGRP
FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a5
WHERE
MSEG.MANDT = MKPF.MANDT
AND MKPF.MANDT = MARA.MANDT
and mkpf.mandt =yplnt.mandt
and mkpf.mandt = t25a5.mandt
AND MKPF.MBLNR = MSEG.MBLNR
AND MARA.MATNR = MSEG.MATNR
AND YPLNT.PPLNT= MSEG.WERKS
AND MSEG.WERKS = T001W.WERKS
AND MARA.PRDHA <> ''
AND MKPF.VGART IN ('WR','WF')
AND MKPF.MJAHR=YEAR(@.BUDAT1)
AND MSEG.AUFNR IS NOT NULL
AND MSEG.BWART IN ('101','102')

and (
t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3)
OR
t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3)
)

GROUP BY t25a5.bezek,T001W.NAME1,YPLNT.VKGRP|||Sorry ... wouldnt work hanafih ...|||Are you running this in Query Analyzer or a stored procedure? Either way, run DBCC DBCC FREEPROCCACHE and try it again. If a stored procedure, just do sp_recompile. It shouldn't take that long to run. Also, have you looked at the execution plan to see if there is anything weird going on there?|||Enigma dude! What kind of schema is this? I've heard of "star" and "snowflake", but I tried diagraming these relationships and all I got was "yarnball". I'm not surprised that SQL Server chokes on UNIONing those WHERE clauses.

Do you happen to have an ERD of these tables? Please post it.|||Hey blind dude ... dont blame me for this schema ...

Don't ya know ... The best-run businesses run ? ;)|||Sorry ... wouldnt work hanafih ...

Then there's something fundamentally wrong with your query or database schema.

The two queries differ by only

and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3)

and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3)

consider:

use pubs

select au_id
from authors
where state = 'CA'
union
select au_id
from authors
where state = 'UT'

select au_id
from authors
where state = 'UT' OR STATE='CA'

The UNION is not one whit different from the OR. If ORing the condition is returning a different result set from UNIONing the seperate queries, your data does not adhere to relational set theory.|||The two queries differ by only

and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3)

and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3)
how do you propose i include the two tables in the select and from clauses|||Enigma, I figured it was probably a schema you inherited. Just the same, your code would be clearer and (possibly) more efficient if you linked your tables with JOINs rather than in the WHERE clause. SQL Server will attempt to convert your WHERE clause syntax to a standard JOIN syntax prior to execution, but given the complexity of your links and the addition of UNION, the complexity may be too much for it to handle.|||Yeah Blind dude ...
Let me say it again ... I think you did not get the joke

The best-run businesses run A Crappy database schema (www.sap.com)

hmm .. let me try what you have said ... but i do not think it would help much :D|||ummm...as Frank Barone would say...

Holy Crap...

Did you run profiler to check it out?

Did you check sp_who and sp_lock?

Holy Crap...|||Yeah I did do those things Brett ... those were the first things that came to my mind when the query did not finish even after 10 minutes ... no deadlocks ... no live locks ...|||I'm assuming that it's not a coincidence...and the problem is repeatable...

What happens if you put each query in it own window and execute them at the same time...

Or schedule two jobs to launch at the exact same time...

I'd be interested if you get any contention from that...

What's the plan say?

How much data is the tables?|||In a second Query Analyzer window, run this:

select spid, blocked, cpu, physical_io, waittype, lastwaittype, waitresource
from master..sysprocesses
where blocked > 0
or spid = (spid that is running query)

On SQL 2000 SP2, I ran into a problem of never-ending queries. The query would run with parallelism, then each parallel part would wait for every other part to finish first. In SP3, they sort of fixed the problem. In SP3, the query gives partial results, along with an error.|||the estimated execution plan showed no problems ... lost the patience to wait it out for the actual execution plan ...

mcrowley ... will tryout that tomorrow ...

brett ... on the verge of passing out now ...er ... i mean going to sleep ...|||May you dream of nothing that is related to sql...unless she's cute ;)