2

I have two inline functions that run quickly enough alone.

When I left join them like this:

select *
from
Function1(-1) F1 
left join
Function2(-1) F2 on F1.key = F2.key

...I get my result set in approx 14 seconds. Perfectly fine for what it's doing.

But if I add a where condition:

Where 
F2.Boolean = 0

...then it takes over 8 minutes to complete.

There are some results that do not have a F2 record so those are null.

I did a test with isnull(f2.boolean,0) = 0 and that runs in 10 seconds, also if a place a or F2.boolean is null it runs quickly.

I am using SQL Server 2008 R2, and tested on SP2 and SP3. Does anyone have ideas or suggestions for what to look at?

The functions are simple select statements joining multiple tables. Nothing special in them. Each individual function returns results of the entire tables in under 10 seconds.

When I add "where one of the date fields in F2 is between two dates", that runs fast. I also just tested on a varchar(2) field in the where clause, and this causes it to be extremely slow as well (but not the datetime field). ok, Why would a filter on a bit field or varchar from the second function cause such a speed decrease, vs a datetime between? Another observed behavior: If I add a filter on F1, to specify only ones that actually have a F2 record first then the original filter on F2 it runs quickly again.

  • 3
    The optimizer can choose a very different plan, especially when you are joining like 10+ tables. Different can mean faster or slower. Without the actual functions, the underlying tables structure, indexes, statistics and the actual execution plans, I think you'll only get guesses, not answers. – ypercubeᵀᴹ Mar 17 '15 at 16:57
  • @Jweaver: You need to check actual execution plan in each case to get an exact answer. I'd guess having WHERE on low cardinality column (such as bit or varchar2) makes impossible to use index even if it exists. Quite opposite with datetime column which I expect to have way higher cardinality. Even without index SQLServer may have statistics on this column which causes choosing much better and faster execution plan. – a1ex07 Mar 17 '15 at 17:04
  • Post some actual, post-execution plans, please. Much easier to inspect those than try to reverse engineer "runs slower" and "runs quickly again." – Aaron Bertrand Mar 17 '15 at 17:35
  • what format is the best way to show the exection plan here – Jweaver Mar 17 '15 at 17:45
  • @Jweaver The XML form of showplan is best. Upload it to somewhere like gist or pastebin and add a link to your question. – Paul White Apr 18 '16 at 16:21
1

Workaround suggestion:

If you can change the functions, try making them multi-statement table value function instead of inline functions. When doing so, you define an output table in the RETURNS part. In the DDL of the output table, declare a good clustered primary key.

Example:

CREATE FUNCTION dbo.fn_test1(@i int)
RETURNS @out TABLE (
    a    char(5) NOT NULL,
    b    date NOT NULL,
    c    int NOT NULL,
    PRIMARY KEY CLUSTERED (a, b)
)
AS

BEGIN;
    INSERT INTO @out (a, b, c)
    SELECT x, y, z
    FROM dbo.someTable
    WHERE someCriteria>@i;

    RETURN;
END;

Even though swapping an inline function for a multi-statement function comes with its own set of advantages and drawbacks, an important difference in your case may be that the output is stored in a temp table with a clustered index before the join happens. If you've properly aligned the clustered indexes on the two functions, joining their output tables should be a speedy affair.

Here's an idea of what kind of query plan you could expect (excluding the inner workings of the functions, obviously).

Example query plan

0

Most times this situation comes out from statistic on field "Boolean". Remember that statistics may come from index and can be create automatically or manually. Be sure that statistics on this field are updated. In SQL below 2014 (Cardinality Estimator below 120) values out of statistics has estimation of 0 and this value might be out of statistics because statistics is not updated or sample of update does not take rows with value filtered in your query. You can walkaround this by using variable for value of this column and use OPTION (OPTIMIZE FOR(@ariable UNKNOWN)). In this way statistics are not considered since this will be no longer parameter to the execution plan.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.