Need help on rewriting a query to run faster

[katekis]

The below example query takes a long time to run. I believe it will work better if I change the where in (Select) statement part to instead be a from table join. But I do not know how to write the SQL statement.

The below example query looks to see what customer_ID is not in both the Savings and Checking Table. I am looking for customers that have a savings account but do not have a checking account and are over the age 18.

Can someone let me know if I can rewrite the SQL statement to run more efficiently?

SELECT Saving.*
FROM Saving
where Saving.customer_id not in
(select customer_id from Checking)
AND DATEDIFF(month, cfmr_birthdate, getdate()) >= 216
--Age 18

Thanks,

 

[markros]

Couple of points.

1. To change your original query into the left join
<pre>
select S.* from Saving S LEFT JOIN Checking C on S.Customer_ID = C.Customer_ID and DATEDIFF(month, C.cfmr_birthdate, getdate()) >= 216 where C.Customer_ID is NULL</pre>

However, the problem also is in DateDiff condition.

 

[gmmastros]

try this:

Select Savings.*
From   Savings
       Left Join Checking
         On Savings.customer_id = Checking.CustomerId
Where  Checking.Customer_Id Is NULL
       And cfmr_birthdate < DateAdd(Month, -216, GetDate())

To find items in one table that do not exist in another table, I usually use a left join and add a where clause (where the value from the right table is null).

Also notice that I changed the 2nd where clause condition to make it [google]sql server sargable[/google]. If you don't know what sargable means, then I highly encourage you to spend 20 minutes reading up on it. It can sometimes make a drastic difference in performance.

Also note that I assume the cfmr_birthdate column exists in the savings table. If it's in the checking table, then we will need to tweak the query a little to produce the correct result.

Please (if you don't mind), post the execution time of your original query and the execution time of my suggestion. If the query time is still not acceptable, there are some indexing tricks we can apply to make the query even better.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

select S.* from Saving S LEFT JOIN Checking C on S.Customer_ID = C.Customer_ID and C.cfmr_Birthday >=DATEADD(month, 216, getdate())  where C.Customer_ID is NULL

 

[markros]

To be precise, your query should be

select S.* from Saving S LEFT JOIN Checking C on S.Customer_ID = C.Customer_ID and C.cfmr_Birthday >=DATEADD(month, -216, getdate())  where C.Customer_ID is NULL

Assuming that cfmr_Birthday is in the Checking table, though I suspect it should be in Customers table, not included in this query for some reason.

 

[markros]

And, to be even more precise If you want customers older than 18 years, then the condition should be

Birthday <= DateAdd(month, -216, GetDate())

Somehow I always got confused when dates are involved...

 

[SQLSister]

Oh and if you don't actually use all the fields in Saving, then don't ask for them.

"NOTHING is more important in a database than integrity." ESquared

 

[katekis]

I get different number of records back from these two queries. I am trying to write the first query to it runs faster. The new query should return the same number of records or something is incorrect.

I left the birthdate part out of these query. I can work adding that to the query once I get this part to work.

--#1
SELECT Saving.
FROM Saving
where Saving.customer_id in
(select Checking.customer_id from Checking)

--#2
select S.*
from Saving S
LEFT JOIN Checking C on S.Customer_ID = C.Customer_ID
where C.Customer_ID is NULL

 

[gmmastros]

is it possible that you have null values in your customer_id column from either table.

Select Count(*) From Saving Where Customer_ID Is NULL

Select Count(*) From Saving Where Customer_ID Is NULL

Run the two queries above and let me know if either one returns a value that is not 0.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

The 2nd query is the same as the first. My bad.

Select Count(*) From Saving Where Customer_ID Is NULL

Select Count(*) From Checking Where Customer_ID Is NULL

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[katekis]

I get 0 back on both queries

Select Count(*) From Saving Where Customer_ID Is NULL

Select Count(*) From Checking Where Customer_ID Is NULL

 

[markros]

They both should return the same number of records. Can you verify that Customer_ID field has the exact same type in both tables? Is it an integer field?

 

[markros]

Also, I just noticed:

Your first query is now using "IN" instead of the original "NOT IN"

NOT IN is the same as LEFT JOIN with WHERE RightTable.PkField IS NULL

IN is the same as EXISTS, so

select S.* from Savings where EXISTS (select 1 from Checking C where C.Customer_ID = S.Customer_ID)

 

[markros]

Correction to the last query above:

select S.* from Savings S where EXISTS (select 1 from Checking C where C.Customer_ID = S.Customer_ID)

 

[katekis]

The problem was the "NOT IN"

ALL is good now Thanks!

 

[markros]

So, is it working faster with LEFT JOIN now?