July 18, 2013 at 1:17 pm
CREATE PROCEDURE dbo.NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS
@lPatient int,
@nRecordStatus
int,
@nClassLevel int=5,
@nSecurityLevel
int=0,
@lMap_UserToOffice
int=0
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN
Declare @lUser int,@lUserActionMappingTable int
select @luser=lUser from map_userToOffice where lid=@lMap_UserToOffice
set @lUserActionMappingTable=32
--Disable the AOS SECURITY FOR CPP
if @nSecurityLevel=2 set @nSecurityLevel=1
if @lMap_UserToOffice>0 and @nSecurityLevel>=2 -- if there is a user and masking is on(bit 1=enabled masking)
begin
-------------
declare @dCurDate datetime, @lOffset int
--set @dCurDate=getDate()-- may need to adjust this for users timezone
exec SESSION_GetUserDate
@lUser =@luser ,@dUserDate =@dCurDate OUTPUT,@lOffset=@lOffset OUTPUT--This gets the date in the users timezone
/*
Get the permission ID for this section....
*/
declare @lCustodianPermission int
select @lCustodianPermission =lid from Custodian_Permissions where szcode='CPP'
declare @PhysiciansAccess table (lPhysician int primary key)
insert into @PhysiciansAccess (lPhysician)
select m.lPhysician from map_UserToCustodian m
inner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian=m.lid and cp.lCustodianpermission=@lCustodianPermission
where m.lUser=@luser and m.nRecordStatus=1 and m.bAllPatient=1 and isNull(m.dStartDate,'1900-01-01')<=@dCurDate and isnull(m.dEndDate,'9000-01-01')>=@dCurDate
Union
select lPhysician from map_UserToCustodian m
inner join map_UserToCustodian_Per_patient p on p.lmap_UserToCustodian=m.lid
inner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian_Per_Patient=p.lid and lCustodianpermission=@lCustodianPermission
where lUser=@luser and m.nRecordStatus=1 and p.nRecordStatus=1 and bAllPatient=0 and p.lPatient=@lPatient
and isNull(p.dStartDate,'1900-01-01')<=@dCurDate and isNull(p.dEndDate,'9000-01-01')>=@dCurDate--only get if the expiry dates are valid
--select * from @PhysiciansAccess
declare @OfficeAccess table (lOffice int primary key)
/*
go get all offices that the patient is a member of
filter out offices that this patient has a custodain and that custodain did not allow access to this user
Note: there is no need to filter by section for this query. The Section has been filtered above, and is used in the @PhysiciansAccess list
NOTE: the default values for office is -1 if the information was recorded without an office. This means that it is always accessable.
*/
insert into @OfficeAccess (lOffice) values(0)
insert into @OfficeAccess (lOffice)
select map.loffice from
map_PatientToOffice map
inner join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.loffice
inner join @PhysiciansAccess PA on PA.lphysician=mapc.lPhysician
where map.lpatient=@lPatient
--and PA.lPhysician is not null
union
select map.loffice from
map_PatientToOffice map
left join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.loffice
where map.lpatient=@lPatient
and isNull(mapc.lPhysician,0) <=0
-----------
SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected
,Case when exists (select * from
mask_role
inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRole
inner join map_userTOoffice map on map.lid=mapRole.lMap_userToOffice
where mask_role.nMappingTable=@lUserActionMappingTable
and map.lUser=@luser
and mask_role.lRowID=p.lid
and mask_role.nMaskStatus=1
)THEN 1
WHen Mu.lid is not null
THEN 1
else 0
END
as bMask
FROM [PhysicianLabReportsTests] a
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
JOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTest
left Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1
left join @PhysiciansAccess PA on (PA.lPhysician=p.lPhysician) --LEFT Join for all the physicains the user has access to ALL patient data recorded under that physician
left join @OfficeAccess O on O.loffice=p.loffice--LEFT Join for all the offices the user has access to ALL patient data in that office
WHERE (p.lPatient=@lPatient
AND p.bSignedOff=1
AND p.szFiledBy<>'auto'
AND a.nRecordStatus=@nRecordStatus
and p.nclassificationlevel>=@nClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
JOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lID
WHERE y.lPatient=@lPatient
AND z.lPhysicianLabTest=a.lPhysicianLabTest
AND z.nRecordStatus=@nRecordStatus
AND y.bSignedOff=1)) = 0
)
or
(p.lPatient=@lPatient
-- AND p.bSignedOff=1
AND p.szFiledBy='auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus=@nRecordStatus
and p.nclassificationlevel>=@nClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
JOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lID
WHERE y.lPatient=@lPatient
AND z.lPhysicianLabTest=a.lPhysicianLabTest
AND z.nRecordStatus=@nRecordStatus
and Z.bAdd2CPP = 1
-- AND y.bSignedOff=1
)) = 0
)
and isnull(PA.lPhysician,O.lOffice) is not null--Only return records if EITHER of the left joins returned a matching record
ORDER BY b.szDescription
end
else if @lMap_UserToOffice>0 and @nSecurityLevel>=1 -- if there is a user and masking is on(bit 1=enabled masking)
begin
SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected
,Case when exists (select * from
mask_role
inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRole
inner join map_userTOoffice map on map.lid=mapRole.lMap_userToOffice
where mask_role.nMappingTable=@lUserActionMappingTable
and map.lUser=@luser
and mask_role.lRowID=p.lid
and mask_role.nMaskStatus=1
)THEN 1
WHen Mu.lid is not null
THEN 1
else 0
END
as bMask
FROM [PhysicianLabReportsTests] a
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
JOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTest
left Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1
WHERE (p.lPatient=@lPatient
AND p.bSignedOff=1
AND p.szFiledBy<>'auto'
AND a.nRecordStatus=@nRecordStatus
and p.nclassificationlevel>=@nClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
JOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lID
WHERE y.lPatient=@lPatient
AND z.lPhysicianLabTest=a.lPhysicianLabTest
AND z.nRecordStatus=@nRecordStatus
AND y.bSignedOff=1)) = 0
)
or
(p.lPatient=@lPatient
-- AND p.bSignedOff=1
AND p.szFiledBy='auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus=@nRecordStatus
and p.nclassificationlevel>=@nClassLevel
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
JOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lID
WHERE y.lPatient=@lPatient
AND z.lPhysicianLabTest=a.lPhysicianLabTest
AND z.nRecordStatus=@nRecordStatus
and Z.bAdd2CPP = 1
-- AND y.bSignedOff=1
)) = 0
)
ORDER BY b.szDescription
end
else
begin
SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory,0 as bMask, p.dDateCollected
FROM [PhysicianLabReportsTests] a
JOIN [PhysicianLabReports] p ON p.lID=a.lLabReport
JOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTest
WHERE (p.lPatient=@lPatient
AND p.bSignedOff=1
AND p.szFiledBy<>'auto'
AND a.nRecordStatus=@nRecordStatus
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
JOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lID
WHERE y.lPatient=@lPatient
AND z.lPhysicianLabTest=a.lPhysicianLabTest
AND z.nRecordStatus=@nRecordStatus
AND y.bSignedOff=1)) = 0
)
or
(p.lPatient=@lPatient
-- AND p.bSignedOff=1
AND p.szFiledBy='auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus=@nRecordStatus
AND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
JOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lID
WHERE y.lPatient=@lPatient
AND z.lPhysicianLabTest=a.lPhysicianLabTest
AND z.nRecordStatus=@nRecordStatus
and Z.bAdd2CPP = 1
-- AND y.bSignedOff=1
)) = 0
)
ORDER BY b.szDescription
end
END
GO
July 18, 2013 at 1:28 pm
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2013 at 1:29 pm
Phew!!! That is a lot of unformatted code to read.
CREATE PROCEDURE dbo.NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS @lPatient INT,
@nRecordStatus INT,
@nClassLevel INT = 5,
@nSecurityLevel INT = 0,
@lMap_UserToOffice INT = 0
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN
DECLARE @lUser INT,
@lUserActionMappingTable INT
SELECT @luser = lUser
FROM map_userToOffice
WHERE lid = @lMap_UserToOffice
SET @lUserActionMappingTable = 32
--Disable the AOS SECURITY FOR CPP
IF @nSecurityLevel = 2
SET @nSecurityLevel = 1
IF @lMap_UserToOffice > 0
AND @nSecurityLevel >= 2 -- if there is a user and masking is on(bit 1=enabled masking)
BEGIN
-------------
DECLARE @dCurDate DATETIME,
@lOffset INT
--set @dCurDate=getDate()-- may need to adjust this for users timezone
EXEC SESSION_GetUserDate @lUser = @luser,
@dUserDate = @dCurDate OUTPUT,
@lOffset = @lOffset OUTPUT --This gets the date in the users timezone
/*
Get the permission ID for this section....
*/
DECLARE @lCustodianPermission INT
SELECT @lCustodianPermission = lid
FROM Custodian_Permissions
WHERE szcode = 'CPP'
DECLARE @PhysiciansAccess TABLE (lPhysician INT PRIMARY KEY)
INSERT INTO @PhysiciansAccess (lPhysician)
SELECT m.lPhysician
FROM map_UserToCustodian m
INNER JOIN map_UserToCustodian_permissions cp ON cp.lmap_UserToCustodian = m.lid
AND cp.lCustodianpermission = @lCustodianPermission
WHERE m.lUser = @luser
AND m.nRecordStatus = 1
AND m.bAllPatient = 1
AND isNull(m.dStartDate, '1900-01-01') <= @dCurDate
AND isnull(m.dEndDate, '9000-01-01') >= @dCurDate
UNION
SELECT lPhysician
FROM map_UserToCustodian m
INNER JOIN map_UserToCustodian_Per_patient p ON p.lmap_UserToCustodian = m.lid
INNER JOIN map_UserToCustodian_permissions cp ON cp.lmap_UserToCustodian_Per_Patient = p.lid
AND lCustodianpermission = @lCustodianPermission
WHERE lUser = @luser
AND m.nRecordStatus = 1
AND p.nRecordStatus = 1
AND bAllPatient = 0
AND p.lPatient = @lPatient
AND isNull(p.dStartDate, '1900-01-01') <= @dCurDate
AND isNull(p.dEndDate, '9000-01-01') >= @dCurDate --only get if the expiry dates are valid
--select * from @PhysiciansAccess
DECLARE @OfficeAccess TABLE (lOffice INT PRIMARY KEY)
/*
go get all offices that the patient is a member of
filter out offices that this patient has a custodain and that custodain did not allow access to this user
Note: there is no need to filter by section for this query. The Section has been filtered above, and is used in the @PhysiciansAccess list
NOTE: the default values for office is -1 if the information was recorded without an office. This means that it is always accessable.
*/
INSERT INTO @OfficeAccess (lOffice)
VALUES (0)
INSERT INTO @OfficeAccess (lOffice)
SELECT map.loffice
FROM map_PatientToOffice map
INNER JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient
AND mapc.loffice = map.loffice
INNER JOIN @PhysiciansAccess PA ON PA.lphysician = mapc.lPhysician
WHERE map.lpatient = @lPatient
--and PA.lPhysician is not null
UNION
SELECT map.loffice
FROM map_PatientToOffice map
LEFT JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient
AND mapc.loffice = map.loffice
WHERE map.lpatient = @lPatient
AND isNull(mapc.lPhysician, 0) <= 0
-----------
SELECT a.*,
b.szDescription,
p.dDateOrdered,
p.dDateSignedOff,
p.szLaboratory,
p.dDateCollected,
CASE
WHEN EXISTS (
SELECT *
FROM mask_role
INNER JOIN map_UserAtOfficeToRole mapRole ON mapRole.lUserRole = mask_Role.lRole
INNER JOIN map_userTOoffice map ON map.lid = mapRole.lMap_userToOffice
WHERE mask_role.nMappingTable = @lUserActionMappingTable
AND map.lUser = @luser
AND mask_role.lRowID = p.lid
AND mask_role.nMaskStatus = 1
)
THEN 1
WHEN Mu.lid IS NOT NULL
THEN 1
ELSE 0
END AS bMask
FROM [PhysicianLabReportsTests] a
INNER JOIN [PhysicianLabReports] p ON p.lID = a.lLabReport
INNER JOIN [LaboratoryTest] b ON b.lID = a.lPhysicianLabTest
LEFT JOIN Mask_User MU ON MU.nMappingTable = @lUserActionMappingTable
AND MU.lUser = @luser
AND MU.lRowID = p.lid
AND MU.nMaskStatus = 1
LEFT JOIN @PhysiciansAccess PA ON (PA.lPhysician = p.lPhysician) --LEFT Join for all the physicains the user has access to ALL patient data recorded under that physician
LEFT JOIN @OfficeAccess O ON O.loffice = p.loffice --LEFT Join for all the offices the user has access to ALL patient data in that office
WHERE (
p.lPatient = @lPatient
AND p.bSignedOff = 1
AND p.szFiledBy <> 'auto'
AND a.nRecordStatus = @nRecordStatus
AND p.nclassificationlevel >= @nClassLevel
AND DateDiff(d, p.dDateCollected, (
SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID
WHERE y.lPatient = @lPatient
AND z.lPhysicianLabTest = a.lPhysicianLabTest
AND z.nRecordStatus = @nRecordStatus
AND y.bSignedOff = 1
)) = 0
)
OR (
p.lPatient = @lPatient
-- AND p.bSignedOff=1
AND p.szFiledBy = 'auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus = @nRecordStatus
AND p.nclassificationlevel >= @nClassLevel
AND DateDiff(d, p.dDateCollected, (
SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID
WHERE y.lPatient = @lPatient
AND z.lPhysicianLabTest = a.lPhysicianLabTest
AND z.nRecordStatus = @nRecordStatus
AND Z.bAdd2CPP = 1
-- AND y.bSignedOff=1
)) = 0
)
AND isnull(PA.lPhysician, O.lOffice) IS NOT NULL --Only return records if EITHER of the left joins returned a matching record
ORDER BY b.szDescription
END
ELSE
IF @lMap_UserToOffice > 0
AND @nSecurityLevel >= 1 -- if there is a user and masking is on(bit 1=enabled masking)
BEGIN
SELECT a.*,
b.szDescription,
p.dDateOrdered,
p.dDateSignedOff,
p.szLaboratory,
p.dDateCollected,
CASE
WHEN EXISTS (
SELECT *
FROM mask_role
INNER JOIN map_UserAtOfficeToRole mapRole ON mapRole.lUserRole = mask_Role.lRole
INNER JOIN map_userTOoffice map ON map.lid = mapRole.lMap_userToOffice
WHERE mask_role.nMappingTable = @lUserActionMappingTable
AND map.lUser = @luser
AND mask_role.lRowID = p.lid
AND mask_role.nMaskStatus = 1
)
THEN 1
WHEN Mu.lid IS NOT NULL
THEN 1
ELSE 0
END AS bMask
FROM [PhysicianLabReportsTests] a
INNER JOIN [PhysicianLabReports] p ON p.lID = a.lLabReport
INNER JOIN [LaboratoryTest] b ON b.lID = a.lPhysicianLabTest
LEFT JOIN Mask_User MU ON MU.nMappingTable = @lUserActionMappingTable
AND MU.lUser = @luser
AND MU.lRowID = p.lid
AND MU.nMaskStatus = 1
WHERE (
p.lPatient = @lPatient
AND p.bSignedOff = 1
AND p.szFiledBy <> 'auto'
AND a.nRecordStatus = @nRecordStatus
AND p.nclassificationlevel >= @nClassLevel
AND DateDiff(d, p.dDateCollected, (
SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID
WHERE y.lPatient = @lPatient
AND z.lPhysicianLabTest = a.lPhysicianLabTest
AND z.nRecordStatus = @nRecordStatus
AND y.bSignedOff = 1
)) = 0
)
OR (
p.lPatient = @lPatient
-- AND p.bSignedOff=1
AND p.szFiledBy = 'auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus = @nRecordStatus
AND p.nclassificationlevel >= @nClassLevel
AND DateDiff(d, p.dDateCollected, (
SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID
WHERE y.lPatient = @lPatient
AND z.lPhysicianLabTest = a.lPhysicianLabTest
AND z.nRecordStatus = @nRecordStatus
AND Z.bAdd2CPP = 1
-- AND y.bSignedOff=1
)) = 0
)
ORDER BY b.szDescription
END
ELSE
BEGIN
SELECT a.*,
b.szDescription,
p.dDateOrdered,
p.dDateSignedOff,
p.szLaboratory,
0 AS bMask,
p.dDateCollected
FROM [PhysicianLabReportsTests] a
INNER JOIN [PhysicianLabReports] p ON p.lID = a.lLabReport
INNER JOIN [LaboratoryTest] b ON b.lID = a.lPhysicianLabTest
WHERE (
p.lPatient = @lPatient
AND p.bSignedOff = 1
AND p.szFiledBy <> 'auto'
AND a.nRecordStatus = @nRecordStatus
AND DateDiff(d, p.dDateCollected, (
SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID
WHERE y.lPatient = @lPatient
AND z.lPhysicianLabTest = a.lPhysicianLabTest
AND z.nRecordStatus = @nRecordStatus
AND y.bSignedOff = 1
)) = 0
)
OR (
p.lPatient = @lPatient
-- AND p.bSignedOff=1
AND p.szFiledBy = 'auto'
AND a.bAdd2CPP = 1
AND a.nRecordStatus = @nRecordStatus
AND DateDiff(d, p.dDateCollected, (
SELECT Max(y.dDateCollected)
FROM [PhysicianLabReports] y
INNER JOIN [PhysicianLabReportsTests] z ON z.lLabReport = y.lID
WHERE y.lPatient = @lPatient
AND z.lPhysicianLabTest = a.lPhysicianLabTest
AND z.nRecordStatus = @nRecordStatus
AND Z.bAdd2CPP = 1
-- AND y.bSignedOff=1
)) = 0
)
ORDER BY b.szDescription
END
END
GO
There seems to be a number of things that might cause this to be slow. You have a number of UNION, you also have a number of nonSARGable predicates. To be honest your queries that are unioned seem to be unnecessary. They are the exact same query except that the second one has additional filtering requirements. That means that ALL the rows in the second are already there in the first query.
All that aside the biggest performance issue is because you have multiple execution paths. That means that based on certain conditions you execute entirely different blocks of code.
Check out Gail's article about this topic and some of the best ways to overcome this.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2013 at 1:32 pm
This SP is running ON
Microsoft SQL Enterprise Manager
Microsoft Corporation
Version: 8.0
July 18, 2013 at 1:33 pm
can you please post the changes you like to make then i will test it?
Regards
July 18, 2013 at 1:34 pm
huum (7/18/2013)
This SP is running ONMicrosoft SQL Enterprise Manager
Microsoft Corporation
Version: 8.0
There is nothing like table definitions, index definitions and execution plan as Gail requested, and what you posted is nothing like table definitions, index definitions and execution plan.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2013 at 1:35 pm
Please read the article I posted as well as the blog post that Sean referenced.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2013 at 1:36 pm
huum (7/18/2013)
can you please post the changes you like to make then i will test it?Regards
Are you asking me to spend a whole day to try to restructure your code with no tables and no problem definition for a stored proc that is nearly 300 lines long? Then when I am done you will test it and see how I did? How much are you paying me for this?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2013 at 1:37 pm
No no as you mentioned about un necessary repetative part of SP, thats what i meant
July 18, 2013 at 2:02 pm
Thank you SIr
July 18, 2013 at 2:04 pm
huum (7/18/2013)
No no as you mentioned about un necessary repetative part of SP, thats what i meant
Ahh gotcha. 😉
Consider this piece of code.
INSERT INTO @OfficeAccess (lOffice)
SELECT map.loffice
FROM map_PatientToOffice map
INNER JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient
AND mapc.loffice = map.loffice
INNER JOIN @PhysiciansAccess PA ON PA.lphysician = mapc.lPhysician
WHERE map.lpatient = @lPatient
--and PA.lPhysician is not null
UNION
SELECT map.loffice
FROM map_PatientToOffice map
LEFT JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient
AND mapc.loffice = map.loffice
WHERE map.lpatient = @lPatient
AND isNull(mapc.lPhysician, 0) <= 0
Those two queries are VERY similar. I am assuming that since you used UNION you want to exclude duplicates?
I don't have a table to work with to test but I THINK this should do the same thing.
INSERT INTO @OfficeAccess (lOffice)
SELECT map.loffice
FROM map_PatientToOffice map
INNER JOIN map_PatientAtOfficeToCustodian mapc ON mapc.lpatient = map.lpatient
AND mapc.loffice = map.loffice
left /*INNER JOIN*/ @PhysiciansAccess PA ON PA.lphysician = mapc.lPhysician
WHERE map.lpatient = @lPatient
and (PA.lPhysician is not null or isNull(mapc.lPhysician, 0) <= 0)
group by map.loffice
Assuming that returns the same data this would be a lot simpler. I think you could do something similar to the first insert/union too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply