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

Speed up my query

P: 17
I have a query that needs to refresh after updating. The problem is that the query takes about 10 seconds to refresh. Anyone know if there is a way to speed this query up?

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT tblInvoices.CustomerID, tblInvoices.CustomerName
  2. FROM tblInvoices LEFT JOIN tblInvoiceCustomerBridge ON (tblInvoices.CustomerName= tblInvoiceCustomerBridge.SystemCustomerName) AND (tblInvoices.CustomerID= tblInvoiceCustomerBridge.SystemCustomerNumber)
  3. WHERE (((tblInvoiceCustomerBridge.SystemCustomerNumber) Is Null)) AND tblInvoices.State In(select state from States) AND tblInvoices.TotalTax=0 AND tblInvoices.NetSales<>0
  4. ORDER BY tblInvoices.CustomerName;
Mar 10 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
Expand|Select|Wrap|Line Numbers
  1. ...
  2. WHERE (((tblInvoiceCustomerBridge.SystemCustomerNumber) Is Null)) AND tblInvoices.State In(select state from States) AND ...
Hi BPlantes. The subquery in your where clause appears not to be doing anything other than making sure that the invoice State is one of the valid states recorded in the States table. If your table relationships are set correctly you should not need such a test; relational constraints prevent invalid values from being stored.

The use of the subquery can lead to substantial performance reductions, because it is run at least once for each row returned.

-Stewart
Mar 10 '08 #2

Post your reply

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