July 12, 2013 at 7:55 am
hi
my sp is taking 5 min in prod.any way i can improve performance
BEGIN TRY
DECLARE @alerts TABLE
(BatchId int,
ConsumerId BIGINT,
MemberId INT,
RId INT,
Category INT,
MailToMem BIT,
MailToProv BIT,
SortOrder varchar(25),
StatusVARCHAR(8),
ReasonCD varchar(8),
Active bit
)
DECLARE @Mid TABLE (ConsumerId BIGINT)
INSERT INTO @Mid
( ConsumerId )
SELECT DISTINCT ConsumerId
FROM MCID_XREF xref WITH (NOLOCK)
WHERE MemberId = @MemberID
--List of all alerts based on MemberID OR MasterConsumerID
INSERT INTO @alerts
( BatchId ,
ConsumerId ,
MemberId ,
RId ,
Category ,
MailToMem ,
MailToProv ,
SortOrder ,
Status,
ReasonCD,
Active
SELECT
BatchId ,
ConsumerId ,
MemberId ,
RId ,
Category ,
MailToMem ,
MailToProv ,
SortOrder ,
Status,
ReasonCD,
Active
FROM dbo.Alerts a
WHERE MemberId = @MemberID
OR ConsumerId IN (SELECT ConsumerId FROM @Mid)
select a.MemberId,a.SortOrder ,a.Status ,a.ReasonCD ,a.Active, r.Rid,r.RType,r.Sensitive,
r.Weight,r.ROI,r.Program,r.Mgmt_Ranking,r.Significant,
(select top 1 Name from library WITH (NoLock)
where rID = r.ruleid) as ruleName,
min(b.analysisasofdate) initiallyIdentified,
max(b.analysisasofdate) mostRecentlyIdentified,
a.category,
v_cnt.ResponseCategoryCount,
max(b.alertBatchId) batchid, r.harvardmednumber
from library r WITH (NoLock)
inner join @alerts a on a.ruleid = r.ruleid
and a.alertbatchid = r.alertbatchid
inner join batch b WITH (NoLock) on b.alertbatchId = a.alertbatchId
and (a.mailToProv = 1 or a.mailToMem = 1)
left outer join dbo.fn_CategoryCount(@memberid) v_cnt
on v_cnt.ruleid = a.Rid and a.MemberId = v_cnt.memberid
where a.MemberId = @MemberID
END TRY
BEGIN CATCH
--ERROR
END CATCH
GO
any help?
July 12, 2013 at 7:57 am
Step 1 : Don't use table variables.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 12, 2013 at 8:04 am
what can i use instead of that?
July 12, 2013 at 8:08 am
use temporary tables with good indexes
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 12, 2013 at 8:11 am
can u give me example with indexes
July 12, 2013 at 8:11 am
mister.magoo (7/12/2013)
use temporary tables with good indexes
And use temporary tables with columns which match the joins in the query:
inner join @alerts a on a.ruleid = r.ruleid
and a.alertbatchid = r.alertbatchid
The definition of table @alerts doesn't contain column alertbatchid.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2013 at 8:30 am
Drop the table variables altogether:
SELECT
a.MemberId,
a.SortOrder ,
a.Status ,
a.ReasonCD ,
a.Active,
r.Rid,
r.RType,
r.Sensitive,
r.Weight,
r.ROI,
r.Program,
r.Mgmt_Ranking,
r.Significant,
(select top 1 Name from library WITH (NoLock)
where rID = r.ruleid) as ruleName,
min(b.analysisasofdate) initiallyIdentified,
max(b.analysisasofdate) mostRecentlyIdentified,
a.category,
v_cnt.ResponseCategoryCount,
max(b.alertBatchId) batchid,
r.harvardmednumber
from library r WITH (NoLock)
inner join (
SELECT
BatchId ,
ConsumerId ,
MemberId ,
RId ,
Category ,
MailToMem ,
MailToProv ,
SortOrder ,
Status,
ReasonCD,
Active
FROM dbo.Alerts a
WHERE MemberId = @MemberID
OR ConsumerId IN (
SELECT ConsumerId
FROM MCID_XREF xref WITH (NOLOCK)
WHERE MemberId = @MemberID)
) a on a.ruleid = r.ruleid -- column a.ruleid doesn't exist
and a.alertbatchid = r.alertbatchid -- column a.alertbatchid doesn't exist
and (a.mailToProv = 1 or a.mailToMem = 1)
inner join batch b WITH (NoLock) on b.alertbatchId = a.alertbatchId
left outer join dbo.fn_CategoryCount(@memberid) v_cnt
on v_cnt.ruleid = a.Rid and a.MemberId = v_cnt.memberid
Can you post the code for function fn_CategoryCount?
Can you post an actual execution plan?
Cheers
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2013 at 8:30 am
riya_dave (7/12/2013)
can u give me example with indexes
http://www.sqlteam.com/article/optimizing-performance-indexes-on-temp-tables
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply