How To Avoid Table Scan

Hot Topic

Calling SSIS packages from within a Script task
Autocomplete combobox
DB Mirroring VS. Clustered Failover Questions
user rights and executing of SSIS package
SSIS Package runs in BI but not VB.Net
Using UniqueIdentifier as Primary key
sql server management studio nov ctp
Moving DB one server to another with Broker Enabled
Passing Variables
Cannot access database using SQLCMD

SQL Server

Concatenating column values for each uniqueid
Question about SQL Server 2000 Rollback
Error of import text file into mssql
OUTER JOIN table limit?
How to implement this scenario
Data Transformation Services Process Task
OLEDB versus ADO.NET for UPDATE statement using parms
Combining text data rows
Percentile
.NET Framework 2.0 question

How To Avoid Table Scan

Hello All,

Is their any way I can avoid a Table Scan by modifying the follow WHERE clause

WHERE tbl_mis_Dashboard.loan_active_flg = 1
AND [tbl_mis_Dashboard].[loan_create_dt]
between DATEADD(dd, DATEDIFF(dd,0,dateadd(mm,-13,getdate())), 0)
And getdate()

The field loan_create_dt is indexed on the table tbl_mis_Dashboard. The query returns approximately 256K records which is appropriate and necessary.

Thanks all,
Orlanzo



Answer this question

How To Avoid Table Scan

  • Greetings

    Hello Adam,
    I appreciate your response. The entire query is listed below. The column loan_active.flg is indexed. Neither it or the index for loan_create_dt is clustered. As you will see, I'm selecting quite a few fields. I've read including too many in covering index isn't always the most efficient. Would you agree

    Regards,
    Orlanzo

    SELECT Year([loan_D1_combined_fund_dt]) AS [Year], Month([loan_D1_combined_fund_dt]) AS [Month], Count(dbo_tbl_mis_DashBoard.loan_id) AS [Total Loans], Sum(dbo_tbl_mis_DashBoard.loan_loan_amt) AS [Total Loan Amount], Avg(dbo_tbl_mis_DashBoard.loan_loan_amt) AS [Average Loan Amount], Min(dbo_tbl_mis_DashBoard.loan_loan_amt) AS [Minimum Loan Amount], Max(dbo_tbl_mis_DashBoard.loan_loan_amt) AS [Maximum Loan Amount], Avg(dbo_tbl_mis_DashBoard.loan_note_rate) AS [Ave Loan Rate], Sum(dbo_tbl_mis_Dashboard!loan_note_rate*dbo_tbl_mis_Dashboard!loan_loan_amt) AS [WAC Step1], Sum(IIf(dbo_tbl_mis_Dashboard!loan_doc_type_label="Stated Income",dbo_tbl_mis_Dashboard!loan_loan_amt,0)) AS [$ % Stated], Sum(IIf(dbo_tbl_mis_Dashboard!loan_lien_position=1,IIf(dbo_tbl_mis_Dashboard!product_type_label="FIXED",dbo_tbl_mis_Dashboard!loan_loan_amt,0))) AS [$ % of FIXED Lien 1], Sum(IIf(Left(dbo_tbl_mis_Dashboard!product_label,2)="IO",dbo_tbl_mis_Dashboard!loan_loan_amt,0)) AS [$ % IO], Sum(IIf(dbo_v_mis_Report_Status_Pivot![Problem Performing_dt] Is Not Null,dbo_tbl_mis_Dashboard!loan_loan_amt,IIf(dbo_v_mis_Report_Status_Pivot![Problem Non-Performing_dt] Is Not Null,dbo_tbl_mis_Dashboard!loan_loan_amt,IIf(dbo_v_mis_Report_Status_Pivot![Problem/REO_dt] Is Not Null,dbo_tbl_mis_Dashboard!loan_loan_amt,IIf(dbo_v_mis_Report_Status_Pivot![Problem/D1Portfolio_dt] Is Not Null,dbo_tbl_mis_Dashboard!loan_loan_amt,IIf(dbo_tbl_mis_Dashboard!LExt_rprch_ind<>0,dbo_tbl_mis_Dashboard!loan_loan_amt,0)))))) AS [Problem_Loan_$%], Sum(IIf(dbo_tbl_mis_Dashboard!product_label Like "*40*",dbo_tbl_mis_Dashboard!loan_loan_amt,0)) AS [$ % 40 Yr Step1], Sum(dbo_tbl_mis_Dashboard!loan_CLTV*dbo_tbl_mis_Dashboard!loan_loan_amt) AS [CLTV Step1], Sum(IIf(dbo_v_mis_Report_Status_Pivot!Redirected_dt Is Not Null,1,0)) AS Redirected_dt_Step1, Sum(IIf(dbo_v_mis_Report_Status_Pivot![Pended At Support Office_dt] Is Not Null,1,0)) AS Pended_at_SO_dt_Step1, Sum(IIf(dbo_tbl_mis_Dashboard!lsh_status_label="Paid Off",dbo_tbl_mis_Dashboard!loan_loan_amt,0)) AS [$ % Paid Off], Sum(IIf(dbo_tbl_mis_DashBoard!lsh_current_status_id=27,dbo_tbl_mis_DashBoard!loan_loan_amt,IIf(dbo_tbl_mis_DashBoard!lsh_current_status_id=29,dbo_tbl_mis_DashBoard!loan_loan_amt,IIf(dbo_tbl_mis_DashBoard!lsh_current_status_id=47,dbo_tbl_mis_DashBoard!loan_loan_amt,IIf(dbo_tbl_mis_DashBoard!lsh_current_status_id=48,dbo_tbl_mis_DashBoard!loan_loan_amt,IIf(dbo_tbl_mis_DashBoard!LExt_rprch_ind<>0,dbo_tbl_mis_DashBoard!loan_loan_amt,0)))))) AS [Active Problem Loans $ %]
    FROM dbo_tbl_mis_DashBoard LEFT JOIN dbo_v_mis_Report_Status_Pivot ON (dbo_tbl_mis_DashBoard.company_id = dbo_v_mis_Report_Status_Pivot.company_id) AND (dbo_tbl_mis_DashBoard.loan_id = dbo_v_mis_Report_Status_Pivot.loan_id)
    WHERE (((dbo_tbl_mis_DashBoard.loan_active_flg)=1) AND ((dbo_tbl_mis_DashBoard.FUND_Date_Flag)="Yes") AND ((dbo_tbl_mis_DashBoard.loan_D1_combined_fund_dt)<Now()))
    GROUP BY Year([loan_D1_combined_fund_dt]), Month([loan_D1_combined_fund_dt])
    ORDER BY Year([loan_D1_combined_fund_dt]) DESC , Month([loan_D1_combined_fund_dt]) DESC;



  • jrasmussen

    --This particular case, roughly half the cost.
    DECLARE @DT datetime
    SET @DT = getDate()
    CREATE TABLE #xyz (Date1 DateTime)

    INSERT #xyz
    SELECT DateAdd(ms, number * -1, @DT)
    FROM master..spt_values

    CREATE CLUSTERED INDEX IX_X ON #XYZ (Date1)

    SET STATISTICS PROFILE ON

    SELECT Date1
    FROM #xyz
    WHERE Date1 < GetDate()
    SELECT Date1
    FROM #xyz
    WHERE Date1 < @DT

    SET STATISTICS PROFILE OFF

    DROP TABLE #xyz

  • Looter

    First, Show Execution Plan shows actual execution rather than estimated. It shows a graphical form of statistics profile. (http://www.microsoft.com/technet/community/chats/trans/sql/chat0503.mspx)

    Second, using the statistics profile with a variable consistantly produces lower cost.

    This is consistent in real world data and can mean real time in larger tables.

  • MichaelPolak

    It's quite funny really. I get this question all the time. The answer is:

    Sometimes table scans are good, sometimes they are bad. It depends on your data, your index's (covering/cluster index's etc...) and what joins you are doing.

    To optimise your query, you need to look at the subtree cost inside of Query Analyser (or SQL Server Management Studio for 2005). If adding index's reduces the subtree cost, then they are worth having. If it makes the subtree cost worse, drop them and let the table scan happen.

    By looking at your query, I would suggest that you might actually benefit from de-normalization. I.e. combine the data into another (single) table and query that table. This is quite common in data warehousing. You can use DTS or SSIS to do this.

    Regards

    Paul



  • Jstn

    While it may not avoid a table scan, if your query is in a stored procedure, you could do something like this:

    Declare @DT datetime

    Set @DT = getDate()

    WHERE tbl_mis_Dashboard.loan_active_flg = 1
    AND [tbl_mis_Dashboard].[loan_create_dt]
    between DATEADD(dd, DATEDIFF(dd,0,dateadd(mm,-13,@DT)), 0)
    And @DT

    I've seen this costing about a third of the other way. Reason getDate() isn't deterministic, therefore has to compute on every row.

  • Venky_SG

    Adam, Paul, et. al,

    Thaks so much everyone for your time and input. Everything has been very helpful. And Adam, I'll certainly pick up the books you listed. It lloks like I've going to have a bit of fun in the upcoming months becoming more proficient in performance tuning and optimization. Again, thanks everyone for getting me on the right track.

    Regards,
    Orlanzo


  • rDNA

    Along similar lines, if tbl_mis_Dashboard.load_active_flg is a bit field, it will have to be CAST to an int for every row in order to compare it to 1.

    Try WHERE tbl_mis_Dashboard.load_active_flg = CAST(1, bit)

  • Lieblick

    Hi Orlanzo,
     
    When you say they're both "indexed", do you mean that there are two indexes (one for each column), or a single composite index including both columns
     
    Covering indexes are always the most efficient way to query a table, BUT large covering indexes have a big performance penalty when updating data, so that's really the area you need to consider if you're going to go that route.  Also, in SQL Server 2000 (I'm not sure what version you're using), you can only have up to 16 columns in an index, so you probably wouldn't be able to cover that query anyway.
     
    What's the clustered index on your table
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     
    Hello Adam,
    I appreciate your response. The entire query is listed below. The column loan_active.flg is indexed. Neither it or the index for loan_create_dt is clustered. As you will see, I'm selecting quite a few fields. I've read including too many in covering index isn't always the most efficient. Would you agree

    Regards,
    Orlanzo

    SELECT Year([loan_D1_combined_fund_dt]) AS [Year], Month([loan_D1_combined_fund_dt]) AS [Month], Count(dbo_tbl_mis_DashBoard.loan_id) AS [Total Loans], Sum(dbo_tbl_mis_DashBoard.loan_loan_amt) AS [Total Loan Amount], Avg(dbo_tbl_mis_DashBoard.loan_loan_amt) AS [Average Loan Amount], Min(dbo_tbl_mis_DashBoard.loan_loan_amt) AS [Minimum Loan Amount], Max(dbo_tbl_mis_DashBoard.loan_loan_amt) AS [Maximum Loan Amount], Avg(dbo_tbl_mis_DashBoard.loan_note_rate) AS [Ave Loan Rate], Sum(dbo_tbl_mis_Dashboard!loan_note_rate*dbo_tbl_mis_Dashboard!loan_loan_amt) AS [WAC Step1], Sum(IIf(dbo_tbl_mis_Dashboard!loan_doc_type_label="Stated Income",dbo_tbl_mis_Dashboard!loan_loan_amt,0)) AS [$ % Stated], Sum(IIf(dbo_tbl_mis_Dashboard!loan_lien_position=1,IIf(dbo_tbl_mis_Dashboard!product_type_label="FIXED",dbo_tbl_mis_Dashboard!loan_loan_amt,0))) AS [$ % of FIXED Lien 1], Sum(IIf(Left(dbo_tbl_mis_Dashboard!product_label,2)="IO",dbo_tbl_mis_Dashboard!loan_loan_amt,0)) AS [$ % IO], Sum(IIf(dbo_v_mis_Report_Status_Pivot![Problem Performing_dt] Is Not Null,dbo_tbl_mis_Dashboard!loan_loan_amt,IIf(dbo_v_mis_Report_Status_Pivot![Problem Non-Performing_dt] Is Not Null,dbo_tbl_mis_Dashboard!loan_loan_amt,IIf(dbo_v_mis_Report_Status_Pivot![Problem/REO_dt] Is Not Null,dbo_tbl_mis_Dashboard!loan_loan_amt,IIf(dbo_v_mis_Report_Status_Pivot![Problem/D1Portfolio_dt] Is Not Null,dbo_tbl_mis_Dashboard!loan_loan_amt,IIf(dbo_tbl_mis_Dashboard!LExt_rprch_ind<>0,dbo_tbl_mis_Dashboard!loan_loan_amt,0)))))) AS [Problem_Loan_$%], Sum(IIf(dbo_tbl_mis_Dashboard!product_label Like "*40*",dbo_tbl_mis_Dashboard!loan_loan_amt,0)) AS [$ % 40 Yr Step1], Sum(dbo_tbl_mis_Dashboard!loan_CLTV*dbo_tbl_mis_Dashboard!loan_loan_amt) AS [CLTV Step1], Sum(IIf(dbo_v_mis_Report_Status_Pivot!Redirected_dt Is Not Null,1,0)) AS Redirected_dt_Step1, Sum(IIf(dbo_v_mis_Report_Status_Pivot![Pended At Support Office_dt] Is Not Null,1,0)) AS Pended_at_SO_dt_Step1, Sum(IIf(dbo_tbl_mis_Dashboard!lsh_status_label="Paid Off",dbo_tbl_mis_Dashboard!loan_loan_amt,0)) AS [$ % Paid Off], Sum(IIf(dbo_tbl_mis_DashBoard!lsh_current_status_id=27,dbo_tbl_mis_DashBoard!loan_loan_amt,IIf(dbo_tbl_mis_DashBoard!lsh_current_status_id=29,dbo_tbl_mis_DashBoard!loan_loan_amt,IIf(dbo_tbl_mis_DashBoard!lsh_current_status_id=47,dbo_tbl_mis_DashBoard!loan_loan_amt,IIf(dbo_tbl_mis_DashBoard!lsh_current_status_id=48,dbo_tbl_mis_DashBoard!loan_loan_amt,IIf(dbo_tbl_mis_DashBoard!LExt_rprch_ind<>0,dbo_tbl_mis_DashBoard!loan_loan_amt,0)))))) AS [Active Problem Loans $ %]
    FROM dbo_tbl_mis_DashBoard LEFT JOIN dbo_v_mis_Report_Status_Pivot ON (dbo_tbl_mis_DashBoard.company_id = dbo_v_mis_Report_Status_Pivot.company_id) AND (dbo_tbl_mis_DashBoard.loan_id = dbo_v_mis_Report_Status_Pivot.loan_id)
    WHERE (((dbo_tbl_mis_DashBoard.loan_active_flg)=1) AND ((dbo_tbl_mis_DashBoard.FUND_Date_Flag)="Yes") AND ((dbo_tbl_mis_DashBoard.loan_D1_combined_fund_dt)<Now()))
    GROUP BY Year([loan_D1_combined_fund_dt]), Month([loan_D1_combined_fund_dt])
    ORDER BY Year([loan_D1_combined_fund_dt]) DESC , Month([loan_D1_combined_fund_dt]) DESC;

  • lucasjam

    Actually this is closer to the original problem, a GetDate() with a between. Show execution plan and you will see the cost diff. You can reverse the queries and get the same results, so it isn't benefitting from cache.

    DECLARE @DT datetime

    SET @DT = getDate()

    CREATE TABLE #xyz (Date1 DateTime, Date2 DateTime)

    INSERT #xyz
    SELECT DateAdd(ms, number * -1, @DT), DateAdd(ms, number, @DT)
    FROM master..spt_values

    CREATE CLUSTERED INDEX IX_X ON #XYZ (Date1, Date2)

    SET STATISTICS PROFILE ON


    SELECT Date1, Date2
    FROM #xyz
    WHERE Date1 BETWEEN Date2 AND GETDATE()

    SELECT Date1, Date2
    FROM #xyz
    WHERE Date1 BETWEEN Date2 AND @DT

    SET STATISTICS PROFILE OFF

    DROP TABLE #xyz

  • Ike

    Paul, Adam:

    Thank you both for your input. It is very much appreciated. The query I provided earlier was not the one in question; however, as you mentioned Paul - denormalizing the data for reporting would appear to be beneficial in that case. Below is the SQL corresponding o the WHERE clause of my original post. In this situation I'm pulling from a single table.

    Paul - I'll review the execution plan and do some trial and error exercises with the indexes on the table; including, the creation of a compound index as Adam suggested.

    Is their anything of particular importance I should be aware of when reviewing/obtaining an execution plan from within Query Analyzer

    Thanks again,
    Orlanzo

    BTW - I'm using SQL Server 2000.

    select tbl_mis_Dashboard.loan_active_flg,
    tbl_mis_Dashboard.Month_loan_create_dt AS [Month Create Date],
    tbl_mis_Dashboard.Year_loan_create_dt AS [Year Create Date],
    tbl_mis_Dashboard.loan_id,
    tbl_mis_Dashboard.company_id,
    convert(char(10), tbl_mis_Dashboard.loan_create_dt,101) AS [create date],
    convert(char(10),tbl_mis_Dashboard.loan_create_dt,101) as [Loan Create Date],
    tbl_mis_Dashboard.loan_orig_company_id,
    tbl_mis_Dashboard.loan_purpose_cd,
    tbl_mis_Dashboard.loan_purpose_label,
    tbl_mis_Dashboard.loan_doc_type_cd,
    tbl_mis_Dashboard.loan_doc_type_label,
    tbl_mis_Dashboard.loan_loan_amt AS loan_loan_amt,
    tbl_mis_Dashboard.loan_note_rate AS loan_note_rate,
    tbl_mis_Dashboard.loan_lien_position,
    tbl_mis_Dashboard.loan_acct_number,
    tbl_mis_Dashboard.product_type_label,
    tbl_mis_Dashboard.product_label,
    tbl_mis_Dashboard.broker_location_id,
    tbl_mis_Dashboard.broker_id,
    tbl_mis_Dashboard.Branch_Investor_Name,
    tbl_mis_Dashboard.[Virtual Branch] AS [Virtual Branch#],
    tbl_mis_Dashboard.broker_status_type_cd,
    tbl_mis_Dashboard.broker_status_type_label,
    tbl_mis_Dashboard.d1_online,
    tbl_mis_Dashboard.loan_loan_amt
    * tbl_mis_Dashboard.loan_note_rate
    AS [WAC loan amt x loan rate],
    tbl_mis_Dashboard.loan_loan_amt AS [loan_loan_amt IS Null],
    tbl_mis_Dashboard.loan_note_rate AS [loan_note_rate IS NULL],
    tbl_mis_Dashboard.employee_name,
    tbl_mis_Dashboard.orig_type,
    tbl_mis_Dashboard.loan_LTV,
    tbl_mis_Dashboard.loan_loan_amt
    * tbl_mis_Dashboard.loan_LTV AS [WALTV loan amt x loan LTV],
    tbl_mis_Dashboard.property_city,
    tbl_mis_Dashboard.property_state_cd,
    Left(tbl_mis_Dashboard.property_zip,5) AS property_zip,
    isnull(tbl_mis_Dashboard.loan_loan_grade_cd,'IS NULL')
    AS loan_loan_grade_cd,
    tbl_mis_Dashboard.loan_orig_company_label,
    convert(char(10),tbl_mis_Dashboard.rate_sheet_date,101) as [Rate Sheet Date],
    convert(char(10),tbl_mis_Dashboard.loan_D1_combined_fund_dt,101) as [Loan D1 Combined Fund Date],
    convert(char(10),tbl_mis_Dashboard.loan_D1_combined_fund_dt,101) AS [FUND Date],
    tbl_mis_Dashboard.broker_location_name,
    tbl_mis_Dashboard.lsh_status_label,
    tbl_mis_Dashboard.property_structure_label,
    tbl_mis_Dashboard.property_occupancy_label,
    tbl_mis_Dashboard.loan_CLTV,
    tbl_mis_Dashboard.property_rural_flg,
    tbl_mis_Dashboard.first_time_flg,
    tbl_mis_Dashboard.[Primary Credit Score]
    * tbl_mis_Dashboard.loan_loan_amt AS [FICO Step1],
    tbl_mis_Dashboard.[Primary Credit Score],
    tbl_mis_Dashboard.FUND_Date_Flag AS [FUND Date Flag],
    abs(datediff("d",tbl_mis_Dashboard.loan_D1_combined_fund_dt,
    tbl_mis_Dashboard.loan_create_dt)) AS [Days to Fund],
    tbl_mis_Dashboard.[PrePay Flag] AS [PrePay Flag],
    tbl_mis_Dashboard.account_executive,
    tbl_mis_Dashboard.BROKER_LOC_street_city AS broker_street_city,
    tbl_mis_Dashboard.BROKER_LOC_street_state_cd AS broker_street_state_cd,
    tbl_mis_Dashboard.[Current AE assigned to Broker Loc ID],
    abs(Datediff("d",tbl_mis_Dashboard.loan_create_dt,
    tbl_mis_Dashboard.rate_sheet_date)) AS LPE_Honor_Period,
    tbl_mis_Dashboard.[Sales Manager LL],
    tbl_mis_Dashboard.loan_debt_ratio,
    tbl_mis_Dashboard.gross_margin_disp,
    tbl_mis_Dashboard.BROKER_LOC_create_dt,
    tbl_mis_Dashboard.broker_comp
    FROM tbl_mis_Dashboard
    WHERE tbl_mis_Dashboard.loan_active_flg = 1
    AND [tbl_mis_Dashboard].[Month_loan_create_dt]
    Between Month(getdate())-13 And Month(getdate())
    AND [tbl_mis_Dashboard].[Year_loan_create_dt]=Year(getDate())



  • who_am_I

    It's hard to say, since we don't know what data is in the table or what columns you're selecting (that's just as important as what's being used in the WHERE clause).  You might try adding the loan_active_flg and any columns in the SELECT list to your index on [loan_create_dt], assuming it's a non-clustered index.  If it's a clustered index, then perhaps a table scan is the most efficient way to get the data for this query -- sometimes, it is.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     
    Hello All,

    Is their any way I can avoid a Table Scan by modifying the follow WHERE clause

    WHERE tbl_mis_Dashboard.loan_active_flg = 1
    AND [tbl_mis_Dashboard].[loan_create_dt]
    between DATEADD(dd, DATEDIFF(dd,0,dateadd(mm,-13,getdate())), 0)
    And getdate()

    The field loan_create_dt is indexed on the table tbl_mis_Dashboard. The query returns approximately 256K records which is appropriate and necessary.

    Thanks all,
    Orlanzo

  • k m

    Orlanzo,
     
    Yes, there are a lot of things you should be aware of.  It's actually relatively straightforward once you get the hang of it, but there is a bit of a learning curve.  I recommend doing some reading on www.sql-server-performance..com -- they have some good intro material on the topic.  You might also get a good book.  I recommend Ken England's "Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook".  Another one that a lot of people seem to like is "SQL Server Query Performance Tuning Distilled, Second Edition" by Sajal Dam.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     
    Paul, Adam:

    Thank you both for your input. It is very much appreciated. The query I provided earlier was not the one in question; however, as you mentioned Paul - denormalizing the data for reporting would appear to be beneficial in that case. Below is the SQL corresponding o the WHERE clause of my original post. In this situation I'm pulling from a single table.

    Paul - I'll review the execution plan and do some trial and error exercises with the indexes on the table; including, the creation of a compound index as Adam suggested.

    Is their anything of particular importance I should be aware of when reviewing/obtaining an execution plan from within Query Analyzer

    Thanks again,
    Orlanzo

    BTW - I'm using SQL Server 2000.

    select tbl_mis_Dashboard.loan_active_flg,
    tbl_mis_Dashboard.Month_loan_create_dt AS [Month Create Date],
    tbl_mis_Dashboard.Year_loan_create_dt AS [Year Create Date],
    tbl_mis_Dashboard.loan_id,
    tbl_mis_Dashboard.company_id,
    convert(char(10), tbl_mis_Dashboard.loan_create_dt,101) AS [create date],
    convert(char(10),tbl_mis_Dashboard.loan_create_dt,101) as [Loan Create Date],
    tbl_mis_Dashboard.loan_orig_company_id,
    tbl_mis_Dashboard.loan_purpose_cd,
    tbl_mis_Dashboard.loan_purpose_label,
    tbl_mis_Dashboard.loan_doc_type_cd,
    tbl_mis_Dashboard.loan_doc_type_label,
    tbl_mis_Dashboard.loan_loan_amt AS loan_loan_amt,
    tbl_mis_Dashboard.loan_note_rate AS loan_note_rate,
    tbl_mis_Dashboard.loan_lien_position,
    tbl_mis_Dashboard.loan_acct_number,
    tbl_mis_Dashboard.product_type_label,
    tbl_mis_Dashboard.product_label,
    tbl_mis_Dashboard.broker_location_id,
    tbl_mis_Dashboard.broker_id,
    tbl_mis_Dashboard.Branch_Investor_Name,
    tbl_mis_Dashboard.[Virtual Branch] AS [Virtual Branch#],
    tbl_mis_Dashboard.broker_status_type_cd,
    tbl_mis_Dashboard.broker_status_type_label,
    tbl_mis_Dashboard.d1_online,
    tbl_mis_Dashboard.loan_loan_amt
    * tbl_mis_Dashboard.loan_note_rate
    AS [WAC loan amt x loan rate],
    tbl_mis_Dashboard.loan_loan_amt AS [loan_loan_amt IS Null],
    tbl_mis_Dashboard.loan_note_rate AS [loan_note_rate IS NULL],
    tbl_mis_Dashboard.employee_name,
    tbl_mis_Dashboard.orig_type,
    tbl_mis_Dashboard.loan_LTV,
    tbl_mis_Dashboard.loan_loan_amt
    * tbl_mis_Dashboard.loan_LTV AS [WALTV loan amt x loan LTV],
    tbl_mis_Dashboard.property_city,
    tbl_mis_Dashboard.property_state_cd,
    Left(tbl_mis_Dashboard.property_zip,5) AS property_zip,
    isnull(tbl_mis_Dashboard.loan_loan_grade_cd,'IS NULL')
    AS loan_loan_grade_cd,
    tbl_mis_Dashboard.loan_orig_company_label,
    convert(char(10),tbl_mis_Dashboard.rate_sheet_date,101) as [Rate Sheet Date],
    convert(char(10),tbl_mis_Dashboard.loan_D1_combined_fund_dt,101) as [Loan D1 Combined Fund Date],
    convert(char(10),tbl_mis_Dashboard.loan_D1_combined_fund_dt,101) AS [FUND Date],
    tbl_mis_Dashboard.broker_location_name,
    tbl_mis_Dashboard.lsh_status_label,
    tbl_mis_Dashboard.property_structure_label,
    tbl_mis_Dashboard.property_occupancy_label,
    tbl_mis_Dashboard.loan_CLTV,
    tbl_mis_Dashboard.property_rural_flg,
    tbl_mis_Dashboard.first_time_flg,
    tbl_mis_Dashboard.[Primary Credit Score]
    * tbl_mis_Dashboard.loan_loan_amt AS [FICO Step1],
    tbl_mis_Dashboard.[Primary Credit Score],
    tbl_mis_Dashboard.FUND_Date_Flag AS [FUND Date Flag],
    abs(datediff("d",tbl_mis_Dashboard.loan_D1_combined_fund_dt,
    tbl_mis_Dashboard.loan_create_dt)) AS [Days to Fund],
    tbl_mis_Dashboard.[PrePay Flag] AS [PrePay Flag],
    tbl_mis_Dashboard.account_executive,
    tbl_mis_Dashboard.BROKER_LOC_street_city AS broker_street_city,
    tbl_mis_Dashboard.BROKER_LOC_street_state_cd AS broker_street_state_cd,
    tbl_mis_Dashboard.[Current AE assigned to Broker Loc ID],
    abs(Datediff("d",tbl_mis_Dashboard.loan_create_dt,
    tbl_mis_Dashboard.rate_sheet_date)) AS LPE_Honor_Period,
    tbl_mis_Dashboard.[Sales Manager LL],
    tbl_mis_Dashboard.loan_debt_ratio,
    tbl_mis_Dashboard.gross_margin_disp,
    tbl_mis_Dashboard.BROKER_LOC_create_dt,
    tbl_mis_Dashboard.broker_comp
    FROM tbl_mis_Dashboard
    WHERE tbl_mis_Dashboard.loan_active_flg = 1
    AND [tbl_mis_Dashboard].[Month_loan_create_dt]
    Between Month(getdate())-13 And Month(getdate())
    AND [tbl_mis_Dashboard].[Year_loan_create_dt]=Year(getDate())

  • MarkH001

    Actually, GETDATE() will only be calculated once if used in-line in the WHERE clause.  The following batch illustrates this behavior:
     
    ---
    CREATE TABLE #xyz (x INT)
     
    INSERT #xyz
    SELECT number
    FROM master..spt_values
     
    CREATE CLUSTERED INDEX IX_X ON #XYZ (x)
     
    SET STATISTICS PROFILE ON
     
    SELECT x
    FROM #xyz
    WHERE x = DATEPART(ms, GETDATE())
     
    SET STATISTICS PROFILE OFF
     
    DROP TABLE #xyz
    ---
     
    If you look at the profile output, you'll see that this plan uses an index seek; that would be impossible if the DATEPART expression (and, therefore, GETDATE()) had to be re-calculated for every row.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    While it may not avoid a table scan, if your query is in a stored procedure, you could do something like this:

    Declare @DT datetime

    Set @DT = getDate()

    WHERE tbl_mis_Dashboard.loan_active_flg = 1
    AND [tbl_mis_Dashboard].[loan_create_dt]
    between DATEADD(dd, DATEDIFF(dd,0,dateadd(mm,-13,@DT)), 0)
    And @DT

    I've seen this costing about a third of the other way. Reason getDate() isn't deterministic, therefore has to compute on every row.

  • Alex Bell

    Those are estimates only, and the worse I/O and CPU estimates are based on the non-determinism of GETDATE(), so they're worst-case scenarios.   More interesting is to take a look at real numbers...  If I run the following batch on my end, the GETDATE version actually outperforms the variable version on some runs, depending on how many rows are returned (it's impossible, unfortunately, to compare them 1:1, since GETDATE() and the value of the variable will never be the same, and the two always return different row counts -- you should run this a few times and compare):
     
     
    ---
    DECLARE @DT datetime
    SET @DT = getDate()
     
    CREATE TABLE #xyz (Date1 DateTime, Date2 DateTime)
     
    INSERT #xyz
    SELECT DateAdd(ms, a.number * -1, @DT), DateAdd(ms, b.number, @DT)
    FROM master..spt_values a, master..spt_values b
     
    CREATE CLUSTERED INDEX IX_X ON #XYZ (Date1, Date2)
     

    RAISERROR('QUERY 1', 10, 1) WITH NOWAIT
    RAISERROR('---------------------------------------------------------', 10, 1) WITH NOWAIT
     
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
     
    SELECT Date1, Date2
    FROM #xyz
    WHERE Date1 BETWEEN Date2 AND @DT
     
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
     
    RAISERROR('QUERY 2', 10, 1) WITH NOWAIT
    RAISERROR('---------------------------------------------------------', 10, 1) WITH NOWAIT
     
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
     
    SELECT Date1, Date2
    FROM #xyz
    WHERE Date1 BETWEEN Date2 AND GETDATE()
     
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
     
    DROP TABLE #xyz
    ---
     
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     

    Actually this is closer to the original problem, a GetDate() with a between. Show execution plan and you will see the cost diff. You can reverse the queries and get the same results, so it isn't benefitting from cache.

    DECLARE @DT datetime

    SET @DT = getDate()

    CREATE TABLE #xyz (Date1 DateTime, Date2 DateTime)

    INSERT #xyz
    SELECT DateAdd(ms, number * -1, @DT), DateAdd(ms, number, @DT)
    FROM master..spt_values

    CREATE CLUSTERED INDEX IX_X ON #XYZ (Date1, Date2)

    SET STATISTICS PROFILE ON


    SELECT Date1, Date2
    FROM #xyz
    WHERE Date1 BETWEEN Date2 AND GETDATE()

    SELECT Date1, Date2
    FROM #xyz
    WHERE Date1 BETWEEN Date2 AND @DT

    SET STATISTICS PROFILE OFF

    DROP TABLE #xyz
  • How To Avoid Table Scan
Answer this question