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 ;)

Need a runtime processor license for SQL Server 2005

We are an ISV in the U.S. One of our customers, a United Nations agency located in Europe, urgently needs a runtime processor license for SQL Server 2005 Standard Edition. These licenses can be purchased only from companies that are members of the ISV Royalty Licensing Program, which we have not yet joined. Back in 2004, Microsoft accepted our company into its "Product Integration Program" (PIP), which was later discontinued and rolled into the ISV Royalty Licensing Program. The PIP allowed us to purchase SQL Server runtime licenses and sell them to our customers along with our off-the-shelf application.

The key differences between a runtime license and a "full-use" license are that a runtime license:
- costs less than half as much (US$1,758 vs. $4,876)
- can legally be used for only one application. Our customer is interested in SQL Server only to use it for our application, since they are not a SQL Server shop.

Are there any ISVs out there who are members of the ISV Royalty Licensing Program who would be interested in selling my customer a runtime processor license for SQL Server 2005? The cost from Microsoft is $1,758, but of course you would add your normal markup to that. We ourselves will not mark it up for this United Nations agency since we just want to give them what they need.

If interested, or if you can point me where to look for someone who might be, then please contact me at awad at auditleverage.com. Many thanks.
Mike

Hi,

I an not sure if that would comply with the licensing agreements, because AFAIK the customers can and is allowed only to use the database for their application, not for any other.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de