October 15, 2008 at 4:28 pm
You know, I dont know how we could have missed it, but looking back over the information provided, I do not see any execution plans? Also, the table definitions do not include any Keys or Indexes. Can we please get those? Remember to use the sqlplan/XML format for the execution plans.
Thanks
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 15, 2008 at 4:37 pm
MrBaseball34 (10/15/2008)
I guess I can but getting past the speed issue seems to be the biggest problem.
Here is all data for the MRN 5079
October 15, 2008 at 4:38 pm
will post the exec plans and index data tomorrow morn.
October 21, 2008 at 3:32 pm
Ok, sorry for the delay. I created an index on MRN+Status on ptDictation today and
made no difference in the plan.
Here is the execution plans and code used:
-- Query1.sqlplan
-- 346195 records in 54 secs
-- Code:
SELECT CASE
WHEN EXISTS (SELECT d.MRN FROM ptDictation d
WHERE d.Status = 'Approved'
AND e.MRN = d.MRN
AND d.DOS = e.DateOfService ) THEN 2
WHEN EXISTS (SELECT s.MRN FROM ptSchedule s
WHERE s.ApptTypeID = 5
AND e.MRN = s.MRN
AND CONVERT(varchar,s.StartDtTm, 10) = CONVERT(varchar,e.DateofService, 10)) THEN 1
ELSE 0
END as DictationState
FROM ptEncounter e
-- Query2.sqlplan
-- 346195 records in 8 secs
-- Code:
SELECT CASE
WHEN EXISTS (SELECT d.MRN FROM ptDictation d
INNER JOIN ptEncounter ee
ON ee.MRN = d.MRN AND d.DOS = e.DateOfService
WHERE d.Status = 'Approved') THEN 2
WHEN EXISTS (SELECT s.MRN FROM ptSchedule s
INNER JOIN ptEncounter eee
ON eee.MRN = s.MRN
AND CONVERT(varchar,s.StartDtTm, 10) = CONVERT(varchar,e.DateofService, 10)
WHERE s.ApptTypeID = 5) THEN 1
ELSE 0
END as DictationState
FROM ptEncounter e
-- Query3.sqlplan
-- ???? 1521479 records in 18 secs
-- Code:
SELECT CASE
WHEN d.MRN IS NOT NULL THEN 2
WHEN s.MRN IS NOT NULL THEN 1
ELSE 0
END as DictationState
FROM ptEncounter e
LEFT JOIN ptDictation d
ON e.MRN = d.MRN
AND d.DOS = e.DateOfService
AND d.Status = 'Approved'
LEFT JOIN ptSchedule s
ON e.MRN = s.MRN
AND CONVERT(varchar,s.StartDtTm, 10) = CONVERT(varchar,e.DateofService, 10)
AND s.ApptTypeID = 5
October 21, 2008 at 3:51 pm
Could you please post the table definitions, including the Keys and Indexes? Thanks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 22, 2008 at 11:08 am
RBY...
-- ptEncounter
CREATE TABLE [dbo].[ptEncounter](
[EncounterID] [int] IDENTITY(1,1) NOT NULL,
[BillNumber] [int] NOT NULL,
[ClaimNumber] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LatestRec] [bit] NOT NULL,
[MRN] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SubscriberContactID] [int] NULL,
[LocationID] [int] NULL,
[AuthID] [int] NULL,
[BillerUserID] [int] NULL,
[PostingDtTm] [datetime] NULL,
[SiteID] [int] NULL,
[DeptID] [int] NULL,
[InsuranceID] [int] NULL,
[RefPhysID] [int] NULL,
[ProviderID] [int] NULL,
[DateOfService] [datetime] NULL,
[DateOfIllness] [datetime] NULL,
[TotalAmt] [decimal](18, 2) NULL,
[PaidAmt] [decimal](18, 2) NULL,
[OpenAmt] [decimal](18, 2) NULL,
[PaidInFull] [bit] NULL,
[ClaimSubmissionDtTm] [datetime] NULL,
[ClaimStatus] [int] NULL,
[Diag1] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Diag2] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Diag3] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Diag4] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatementMessage1] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatementMessage2] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillComment1] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillComment2] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AdmitDtTm] [datetime] NULL,
[DischargeDtTm] [datetime] NULL,
[AutoAccidentRelated] [bit] NULL,
[EmploymentAccidentRelated] [bit] NULL,
[OtherAccidentRelated] [bit] NULL,
[State] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PreAuthNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedByUserID] [int] NULL,
[CreatedDtTm] [datetime] NULL,
[ModifiedByUserID] [int] NULL,
[ModifiedDtTm] [datetime] NULL,
[DeletedByUserID] [int] NULL,
[DeletedDtTm] [datetime] NULL,
[FirstOnsetDtTm] [datetime] NULL,
[AcceptAssignment] [bit] NULL,
[Errors] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BucketID] [int] NULL,
CONSTRAINT [PK_ptEncounter] PRIMARY KEY CLUSTERED
(
[EncounterID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [AHOG]
GO
ALTER TABLE [dbo].[ptEncounter] WITH NOCHECK ADD CONSTRAINT [FK_ptEncounter_coApptSites] FOREIGN KEY([SiteID])
REFERENCES [dbo].[coApptSites] ([SiteID])
GO
ALTER TABLE [dbo].[ptEncounter] CHECK CONSTRAINT [FK_ptEncounter_coApptSites]
GO
ALTER TABLE [dbo].[ptEncounter] WITH NOCHECK ADD CONSTRAINT [FK_ptEncounter_coBillingDepartments] FOREIGN KEY([DeptID])
REFERENCES [dbo].[coBillingDepartments] ([DepartmentID])
GO
ALTER TABLE [dbo].[ptEncounter] CHECK CONSTRAINT [FK_ptEncounter_coBillingDepartments]
GO
ALTER TABLE [dbo].[ptEncounter] WITH NOCHECK ADD CONSTRAINT [FK_ptEncounter_ptAuthorizations] FOREIGN KEY([AuthID])
REFERENCES [dbo].[ptAuthorizations] ([AuthID])
GO
ALTER TABLE [dbo].[ptEncounter] CHECK CONSTRAINT [FK_ptEncounter_ptAuthorizations]
GO
ALTER TABLE [dbo].[ptEncounter] WITH NOCHECK ADD CONSTRAINT [FK_ptEncounter_ptContacts] FOREIGN KEY([SubscriberContactID])
REFERENCES [dbo].[ptContacts] ([ContactID])
GO
ALTER TABLE [dbo].[ptEncounter] CHECK CONSTRAINT [FK_ptEncounter_ptContacts]
GO
ALTER TABLE [dbo].[ptEncounter] WITH NOCHECK ADD CONSTRAINT [FK_ptEncounter_ptInsurancePlans] FOREIGN KEY([InsuranceID])
REFERENCES [dbo].[ptInsurancePlans] ([InsurancePlanID])
GO
ALTER TABLE [dbo].[ptEncounter] CHECK CONSTRAINT [FK_ptEncounter_ptInsurancePlans]
-- ptEncounter secondary Indexes
CREATE NONCLUSTERED INDEX [IX_ptEncounter_MRN] ON [dbo].[ptEncounter]
(
[MRN] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
-- ptDictation
CREATE TABLE [dbo].[ptDictation](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MRN] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DOS] [datetime] NULL,
[DocumentNum] [int] NULL,
[SectionTitle] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SectionOrder] [decimal](10, 2) NULL,
[Status] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Doctor] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SystemTime] [timestamp] NULL,
[Text] [varchar](7800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Deleted] [int] NULL,
[DeletedBy] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DeletedDate] [datetime] NULL,
[Signed] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Printed] [int] NULL,
[DictationDoc] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgDocNum] [int] NULL CONSTRAINT [DF_ptDictation_OrgDocNum] DEFAULT (0),
[TimeStamp] [datetime] NULL,
[PrintedDate] [datetime] NULL,
[Location] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ptDictation] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-- ptDictation secondary indexes
CREATE NONCLUSTERED INDEX [IX_ptDictation_Deleted] ON [dbo].[ptDictation]
(
[Deleted] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ptDictation_DocumentNum] ON [dbo].[ptDictation]
(
[DocumentNum] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ptDictation_DOS] ON [dbo].[ptDictation]
(
[DOS] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ptDictation_Location] ON [dbo].[ptDictation]
(
[Location] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ptDictation_MRN] ON [dbo].[ptDictation]
(
[MRN] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ptDictation_MRN_Status] ON [dbo].[ptDictation]
(
[MRN] ASC,
[Status] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ptDictation_SectionTitle] ON [dbo].[ptDictation]
(
[SectionTitle] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_ptDictation_Status] ON [dbo].[ptDictation]
(
[Status] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
-- ptSchedule
CREATE TABLE [dbo].[ptSchedule](
[ScheduleId] [int] IDENTITY(1,1) NOT NULL,
[MRN] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ApptTypeID] [int] NULL,
[ResourceId] [int] NULL,
[StartDtTm] [datetime] NULL,
[EndDtTm] [datetime] NULL,
[Description] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [int] NULL,
[CheckedInDtTm] [datetime] NULL,
[CheckedOutDtTm] [datetime] NULL,
[Flow] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModifiedDtTm] [datetime] NULL,
[ModifiedByUserID] [int] NULL,
[WaitList] [int] NULL,
[ApptBlockID] [int] NULL,
[temp] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EncounterID] [int] NULL,
CONSTRAINT [PK_ptSchedule] PRIMARY KEY CLUSTERED
(
[ScheduleId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [AHOG]
GO
ALTER TABLE [dbo].[ptSchedule] WITH CHECK ADD CONSTRAINT [FK_ptSchedule_coApptResources] FOREIGN KEY([ResourceId])
REFERENCES [dbo].[coApptResources] ([ResourceId])
GO
ALTER TABLE [dbo].[ptSchedule] WITH CHECK ADD CONSTRAINT [FK_ptSchedule_coApptStatus] FOREIGN KEY([Status])
REFERENCES [dbo].[coApptStatus] ([ID])
GO
ALTER TABLE [dbo].[ptSchedule] WITH CHECK ADD CONSTRAINT [FK_ptSchedule_coApptTypes] FOREIGN KEY([ApptTypeID])
REFERENCES [dbo].[coApptTypes] ([ApptTypeID])
-- No secondary indexes on ptSchedule
October 22, 2008 at 12:12 pm
Thanks. You need these two indexes:
CREATE NONCLUSTERED INDEX [IX_ptDictation_MRN_Status] ON [dbo].[ptDictation]
( [MRN] ASC,
[DOS] ASC,
[Status] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ptEncounter_MRN] ON [dbo].[ptEncounter]
( [MRN] ASC,
[DateOfService] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
You might need more, but by my estimates, this should cut your overall run time from 80 seconds to about 20-30 seconds.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 22, 2008 at 12:15 pm
Oops, you should change the Index names (I did a cut and paste).
Or alternatively, drop the existing indexes of the same names as they are redundant now.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 10:28 am
Did you get a chance to try this?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 1:41 pm
Yes, I did and it had no effect on the performance when run against the entire table.
October 24, 2008 at 3:12 pm
Really?!? That is extremely surprising to me, I cannot imagine why it wouldn't use the new index?
Can you try the following version of Query1 and post the Actual Execution Plan?
-- Query1
SELECT CASE
WHEN EXISTS (SELECT d.MRN FROM ptDictation d WITH (INDEX ({whatever_the_new_index_is}))
WHERE d.Status = 'Approved'
AND e.MRN = d.MRN
AND d.DOS = e.DateOfService ) THEN 2
WHEN EXISTS (SELECT s.MRN FROM ptSchedule s
WHERE s.ApptTypeID = 5
AND e.MRN = s.MRN
AND CONVERT(varchar,s.StartDtTm, 10) = CONVERT(varchar,e.DateofService, 10)) THEN 1
ELSE 0
END as DictationState
FROM ptEncounter e
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply