March 15, 2011 at 8:56 am
Any sugestions to improve the performance of this stored procedure?
***********************
USE
[Plato]
GO
/****** Object: StoredProcedure [dbo].[PlatoSearchTPS] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
PROCEDURE [dbo].[PlatoSearchTPS]
@MedicalRecordNumber
VARCHAR(100) = NULL
,@AltMedicalRecordNumber VARCHAR(100) = NULL
,@SSN VARCHAR(100) = NULL
,@PLastName VARCHAR(100) = NULL
,@PFirstName VARCHAR(100) = NULL
,@PMiddleName VARCHAR(100) = NULL
,@PSuffix VARCHAR(100) = NULL
,@PDOB VARCHAR(100) = NULL
,@PRace VARCHAR(100) = NULL
,@PGender VARCHAR(100) = NULL
,@VisitCode VARCHAR(100) = NULL
,@AdmitDate VARCHAR(100) = NULL
,@DischargeDate VARCHAR(100) = NULL
,@VisitDate VARCHAR(100) = NULL
,@SurgeryDate VARCHAR(100) = NULL
,@OnSetDate VARCHAR(100) = NULL
,@InjuryDate VARCHAR(100) = NULL
,@StartOfCareDate VARCHAR(100) = NULL
,@AdmitPhy VARCHAR(100) = NULL
,@AttendPhy VARCHAR(100) = NULL
,@ReferPhy VARCHAR(100) = NULL
,@Anestesiologist VARCHAR(100) = NULL
,@AssistantPhy VARCHAR(100) = NULL
,@ConsultingPhy VARCHAR(100) = NULL
,@PrimaryCare VARCHAR(100) = NULL
,@SupervisingPhy VARCHAR(100) = NULL
,@Provider VARCHAR(100) = NULL
,@Surgeon VARCHAR(100) = NULL
,@PLocation VARCHAR(100) = NULL
,@PType VARCHAR(100) = NULL
,@SubAcctID VARCHAR(100) = NULL
,@FacilityNumber VARCHAR(100) = NULL
,@RoomNumber VARCHAR(100) = NULL
,@BedNumber VARCHAR(100) = NULL
,@ChartLocation VARCHAR(100) = NULL
,@EncounterStatus VARCHAR(100) = NULL
,@ChiefComplaint VARCHAR(100) = NULL
,@Employer VARCHAR(100) = NULL
,@Occupation VARCHAR(100) = NULL
,@Insured VARCHAR(100) = NULL
,@InsuranceCompany VARCHAR(100) = NULL
,@HandDominance VARCHAR(100) = NULL
,@Diagnosis VARCHAR(100) = NULL
,@LastModified VARCHAR(100) = NULL
,@PUserDefined1 VARCHAR(100) = NULL
,@PUserDefined2 VARCHAR(100) = NULL
,@PUserDefined3 VARCHAR(100) = NULL
,@PUserDefined4 VARCHAR(100) = NULL
,@PUserDefined5 VARCHAR(100) = NULL
,@EUserDefined1 VARCHAR(100) = NULL
,@EUserDefined2 VARCHAR(100) = NULL
,@EUserDefined3 VARCHAR(100) = NULL
,@EUserDefined4 VARCHAR(100) = NULL
,@EUserDefined5 VARCHAR(100) = NULL
,@PDOBList VARCHAR(100) = NULL
,@UseExact BIT = 0
AS
BEGIN
DECLARE
@sqlCommand varchar(MAX)
DECLARE
@whereClause varchar(MAX)
DECLARE
@totSql nvarchar(MAX)
DECLARE
@ParmDefinition nvarchar(MAX)
DECLARE
@medrec varchar(100)
DECLARE
@acctnum varchar(100)
DECLARE
@subacct int
DECLARE
@equals varchar(10)
DECLARE
@likeClause varchar(10)
SET NOCOUNT ON;
-- Create temp table so we only have to search once
CREATE
TABLE #validADTRows (MedicalRecordNumber varchar(50),
AltMedicalRecordNumber
varchar(50), EncounterId int, PatientId int,
FacilityNumber
varchar(20))
set
@whereClause = ''
set
@ParmDefinition = '@subacct int, @medrec varchar(100),@acctnum varchar(100),@SSN varchar(100)' +
',@PLastName varchar(100),@PFirstName varchar(100),@PMiddleName varchar(100),@PSuffix varchar(30)' +
',@PDOB varchar(30),@PRace varchar(30),@PGender varchar(10),@VisitCode varchar(100)'+
',@AdmitDate varchar(30),@DischargeDate varchar(30),@VisitDate varchar(30),@SurgeryDate varchar(30)' +
',@OnSetDate varchar(30),@InjuryDate varchar(30),@StartOfCareDate varchar(30),@AdmitPhy varchar(100)' +
',@AttendPhy varchar(100),@ReferPhy varchar(100),@Anestesiologist varchar(100),@AssistantPhy varchar(100)' +
',@ConsultingPhy varchar(100),@PrimaryCare varchar(100),@SupervisingPhy varchar(100),@Provider varchar(100)' +
',@Surgeon varchar(100),@PLocation varchar(100),@PType varchar(30)' +
',@FacilityNumber varchar(50),@RoomNumber varchar(20),@BedNumber varchar(20),@ChartLocation varchar(100)' +
',@EncounterStatus varchar(100),@ChiefComplaint varchar(100),@Employer varchar(100),@Occupation varchar(100)' +
',@Insured varchar(10),@InsuranceCompany varchar(100),@HandDominance varchar(20),@Diagnosis varchar(100)' +
',@LastModified varchar(30)' +
',@PUserDefined1 varchar(100),@PUserDefined2 varchar(100),@PUserDefined3 varchar(100),@PUserDefined4 varchar(100),@PUserDefined5 varchar(100)' +
',@EUserDefined1 varchar(100),@EUserDefined2 varchar(100),@EUserDefined3 varchar(100),@EUserDefined4 varchar(100),@EUserDefined5 varchar(100)' +
',@PDOBList varchar(100)'
set
@medrec = ISNULL(@MedicalRecordNumber, ISNULL(@AltMedicalRecordNumber, ''))
set
@acctnum = ISNULL(@AltMedicalRecordNumber, ISNULL(@MedicalRecordNumber, ''))
set
@subacct = ISNULL(CONVERT(int, @SubAcctID), 0)
if
@UseExact = 0 OR @UseExact IS NULL
BEGIN
set @equals = ' LIKE '
set @likeClause = '''%'''
END
else
BEGIN
set @equals = ' = '
set @likeClause = ''''''
END
SET
TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 10/29/2010 Jim C.: Added support for facility family concept.
-- Basic sql command for search
set
@sqlCommand='
INSERT INTO #validADTRows (MedicalRecordNumber, AltMedicalRecordNumber, EncounterId, PatientId, FacilityNumber)
SELECT TOP 1000 MedicalRecordNumber, AltMedicalRecordNumber, a.EncounterId, a.PatientId, a.FacilityNumber
FROM Adt_Data a WITH (NOLOCK)
INNER JOIN dbo.AncestorsAndDescendantsOfSubAccount(@subacct) Family
ON a.SubAcctID = Family.SubAccountID
-- INNER JOIN ADT_Unique_Patient_Ids p ON (p.PatientId = a.PatientId)
-- INNER JOIN ADT_Unique_Encounter_Ids u ON (u.EncounterId = a.EncounterId)
WHERE a.LstTransactionType != ''Lkd''
AND a.Enc_LstTransactionType != ''Lkd''
--AND SubAcctID = @subacct
AND (
MedicalRecordNumber '
+ @equals + ' @medrec + ' + @likeClause + '
OR AltMedicalRecordNumber '
+ @equals + ' @acctnum + ' + @likeClause + '
)
'
if @SSN IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(SSN, '''') LIKE @SSN + ''%'' '
if @PLastName IS NOT NULL
set @whereClause = @whereClause +
'AND (COALESCE(LastName, '''') LIKE @PLastName + ''%'')'
if @PFirstName IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(FirstName, '''') LIKE @PFirstName + ''%'''
if @PMiddleName IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(MiddleName, '''') LIKE @PMiddleName + ''%'''
if @PSuffix IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Suffix, '''') LIKE @PSuffix + ''%'''
if @PDOB IS NOT NULL
BEGIN
IF CHARINDEX('/',@PDOB) > 0
BEGIN
IF SUBSTRING(@PDOB, LEN(@PDOB)-2,1) = '/'
BEGIN
DECLARE @yearDOB VARCHAR(5)
set @yearDOB = SUBSTRING(@PDOB, LEN(@PDOB)-1,2)
--SELECT 'YearDOB = ' + @yearDOB
IF CONVERT(int,@yearDOB) > CONVERT(int,SUBSTRING(CONVERT(VARCHAR(5),DATEPART(yy,getdate())),3,2))
BEGIN
set @yearDOB = '19' + @yearDOB
END
ELSE
BEGIN
set @yearDOB = '20' + @yearDOB
END
set @PDOB = SUBSTRING(@PDOB,1,LEN(@PDOB)-2) + @yearDOB
END
END
--select 'PDOB = ' + @PDOB
set @whereClause = @whereClause +
'AND CONVERT(datetime,DOB,101) = CONVERT(datetime,@PDOB,101) '
-- 'AND (DOB BETWEEN CONVERT(datetime,@PDOB, 101) AND (CONVERT(datetime, @PDOB, 101) + 1)) '
END
if @PRace IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Race, '''') LIKE @PRace + ''%'''
if @PGender IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Sex, '''') LIKE @PGender + ''%'''
if @VisitCode IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(VisitCode, '''') LIKE @VisitCode + ''%'''
if @AdmitDate IS NOT NULL
set @whereClause = @whereClause +
'AND (AdmitDate BETWEEN CONVERT(datetime,@AdmitDate, 101) AND (CONVERT(datetime, @AdmitDate, 101) + 1)) '
if @DischargeDate IS NOT NULL
set @whereClause = @whereClause +
'AND (DischargeDate BETWEEN CONVERT(datetime,@DischargeDate, 101) AND (CONVERT(datetime,@DischargeDate, 101) + 1)) '
if @VisitDate IS NOT NULL
set @whereClause = @whereClause +
'AND (VisitDate BETWEEN CONVERT(datetime,@VisitDate, 101) AND (CONVERT(datetime, @VisitDate, 101) + 1)) '
if @SurgeryDate IS NOT NULL
set @whereClause = @whereClause +
'AND (SurgeryDate BETWEEN CONVERT(datetime,@SurgeryDate, 101) AND (CONVERT(datetime, @SurgeryDate, 101) + 1)) '
if @OnSetDate IS NOT NULL
set @whereClause = @whereClause +
'AND (OnSetDate BETWEEN CONVERT(datetime,@OnSetDate, 101) AND (CONVERT(datetime, @OnSetDate, 101) + 1)) '
if @InjuryDate IS NOT NULL
set @whereClause = @whereClause +
'AND (InjuryDate BETWEEN CONVERT(datetime,@InjuryDate, 101) AND (CONVERT(datetime, @InjuryDate, 101) + 1)) '
if @StartOfCareDate IS NOT NULL
set @whereClause = @whereClause +
'AND (StartOfCareDate BETWEEN CONVERT(datetime,@StartOfCareDate, 101) AND (CONVERT(datetime, @StartOfCareDate, 101) + 1)) '
if @AdmitPhy IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(AdmitPhys, '''') LIKE ''%'' + @AdmitPhy + ''%'''
if @AttendPhy IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(AttendPhys, '''') LIKE ''%'' + @AttendPhy + ''%'''
if @ReferPhy IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(ReferPhys, '''') LIKE ''%'' + @ReferPhy + ''%'''
if @Anestesiologist IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Anestesiologist, '''') LIKE ''%'' + @Anestesiologist + ''%'''
if @AssistantPhy IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(AssistantPhys, '''') LIKE ''%'' + @AssistantPhy + ''%'''
if @ConsultingPhy IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(ConsultingPhys, '''') LIKE ''%'' + @ConsultingPhy + ''%'''
if @primarycare IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(PrimaryCare, '''') LIKE ''%'' + @primarycare + ''%'''
if @SupervisingPhy IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(SupervisingPhy, '''') LIKE ''%'' + @SupervisingPhy + ''%'''
if @Provider IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Provider, '''') LIKE ''%'' + @Provider + ''%'''
if @Surgeon IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Surgeon, '''') LIKE ''%'' + @Surgeon + ''%'''
if @PLocation IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(PatientLocation, '''') LIKE @PLocation + ''%'''
if @PType IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(PatientType, '''') LIKE @PType + ''%'''
if @FacilityNumber IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(FacilityNumber, '''') LIKE @FacilityNumber + ''%'''
if @RoomNumber IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(RoomNumber, '''') LIKE @RoomNumber + ''%'''
if @BedNumber IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(BedNumber, '''') LIKE @BedNumber + ''%'''
if @ChartLocation IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(ChartLocation, '''') LIKE @ChartLocation + ''%'''
if @EncounterStatus IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(EncounterStatus, '''') LIKE @EncounterStatus + ''%'''
if @ChiefComplaint IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(ChiefComplaint, '''') LIKE @ChiefComplaint + ''%'''
if @Employer IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Employer, '''') LIKE @Employer + ''%'''
if @Occupation IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Occupation, '''') LIKE @Occupation + ''%'''
if @Insured IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Insured, '''') LIKE @Insured + ''%'''
if @InsuranceCompany IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(InsuranceCompany, '''') LIKE @InsuranceCompany + ''%'''
if @HandDominance IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(HandDominance, '''') LIKE @HandDominance + ''%'''
if @Diagnosis IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Diagnosis, '''') LIKE @Diagnosis + ''%'''
if @LastModified IS NOT NULL
set @whereClause = @whereClause +
'AND (LastModified BETWEEN CONVERT(datetime,@LastModified, 101) AND (CONVERT(datetime, @LastModified, 101) + 1)) '
if @PUserDefined1 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(UserDefined1, '''') LIKE @PUserDefined1 + ''%'''
if @PUserDefined2 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(UserDefined2, '''') LIKE @PUserDefined2 + ''%'''
if @PUserDefined3 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(UserDefined3, '''') LIKE @PUserDefined3 + ''%'''
if @PUserDefined4 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(UserDefined4, '''') LIKE @PUserDefined4 + ''%'''
if @PUserDefined5 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(UserDefined5, '''') LIKE @PUserDefined5 + ''%'''
if @EUserDefined1 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Enc_UserDefined1, '''') LIKE @EUserDefined1 + ''%'''
if @EUserDefined2 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Enc_UserDefined2, '''') LIKE @EUserDefined2 + ''%'''
if @EUserDefined3 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Enc_UserDefined3, '''') LIKE @EUserDefined3 + ''%'''
if @EUserDefined4 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Enc_UserDefined4, '''') LIKE @EUserDefined4 + ''%'''
if @EUserDefined5 IS NOT NULL
set @whereClause = @whereClause +
'AND COALESCE(Enc_UserDefined5, '''') LIKE @EUserDefined5 + ''%'''
if @PDOBList IS NOT NULL
begin
if @MedicalRecordNumber IS NULL
set @whereClause = @whereClause +
'AND DOB IN (' + @PDOBList + ')'
else
set @whereClause = @whereClause +
'OR DOB IN (' + @PDOBList + ')'
end
set @totSql = @sqlCommand + @whereClause + CHAR(13) + CHAR(10) + 'ORDER BY a.LastModified '
print @totSql
execute sp_executeSql @totSql, @ParmDefinition, @subacct=@subacct,@medrec=@medrec,@acctnum=@acctnum,@SSN=@SSN,
@PLastName
=@PLastName,@PFirstName=@PFirstName,@PMiddleName=@PMiddleName,@PSuffix=@PSuffix,
@PDOB
=@PDOB,@PRace=@PRace,@PGender=@PGender,@VisitCode=@VisitCode,@AdmitDate=@AdmitDate,
@DischargeDate
=@DischargeDate,@VisitDate=@VisitDate,@SurgeryDate=@SurgeryDate,@OnSetDate=@OnSetDate,
@InjuryDate
=@InjuryDate,@StartOfCareDate=@StartOfCareDate,@AdmitPhy=@AdmitPhy,@AttendPhy=@AttendPhy,
@ReferPhy
=@ReferPhy,@Anestesiologist=@Anestesiologist,@AssistantPhy=@AssistantPhy,
@ConsultingPhy
=@ConsultingPhy,@PrimaryCare=@PrimaryCare,@SupervisingPhy=@SupervisingPhy,
@Provider
=@Provider,@Surgeon=@Surgeon,@PLocation=@PLocation,@PType=@PType,
@FacilityNumber
=@FacilityNumber,@RoomNumber=@RoomNumber,@BedNumber=@BedNumber,@ChartLocation=@ChartLocation,
@EncounterStatus
=@EncounterStatus,@ChiefComplaint=@ChiefComplaint,@Employer=@Employer,
@Occupation
=@Occupation,@Insured=@Insured,@InsuranceCompany=@InsuranceCompany,@HandDominance=@HandDominance,
@Diagnosis
=@Diagnosis,@LastModified=@LastModified,@PUserDefined1=@PUserDefined2,@PUserDefined2=@PUserDefined2,
@PUserDefined3
=@PUserDefined3,@PUserDefined4=@PUserDefined4,@PUserDefined5=@PUserDefined5,
@EUserDefined1
=@EUserDefined1,@EUserDefined2=@EUserDefined2,@EUserDefined3=@EUserDefined3,
@EUserDefined4
=@EUserDefined4,@EUserDefined5=@EUserDefined5,@PDOBList=@PDOBList
-- Only want distinct patient rows i.e. one row for each patient...
-- List of matching patients
SELECT DISTINCT adt.*, 0 AS Selected
FROM #validADTRows vr
INNER JOIN ADT_Unique_Patient_Ids u WITH (NOLOCK)
ON (u.PatientId = vr.PatientId)
INNER JOIN Adt_Patient_Data adt WITH (NOLOCK)
ON (adt.PatientId = vr.PatientId)
ORDER BY MedicalRecordNumber
-- List of matching encounters
SELECT DISTINCT adt.*, 0 AS Selected FROM #validADTRows vr
INNER JOIN ADT_Unique_Patient_Ids pu WITH (NOLOCK)
ON (pu.PatientId = vr.PatientId)
INNER JOIN Adt_Encounter_Data adt WITH (NOLOCK)
-- ON (adt.EncounterId = vr.EncounterId)
ON (adt.MedicalRecordNumber = vr.MedicalRecordNumber AND
adt
.FacilityNumber = vr.FacilityNumber)
INNER JOIN ADT_Unique_Encounter_Ids u WITH (NOLOCK)
ON (u.EncounterId = adt.EncounterId)
ORDER BY MedicalRecordNumber
-- List of matching orders
SELECT DISTINCT tov.*, 0 AS Selected FROM PlatoOrderView tov WITH (NOLOCK)
INNER JOIN Adt_Encounter_Data ed WITH (NOLOCK)
ON (tov.EncounterId = ed.EncounterId)
INNER JOIN ADT_Unique_Order_Ids ou WITH (NOLOCK)
ON (ou.OrderId = tov.OrderId)
WHERE ed.EncounterIdVisitCode IN
(
SELECT adt.EncounterIdVisitCode FROM #validADTRows vr
INNER JOIN ADT_Unique_Patient_Ids pu WITH (NOLOCK)
ON (pu.PatientId = vr.PatientId)
INNER JOIN Adt_Encounter_Data adt WITH (NOLOCK)
ON (adt.MedicalRecordNumber = vr.MedicalRecordNumber AND
adt
.FacilityNumber = vr.FacilityNumber)
INNER JOIN ADT_Unique_Encounter_Ids u WITH (NOLOCK)
ON (u.EncounterId = adt.EncounterId)
)
ORDER BY EncounterId
-- List of matching providers for the encounters
SELECT DISTINCT epv.*, 0 AS Selected FROM ADT_Encounter_Provider_View epv WITH (NOLOCK)
WHERE epv.EncounterId IN (
SELECT adt.EncounterId FROM #validADTRows vr
INNER JOIN ADT_Unique_Patient_Ids pu WITH (NOLOCK)
ON (pu.PatientId = vr.PatientId)
INNER JOIN Adt_Encounter_Data adt WITH (NOLOCK)
ON (adt.MedicalRecordNumber = vr.MedicalRecordNumber AND
adt
.FacilityNumber = vr.FacilityNumber)
INNER JOIN ADT_Unique_Encounter_Ids u WITH (NOLOCK)
ON (u.EncounterId = adt.EncounterId)
)
ORDER BY EncounterId, DetailTypeId
-- List of matching providers for the orders
SELECT DISTINCT aopv.*, 0 AS Selected FROM ADT_Order_Provider_View aopv WITH (NOLOCK)
WHERE aopv.OrderId IN
(
SELECT tov.OrderId FROM PlatoOrderView tov WITH (NOLOCK)
INNER JOIN Adt_Encounter_Data ed WITH (NOLOCK)
ON (tov.EncounterId = ed.EncounterId)
INNER JOIN ADT_Unique_Order_Ids ou WITH (NOLOCK)
ON (ou.OrderId = tov.OrderId)
WHERE ed.EncounterIdVisitCode IN
(
SELECT adt.EncounterIdVisitCode FROM #validADTRows vr
INNER JOIN ADT_Unique_Patient_Ids pu WITH (NOLOCK)
ON (pu.PatientId = vr.PatientId)
INNER JOIN Adt_Encounter_Data adt WITH (NOLOCK)
ON (adt.MedicalRecordNumber = vr.MedicalRecordNumber AND
adt
.FacilityNumber = vr.FacilityNumber)
INNER JOIN ADT_Unique_Encounter_Ids u WITH (NOLOCK)
ON (u.EncounterId = adt.EncounterId)
)
)
ORDER BY OrderId
DROP
TABLE #validADTRows
END
March 15, 2011 at 9:48 am
First thing that jumped out at me: dbo.AncestorsAndDescendantsOfSubAccount(@subacct) Family
Is that a multi-statement user defined function? If so, that's where I'd start. Eliminate that.
Without an execution plan, it's hard to know where else to focus, especially on a query this large.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 15, 2011 at 9:55 am
Yes, and don't use NOLOCK in production code unless you don't mind getting approximate, as opposed to accurate, data.
John
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply