-1

I have a large query which I am trying top optimize. One of the condition which I would like to change is as shown below. i.e I am trying to remove scalar functions.

Current:

DATE( ACTIVITY.CREATED) ='2009-02-18'

to

ACTIVITY.CREATED BETWEEN  '2009-02-18 00:00:00' AND '2009-02-18 24:00:00'

After checking the Explain plan I could not see much improvement. In fact Estimated Cost to 4532.601074 from 4532.380859

Any advise will be helpful.

Vinayak Dornala
  • 1,609
  • 1
  • 21
  • 27
  • ehh You don't eliminate scalar functions you write them in line just like you did. For example if your retreiving a part description with a custom scalar function description(partno) you simply join to the partsDescription file and display the description column. If you have any sub selects remove them and rewrite as a join or common table expression and FGS remove any like statements that start with a wildcard. – danny117 Feb 27 '15 at 22:55
  • Advice will require *at least* seeing the query along with any relevant data definitions. After that, it might be necessary to see what indexes exist. And at most it might require seeing the Explain. – user2338816 Feb 28 '15 at 01:24
  • @danny117 - Using a function on a condition usually means the optimizer can't satisfy the check via an index (supposing a relevant one exists, which may be unlikely in this case). @Vinayak - I'm not sure if that `BETWEEN` is doing what you expect. Remember that the upper bound is inclusive, so it may be including any rows from midnight of the next day (I'm not sure if `24:00:00` sorts less than `00:00:00`...). Please, do yourself a favor and just [use an exclusive upper bound](http://www2.sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx). – Clockwork-Muse Mar 02 '15 at 14:09
  • it would be 23:59:59-9999999 or so. – danny117 Mar 02 '15 at 15:19

1 Answers1

0

Why do you want to remove that function? Eliminating the DATE() function in that predicate will only make a difference if the optimizer determines that an existing index on the CREATED column will provide a better access path. If there is no such index, or if the optimizer has selected a better index (for the other predicates in your query), then it's not surprising that you get the results you found.

Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25