How can I optimise this Query ?

Question

How can I optimize this query if given the following query returns either all entries in the table or entries that match only up to current date ? btw: The Query is targeted to a Oracle Linked Server on MS Sql 2005 as an Inline function.. Do not want this to be a table value function..

ALTER function [dbo].[ftsls031nnnHades](@withExpiredEntries bit =0)
 returns table as return    
select * 
  from openQuery(Hades ,"select '010' comno, 
                                trim(t$cuno) t$cuno,
                                trim(t$cpgs) t$cpgs,
                                t$dile,
                                t$qanp,
                                to_char(t$stdt,'dd Mon yy') t$stdt,
                                to_char(t$tdat,'dd Mon yy') t$tdat,
                                to_char(t$disc,'999.99') t$disc,
                                t$damt,
                                t$cdis,
                                t$gnpr,
                                t$refcntd,
                                t$refcntu 
                           from baan.ttdsls031010 
                          where (to_char(t$Tdat,'yyyy-mm-dd') >= To_char(current_date,'yyyy-mm-dd')) 
                            and (to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd')) 
                         union all       
                         select '020' comno, 
                                trim(t$cuno) t$cuno,
                                trim(t$cpgs) t$cpgs,
                                t$dile,t$qanp,
                                to_char(t$stdt,'dd Mon yy') t$stdt,
                                to_char(t$tdat,'dd Mon yy') t$tdat,
                                to_char(t$disc,'999.99') t$disc,
                                t$damt,
                                t$cdis,
                                t$gnpr,
                                t$refcntd,
                                t$refcntu 
                           from baan.ttdsls031020 
                          where (to_char(t$tdAt,'yyyy-mm-dd') >= To_char(current_date,'yyyy-mm-dd')) 
                            and (to_char(t$stdt,'yyyy-mm-dd') <= To_char(current_date,'yyyy-mm-dd'))  ")

p.s: Column naming conventions may be alien to those who are of non BaaN .. Please excuese me for bringing up 'BaaN' conventions into StackOverflow.

Answer 1

Not sure how much this will improve performance, but the first thing I'd do is replace the date to string conversion with just date functions. That is, use trunc() instead of to_char().

Answer 2

Never perform any functional processing of your date column (t$Tdat and t$stdt are of this type, aren't they?) unless you have the corresponding function-based index. This approach doesn't allow you to use indexes on t$stdt and t$Tdat and drops the perfomance dramatically.

Instead, I would rewrite the where clause in the following way:

where t$Tdat >= current_date and t$stdt <= current_date

if current_date is of date type. If it's not, then you can use, for example, to_date(current_date, 'DD-MM-YYYY') instead of it.

Answer 3

Just in case be here now's tip - which is a good one - doesn't work: you'll need to collect some data to know where time is being spent. Please read this OTN-thread to see how to do this for Oracle: http://forums.oracle.com/forums/thread.jspa?messageID=1812597. For SQL Server, the same principles apply: use their tools to find out where this query is spending time on.

Some general information you can share is:

• How many rows are in those two tables
• How many rows are returned by that query
• Which indexes are present on those two tables
• How long does the query currently take
• What response time is acceptable, i.e. when are we done tuning

Regards, Rob.