May 22, 2009 at 8:06 am
i have two views, viewA and viewAOptimised that return exactly the same number of rows. ViewA makes use of a UNION to get a value from one of the tables, however in viewAOptimised i am making use of a function trying to optimize searches.
viewA:
select .... FROM x UNION y WHERE x.i IS NULL
UNION
select .... FROM x UNION y WHERE x.i IS NOT NULL
viewAOptimised:
select ....fnOpt(x.i) FROM x
fnOpt: only scans table y if x.i is not null else it returns NULL. This is because the column x.i is mostly null.
I am surprised. The logical reads using viewA are higher than those using viewAOptimised however, the CPU time when using viewAOptimised is always high even when column x.i is all null. What could be causing this?
Thank you
May 22, 2009 at 10:47 am
This could depend on the events you track. Maybe Gail's article brings light into darkness:
http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/
May 25, 2009 at 12:47 am
Hi Florian,
Thank you very much for the two links. They have already given me a lot of insight. I will definitely change the way i specify my problems.
🙂
May 25, 2009 at 1:02 am
Glad that we could help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply