tsql - joining two tables from different databases hosted on separate servers

Question

I have got 2 databases sitting on different physical servers and linked. I need to join DB1.T1 with DB2.T2 and create an id. The problem is performance. My senior insists using a function and I have created it below.

IF OBJECT_ID (N'dbo.getXXXId', N'FN') IS NOT NULL
    DROP FUNCTION dbo.getTRId;
GO
CREATE FUNCTION dbo.getTRId (@gcPRef bigint)
RETURNS varchar (100)
WITH EXECUTE AS CALLER --may not be necessary. not sure.
AS
BEGIN

     DECLARE @TRID varchar (100);
     SELECT @TRID =  CONVERT(varchar (12), hu2.PropId) 
              + '_'+ CONVERT(varchar (12), c.WSId)
        FROM [172.29.110.133].DB1.dbo.checks c
        join [172.29.110.133].DB1.[dbo].VHier
                          ON VHier.xx= c.xx
        join [172.29.110.133].DB1.[dbo].rvc
                          ON rvc.xx= VHier.xx
                          AND rvc.yy= VHier.yy
        join [172.29.110.133].DB1.[dbo].HUNIT hu
                          ON c.xx= hu.xx
    WHERE c.CheckId = @gcPRef;

     RETURN (@TRID);
END;
GO

I use the query below to query each checkid using the function above.

select getTRId(guestCheckPRef), guestCheckid from DB2.Guest_CHECKS GC
where GC.closeBusinessDate = '2014-06-25'

A couple of things you may like to know:

• DB1 and DB2 are hosted on different physical servers.
• I am not a DBA so please let me know if I am doing anything wrong.
• Approximately 45000 records created daily. so this is the amount of rows..
• I have already tried joining them without involving a function. it takes forever. in 30 seconds, 450 records returned only. I cannot keep tables locked for a long time.
• CONSTRAINT [DB1.PK_CHECK] PRIMARY KEY CLUSTERED
• CONSTRAINT [DB2.XPKGUEST_CHECKS] PRIMARY KEY NONCLUSTERED
• I do not know if constraints are playing a role here. DB2.GUEST_CHECKS.guestCheckPRef is NOT even a FK here. guestCheckPRef is PK in DB1.CHECK.
• performance is very poor. I need to return DB2.propid + DB2.wsid + DB1.guestCheckid.

This is all I can give for now. Any suggestion is appreciated. It does not have to be done with a function.

Thanks in advance. Regards.Oz.

Answer 1

Doing a "select" in a function is generally considered "not a good idea". The select in the function will be repeated once for each row in the result set, which is probably why the performance is bad.

Erp. This was supposed to be a comment, not an answer. To turn this into a proper answer, rewrite the query as a join, without using the function. (I.e. take the contents of the function and integrate it into a single join.)

Your example query should look something like this:

;with getTRID as 
(SELECT CONVERT(varchar (12), hu2.PropId) 
  + '_'+ CONVERT(varchar (12), c.WSId) AS TRID
  FROM [172.29.110.133].DB1.dbo.checks c
join [172.29.110.133].DB1.[dbo].VHier
  ON VHier.xx= c.xx
join [172.29.110.133].DB1.[dbo].rvc
  ON rvc.xx= VHier.xx
  AND rvc.yy= VHier.yy
join [172.29.110.133].DB1.[dbo].HUNIT hu
  ON c.xx= hu.xx)
select getTRId.TRID, guestCheckid from DB2.Guest_CHECKS GC
inner join getTRID ON CheckId = guestCheckPRef
where GC.closeBusinessDate = '2014-06-25'

N.B. I'm working from memory here so, please, no flames for syntax errors! Thx.

Steve G.

Answer 2

Here are a few things to try or consider:

1. Have you checked that the query is using the best available indexes? You could try running the query through the query analyser to see if there's any indexes you could add to improve performance.

2. What version of SQL Server are you running? Depending on the version you might be able to replicate the table from one server to the other to alleviate the cost of running a query across your network.

3. I notice that several of the joins are across to the other server - could you consolidate all of those joins into a single view that is optimised using indexes - may result in less network traffic.

4. Try putting your function on the other server and calling it from the first server to see if there's any performance improvement.