optimizing this query?

[briansol]

i have the following query (to select dupes-- a record in both tables based off btn and completed [dispo 80]) which works fine, but I'm using a join to get only 1 date field before i do an inline select on the same table. I'm wondering if there's a better way to get the same data without the join?

SELECT 
	s.idate AS 'Date of Sales Call',
	t.idate AS 'Date of TPV Call',
	s.btn AS 'Billing Telephone Number',
	s.eon AS 'Order Number',
	s.cust_first_name AS 'Customer First Name',
	s.cust_last_name AS 'Customer Last Name', 
	s.loa AS 'LOA'
FROM 
	sale s
LEFT OUTER JOIN 
	tpv t
	on t.btn = s.btn
	AND t.dispo = '80'
WHERE
	fn_dateonly(s.idate) = fn_dateonly(getdate() -1)
	AND s.dispo = '80'
	AND s.btn IN 
	(
		SELECT btn from tpv
		where fn_dateonly(idate) = fn_dateonly(getdate() -1)
		AND dispo = '80'
	)

any suggestions on improvements?

[r937]

any suggestions on improvements?

yes, a very strong suggestion

stop doing this:

  fn_dateonly(s.idate) = fn_dateonly(getdate() -1)

you are forcing a table scan

[briansol]

edit, see next post....

[briansol]

How's this? It pulls the date function out of the table query...

declare @startdate datetime
declare @enddate datetime

set @startdate = fn_dateonly(getdate()-1) + ' 00:00:00'
set @enddate = fn_dateonly(getdate()) + ' 00:00:00'


SELECT 
	s.btn AS 'Billing Telephone Number',
	s.idate AS 'Date of Sales Call',
	t.idate AS 'Date of TPV Call',	
	s.eon AS 'Electronic Order Number',
	s.cust_first_name AS 'Customer First Name',
	s.cust_last_name AS 'Customer Last Name'
FROM 
	sale S
LEFT OUTER JOIN 
	tpv T 
	on T.btn = S.btn
	AND t.dispo = '80'
WHERE
	s.idate >= @startdate and s.idate < @enddate
	AND s.dispo = '80'
	AND s.btn IN 
	(
		SELECT 
			btn 
		FROM
			tpv
		WHERE
			iacc_date >= @startdate and idate < @enddate
			AND dispo = '80'
	)

[r937]

that's much more efficient

also, i would declare the two variables like this:

set @startdate = dateadd(dd,datediff(dd,0,getdate()),0)
set @enddate = dateadd(dd,1,@startdate)

i'll bet your UDF uses CONVERT -- this method is faster

[briansol]

plus it only calls getdate() once.

I can't notice any differences at this point (again, i have a 10 row test table i'm working with...) but over time it will get to be big. I may just alter this later on to do some seat-of-the-pants benchmarking to see if i can tell a difference.

Thanks for the help Rudy.