I need help to speed up a query to linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

I need help to speed up a query to linked server

Author

Topic

chris_lunt
Starting Member

25 Posts

Posted - 2011-08-04 : 05:47:43

Hello All, I have a query where the last 2 joins go to a linked server and cause the query to run in about 5 seconds, I need it to run in less than 1. Does anybody have any ideas on how to speed this up? Query below.

ALTER PROCEDURE [dbo].[USP_APP_PATIENT_HISTORY_INPATIENT_SPELL_SUMMARY]
@PATNT_REFNO NUMERIC(10,0)

--SELECT * FROM [IE_PAS].DBO.PATIENTS WHERE PASID = 'c00063'

AS

BEGIN

SELECT DISTINCT
--** PATIENT DETAILS
PATIENTS.PATNT_REFNO,
PATIENTS.PASID,
--** SPELL DETAILS
SPELL.PRVSP_REFNO AS SPELL_REFNO,
SPELL.ADMIT_DTTM AS SPELL_ADMIT_DATE,
SPELL.DISCH_DTTM AS SPELL_DISCH_DATE,
SPELL_SPEC.[DESCRIPTION] AS SPELL_SPECIALTY,
SPELL_PROF_CARER.MAIN_IDENT AS SPELL_PROF_CARER_CODE,
ISNULL(SPELL_PROF_CARER.SURNAME, '') + ' ' + ISNULL(SPELL_PROF_CARER.FORENAME, '') AS SPELL_CONSULTANT,
--This section determines THE LINK WE NEED TO PASS TO mEDISEC
CASE WHEN MEDISEC_LETTERS_ADMOF.DNFAUTHEPNO IS NULL
THEN MEDISEC_LETTERS_PRVSP.DNFAUTHEPNO
ELSE MEDISEC_LETTERS_ADMOF.DNFAUTHEPNO END AS MEDISEC_LINK,

--This section determines if the letter has been created in Medisec
CASE WHEN COALESCE(MEDISEC_LETTERS_PRVSP.DNFAUTHSIGNED, MEDISEC_LETTERS_ADMOF.DNFAUTHSIGNED, '') <> ''
THEN 'Y'
ELSE 'N'
END AS LETTER_CREATED
FROM
--** GET THE RELEVANT SPELLS INFO
[IE_PAS].DBO.PROVIDER_SPELLS SPELL WITH (NOLOCK)
LEFT OUTER JOIN [IE_PAS].DBO.SPECIALTIES SPELL_SPEC WITH (NOLOCK) ON SPELL.SPECT_REFNO = SPELL_SPEC.SPECT_REFNO
LEFT OUTER JOIN [IE_PAS].DBO.SERVICE_POINTS SPELL_SPONT WITH (NOLOCK) ON SPELL.SPONT_REFNO = SPELL_SPONT.SPONT_REFNO
LEFT OUTER JOIN [IE_PAS].DBO.PROF_CARERS SPELL_PROF_CARER WITH (NOLOCK) ON SPELL.PROCA_REFNO = SPELL_PROF_CARER.PROCA_REFNO
LEFT OUTER JOIN [IE_PAS].DBO.PATIENTS PATIENTS WITH (NOLOCK) ON SPELL.PATNT_REFNO = PATIENTS.PATNT_REFNO
LEFT OUTER JOIN [DISCHARGESQL].[medisec].[dbo].[AuthorisedDNF] MEDISEC_LETTERS_ADMOF ON CAST(SPELL.ADMOF_REFNO AS VARCHAR) = RIGHT(RTRIM(LTRIM(MEDISEC_LETTERS_ADMOF.dnfauthepno)),7)
LEFT OUTER JOIN [DISCHARGESQL].[medisec].[dbo].[AuthorisedDNF] MEDISEC_LETTERS_PRVSP ON CAST(SPELL.PRVSP_REFNO AS VARCHAR) = RIGHT(RTRIM(LTRIM(MEDISEC_LETTERS_PRVSP.dnfauthepno)),7)
WHERE
SPELL.PATNT_REFNO = @PATNT_REFNO
AND ISNULL(SPELL.ARCHV_FLAG, 'N') = 'N'
AND ISNULL(SPELL_SPEC.ARCHV_FLAG, 'N') <> 'Y'
AND ISNULL(SPELL_SPONT.ARCHV_FLAG, 'N') <> 'Y'
AND ISNULL(SPELL_PROF_CARER.ARCHV_FLAG, 'N') <> 'Y'
ORDER BY
SPELL.ADMIT_DTTM DESC

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-08-04 : 06:48:27

What is currently happening is that for EVERY ROW of your query, its trundling off to the other server to get the data and this will be slowing it down. You're also using 'CAST(SPELL.ADMOF_REFNO AS VARCHAR)' in your join so this will slow things down too AND you're going to the same table on the other server twice.

To counter this, you could possibly drag all the data from [DISCHARGESQL].[medisec].[dbo].[AuthorisedDNF] into a CTE or temp table at the top of your main query. You could filter the fields you require and also do some processing at that stage.
Something like:

;WITH CTE_AuthorisedDNF AS (
SELECT
CAST(SPELL.ADMOF_REFNO AS VARCHAR)
,CAST(SPELL.PRVSP_REFNO AS VARCHAR)
,DNFAUTHEPNO
FROM [DISCHARGESQL].[medisec].[dbo].[AuthorisedDNF]
)

and then proceed with your original select but instead of going accross the server for your AuthorisedDNF table, you could just JOIN onto CTE_AuthorisedDNF.

This will mean that there's only one transfer of data from your other server. This doesn't always make a difference but has often worked for me in the past.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

chris_lunt
Starting Member

25 Posts

Posted - 2011-08-04 : 07:34:07

Cheers Nick, implemented as below and now takes less than 1 second. Brilliant. What's surprised me is the speed of the creation of the CTE when we limit it by another parameter, I thought that might still take a while but it obviously doesn't. May thanks again. Chris.

ALTER PROCEDURE [dbo].[USP_APP_PATIENT_HISTORY_INPATIENT_SPELL_SUMMARY] 248095 116411 248095 -- 298016 --248095

@PATNT_REFNO NUMERIC(10,0)

--SELECT * FROM [IE_PAS].DBO.PATIENTS WHERE PASID = 'c00063'

AS

BEGIN

DECLARE @PASID VARCHAR(20)
SET @PASID = (SELECT PASID FROM [IE_PAS].DBO.PATIENTS WHERE PATNT_REFNO = @PATNT_REFNO)

;WITH CTE_AuthorisedDNF AS (
SELECT
RIGHT(RTRIM(LTRIM(dnfauthepno)),7) AS dnfauthepno,
DNFAUTHSIGNED
FROM [DISCHARGESQL].[medisec].[dbo].[AuthorisedDNF]
WHERE DNFAUTHPN = @PASID
)

SELECT DISTINCT
--** PATIENT DETAILS
PATIENTS.PATNT_REFNO,
PATIENTS.PASID,
--** SPELL DETAILS
SPELL.PRVSP_REFNO AS SPELL_REFNO,
SPELL.ADMIT_DTTM AS SPELL_ADMIT_DATE,
SPELL.DISCH_DTTM AS SPELL_DISCH_DATE,
SPELL_SPEC.[DESCRIPTION] AS SPELL_SPECIALTY,
SPELL_PROF_CARER.MAIN_IDENT AS SPELL_PROF_CARER_CODE,
ISNULL(SPELL_PROF_CARER.SURNAME, '') + ' ' + ISNULL(SPELL_PROF_CARER.FORENAME, '') AS SPELL_CONSULTANT,
--This section determines THE LINK WE NEED TO PASS TO mEDISEC
CASE WHEN MEDISEC_LETTERS_ADMOF.DNFAUTHEPNO IS NULL
THEN MEDISEC_LETTERS_PRVSP.DNFAUTHEPNO
ELSE MEDISEC_LETTERS_ADMOF.DNFAUTHEPNO END AS MEDISEC_LINK,

--This section determines if the letter has been created in Medisec
CASE WHEN COALESCE(MEDISEC_LETTERS_PRVSP.DNFAUTHSIGNED, MEDISEC_LETTERS_ADMOF.DNFAUTHSIGNED, '') <> ''
THEN 'Y'
ELSE 'N'
END AS LETTER_CREATED
FROM
--** GET THE RELEVANT SPELLS INFO
[IE_PAS].DBO.PROVIDER_SPELLS SPELL WITH (NOLOCK)
LEFT OUTER JOIN CTE_AuthorisedDNF MEDISEC_LETTERS_ADMOF WITH (NOLOCK) ON CAST(SPELL.ADMOF_REFNO AS VARCHAR) = RIGHT(RTRIM(LTRIM(MEDISEC_LETTERS_ADMOF.dnfauthepno)),7)
LEFT OUTER JOIN CTE_AuthorisedDNF MEDISEC_LETTERS_PRVSP WITH (NOLOCK) ON CAST(SPELL.PRVSP_REFNO AS VARCHAR) = RIGHT(RTRIM(LTRIM(MEDISEC_LETTERS_PRVSP.dnfauthepno)),7)
LEFT OUTER JOIN [IE_PAS].DBO.SPECIALTIES SPELL_SPEC WITH (NOLOCK) ON SPELL.SPECT_REFNO = SPELL_SPEC.SPECT_REFNO
LEFT OUTER JOIN [IE_PAS].DBO.SERVICE_POINTS SPELL_SPONT WITH (NOLOCK) ON SPELL.SPONT_REFNO = SPELL_SPONT.SPONT_REFNO
LEFT OUTER JOIN [IE_PAS].DBO.PROF_CARERS SPELL_PROF_CARER WITH (NOLOCK) ON SPELL.PROCA_REFNO = SPELL_PROF_CARER.PROCA_REFNO
LEFT OUTER JOIN [IE_PAS].DBO.PATIENTS PATIENTS WITH (NOLOCK) ON SPELL.PATNT_REFNO = PATIENTS.PATNT_REFNO
WHERE
SPELL.PATNT_REFNO = @PATNT_REFNO
AND ISNULL(SPELL.ARCHV_FLAG, 'N') = 'N'
AND ISNULL(SPELL_SPEC.ARCHV_FLAG, 'N') <> 'Y'
AND ISNULL(SPELL_SPONT.ARCHV_FLAG, 'N') <> 'Y'
AND ISNULL(SPELL_PROF_CARER.ARCHV_FLAG, 'N') <> 'Y'
ORDER BY
SPELL.ADMIT_DTTM DESC

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources