SQL Server WITH very slow

Question

I am using SQL Server 2008 and am trying to run:

WITH results (Row, code, p_name, phone, intake_date, shipped_status, shipped_date,
 event_status, intake_status, slsperson, referral_source, dr )
AS
( 
    SELECT ROW_NUMBER() OVER (ORDER BY intake_date ASC)AS Row, code, p_name, phone, 
    intake_date, shipped_status, shipped_date, event_status, intake_status, slsperson, 
    referral_source, dr
    FROM db.schema.sales_referral_list('2012-05-1', '2012-06-1', 'CXJ7')
)
SELECT Row, code as p_id, p_name, phone, intake_status, intake_date, shipped_status,
 shipped_date, event_status, slsperson, referral_source, dr 
FROM results 
WHERE Row BETWEEN 0 AND 50 ORDER BY Row

When I run it without the With AS statement (i.e. just the inner select statement), it executes in about 1 second. When I use the With statement, that I use for pagination on the site, it takes over 15 sec to execute. Is there some optimization I can do to increase the performance on this statement? (sales_referral_list is a table value function that takes 3 parameters. It runs in under 1 sec all the time, so I am relatively sure it is not the problem). Thanks for any help you can give.

==========UPDATE=========

The table value function is as follows: The table value function is as follows:

    (   
-- Add the parameters for the function here
@START DATE,
@END DATE,
@SLSCODE VARCHAR(4)


 )
RETURNS TABLE 
AS
RETURN 
(
-- Add the SELECT statement with parameter references here
    SELECT 
    A.code,
    B.last+', '+B.first AS p_name,
    B.phoneday AS Phone,
    B.slcode,
    B.regdate as intake_date,
    CASE
        WHEN CAST(C.newdate AS  DATE) is not null THEN 'SHIPPED'
        WHEN B.udef1='03' THEN 'NON-SERV'
        ELSE 'NOT SHIPPED'
    END as shipped_status,
    CASE
        WHEN CAST(C.newdate AS  DATE)='1900-01-01' THEN ''
        ELSE CAST(C.newdate AS  DATE)
    END as shipped_date,
    CASE
        WHEN E.status='1' THEN 'ACTIVE'
        WHEN E.status='2' THEN 'COMPLETE'
        WHEN E.status='0' THEN 'DELETED'
        ELSE 'NO EVENT'
    END event_status,
    F.file_status as intake_status,
    D.employee as slsperson,
    B.rfname as referral_source,
    B.dcname as doctor
FROM event.dbo.distinct_account() a
LEFT OUTER JOIN event.dbo.patient_dg() B ON A.code=B.code
LEFT OUTER JOIN event.dbo.newdate() c on a.code=c.ACCOUNT
LEFT OUTER JOIN event.dbo.employee D ON B.slname=D.employee
LEFT OUTER JOIN(
    SELECT
        id,
        patient_id,
        status
    FROM event.dbo.event A
    WHERE A.task_id IN ('WF','WT')
    group by id,patient_id,status
    ) E ON A.code=E.patient_id
LEFT OUTER  JOIN event.dbo.taskWF F ON E.id=F.event_id
    WHERE b.regdate>=@START
AND b.regdate<=@END
AND slcode=@SLSCODE
)

Normal returned results are somewhere between 100 - 500 records. Table Value Function patient_dg has approximately 60,000 records

How many rows are being returned? Your final order by requires all the rows to be generated. You may start to see partial results from the inner query much quicker. — Gordon Linoff, Jun 22 '12 at 18:08
`WITH` is not the cause. It has zero runtime cost. The reason is elsewhere. — usr, Jun 22 '12 at 18:12
Those statements are called **Common Table Expressions** (CTE), and I agree with `usr` - CTE's aren't by default slow or bad - there must be something badly wrong with your table setup and / or indexing (or lack thereof) — marc_s, Jun 22 '12 at 18:14
Include the execution plan and run the query, you can see where the most expensive operations are occuring. This may be an indicator to solving your problem. — Darren, Jun 22 '12 at 18:16
What does your user-defined function `db.schema.sales_referral_list` do?? That would be my first "usual suspect" to look at. Do you have data access inside that UDF? That's **notoriously bad** for performance.... — marc_s, Jun 22 '12 at 18:16
A function in the join like `dbo.distinct_account()` also seems very suspect to me. It appears you've tried to encapsulate code by tucking a bunch of different queries into a bunch of functions. This may make the outer query easier for you to work with, but it royally screws with the optimizer. I would start trying to improve performance by unraveling this nest you've created. This will give the optimizer a much better chance at working with the actual data and statistics that you have, instead of just looking at all these black box functions and shrugging. — Aaron Bertrand, Jun 22 '12 at 18:31
@Arron - Thanks, I removed the nested TVF's and reduced the size of the one I couldn't remove (patient_dg) and execution is down to 2 seconds. Thx everyone for the responses. — Mike, Jun 22 '12 at 19:15

Shintaro Sasaki · Accepted Answer · 2012-07-02T18:50:29.083

You can try to store the results into a temp table (which already implicitly happens in your query in similar way). If you can keep the session thus keeping the #Results temp table, then you can execute the statement with different Row range. It would be quick once #Results is populated.

I forgot to mention, that I guessed the data types of the fields. You probably need to change a few of them.. Matching the types exactly is also helps the performance is what I heard.

CREATE TABLE #WholeResultSet
(
    Id INT NOT NULL PRIMARY KEY,
    [Row] INT NOT NULL,
    p_id INT NOT NULL,
    p_name VARCHAR(MAX) NOT NULL,
    phone VARCHAR(MAX) NOT NULL,
    intake_date DATETIME NOT NULL,
    shipped_status VARCHAR(MAX) NOT NULL,
    shipped_date DATETIME NOT NULL,
    event_status VARCHAR(MAX) NOT NULL,
    intake_status VARCHAR(MAX) NOT NULL,
    slsperson VARCHAR(MAX) NOT NULL,
    referral_source VARCHAR(MAX) NOT NULL,
    dr VARCHAR(MAX)  NOT NULL
) ;

INSERT INTO #WholeResultSet
(
    [Row], p_id, p_name, phone, intake_status, intake_date, shipped_status,
    shipped_date, event_status, slsperson, referral_source, dr 
)
SELECT
    ROW_NUMBER() OVER (ORDER BY intake_date ASC),
    code, p_name, phone,  intake_date, shipped_status,
    shipped_date, event_status, intake_status, slsperson, 
    referral_source, dr
FROM db.schema.sales_referral_list('2012-05-1', '2012-06-1', 'CXJ7') ;

SELECT * FROM #WholeResultSet WHERE Row BETWEEN 0 AND 50 ORDER BY Rownumber ;

SQL Server WITH very slow

1 Answers1