Failed attempt at using shortcutting to produce efficient query
-
I've spent the last few hours trying to get the second query below to complete without doing any disk work. You can see from the query that logically it cannot produce any rows. The first query is successfully returned without doing any disk work, but I can't get the one with the union to do it. If anyone can think of a way of doing this I would be very appreciative.
'Date' is just a large table in my database, it contains dates but it could be any table really.
Query 1:
Query 2:
As you can see from the execution plans, the first query only a contant scan is performed (whatever that means) but in the second a couple of unnecessary index scans are performed.
Any ideas?
/******* Why am I doing this? **********/
I have a number of inline functions that I want to cross apply to a set of data, only, depending on the data in each row the inline functions don't necessarily need to do any work. Was hoping that I could cross apply to a union of all the results of the functions and use shortcutting to prevent any that don't return rows doing any work. If I can't get this to work then I will have to use a cursor, and not call the functions that I know will not return rows....
-
Are you sure using a cursor would be faster than letting some functions that don't return rows do some work? If most of the functions return rows most of the time you should go with what works fastest most of the time. I'm not sure cursor would be faster even if you frequently have functions that don't return rows.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams -
Yes I have a maximum of about 10 rows, that I need to cross apply to functions. I know whether the functions will return rows or not before they get called. A cursor that selectively calls them will definately be faster than just calling all of them for each row.
-
In the end I changed the functions I was cross applying to NOT be inline functions. This means that the initial dataset (small) is brought back first and then the functions are called in a row-by-row fashion, if they do not need to be called they are not called. Not as neat as an inline solution as is esssentially using temp tables behind the scenes?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply