SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    optimizing this query?

    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?


    Code:
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by briansol
    any suggestions on improvements?
    yes, a very strong suggestion

    stop doing this:
    Code:
      fn_dateonly(s.idate) = fn_dateonly(getdate() -1)
    you are forcing a table scan
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    edit, see next post....
    Last edited by briansol; Feb 15, 2006 at 13:17.

  4. #4
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How's this? It pulls the date function out of the table query...

    Code:
    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'
    	)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's much more efficient

    also, i would declare the two variables like this:
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

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