I'm working on a query that selects counts from 2 servers and joins those together. Here is what I have:
SELECT a.NAME , SUM(ISNULL(b.ttl,0)) AS [Total] FROM TableFunction(@user, DEFAULT) a -- Selecting NAME from Table1 takes <1s LEFT JOIN ( -- Sub-Query 1 SELECT COUNT(*) ttl , NAME FROM Table2 c WHERE active=1 GROUP BY NAME -- Sub-Query 1 (< 1s, 662 rows) UNION ALL -- Sub-Query 2 SELECT COUNT(*) ttl , jt.NAME FROM OtherServer.dbase.dbo.Table3 d INNER JOIN OtherServer.dbase.dbo.JoiningTable jt ON d.ID=jt.ID WHERE ISNULL(d.DELETED,0)=0 GROUP BY jt.NAME -- Sub-Query 2 (7s, 5576 rows) -- Full Sub-Query [1 UNION ALL 2] (7s, 6238 rows) ) b ON b.NAME=a.NAME GROUP BY a.NAME --Full query (3:44, 91 rows)
The Execution plan suggests that the problem comes from the remote query (Sub-Grid 2).
How can I improve the runtime of this? Why does a 7 second sub-query take over 3 minutes when joined on a basically instant query?
After some commenting out random things in hopes of finding something useful, it appears to be specifically the
WHERE ISNULL(d.DELETED,0)=0 bit. Removing that line improves query to 4s. Why would this help at all?
Playing around more, it seems the fact that the first table is a table-valued function may have been important. Selecting it's result table into a temp table and using that temp table brings the query down to 8s. The table-valued function code is:
--Inputs @user varchar(50) , @includeDeleted BIT = 0 --Function RETURNS @tbl table (ID int , NAME varchar(max) , DESCRIPTION varchar(max)) AS BEGIN INSERT INTO @tbl (ID, NAME, DESCRIPTION) ( select jt.ID, jt.NAME, jt.DESCRIPTION from OtherServer.dbase.dbo.JoiningTable jt where jt.USER = (select top 1 USER from OtherServer.dbase.dbo.USERS where user_name = @user) and 1=CASE WHEN @includeDeleted=1 THEN 1 ELSE CASE WHEN ISNULL(jt.Deleted, 0) <> 1 THEN 1 ELSE 0 END END ) INSERT INTO @tbl (ID, NAME, DESCRIPTION) ( SELECT ID, name, '' FROM RandomTable rt INNER JOIN RandomJoinTable rjt ON rjt.ID = rt.ID AND rjt.user = @user LEFT JOIN @tbl t ON t.ID = rt.ID WHERE 1=CASE WHEN @includeDeleted=1 THEN 1 ELSE CASE WHEN ISNULL(rt.Deleted,0)=0 THEN 1 ELSE 0 END END AND t.Name IS NULL ) RETURN