SQL query taking very long to execute

Question

I have two versions of the same query which I'm trying to execute on SQL Server 2008.

Version 1:

select 
    count(a.ordernumber) as Orders, 
    sum(b.agentfees) as AgentFees, 
    sum(a.revenue) as Revenue,
    CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date) as Date
from 
    orders a 
join 
    [DC-SQL-V2].FaxFile.dbo.[agent fees] b on a.ordernumber = b.fforder
where 
    a.closeoutdate >= '2014-01-01'
    and b.dtcreated >= '2014-01-01'
    and a.closeoutdate < '2015-01-01'
    and b.dtcreated < '2015-01-01'
    and a.processserving = 1
    and a.branchno = '116'
group by
    CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date)
order by
    CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date)

Version 1 takes around 3 hours to finish which is obviously insane. So I created Version 2 in which I tried to filter the tables first before joining them, but this still takes pretty long (2 hours and still going).

Version 2:

select 
    count(a.ordernumber) as Orders, 
    sum(b.agentfees) as AgentFees, 
    sum(a.revenue) as Revenue,
    CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date) as Date
from 
    (select 
         ordernumber, revenue, closeoutdate, processserving, branchno
     from 
         ORDERS
     where 
         closeoutdate >= '2014-01-01'
         and closeoutdate < '2015-01-01'
         and branchno = '116'
         and processserving = 1) a 
join 
    (select 
         agentfees, fforder, dtcreated
     from 
         [DC-SQL-V2].FaxFile.dbo.[agent fees]
     where 
         dtcreated >= '2014-01-01'
         and dtcreated < '2015-01-01') b on a.ordernumber = b.fforder
group by
    CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' +  CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date)
order by
    CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date)

Here are some additional details:

select count(*) from [DC-SQL-V2].FaxFile.dbo.[agent fees]

779531

select count(*) from ORDERS

3466648

Here are the clustered columns for both tables (among others that I'm not selecting):

ORDERS:

BranchNo
Closeoutdate
ProcessServing

AGENT FEES:

Nothing I'm selecting is clustered

Would someone please be able to suggest how I can improve the performance of my queries?

Answer 1

The costly part is where you are getting detail data from the foreign server and then summarizing it. You should summarize it their and then only bring that back. It is not clear to me how "dtcreated" and "closeoutdate" are related?

Below I summarized the data by month and then brought it over to the local server. So only 12 rows are coming over instead of one per order. This may not be what you want if the fee should be counted in the "closeoutdate" month. But your code had a problem where it would never count an order or fee if there dates were in different years. (Need to clarify what is needed with that.)

select 
    a.CloseMonth,
    a.Orders,
    b.AgentFees
    a.Revenue
   from 

   (select year(closeoutdate) * 100 + month(closeoutdate) as CloseMonth,
    count(a.ordernumber) as Orders, 
    sum(a.revenue) as Revenue
    from orders
         where closeoutdate >= '2014-01-01'
         and closeoutdate < '2015-01-01'
         and branchno = '116'
         and processserving = 1
         group by year(closeoutdate) * 100 + month(closeoutdate)) as a 

    join 
    (select year(dtcreated) * 100 + month(dtcreated) as FeeMonth,
        sum(agentfees) AgentFees
     from [DC-SQL-V2].FaxFile.dbo.[agent fees]
         where 
             dtcreated >= '2014-01-01'
             and dtcreated < '2015-01-01'
             group by year(dtcreated) * 100 + month(dtcreated)) b 
    on a.CloseMonth = b.FeeMonth
    order by a.CloseMonth

Answer 2

Assuming [DC-SQL-V2] is a remote, linked server, I'd recommend bringing that data local first.

/* Making broad assumptions here. Substitute correct data types */
CREATE TABLE #tmpAgentFees (
    agentfees money, 
    fforder int, 
    dtcreated datetime
);

INSERT INTO #tmpAgentFees
    (agentfees, fforder, dtcreated)
    SELECT agentfees, fforder, dtcreated
        FROM [DC-SQL-V2].FaxFile.dbo.[agent fees]
        WHERE dtcreated >= '2014-01-01'
            AND dtcreated < '2015-01-01';

/* Optionally, add an index to perhaps help performance of the join */
CREATE INDEX IX_tmpAgentFees_fforder 
    ON #tmpAgentFees(fforder)
    INCLUDE (agentfees, dtcreated);

select count(a.ordernumber) as Orders, sum(b.agentfees) as AgentFees, sum(a.revenue) as Revenue,
CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date) as Date
from orders a 
    join #tmpAgentFees b
        on a.ordernumber = b.fforder
where a.closeoutdate >= '2014-01-01'
    and a.closeoutdate < '2015-01-01'
    and a.processserving=1
    and a.branchno='116'
group by
CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date)
order by
CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date)

Answer 3

There are some general hints

1. use SQL Server Profiler to find the most costly part
2. use an index on join column fforder
3. CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate) as varchar) as Date) is repeated again and again, if it is possible create a computer column in your table and store the value and create an index on this new column with include in index option on

4. if 3 is not possible use drive table for example as below

   select * from
   (
       select col1,col2,..,CAST('1/' + CAST(DATEPART(Month, a.closeoutdate) as varchar) + '/' + CAST(DATEPART(Year, a.closeoutdate)  as varchar) as Date) from table1 ) drivedTable
   join .....