SQL: Evaluate Customer Type New or Existing with Hierarchical (Related) Customers

Question

I need a more efficient way to evaluate if a customer is new or existing. Criteria for new customer:

Customer, or any related customer, has not placed an order between begin date and end date

I wrote a function to do this, and it works, but it is very slow. We have hierarchical customers with child, parent,...ultimate parent. My function works by taking the ultimate parent of any given customer, selecting all of its children, and then evaluating if an order belonging to any of those children exists in the orders table between the begin date and end date.

The function returns 1 (True) for new customers or 0 (False) for existing customers.

Is there a more efficient way to do this? My function took approximately 20 minutes to run when called on ~3k records.

Function:

set ansi_nulls on
go
set quoted_identifier on
go

create function dbo.is_new_customer (
@ultimate_parent_number nvarchar(255),
@existing_customer_begin_date date,
@existing_customer_end_date date
)
returns int
as
begin
declare @return int

set @return = (
    case
        when not exists (
            select
            t1.customer_number
            from orders_table as t1
            inner join (
                select
                customer_number
                from customers_table
                where ultimate_parent_number = @ultimate_parent_number
            ) as t2
            on t2.customer_number = t1.customer_number
            where t1.order_date between @existing_customer_begin_date
            and @existing_customer_end_date
        )
        then 1
        else 0
    end
)
return @return

end
go

It would probably be more efficient to join to a recursive CTE in your calling query, instead of calling an inline scalar function. — Tab Alleman, May 26 '16 at 15:18

score 0 · Answer 1 · edited May 23 '17 at 11:44

Try it like this using cross apply, also check for indexes, tell management studio to bring the actual execution plan, there you'll probably find recommendations for indexes here is how

set ansi_nulls on
go
set quoted_identifier on
go

create function dbo.is_new_customer (
@ultimate_parent_number nvarchar(255),
@existing_customer_begin_date date,
@existing_customer_end_date date
)
returns int
as
begin
declare @return int

set @return = (
    case
        when not exists (
            select
            t1.customer_number
            from 
            (select
                customer_number
                from customers_table
                where ultimate_parent_number = @ultimate_parent_number) as t2

            cross apply ( select * from orders_table as t1 where t2.customer_number = t1.customer_number) as t1
            where t1.order_date between @existing_customer_begin_date
            and @existing_customer_end_date
        )
        then 1
        else 0
    end
)
return @return

end
go

This may also help you understand how to speed up that query When should I use Cross Apply over Inner Join?

SQL: Evaluate Customer Type New or Existing with Hierarchical (Related) Customers

1 Answers1