CODEWITHSUNDEEP
sqlsql-serversql-server-2012
JD Roberson
JD Roberson

Reputation: 599

SQL Server Cross Apply speed issue

I have two views on separate servers that I am joining together into one view.

This is my query:

SELECT *
FROM
(
 SELECT v1.Name COLLATE DATABASE_DEFAULT as Name, v1.Year as Year
 FROM AgentCallsByMonth v1
 UNION
 SELECT v2.Name as Name, v2.Year as Year
 FROM BH1PCIMD01.eGActiveDB.dbo.AgentChatsByMonth v2
 ) as u CROSS APPLY (
              SELECT       
                     ISNULL(v1.[Month],v2.[Month]) as [Month],
                     ISNULL(v2.[Chats], 0) AS [Chats], ISNULL(v1.[Calls], 0) AS [Calls]               
              FROM master..spt_values v 
                LEFT JOIN AgentCallsByMonth v1 ON v.number = v1.[MM] AND v1.Name = u.Name and v1.Year=u.Year
                LEFT JOIN BH1PCIMD01.eGActiveDB.dbo.AgentChatCountByMonth v2 ON v.number = v2.[MM] AND v2.Name = u.Name and v2.Year=u.Year
              WHERE v.type = 'P' AND v.number < 12 and u.Year = 2012
              ) o

For some reason this is taking forever. If I pull the data from the tables individually it takes less than 30 seconds. Last run of the query above is at 24 minutes and counting.

A little background on what needs to happen.

I have two tables that have a user name, month in a 00-mon format, and a count. Now some months are null as the user did not have any items in count, thus they do not populate. So we are creating the months and putting 0's in there. However in the other table they could have some items in count for a month they did not have a count in the other table.

For example.

Table 1

JoeUser 01-Jan 48
JoeUser 03-Mar 12
JoeUser 09-Sep 3

Table 2

JoeUser 01-Jan 12
JoeUser 02-Feb 4
JoeUser 05-May 6

This is the resulting output:

JoeUser 01-Jan 48 12
JoeUser 02-Feb 0 4
JoeUser 03-Mar 12 0
JoeUser 04-Apr 0 0
JoeUser 05-May 0 6
JoeUser 06-June 0 0
etc

Upvotes: 0

Views: 503

Answers (1)

SQLGuru
SQLGuru

Reputation: 1099

I agree with all the comments before mine, but something else I should point out is that adding the collation using "COLLATE DATABASE_DEFAULT" - will most prob cause a slow down, especially if you table is rather large. Remember, it has to do this for every single row returned.

Upvotes: 1

Related Questions