Between Clause taking more time

[Former Member]

Hello All,

I work on business objects reporting tool which gets data from sybase. We got requirement to change date prompts to dynamic which means user gets an input to choose date prompts to select like "Last year,last quater,current month ,last month etc. For example , when user select last week, I am trying to calcualate them from getdate() passing through between clause.

If I write the query as below for 1 day giving the dates as manually , it is running very fast less and I am getting data in seconds

SELECT sum(a.marks) FROM students a WHERE a.daily_Dte BETWEEN ( '2014-01-27' ) AND ( '2014-01-28' )

Now in the "between" clause I have just changed the dates to dynamic way like below and query is taking veryyy long time to execute

SELECT sum(a.marks) FROM students a WHERE a.daily_Dte between dateadd(dd,-1,getdate()) and  getdate()

My Sybase version is: Adaptive Server Enterprise/12.5.4/EBF 15432 ESD#8/P/Sun_svr4/OS 5.8/ase1254/2105/64-bit/FBO/Sat Mar 22 14:38:37 2008

Can anyone help me reason behind the query taking very long time to execute. I have just used simple getdate() function to calculate date for yesterday and pass the date dynamically but it is very long time to execute. Is their any setting missing which is causing this issue or do I need to specify anything before I run the query. This is not as Issue with indexing because the query is running fine when we give the dates manually. Can anyone please help.Thank you

[sap_mk · Accepted solution]

You might try creating @variables.

declare @dte1 datetime, @dte2 datetime

select @dte1 = dateadd(dd,-1,getdate()), @dte2=getdate()

select .... between @dte1 and @dte2

Mark

[Former Member · Accepted solution]

It's still the same...query taking long time

[Former Member · Accepted solution]

Hi,

that is a very old version of ASE!

However, I'd recommend using

  set showplan on

comparing the fast with the slower query.

The documentation is heavy on this subject but it will show you how analyse and optimise these queries.

Also, look at the definition of the table and datatypes, check you have useful indexes.

The search argument types need to match the table datatypes for best performance.

I hope this is useful,

- jon