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
You don't say what indexes you have on the table
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:
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
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