Results 1 to 6 of 6

Thread: SQL Rewite help

  1. #1
    Join Date
    Jul 2007
    Posts
    29

    Unanswered: SQL Rewite help

    All, I have a query that I use as part of the receiving function for my Access application. It is a HUGE resource hog and I know it can be written more efficiently. The problem is I do not know enough to do it correctly. I am asking for help. If nothing else there has to be a way to clean up the IIF statement etc.

    Me.lstOutstanding.RowSource = "SELECT DISTINCT OrderingHistory.ID, OrderingHistory.OrderNumber AS PO, OrderingHistory.VendorPN, OrderingHistory.Description, OrderingHistory.Quantity - (IIF((SELECT SUM (Partials.Quantity) FROM Partials WHERE OrderingHistory.ID = Partials.OrderingHistoryID) IS NULL, 0,(SELECT SUM (Partials.Quantity) FROM Partials WHERE OrderingHistory.ID = Partials.OrderingHistoryID))) AS Outstanding, OrderingHistory.UI, OrderingHistory.PRBuiltDate AS Requested FROM (SELECT ID, OrderNumber, VendorPN, Description, Quantity, UI, PRBuiltDate FROM OrderingHistory WHERE OrderingHistory.ReceivedDate IS NULL And OrderingHistory.Vendor = " & Me.cboVendor.Value & " AND Enduser = " & Nz(DLookup("ID", "EndUsers", "UserName = '" & Forms!EndUserSelect.cboEnduser.Value & "'"), 3) & "), Partials WHERE (OrderingHistory.VendorPN LIKE '*" & Me.txtVendorPN.Value & "*' OR OrderingHistory.Description LIKE '*" & Me.txtVendorPN.Value & "*') ORDER BY OrderingHistory.PRBuiltDate"


    OR formated

    Me.lstOutstanding.RowSource = "SELECT DISTINCT OrderingHistory.ID, OrderingHistory.OrderNumber AS PO," _
    & " OrderingHistory.VendorPN, OrderingHistory.Description," _
    & " OrderingHistory.Quantity - (IIF((SELECT SUM (Partials.Quantity)" _
    & " FROM Partials WHERE OrderingHistory.ID = Partials.OrderingHistoryID) IS NULL," _
    & " 0,(SELECT SUM (Partials.Quantity) FROM Partials WHERE OrderingHistory.ID" _
    & " = Partials.OrderingHistoryID))) AS Outstanding," _
    & " OrderingHistory.UI, OrderingHistory.PRBuiltDate AS Requested" _
    & " FROM (SELECT ID, OrderNumber, VendorPN, Description, Quantity, UI, PRBuiltDate" _
    & " FROM OrderingHistory WHERE OrderingHistory.ReceivedDate IS NULL And" _
    & " OrderingHistory.Vendor = " & Me.cboVendor.Value & " AND Enduser = " _
    & Nz(DLookup("ID", "EndUsers", "UserName = '" & Forms!EndUserSelect.cboEnduser.Value & "'"), 3) & "), Partials" _
    & " WHERE (OrderingHistory.VendorPN LIKE '*" _
    & Me.txtVendorPN.Value & "*' OR OrderingHistory.Description LIKE '*" & Me.txtVendorPN.Value & "*')" _
    & " ORDER BY OrderingHistory.PRBuiltDate"

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the iif certainly doesn't help - it evaluates both "sides" of the expression always so both SELECTs run. try replacing
    iif(SELECT blah,0,SELECT blah)
    with
    nz(SELECT blah, 0)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2007
    Posts
    29
    Thats great that helped significantly

    Incidentally I needed to use extra parenthesis

    NZ((Select Blah),0)

    Anymore Hints out there?


    Quote Originally Posted by izyrider
    the iif certainly doesn't help - it evaluates both "sides" of the expression always so both SELECTs run. try replacing
    iif(SELECT blah,0,SELECT blah)
    with
    nz(SELECT blah, 0)

    izy

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try designing out the dlookup.. it has no place in a select statement, especially if its a big table. domain functions are performance killers... they are fine if you are needing a single value, but you are paying a heavy price for the dlookup statement on many rows.. too heavy a price.

    generally speaking use as few vba functions as possible in the query, and don't think of using your own functions in a query (unless you have to)

    aside form that theres always the obvious advice make sure all the columns being used in the where clauses and/or joins are indexed

    HTH

  5. #5
    Join Date
    Jul 2007
    Posts
    29
    Quote Originally Posted by healdem
    try designing out the dlookup.. it has no place in a select statement, especially if its a big table. domain functions are performance killers... they are fine if you are needing a single value, but you are paying a heavy price for the dlookup statement on many rows.. too heavy a price.

    generally speaking use as few vba functions as possible in the query, and don't think of using your own functions in a query (unless you have to)

    aside form that theres always the obvious advice make sure all the columns being used in the where clauses and/or joins are indexed

    HTH

    In this specific case the dlookup find a single value and then inserts it into the SELECT statement. That certainly has to be fast than joining another, admittedly small, table. I do not know how any of the behind the scenes part of access work. I am assuming that I take the performance hit only once for the initial assigning. Since the real work of the query is the SUM for each record, I am again assuming, I reap the benefits of not having a extra table joined.

    Please identify where my assumptions go astray. If it matters all tables in question are linked share point lists.

    Also can you please explain further on the indexing?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it should be possible to design your query so that you don't need the dlookup

    dlookup is effectively a specialised select statement, it opens a recordset / connection, it issues a select statement, it returns the data, it closes the connection. its fine for one or two records, but not clever if this query is returning a significant amount of data

    if you are doing a join, or a where clause then if the relevant columns are not indexed then it adversely affects performance, because the sql engine has to do a significant amount of work to do the same job.. if they are indexed then the engine can find records with less work, and quicker.

Posting Permissions

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