Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored procedure working very slow Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 1:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:40 PM
Points: 39, Visits: 146
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
Post #1475185
Posted Thursday, July 18, 2013 1:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 43,042, Visits: 36,200
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1475189
Posted Thursday, July 18, 2013 1:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 13,471, Visits: 12,329
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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1475190
Posted Thursday, July 18, 2013 1:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:40 PM
Points: 39, Visits: 146
This SP is running ON

Microsoft SQL Enterprise Manager

Microsoft Corporation

Version: 8.0
Post #1475193
Posted Thursday, July 18, 2013 1:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:40 PM
Points: 39, Visits: 146
can you please post the changes you like to make then i will test it?
Regards
Post #1475194
Posted Thursday, July 18, 2013 1:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 13,471, Visits: 12,329
huum (7/18/2013)
This SP is running ON

Microsoft 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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1475196
Posted Thursday, July 18, 2013 1:35 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 43,042, Visits: 36,200
Please read the article I posted as well as the blog post that Sean referenced.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1475197
Posted Thursday, July 18, 2013 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 13,471, Visits: 12,329
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1475199
Posted Thursday, July 18, 2013 1:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:40 PM
Points: 39, Visits: 146
No no as you mentioned about un necessary repetative part of SP, thats what i meant
Post #1475200
Posted Thursday, July 18, 2013 2:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:40 PM
Points: 39, Visits: 146
Thank you SIr
Post #1475210
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse