By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,316 Members | 2,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,316 IT Pros & Developers. It's quick & easy.

SQL Optimization

blyxx86
100+
P: 256
Hey everyone,
I am attempting to optimize the sql statements I am currently using.

I have a series of about 5 complex statements. 3 out of 5 of these run very quickly. (I used a timer function to find the time to process.)

However the other 2 run very slowly (6-8 seconds, where as the others take only a few milliseconds). I am analyzing the statements and have found a probably root cause and am now working on optimizing them, but I need your help.

The primary reason I found (from testing) is the "OR" part of each query. When I remove the portion after the "OR" the speed goes to the normal speed of about 39 milliseconds.

My concern is getting these to run more quickly.

The reason for the OR statement is to show historical data for orders that have been filled, but were not filled in the past. Any dates in the SQL statement are generated dynamically, so they do not always show today's date.

Any suggestions?

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT tblRequest.CustomerTracking, tblLocations.LocationCode, tblRequest.ReqDate, tblRequest.ReqTime, [Prefix] & tblModel_1.ModelName AS PendingModel, tblRequest.ETA, tblRequest.PendingCauses, tblRequestDetail.Quantity*[stdQuantity] AS ReqQty, qry_Qtys.Outstanding AS QtyOutstanding, tblRequestType.RequestType, tblInvoice.InvoiceDate, tblInvoice.TrackingNumber 
  2.  
  3. FROM tblInvoice RIGHT JOIN (((qry_Qtys INNER JOIN (tblRequestType RIGHT JOIN (tblModel AS tblModel_1 INNER JOIN ((((tblCustomer INNER JOIN tblRequest ON tblCustomer.CustomerID = tblRequest.CustomerID) INNER JOIN tblRequestDetail ON tblRequest.RequestID = tblRequestDetail.RequestID) LEFT JOIN tblLocations ON tblRequest.LocationID = tblLocations.LocationID) INNER JOIN ((tblModel INNER JOIN tblProduct ON tblModel.ModelID = tblProduct.ModelID) INNER JOIN tblModelDetail ON tblModel.ModelID = tblModelDetail.ModelID) ON tblRequestDetail.ProductID = tblProduct.ProductID) ON tblModel_1.ModelID = tblModelDetail.IncludesModelID) ON tblRequestType.RequestTypeID = tblRequest.RequestTypeID) ON qry_Qtys.RequestID = tblRequest.RequestID) INNER JOIN tblProduct AS tblProduct_1 ON (tblModel_1.ModelID = tblProduct_1.ModelID) AND (qry_Qtys.ProductID = tblProduct_1.ProductID)) LEFT JOIN tblInvoiceDetail ON tblRequest.RequestID = tblInvoiceDetail.RequestID) ON tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID 
  4.  
  5. WHERE (((tblRequest.ReqDate)<#2/4/2008#) AND (([tblRequestDetail].[Quantity]*[stdQuantity])>0) AND ((qry_Qtys.Outstanding)>0) AND ((tblRequestType.RequestTypeID)<>3) AND ((tblRequestDetail.Cleared)=0) AND ((tblRequest.CustomerID)=1) AND ((tblRequest.Canceled)<>-1)) 
  6. OR 
  7. (((tblRequest.ReqDate)<#2/4/2008#) AND (([tblRequestDetail].[Quantity]*[stdQuantity])>0) AND ((qry_Qtys.Outstanding)>=0) AND ((tblInvoice.InvoiceDate)>#2/4/2008#) AND ((tblRequestType.RequestTypeID)<>3) AND ((tblRequestDetail.Cleared)=0) AND ((tblRequest.CustomerID)=1) AND ((tblRequest.Canceled)<>-1)) 
  8.  
  9. ORDER BY [Prefix] & tblModel_1.ModelName, tblRequest.CustomerTracking;
  10.  

I suppose that I could choose to only include this OR statement if the user input date is not today's date. This would be a partial fix, but I would still like to increase the speed otherwise.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT tblRequest.CustomerTracking, tblLocations.LocationCode, tblRequest.ReqDate, tblRequest.ReqTime, [Prefix] & tblModel_1.ModelName AS PendingModel, tblRequest.ETA, tblRequest.PendingCauses, tblRequestDetail.Quantity*[stdQuantity] AS ReqQty, qry_Qtys.Outstanding AS QtyOutstanding, tblRequestType.RequestType, tblInvoice.InvoiceDate, tblInvoice.TrackingNumber 
  2.  
  3. FROM tblInvoice RIGHT JOIN (((qry_Qtys INNER JOIN (tblRequestType RIGHT JOIN (tblModel AS tblModel_1 INNER JOIN ((((tblCustomer INNER JOIN tblRequest ON tblCustomer.CustomerID = tblRequest.CustomerID) INNER JOIN tblRequestDetail ON tblRequest.RequestID = tblRequestDetail.RequestID) LEFT JOIN tblLocations ON tblRequest.LocationID = tblLocations.LocationID) INNER JOIN ((tblModel INNER JOIN tblProduct ON tblModel.ModelID = tblProduct.ModelID) INNER JOIN tblModelDetail ON tblModel.ModelID = tblModelDetail.ModelID) ON tblRequestDetail.ProductID = tblProduct.ProductID) ON tblModel_1.ModelID = tblModelDetail.IncludesModelID) ON tblRequestType.RequestTypeID = tblRequest.RequestTypeID) ON qry_Qtys.RequestID = tblRequest.RequestID) INNER JOIN tblProduct AS tblProduct_1 ON (tblModel_1.ModelID = tblProduct_1.ModelID) AND (qry_Qtys.ProductID = tblProduct_1.ProductID)) LEFT JOIN tblInvoiceDetail ON tblRequest.RequestID = tblInvoiceDetail.RequestID) ON tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID 
  4.  
  5. WHERE (((tblRequest.ReqDate)<#2/4/2008#) AND (([tblRequestDetail].[Quantity]*[stdQuantity])>0) AND ((qry_Qtys.Outstanding)>0) AND ((tblRequestType.RequestTypeID)<>3) AND ((tblRequestDetail.Cleared)=0) AND ((tblRequest.CustomerID)=1) AND ((tblRequest.Canceled)<>-1)) 
  6. IF inputDate < Date() THEN --FOR EXAMPLE
  7. OR 
  8. (((tblRequest.ReqDate)<#2/4/2008#) AND (([tblRequestDetail].[Quantity]*[stdQuantity])>0) AND ((qry_Qtys.Outstanding)>=0) AND ((tblInvoice.InvoiceDate)>#2/4/2008#) AND ((tblRequestType.RequestTypeID)<>3) AND ((tblRequestDetail.Cleared)=0) AND ((tblRequest.CustomerID)=1) AND ((tblRequest.Canceled)<>-1)) 
  9. END IF
  10. ORDER BY [Prefix] & tblModel_1.ModelName, tblRequest.CustomerTracking;
  11.  
Feb 4 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, blyxx.

Two points:
  • boolean expressions may be treated in terms of boolean algebra
    Expr1 * Expr2 * Expr3 + Expr1 * Expr2 * Expr4 = Expr1 * Expr2 * (Expr3 + Expr4)
    * - AND
    + - OR
    This may give some optimization, though Access might itself optimizes it (not sure)
  • try to filter records before joining (in separate queries)

Regards,
Fish
Feb 4 '08 #2

blyxx86
100+
P: 256
Hi, blyxx.

Two points:
  • boolean expressions may be treated in terms of boolean algebra
    Expr1 * Expr2 * Expr3 + Expr1 * Expr2 * Expr4 = Expr1 * Expr2 * (Expr3 + Expr4)
    * - AND
    + - OR
    This may give some optimization, though Access might itself optimizes it (not sure)
  • try to filter records before joining (in separate queries)

Regards,
Fish
I don't think I've ever seen boolean algebra. I will have to experiment with it to figure it out.

However, how would you suggest filtering records in this statement? I know it's somewhat messy (it's the final query that feeds to the reporting page on the web server).

Do you suggest making queries like... "qry_AllShipments" "qry_Models"...etc? Is that what you mean?
Feb 4 '08 #3

FishVal
Expert 2.5K+
P: 2,653
I don't think I've ever seen boolean algebra. I will have to experiment with it to figure it out.
Without getting deep into the question - AND/OR are like */- in regular algebra. In this particular case this means that expression like
Expr1 AND Expr2 AND Expr3 OR Expr1 AND Expr2 AND Expr4
equals to
Expr1 AND Expr2 AND (Expr3 OR Expr4)

However, how would you suggest filtering records in this statement? I know it's somewhat messy (it's the final query that feeds to the reporting page on the web server).

Do you suggest making queries like... "qry_AllShipments" "qry_Models"...etc? Is that what you mean?
Yea. This is what I've meant. I'm not sure whether it will help, but it looks like worth to try.

Regards,
Fish
Feb 4 '08 #4

blyxx86
100+
P: 256
Without getting deep into the question - AND/OR are like */- in regular algebra. In this particular case this means that expression like
Expr1 AND Expr2 AND Expr3 OR Expr1 AND Expr2 AND Expr4
equals to
Expr1 AND Expr2 AND (Expr3 OR Expr4)

Yea. This is what I've meant. I'm not sure whether it will help, but it looks like worth to try.

Regards,
Fish
I did a few small tables to figure out the logic behind boolean algebra. I understood that it is basically multiplying 0's and 1's... I managed to get:
Expand|Select|Wrap|Line Numbers
  1. (([tblRequestDetail].[Cleared]+[tblRequest].[Canceled])=0)
  2.  
I do understand the boolean algebra, now that I have used it once. It just sounds more complicated than it really is.

I will take a look at what separate queries I can create to consolidate them into to improve the time.

However, I do believe I have overcome a hurdle.. Of course it was a design flaw on my part. :)

Below is the new sql...
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT tblRequest.CustomerTracking, tblLocations.LocationCode, tblRequest.ReqDate, tblRequest.ReqTime, [Prefix] & tblModel_1.ModelName AS PendingModel, tblRequest.ETA, tblRequest.PendingCauses, tblRequestDetail.Quantity*[stdQuantity] AS ReqQty, qry_Qtys.Outstanding AS QtyOutstanding, tblRequestType.RequestType, tblInvoice.InvoiceDate, tblInvoice.TrackingNumber 
  2.  
  3. FROM tblInvoice RIGHT JOIN (((qry_Qtys INNER JOIN (tblRequestType RIGHT JOIN (tblModel AS tblModel_1 INNER JOIN ((((tblCustomer INNER JOIN tblRequest ON tblCustomer.CustomerID = tblRequest.CustomerID) INNER JOIN tblRequestDetail ON tblRequest.RequestID = tblRequestDetail.RequestID) LEFT JOIN tblLocations ON tblRequest.LocationID = tblLocations.LocationID) INNER JOIN ((tblModel INNER JOIN tblProduct ON tblModel.ModelID = tblProduct.ModelID) INNER JOIN tblModelDetail ON tblModel.ModelID = tblModelDetail.ModelID) ON tblRequestDetail.ProductID = tblProduct.ProductID) ON tblModel_1.ModelID = tblModelDetail.IncludesModelID) ON tblRequestType.RequestTypeID = tblRequest.RequestTypeID) ON qry_Qtys.RequestID = tblRequest.RequestID) INNER JOIN tblProduct AS tblProduct_1 ON (tblModel_1.ModelID = tblProduct_1.ModelID) AND (qry_Qtys.ProductID = tblProduct_1.ProductID)) LEFT JOIN tblInvoiceDetail ON tblRequest.RequestID = tblInvoiceDetail.RequestID) ON tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID 
  4.  
  5. WHERE (((tblRequest.ReqDate)<'2/4/2008') AND (([tblRequestDetail].[Quantity]*[stdQuantity])>0) AND ((qry_Qtys.Outstanding)>=0) AND ((tblInvoice.InvoiceDate)>'2/4/2008' Or (tblInvoice.InvoiceDate) Is Null) AND ((tblRequestType.RequestTypeID)<>3) AND ((tblRequest.CustomerID)=1) AND (([tblRequestDetail].[Cleared]+[tblRequest].[Canceled])=0)) 
  6.  
  7. ORDER BY [Prefix] & tblModel_1.ModelName, tblRequest.CustomerTracking;
  8.  
The changed line:
Expand|Select|Wrap|Line Numbers
  1. AND ((tblInvoice.InvoiceDate)>'2/4/2008' Or (tblInvoice.InvoiceDate) Is Null)
  2.  
Instead of recreating an entirely new OR statement, I followed the concept behind the algebraic approach and simply 'factored' out the other things in the WHERE clause.

I now have all 5 queries running in under 1 second. Some as little as 19 milliseconds. A much needed improvement over the past running time. The query times used to be less when the database was only used by 1-2 people, but now that it's being used on a production server. It makes a huge difference.

Thank you for the insight! I will still have to experiment with the separate queries for the joins, but am not sure if I will be able to yet. I may save that part for when I migrate over to MS SQL Server. (This week?? SCARY).
Feb 5 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.