CODEWITHSUNDEEP
sqlsql-serveroracle-databaseperformancedblink
Nate Thompson
Nate Thompson

Reputation: 635

SQL Server - Query Taking Long Time with Hard Coded Dates

To preface, this query is using db_links into an oracle environment and is currently being executed in TOAD against the SQL Server database.

I initially had some date variables that the user can enter the date range, and when using these variables, the query executes in 15 seconds. When I hard code the same dates that I was manually inserting into the variables, the query now takes 3.5 minutes.

Why is my query going from 15 seconds to 3.5 minutes when I hard code the dates in rather than using date variables?

Here is the query below: 

select TRANS.FGBTRNH_DOC_CODE,
       '' "calc1",
       TRANS.FGBTRNH_TRANS_DESC,
       case when TRANS.FGBTRNH_DR_CR_IND = 'C' then TRANS.FGBTRNH_TRANS_AMT *-1
              when TRANS.FGBTRNH_DR_CR_IND = 'D' then TRANS.FGBTRNH_TRANS_AMT
              end "calc2",
       CAST(TRANS.FGBTRNH_TRANS_DATE AS DATE) "calc3"
  from [BANTEST.UNWSP.EDU]..FIMSMGR.FGBTRNH TRANS
inner join [BANTEST.UNWSP.EDU]..FIMSMGR.FABINVH INVOICE on TRANS.FGBTRNH_DOC_CODE = INVOICE.FABINVH_CODE

where TRANS.FGBTRNH_ACCT_CODE = '19000'
       and TRANS.FGBTRNH_TRANS_DATE between convert(date,'08/01/2013') and convert(date,'08/31/2013')
       and TRANS.FGBTRNH_DOC_CODE not like 'J%'
       and TRANS.FGBTRNH_TRANS_DESC not like '%AMAZON%'
       and TRANS.FGBTRNH_POSTING_PERIOD <>'00'
       and ( TRANS.FGBTRNH_RUCL_CODE not like 'CA%'
         and TRANS.FGBTRNH_RUCL_CODE not like 'Y%' )

When I use the variables the conditional statement looks like this:

and TRANS.FGBTRNH_TRANS_DATE between :date1 and :date2

Toad then prompts for what the values of :date1 and :date2

Upvotes: 1

Views: 559

Answers (2)

Caff&#233;
Caff&#233;

Reputation: 1171

When you use parameters ("variables"), the server can reutilize the execution plan, as the query is the same as one you've already executed.

If you don't use parameters, every time you change the dates, the server recognizes it as another query and has to build a new execution plan, which takes time.

Upvotes: 0

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

The only thing that stands out is the CONVERT. Please try without it as follows:

TRANS.FGBTRNH_TRANS_DATE between '2013-08-01' and '2013-08-31'

Upvotes: 1

Related Questions