How can I optimise this Query?

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.

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.

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.

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().

In the below way you can optimize the Baan Query

• In Where condition use indexes and combine field if possible.
• In where condition Use "Between/Inrange" when upper and lower limit specified.
• Use "Refers To" if reference is available in data dictionary
• Use few overlapping "Or" condition as possible
• Use only selected field of table in select statement, Which is actually required.
• Use "Order by" to get record in correct sorting format
• If possible Don't use NOT INRANGE,BETWEEN,IN operators because that operator can scan full table.
• Use commit.transaction() to prevent line being print twice.