Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: 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

    Code:
     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.VKGRP
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    You 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.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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 ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Nov 2003
    Posts
    94
    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

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Sorry ... wouldnt work hanafih ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Hey blind dude ... dont blame me for this schema ....

    Don't ya know ... The best-run businesses run ???
    Last edited by Enigma; 05-27-04 at 03:52.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Nov 2003
    Posts
    94
    Quote Originally Posted by Enigma
    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:
    Code:
    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.

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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

    hmm .. let me try what you have said ... but i do not think it would help much
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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 ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •