Results 1 to 4 of 4

Thread: SQL Query help

  1. #1
    Join Date
    May 2007
    Posts
    49

    Unanswered: SQL Query help

    I have a query that counts number of rows as per the criteria specified, query is as follows -


    Code:
    Select
    tbl_ActivityRequest.ACTID,
    (SELECT COUNT(UID) FROM tbl_units WHERE RFS = 1 AND tbl_units.ACTID =  tbl_ActivityRequest.ACTID and Deleted=0) AS RFS_Units,
    (SELECT COUNT(UID) FROM tbl_Units WHERE tbl_units.ActID = tbl_ActivityRequest.ACTID and Deleted=0) AS Num_Units,
    (SELECT COUNT(UID) FROM tbl_Units WHERE tbl_units.ActID = tbl_ActivityRequest.ACTID and Deleted=0 and Completed=0) AS Incomplete_Units,
    (SELECT COUNT(UID) FROM tbl_units INNER JOIN tbl_location ON tbl_location.LID = tbl_units.location  WHERE  tbl_units.ACTID =  tbl_ActivityRequest.ACTID and tbl_location.[name] LIKE 'Repair' and tbl_units.Deleted=0) AS InRepair,
    (SELECT COUNT(ActID) FROM tbl_ReceivingPallets WHERE tbl_ReceivingPallets.ActID = tbl_ActivityRequest.ACTID and COALESCE(Deleted,0) <> 1) AS Total_RPallets,
    (SELECT COUNT(ActID) FROM tbl_ReceivingPallets WHERE tbl_ReceivingPallets.ActID = tbl_ActivityRequest.ACTID AND Inserted =1 AND COALESCE(Deleted,0) <> 1) AS Booked_RPallets,
    (SELECT COUNT(ActID) FROM tbl_ReceivingPallets WHERE tbl_ReceivingPallets.ActID = tbl_ActivityRequest.ACTID and COALESCE(Inserted,0) <> 1 and COALESCE(deleted,0) <> 1) AS Pending_RPallets,
    (SELECT MAX(DateCompleted) FROM tbl_units WHERE  tbl_units.actid = tbl_ActivityRequest.ActID) LastUnitCompletedDate,
    (dbo.func_ASEA_GetOSLA(tbl_ActivityRequest.ActID,getDate())) AS OSLA
    FROM tbl_ActivityRequest
    but this query is taking too much time as tables involved 'tbl_activityRequest' and 'tbl_Units' are quite big and havily accessed by users everyday,
    is there any other better way available to achieve the desired result ?

    I would appreciate any help anyone can give me.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would suggest posting some data definition language complete with indexes and constraints.

    Barring that I would consider using joins over the subqueries and perhaps giving us the text output of the execution plan.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note that this is not "apples for apples", this code corrects at least four apparent logic flaws from the original code. The results of this are "correct" from a logical standpoint, but they may not be the same as the results of the original query.
    Code:
    SELECT ar.ACTID
    ,  Sum(CASE WHEN u.UID IS NOT NULL AND 1 = RFS THEN 1 END) AS RFS_Units
    ,  Sum(CASE WHEN u.UID IS NOT NULL THEN 1 END) AS Num_Units
    ,  Sum(CASE WHEN u.UID IS NOT NULL AND 0 = Completed THEN 1 END) AS Incomlete_Units
    ,  Sum(CASE WHEN u.UID IS NOT NULL AND 'Repair' = z1.[name] THEN 1 END) AS InRepair
    ,  Sum(CASE WHEN rp.ActID IS NOT NULL THEN 1 END) AS Total_RPallets
    ,  Sum(CASE WHEN rp.ActID IS NOT NULL AND 1 = Inserted THEN 1 END) AS Pending_RPallets
    ,  Max(u.DateCompleted) AS LastUnitCompletdDate
    ,  dbo.func_ASEA_GetOSLA(ar.ActID, GetDate()) AS OSLA
       FROM tbl_ActivityRequest AS ar
       LEFT OUTER JOIN tbl_units AS u
          ON (u.ActID = ar.ACTID
          AND 0 = u.Deleted)
       LEFT OUTER JOIN Tbl_Location AS z1
          ON (z1.LID = u.LID)
       LEFT OUTER JOIN tbl_ReceivingPallets AS rp
          ON (rp.ActID = ar.ACTID
          AND Coalesce(rp.Deleted, 0) <> 1)
    -PatP

  4. #4
    Join Date
    May 2007
    Posts
    49
    Hi PatP,

    I tested your query on production database, but it is taking around 2.00 mins. more than original query.

    DDL for tables is quite big so posting it in the form of attachment.
    Attached Files Attached Files
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

Posting Permissions

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