between clause taking longtime

[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 we write the query as below, giving the dates as manually , it is running very fast less than 1 min:

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

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 CONVERT(VARCHAR(20),DATEADD(wk,DATEDIFF(wk,'1900-01-08',GETDATE()),'1900-01-01'),111) and CONVERT(VARCHAR(20),DATEADD(wk,DATEDIFF(wk,'1900-01-08',GETDATE()),'1900-01-07'),111)

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 last week and pass the date dynamically but it is very long time to execute. Is thier any setting missing which is causing this issue or do I need to specify before I run the query

[Former Member]

If you use very complex build parameters for query, you need to build the SQL and execute it with EXECUTE IMMEDIATE:

begin
  declare aSQL long varchar;
  set aSQL = string('SELECT sum(a.marks) 
                       FROM students a 
                      WHERE a.daily_Dte between ''',
                     CONVERT(VARCHAR(20),DATEADD(wk,DATEDIFF(wk,'1900-01-08',GETDATE()),'1900-01-01'),111),
                     '''  and ''',
                      CONVERT(VARCHAR(20),DATEADD(wk,DATEDIFF(wk,'1900-01-08',GETDATE()),'1900-01-07'),111),
                     '''');
  EXECUTE IMMEDIATE WITH RESULT SET ON aSQL;
end;

[VolkerBarth]

Sha's query does not need to be constructed via dynamic SQL (aka EXECUTE IMMEDIATE) - and it won't perform better that way, either...

[VolkerBarth]

In addition to Justin's answer:

There's no need (but a performance penalty) to convert the range condition from datetime/timestamp (i.e. the return type of DATEADD()) to a varchar when the compared column type is a datetime/date/time type itself.

[justin_willey]

You don't say what indexes you have on the table STUDENTS, but presumably there is one on daily_Dte.

I suspect your problem is that the complicated expressions you use to derive the dates are not sargable (ie SQL Anywhere cannot use the index but must use a table scan.) This would be confirmed or not by looking at the query plan, which you can get from ISQL. The exact way of doing this depends on which version of SQL Anywhere you are using.

Using a simpler expression to get the dates may help eg:

SELECT sum(a.marks) FROM students a WHERE a.daily_Dte between dateadd(day,-7,current date) , dateadd(day,-1,current date)

as this avoids data type conversions. It would be worth posting here which version of SQL Anywhere you are using because sargability of expressions has changed over time. If you aren't sure just run select @@version

Going forward, it might also be worth considering a clustered index, but that won't help if your index isn't being used!

---

UPDATE - This all assumes that daily_Dte is defined as a DATE, but I presume it would have to be for your between condition to work properly 🙂

[Former Member]

Hello Justin,

Issue with index couldn't be a problem bcoz when we give dates manually like this ( '2014-01-20' ) AND ( '2014-01-26' ) , it is working fine and I am getting results in less than 2 secs.

Now I changed the query to get the date for only single day like this and even this is taking time

WHERE a.daily_Dte BETWEEN dateadd(day,-1,getdate()) AND getdate()

I am new to sybase not sure If I am posted this in correct forum. 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

Regards, Sha

[justin_willey]

Hi

I'm afraid that you are in the wrong forum for answers on ASE - this forum is about SQL Anywhere which is an entirely different database engine - you should have a look at the SAP Community forums here: http://scn.sap.com/community/sybase-adaptive-server-enterprise/content

I would still recommend looking at the query plan to see what is happening, but how you do that in ASE I couldn't tell you.