May 3, 2012 at 9:40 am
I have a stored procedure that never finishes. I run the query inside the stored procedure and it takes 17 seconds.
Any thoughts? I tried using with recompile in the stored proc, dbcc freeproccache. But nothing seems to work.
May 3, 2012 at 9:56 am
pamozer (5/3/2012)
I have a stored procedure that never finishes. I run the query inside the stored procedure and it takes 17 seconds.Any thoughts? I tried using with recompile in the stored proc, dbcc freeproccache. But nothing seems to work.
It could be any thing. what we need is more detail. can you post the code for the Stored Procedure the Table Deffinitions including indexes, some sample data, an explination for what your SP is doing, and the approximate size of the data you are running on?
Right now you have asked me "Solve for X, Y = 7"
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 3, 2012 at 10:01 am
pamozer (5/3/2012)
I have a stored procedure that never finishes. I run the query inside the stored procedure and it takes 17 seconds.Any thoughts? ...
I have one:
It's not working is it?
May 3, 2012 at 10:15 am
Sorry about that. Also when I run a trace against the stored proc it doesn't get past the first Insert.
STored Procedure
Create PROCEDURE [dbo].[ReportDataProvider_DRO]
@EndDate datetime =Null,
@PracticeID Int =Null
----WITH RECOMPILE
AS
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET CONCAT_NULL_YIELDS_NULL ON
SET Numeric_Roundabort off
-----Debugging
--DECLARE
--@EndDate DATETIME,
--@PracticeID INT
--SELECT
--@EndDate = NULL,
--@PracticeID=Null
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVELREAD UNCOMMITTED
IF @EndDate IS NULL SET @Enddate=GETDATE()
SET @EndDate = DATEADD(S,-1,DATEADD(D,1,dbo.fn_DateOnly( @EndDate )))
CREATE TABLE #AR_ASN (
PracticeID INT,
PatientID INT
, ClaimID INT
,TotalAmount DECIMAL (19,9)
)
--Patient and Insurance
INSERT INTO #AR_ASN(
PracticeID,
TotalAMount
)
SELECT
CA.PracticeID,
TotalAmount=SUM(
CASE
WHEN ca.CLaimID IS NULL THEN case
when ClaimTransactionTypeCode = 'PAY' and ca.ca_PostingDate>@EndDate then 0-- we can not take future payments
when ClaimTransactionTypeCode = 'CST' THEN amount else -1*Amount
END -- 'Unassigned'
WHEN ClaimTransactionTypeCode='PAY' AND PayerTypeCode='P' -- Include patient payment against total
THEN Amount*-1
WHEN ClaimTransactionTypeCode = 'CST' THEN Amount ELSE Amount*-1 END
)
FROM vwReporting_Claim_ClaimAccounting CA WITH (NOEXPAND)
Left Join Payment P On CA.PracticeId=P.PracticeID And P.PaymentID=CA.PaymentID
WHERE
ca_PostingDate <=@EndDate
AND
ClaimTransactionTypeCode IN ('PAY', 'ADJ', 'CST')
AND CA.PracticeID=ISNULL(@PracticeID, ca.PracticeID)
GROUP BY
ca.PracticeID
--SELECT *
--FROM #AR_ASN AS ASN
SELECT PracticeID, SUM(PaymentAmount) AS PaymentAmount
INTO #SummarizedUnapplied
FROM(
SELECTP.PracticeID,
PayerTypeCode,
PayerID,
PaymentAmount = SUM( ISNULL(PaymentAmount, 0) + ISNULL(a.AppliedAmount, 0) )
FROM Payment P
LEFT OUTER JOIN ---Unapplied Amount---
(SELECTp.PracticeID,
p.PaymentID,
SUM(case when p.ca_postingDate <= @endDate then -1 * Amount else 0 end ) AppliedAmount
FROM vwReporting_Claim_Payments p with(NOEXPAND)
WHERE (p.pay_PostingDate <= @endDate OR p.ca_PostingDate <= @endDate )
AND ClaimTransactionTypeCode = 'PAY'
AND p.PracticeID=ISNULL(@PracticeID, p.PracticeID)
GROUP BY p.PracticeID, p.PaymentID)AS a
ON a.PaymentID = p.PaymentID AND P.PracticeID=a.PracticeID
WHERE p.PostingDate <= @EndDate AND P.PracticeID=ISNULL(@PracticeID, P.PracticeID)
GROUP BY P.PracticeID, p.PayerTypeCode, p.PayerID
UNION ALL
-----REfunds
select r.PracticeID, PayerTypeCode, p.PayerID, sum(-1 * rtp.amount)
from refund r
INNER jOIn refundToPayments rtp on r.RefundID = rtp.RefundID
INNER JOIN Payment p on p.practiceID = r.practiceID AND p.PaymentID = rtp.PaymentID
WHERE rtp.PostingDate <= @EndDate
AND r.RefundStatusCode = 'I'
AND r.PracticeID=ISNULL(@PracticeID, r.PracticeID)
group by r.PracticeID, p.PayerID, payerTypeCode
UNION ALL
---Capitated
select P.PracticeID, PayerTypeCode, PayerID, sum(-1 * rtp.amount)
from CapitatedAccountToPayment rtp
INNER JOIN Payment p on p.PaymentID = rtp.PaymentID
WHERE rtp.PostingDate <= @EndDate AND p.PracticeID=ISNULL(@PracticeID, p.PracticeID)
group BY P.PracticeID, p.PayerID, payerTypeCode
) AS V
GROUP BY PracticeID
SELECT aa.PracticeID ,
SUM(TotalAmount) - ISNULL(su.PaymentAmount, 0)AS GrandTotalAR
INTO #AR
FROM #AR_ASN aa
LEFT JOIN #SummarizedUnapplied AS su ON aa.practiceID = su.PracticeID
GROUP BY aa.PracticeID ,
ISNULL(su.PaymentAmount, 0)
ORDER BY aa.PracticeID
CREATE TABLE #ASNMin (PracticeID INT,ClaimID INT, ClaimTransactionID INT)
INSERT INTO #ASNMin(PracticeID,ClaimID, ClaimTransactionID)
SELECT CAA.PracticeID, CAA.ClaimID, MAX(ClaimTransactionID) ClaimTransactionID
FROM ClaimAccounting_Assignments CAA
WHERE CAA.PostingDate BETWEEN DATEADD(dd,-90,@EndDate) AND @EndDate AND CAA.PracticeID=ISNULL(@PracticeID, caa.PracticeID)
GROUP BY CAA.ClaimID, CAA.PracticeID
ALTER TABLE #AR
ADD DatabaseName VARCHAR(255)
UPDATE #AR
SET DatabaseName=(SELECT DB_NAME())
SELECT a.PracticeID,a.GrandTotalAR, TotalChargesfor90days,sub.DailyCharges, a.GrandTotalAR/sub.DailyCharges AS DRO, DatabaseName
FROM #AR AS a
INNER JOIN
---Charges for Last 90 days based on claim posting date
(
SELECT ca.PracticeID,SUM(Amount)AS TotalChargesfor90days,SUM(Amount)/90 AS DailyCharges
FROM ClaimAccounting AS ca
INNER JOIN ClaimAccounting_Assignments AS caa ON ca.ClaimID = caa.ClaimID AND ca.PracticeID = caa.PracticeID
INNER JOIN #ASNMin AS am ON caa.ClaimID = am.ClaimID AND caa.ClaimTransactionID = am.ClaimTransactionID AND caa.PracticeID = am.PracticeID
WHERE ca.ClaimTransactionTypeCode='CST'
AND ca.PostingDate BETWEEN DATEADD(dd,-90,@EndDate) AND @EndDate AND CAA.PracticeID=ISNULL(@PracticeID, caa.PracticeID)
GROUP BY ca.PracticeID) AS sub ON a.PracticeID = sub.PracticeID
ORDER BY DRO
DROP TABLE #AR_ASN, #AR, #ASNMin
--DROP TABLE #ASN
DROP TABLE #SummarizedUnapplied
END
GO
Indexed View
USE [superbill_1087_dev]
GO
/****** Object: View [dbo].[vwReporting_Claim_ClaimAccounting] Script Date: 05/03/2012 09:07:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[vwReporting_Claim_ClaimAccounting]
WITH SCHEMABINDING
as
Select C.ClaimID, C.PracticeId,C.EncounterProcedureID, CA.ProviderID, CA.ClaimTransactionID, CA.ClaimTransactionTypeCode,CA.ProcedureCount,
CA.Amount, CA.ARAmount, CA.PostingDate as CA_PostingDate, CA.PaymentID
from dbo.Claim AS C
Inner Join dbo.ClaimAccounting AS ca On Ca.ClaimId=c.ClaimId And CA.PracticeId=c.PracticeID
GO
USE [superbill_1087_dev]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [CX_vwReporting_Claim_ClaimAccounting_ClaimID_PracticeID_ClaimTransactionID] Script Date: 05/03/2012 09:23:54 ******/
CREATE UNIQUE CLUSTERED INDEX [CX_vwReporting_Claim_ClaimAccounting_ClaimID_PracticeID_ClaimTransactionID] ON [dbo].[vwReporting_Claim_ClaimAccounting]
(
[ClaimID] ASC,
[PracticeId] ASC,
[ClaimTransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [superbill_1087_dev]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [IX_Reporting_Claim_ClaimAccounting_ClaimTransactionTypeCode_Posting_Date_INC_ClaimID_PracticeID] Script Date: 05/03/2012 09:24:23 ******/
CREATE NONCLUSTERED INDEX [IX_Reporting_Claim_ClaimAccounting_ClaimTransactionTypeCode_Posting_Date_INC_ClaimID_PracticeID] ON [dbo].[vwReporting_Claim_ClaimAccounting]
(
[ClaimTransactionTypeCode] ASC,
[CA_PostingDate] ASC
)
INCLUDE ( [ClaimID],
[PracticeId],
[EncounterProcedureID],
[Amount],
[PaymentID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [superbill_1087_dev]
GO
/****** Object: View [dbo].[vwReporting_Claim_Payments] Script Date: 05/03/2012 09:08:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[vwReporting_Claim_Payments]
WITH SCHEMABINDING
as
Select C.ClaimID, C.PracticeId,C.EncounterProcedureID, CA.ProviderID, CA.ClaimTransactionID, CA.ClaimTransactionTypeCode,CA.ProcedureCount, CA.Amount, CA.ARAmount, CA.PostingDate as CA_PostingDate,
P.PaymentId, P.PaymentAmount, P.PayerTypeCode, P.PayerID, P.PostingDate Pay_PostingDate, P.BatchID
from dbo.Claim AS C
Inner Join dbo.ClaimAccounting AS ca On Ca.ClaimId=c.ClaimId And CA.PracticeId=c.PracticeID
Inner Join dbo.Payment AS P on ca.PracticeID=P.PracticeId And Ca.PaymentId=p.PaymentID
GO
USE [superbill_1087_dev]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [CX_vwReporting_Claim_Payments_ClaimID_PracticeID_ClaimTransactionID] Script Date: 05/03/2012 09:24:47 ******/
CREATE UNIQUE CLUSTERED INDEX [CX_vwReporting_Claim_Payments_ClaimID_PracticeID_ClaimTransactionID] ON [dbo].[vwReporting_Claim_Payments]
(
[ClaimID] ASC,
[PracticeId] ASC,
[ClaimTransactionID] ASC,
[PaymentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [superbill_1087_dev]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [IX_Reporting_Claim_Payments_ClaimTransactionTypeCode_INC_PracticeID_Amount_PostingDate_PaymentID_Pay_PostingDate] Script Date: 05/03/2012 09:24:58 ******/
CREATE NONCLUSTERED INDEX [IX_Reporting_Claim_Payments_ClaimTransactionTypeCode_INC_PracticeID_Amount_PostingDate_PaymentID_Pay_PostingDate] ON [dbo].[vwReporting_Claim_Payments]
(
[ClaimTransactionTypeCode] ASC
)
INCLUDE ( [PracticeId],
[Amount],
[CA_PostingDate],
[PaymentId],
[Pay_PostingDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [superbill_1087_dev]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [IX_Reporting_Claim_Payments_ClaimTransactionTypeCode_PaymentAmount] Script Date: 05/03/2012 09:25:07 ******/
CREATE NONCLUSTERED INDEX [IX_Reporting_Claim_Payments_ClaimTransactionTypeCode_PaymentAmount] ON [dbo].[vwReporting_Claim_Payments]
(
[ClaimTransactionTypeCode] ASC,
[PaymentAmount] ASC
)
INCLUDE ( [PracticeId],
[Amount],
[CA_PostingDate],
[PaymentId],
[Pay_PostingDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Do you really want all the table definitions? I'm more than happy to that. Just a lot of code
The claim table has approximately 704K records
ClaimAccounting has 3M
Payment Has 72K
ClaimAccounting_Assignments 700k
vwReporting_Claim_ClaimAccounting 2.3M
vwReporting_Claim_Payments 1.3M
May 3, 2012 at 10:21 am
So The stored procedure is getting aging information for accounts up to the current date and all charges for the last 90. Based on claim information.
USE [superbill_1087_dev]
GO
USE [superbill_1087_dev]
GO
/****** Object: Table [dbo].[Claim] Script Date: 05/03/2012 09:56:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
SET ARITHABORT ON
GO
CREATE TABLE [dbo].[Claim](
[ClaimID] [int] IDENTITY(1,1) NOT NULL,
[PracticeID] [int] NOT NULL,
[ClaimStatusCode] [char](1) NOT NULL,
[PatientID] [int] NULL,
[ReleaseSignatureSourceCode] [char](1) NULL,
[EncounterProcedureID] [int] NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[TIMESTAMP] [timestamp] NULL,
[LocalUseData] [varchar](25) NULL,
[ReferringProviderIDNumber] [varchar](32) NULL,
[NonElectronicOverrideFlag] [bit] NULL,
[ClearinghouseTrackingNumber] [varchar](64) NULL,
[PayerTrackingNumber] [varchar](64) NULL,
[ClearinghouseProcessingStatus] [varchar](1) NULL,
[PayerProcessingStatus] [varchar](256) NULL,
[ClearinghousePayer] [varchar](64) NULL,
[ClearinghousePayerReported] [varchar](64) NULL,
[PayerProcessingStatusTypeCode] [char](3) NULL,
[CurrentPayerProcessingStatusTypeCode] [char](3) NULL,
[CurrentClearinghouseProcessingStatus] [varchar](1) NULL,
[CreatedUserID] [int] NULL,
[ModifiedUserID] [int] NULL,
[DKProcedureDateOfServiceID] [int] NULL,
[SearchIndex] AS ((((((((((isnull([ClearinghousePayer],'')+';')+isnull([ClearinghousePayerReported],''))+';')+isnull([ClearinghouseProcessingStatus],''))+';')+isnull([PayerProcessingStatus],''))+';')+isnull([PayerTrackingNumber],''))+';')+isnull([ClearinghouseTrackingNumber],'')),
CONSTRAINT [PK_Claim] PRIMARY KEY NONCLUSTERED
(
[ClaimID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [superbill_1087_dev]
/****** Object: Index [CI_Claim] Script Date: 05/03/2012 09:56:38 ******/
CREATE UNIQUE CLUSTERED INDEX [CI_Claim] ON [dbo].[Claim]
(
[PracticeID] ASC,
[DKProcedureDateOfServiceID] DESC,
[EncounterProcedureID] DESC,
[ClaimID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_1087_dev]
/****** Object: Index [IX_Claim_ClaimStatusCode] Script Date: 05/03/2012 09:56:38 ******/
CREATE NONCLUSTERED INDEX [IX_Claim_ClaimStatusCode] ON [dbo].[Claim]
(
[ClaimStatusCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_1087_dev]
/****** Object: Index [IX_Claim_EncounterProcedureID] Script Date: 05/03/2012 09:56:38 ******/
CREATE NONCLUSTERED INDEX [IX_Claim_EncounterProcedureID] ON [dbo].[Claim]
(
[EncounterProcedureID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_1087_dev]
/****** Object: Index [IX_Claim_LocalUseData] Script Date: 05/03/2012 09:56:38 ******/
CREATE NONCLUSTERED INDEX [IX_Claim_LocalUseData] ON [dbo].[Claim]
(
[LocalUseData] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_1087_dev]
/****** Object: Index [IX_Claim_PatientID] Script Date: 05/03/2012 09:56:38 ******/
CREATE NONCLUSTERED INDEX [IX_Claim_PatientID] ON [dbo].[Claim]
(
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
USE [superbill_1087_dev]
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
/****** Object: Index [IX_Claim_PracticeID_SearchIndex] Script Date: 05/03/2012 09:56:38 ******/
CREATE NONCLUSTERED INDEX [IX_Claim_PracticeID_SearchIndex] ON [dbo].[Claim]
(
[PracticeID] ASC,
[SearchIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Claim] WITH CHECK ADD CONSTRAINT [FK_Claim_ClaimStatus] FOREIGN KEY([ClaimStatusCode])
REFERENCES [dbo].[ClaimStatus] ([ClaimStatusCode])
GO
ALTER TABLE [dbo].[Claim] CHECK CONSTRAINT [FK_Claim_ClaimStatus]
GO
ALTER TABLE [dbo].[Claim] WITH NOCHECK ADD CONSTRAINT [FK_Claim_EncounterProcedureID] FOREIGN KEY([EncounterProcedureID])
REFERENCES [dbo].[EncounterProcedure] ([EncounterProcedureID])
GO
ALTER TABLE [dbo].[Claim] CHECK CONSTRAINT [FK_Claim_EncounterProcedureID]
GO
ALTER TABLE [dbo].[Claim] WITH NOCHECK ADD CONSTRAINT [FK_Claim_PayerProcessingStatusType] FOREIGN KEY([PayerProcessingStatusTypeCode])
REFERENCES [dbo].[PayerProcessingStatusType] ([PayerProcessingStatusTypeCode])
GO
ALTER TABLE [dbo].[Claim] CHECK CONSTRAINT [FK_Claim_PayerProcessingStatusType]
GO
ALTER TABLE [dbo].[Claim] WITH CHECK ADD CONSTRAINT [FK_Claim_Practice] FOREIGN KEY([PracticeID])
REFERENCES [dbo].[Practice] ([PracticeID])
GO
ALTER TABLE [dbo].[Claim] CHECK CONSTRAINT [FK_Claim_Practice]
GO
ALTER TABLE [dbo].[Claim] ADD CONSTRAINT [DF__Claim__CreatedDa__0FF8DE83] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[Claim] ADD CONSTRAINT [DF__Claim__ModifiedD__10ED02BC] DEFAULT (getdate()) FOR [ModifiedDate]
GO
ALTER TABLE [dbo].[Claim] ADD CONSTRAINT [DF_NonElectronicOverrideFlag] DEFAULT (0) FOR [NonElectronicOverrideFlag]
GO
USE [superbill_1087_dev]
GO
/****** Object: Table [dbo].[ClaimAccounting] Script Date: 05/03/2012 09:19:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClaimAccounting](
[PracticeID] [int] NOT NULL,
[ClaimID] [int] NOT NULL,
[ProviderID] [int] NOT NULL,
[PatientID] [int] NOT NULL,
[ClaimTransactionID] [int] NOT NULL,
[ClaimTransactionTypeCode] [char](3) NULL,
[Status] [bit] NOT NULL,
[ProcedureCount] [decimal](19, 4) NULL,
[Amount] [money] NULL,
[ARAmount] [money] NULL,
[PostingDate] [datetime] NOT NULL,
[CreatedUserID] [int] NULL,
[PaymentID] [int] NULL,
[EncounterProcedureID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ClaimAccounting] ADD CONSTRAINT [DF_ClaimAccounting_Status] DEFAULT (0) FOR [Status]
GO
ALTER TABLE [dbo].[ClaimAccounting] ADD CONSTRAINT [DF_ClaimAccounting_ProcedureCount] DEFAULT ((0)) FOR [ProcedureCount]
GO
/****** Object: Table [dbo].[ClaimAccounting_Assignments] Script Date: 05/03/2012 09:19:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClaimAccounting_Assignments](
[PracticeID] [int] NULL,
[ClaimID] [int] NULL,
[ClaimTransactionID] [int] NULL,
[InsurancePolicyID] [int] NULL,
[InsuranceCompanyPlanID] [int] NULL,
[PatientID] [int] NULL,
[LastAssignment] [bit] NULL,
[Status] [bit] NULL,
[PostingDate] [datetime] NOT NULL,
[EndPostingDate] [datetime] NULL,
[LastAssignmentOfEndPostingDate] [bit] NULL,
[EndClaimTransactionID] [int] NULL,
[DKPostingDateID] [int] NULL,
[DKEndPostingDateID] [int] NULL,
[RelativePrecedence] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ClaimAccounting_Assignments] ADD CONSTRAINT [DF_ClaimAccounting_Assignments_LastAssignment] DEFAULT (0) FOR [LastAssignment]
GO
ALTER TABLE [dbo].[ClaimAccounting_Assignments] ADD CONSTRAINT [DF_ClaimAccounting_Assigments_Status] DEFAULT (0) FOR [Status]
GO
ALTER TABLE [dbo].[ClaimAccounting_Assignments] ADD CONSTRAINT [DF_ClaimAccounting_Assignments_LastAssignmentOfEndPostingDate] DEFAULT ((0)) FOR [LastAssignmentOfEndPostingDate]
GO
USE [superbill_1087_dev]
GO
/****** Object: Table [dbo].[Payment] Script Date: 05/03/2012 09:21:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Payment](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[PracticeID] [int] NOT NULL,
[PaymentAmount] [money] NOT NULL,
[PaymentMethodCode] [char](1) NOT NULL,
[PayerTypeCode] [char](1) NULL,
[PayerID] [int] NULL,
[PaymentNumber] [varchar](30) NULL,
[Description] [varchar](250) NULL,
[CreatedDate] [datetime] NULL,
[ModifiedDate] [datetime] NULL,
[TIMESTAMP] [timestamp] NULL,
[SourceEncounterID] [int] NULL,
[PostingDate] [datetime] NOT NULL,
[PaymentTypeID] [int] NULL,
[DefaultAdjustmentCode] [varchar](10) NULL,
[BatchID] [varchar](50) NULL,
[CreatedUserID] [int] NULL,
[ModifiedUserID] [int] NULL,
[SourceAppointmentID] [int] NULL,
[EOBEditable] [bit] NULL,
[AdjudicationDate] [datetime] NULL,
[ClearinghouseResponseID] [int] NULL,
[ERAErrors] [xml] NULL,
[AppointmentID] [int] NULL,
[AppointmentStartDate] [datetime] NULL,
[PaymentCategoryID] [int] NULL,
[overrideClosingDate] [bit] NOT NULL,
[IsOnline] [bit] NOT NULL,
CONSTRAINT [PK_Payment] PRIMARY KEY NONCLUSTERED
(
[PaymentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Payment] WITH CHECK ADD CONSTRAINT [FK_Payment_Appointment] FOREIGN KEY([AppointmentID])
REFERENCES [dbo].[Appointment] ([AppointmentID])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_Appointment]
GO
ALTER TABLE [dbo].[Payment] WITH CHECK ADD CONSTRAINT [FK_Payment_Encounter] FOREIGN KEY([SourceEncounterID])
REFERENCES [dbo].[Encounter] ([EncounterID])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_Encounter]
GO
ALTER TABLE [dbo].[Payment] WITH CHECK ADD CONSTRAINT [FK_Payment_PaymentBusinessRuleLog] FOREIGN KEY([PaymentID])
REFERENCES [dbo].[Payment] ([PaymentID])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_PaymentBusinessRuleLog]
GO
ALTER TABLE [dbo].[Payment] WITH CHECK ADD CONSTRAINT [FK_Payment_PaymentCategoryID] FOREIGN KEY([PaymentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_PaymentCategoryID]
GO
ALTER TABLE [dbo].[Payment] WITH CHECK ADD CONSTRAINT [FK_Payment_PaymentMethodCode] FOREIGN KEY([PaymentMethodCode])
REFERENCES [dbo].[PaymentMethodCode] ([PaymentMethodCode])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_PaymentMethodCode]
GO
ALTER TABLE [dbo].[Payment] WITH CHECK ADD CONSTRAINT [FK_Payment_PaymentType] FOREIGN KEY([PaymentTypeID])
REFERENCES [dbo].[PaymentType] ([PaymentTypeID])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_PaymentType]
GO
ALTER TABLE [dbo].[Payment] WITH CHECK ADD CONSTRAINT [FK_Payment_Practice] FOREIGN KEY([PracticeID])
REFERENCES [dbo].[Practice] ([PracticeID])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_Practice]
GO
ALTER TABLE [dbo].[Payment] WITH CHECK ADD CONSTRAINT [FK_PaymentToAdjustment] FOREIGN KEY([DefaultAdjustmentCode])
REFERENCES [dbo].[Adjustment] ([AdjustmentCode])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_PaymentToAdjustment]
GO
ALTER TABLE [dbo].[Payment] ADD CONSTRAINT [DF__Payment__Created__409B4A87] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[Payment] ADD CONSTRAINT [DF__Payment__Modifie__418F6EC0] DEFAULT (getdate()) FOR [ModifiedDate]
GO
ALTER TABLE [dbo].[Payment] ADD CONSTRAINT [DF_Payment_EOBEditable] DEFAULT ((1)) FOR [EOBEditable]
GO
ALTER TABLE [dbo].[Payment] ADD CONSTRAINT [DF_Payment_overrideClosingDate] DEFAULT ((0)) FOR [overrideClosingDate]
GO
ALTER TABLE [dbo].[Payment] ADD CONSTRAINT [DF_IsOnline] DEFAULT ((0)) FOR [IsOnline]
GO
[/code]
May 3, 2012 at 10:37 am
I think this is a big issue but im new to performance tuning so i may get a lesson as well.
this section of code in each where clause (or most of them any way):
AND CA.PracticeID=ISNULL(@PracticeID, ca.PracticeID)
i believe is causing an index scan instead of a seek. when you run the code in a query window with your 1-2 second execution time do you place a practiceID in each query??
i would look at http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ it deals with catch all queries like you have.
EDIT to add [ url ] tags
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 3, 2012 at 10:39 am
No. In the case that I am running this I am setting the PracticeID to null. The PracticeId Parameter is optional so I have to account for it somehow.
May 3, 2012 at 10:48 am
can you post the execution plans for each query. i think since ISNULL() is not SARGABLE it is causing an index scan which is slow to begin with. then you have the same problem with 5 queries. it can add alot of time in the index seeks. also can you post your indexes on the tables? the only ones in the table deff's were non clustered Primary Keys. after fixing the catch all we may need a new index as well.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 3, 2012 at 10:55 am
If it were the indexes on the PracticeID wouldn't it be an issue in the query as well as the stored procedure? I eliminated the practiceId parameter all together to test your theory. The stored procedure still is not finishing.
May 3, 2012 at 11:10 am
pamozer (5/3/2012)
If it were the indexes on the PracticeID wouldn't it be an issue in the query as well as the stored procedure? I eliminated the practiceId parameter all together to test your theory. The stored procedure still is not finishing.
so im showing my noobish ness here by asking a question i should have asked earlier.
can you post the execution times on each step of the SP. run through them each every section. along with the execution plans.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 3, 2012 at 1:38 pm
I would agree with cap'n about ISNULL in your where clause but you are right that would not affect performance running this in different environments. This sounds like a classic case of parameter sniffing.
Check out Gail's blog posts about it here:
Parameter sniffing Part 2[/url]
Parameter sniffing Part 3[/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/
May 3, 2012 at 1:44 pm
By the looks of this, I'm going to hazard a guess that parameter sniffing is the issue here. You'll want to Bing/Google/whatever "parameter sniffing" in order to get more data on it, but you can start here: http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 3, 2012 at 3:21 pm
So i thought it might be parameter sniffing. So I added a with Recompile to help with that. Also the isnull isn't really a changeable item unless I make it an or. Which really isn't going to perform much better.
I will take a look at the links you sent.
thanks
May 4, 2012 at 2:47 am
pamozer (5/3/2012)
Sorry about that. Also when I run a trace against the stored proc it doesn't get past the first Insert....
How do you know this? Have you traced it?
What the size of data is going to be inserted into first # table?
Comment out INSERT and leave just first SELECT in your sp to try if the recordset will be returned at all.
If so, any problem with tempdb?
May 4, 2012 at 10:16 am
It turns out it was parameter sniffing. Still not sure why the With Recompile didn't fix the problem. Thanks everyone for your suggestions.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply