December 21, 2012 at 3:23 am
Hi,
here is the query bellow consist of joins but it takes lot of time to execute morethan 5 minutes.. how can I reduced its time--
SELECT DimLocation.dimLocationId,
dimpatient.dimpatientID,
DimCategory.DimCategoryID,
lkpProvider.dimlkpProviderId,
DimInsurance.dimInsId,
--DimSpecialities.dimSpecId,
DxTx.[Flocation],
DxTx.[Faccount]
,DxTx.[Fpatient]
,DxTx.[fCategory]
,DxTx.[Fcontractno]
--,DxTx.[Fdxnumber]
,DxTx.[Fdiagdate]
,DxTx.[Forthogen]
,DxTx.[Fprocedure]
,DimProcedure.fMaxQty
,DxTx.[Fprovider]
,DxTx.[FDxProvider]
,DxTx.[fTxConsult]
,DimProcedure.fToothEntry as Ftooth
,DimProcedure.fSurfaces as Fsurface
,DxTx.[Fdxsold]
,DxTx.[Ftxnumber]
,DxTx.[Ftxdate]
--,DxTx.[Ffee]
,DxTx.[Finsdue]
,DxTx.[Fpvtdue]
,DxTx.[Fprodamt]
,TxPayPlan.[Frvu]
,TxPayPlan.fEBill as Fbillable
-- ,DxTx.[Festimate]
,DxTx.Fbilledclaims as claimPaid
,DimProcedure.Fadacode
,DxTx.[Finscode]
-- ,DxTx.[fPANumber]
--,DxTx.delete
-- ,DxTx.[Fupdateby]
-- ,DxTx.[Fwhen]
,DxTx.DxTxID
--,DxTx.[fDuv]
--,DxTx.[fSchedID]
,DimProcedure.fOptional as Fopt
,DxTx.[fCORProc]
----,DxTx.[fCaseNo]
,DxTx.RDH
--,Dware3.dbo.DimPatient.dimpatientid
-- ,Dware3.dbo.DimPatient.dimLocationId
FROM DxTx
--inner join Dware3.dbo.DimPatient on DxTx.Fpatient = Dware3.dbo.DimPatient.fpatient
left join Dimlocation on DxTx.Flocation collate Latin1_General_CI_AI = DimLocation.flocation
left join DimInsurance on DxTx.Finscode collate Latin1_General_CI_AI = DimInsurance.FinsCode and DimInsurance.dimLocationId = DimLocation.dimLocationId
left join DimPatient on dimpatient.chartno = dxtx.EChartNo and DimPatient.Fcategory = dxtx.fCategory and dimpatient.fPatient = dxtx.Fpatient
left join DimCategory on DimCategory.Fcategory = DxTx.fCategory
left join lkpProvider on lkpProvider.ProviderCode = DxTx.fProvider and lkpProvider.ProviderCode = dxtx.fProvider
left join DimProcedure on DimProcedure.fProcedure = dxtx.Fprocedure
left join TxPayPlan on TxPayPlan.Faccount = dxtx.Faccount and TxPayPlan.Finscode =dxtx.Finscode
I cant give you the table structure as there are lots of tables...
plz tel me how can I improve the performance of this query
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 21, 2012 at 3:59 am
kapil_kk (12/21/2012)
...I cant give you the table structure as there are lots of tables...
plz tel me how can I improve the performance of this query
To improve the performance of your query you should do performance tuning.
I can't give you the details as there are a lot of thing there "depends on" your table structures, indexes etc. 😉
Hope this helps.
If you are seriously looking for help with performance, you will need to provide relevant DDL and current execution plan.
December 21, 2012 at 4:10 am
these are the table structure that i am using:
CREATE TABLE [dbo].[DimInsurance](
[dimInsId] [int] IDENTITY(1,1) NOT NULL,
[EtlDate] [datetime] NOT NULL,
[dimLocationId] [int] NULL,
[dimInsuranceGroupId] [int] NULL,
[InsuranceGroupName] [nvarchar](30) NULL,
[Fcategory] [nvarchar](30) NULL,
[Fbillout] [tinyint] NULL,
[FinsCode] [nvarchar](10) NULL,
[Finsname] [nvarchar](60) NULL,
[Faddress1] [nvarchar](60) NULL,
[Faddress2] [nvarchar](60) NULL,
[Fcity] [nvarchar](40) NULL,
[Fstate] [nvarchar](4) NULL,
[Fzip] [nvarchar](10) NULL,
[Fphone] [nvarchar](28) NULL,
[NewInsCode] [nvarchar](28) NULL,
[RecStatus] [nchar](10) NULL,
CONSTRAINT [PK_DimInsurance] PRIMARY KEY CLUSTERED
(
[dimInsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DimInsurance] ADD DEFAULT ('Current') FOR [RecStatus]
GO
--------------------------------------------------------------------------------------------------CREATE TABLE [dbo].[DimLocation](
[dimLocationId] [int] IDENTITY(1,1) NOT NULL,
[fLocation] [nvarchar](6) NULL,
[fLocationName] [nvarchar](60) NULL,
[fAddressLine1] [nvarchar](50) NULL,
[fAddressLine2] [nvarchar](50) NULL,
[fCity] [nvarchar](40) NULL,
[fState] [nvarchar](4) NULL,
[fZipCode] [nvarchar](10) NULL,
[fTelephoneNumber] [nvarchar](14) NULL,
[RecStatus] [nchar](10) NULL,
[ETLDate] [datetime] NULL,
CONSTRAINT [PK_DimLocation] PRIMARY KEY CLUSTERED
(
[dimLocationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DimLocation] ADD DEFAULT ('Current') FOR [RecStatus]
GO
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[DimPatient](
[dimpatientId] [int] IDENTITY(1,1) NOT NULL,
[EtlDate] [datetime] NULL,
[Flastname] [nvarchar](40) NULL,
[Ffirstname] [nvarchar](40) NULL,
[Fmiddlename] [nvarchar](10) NULL,
[Fsalutation] [nvarchar](10) NULL,
[Faddress1] [nvarchar](50) NULL,
[Faddress2] [nvarchar](50) NULL,
[Fcity] [nvarchar](40) NULL,
[Fstate] [nvarchar](4) NULL,
[FEMAIL] [nvarchar](40) NULL,
[Fzipcode] [nvarchar](10) NULL,
[Fworkphone] [nvarchar](28) NULL,
[Fhomephone] [nvarchar](28) NULL,
[Fsex] [tinyint] NULL,
[Frelation] [tinyint] NULL,
[Fdob] [smalldatetime] NULL,
[fEnglish] [tinyint] NULL,
[Faccount] [int] NULL,
[fPatient] [tinyint] NULL,
[chartno] [nvarchar](55) NULL,
[firsttxdate] [smalldatetime] NULL,
[RecStatus] [nchar](10) NULL,
[DimCategoryID] [int] NULL,
[Fcategory] [nvarchar](15) NULL,
[dimLocationId] [int] NULL,
CONSTRAINT [PK_DimPatient] PRIMARY KEY CLUSTERED
(
[dimpatientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DimPatient] ADD DEFAULT ('Current') FOR [RecStatus]
GO
----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[DxTx](
[Flocation] [nvarchar](3) NULL,
[Faccount] [int] NULL,
[Fpatient] [tinyint] NULL,
[Fmanager] [nvarchar](50) NULL,
[fRegMngr] [int] NULL,
[fCategory] [nvarchar](15) NULL,
[Fcontractno] [tinyint] NULL,
[Forthogen] [tinyint] NULL,
[Fprocedure] [nvarchar](10) NULL,
[fTxConsult] [nvarchar](10) NULL,
[fReferral] [nvarchar](75) NULL,
[fReferralGroup] [int] NULL,
[Finsdue] [money] NULL,
[Fpvtdue] [money] NULL,
[Fprodamt] [money] NULL,
[PropDesc] [nvarchar](90) NULL,
[fProvider] [nvarchar](3) NULL,
[fDxProvider] [nvarchar](3) NULL,
[UCRfee] [money] NULL,
[DVUfee] [money] NULL,
[Ftxnumber] [smallint] NULL,
[Ftxdate] [smalldatetime] NULL,
[Finscode] [nvarchar](20) NULL,
[EChartNo] [nvarchar](15) NULL,
[fYear] [int] NULL,
[fMonth] [int] NULL,
[fDay] [int] NULL,
[Fdiagdate] [smalldatetime] NULL,
[Fdxsold] [smalldatetime] NULL,
[RDH] [tinyint] NULL,
[Ftxsuffix] [tinyint] NULL,
[FInsgrpCode] [tinyint] NULL,
[Fbilledclaims] [tinyint] NULL,
[Fprintdate] [smalldatetime] NULL,
[Fnewtx] [tinyint] NULL,
[TxPayPlanId] [int] NULL,
[Fupdateby] [nvarchar](10) NULL,
[Fwhen] [smalldatetime] NULL,
[DxTxID] [int] NULL,
[fCORProc] [int] NULL,
[fCost] [money] NULL
) ON [PRIMARY]
----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[lkpProvider](
[EtlDate] [datetime] NULL,
[dimlkpProviderId] [int] IDENTITY(1,1) NOT NULL,
[ProviderCode] [nvarchar](6) NULL,
[firstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Speciality] [tinyint] NULL,
[PayID] [nvarchar](10) NULL,
[fEmployeeID] [int] NULL,
[RecStatus] [nchar](10) NULL,
[fOffProd] [tinyint] NULL,
[RDH] [tinyint] NULL,
CONSTRAINT [PK_dimlkpProviderId] PRIMARY KEY CLUSTERED
(
[dimlkpProviderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[lkpProvider] ADD DEFAULT ('Current') FOR [RecStatus]
GO
--------------------------------------------------------------------------------------
CREATE TABLE [dbo].[DimProcedure](
[ProcedureId] [int] IDENTITY(1,1) NOT NULL,
[EtlDate] [datetime] NULL,
[fProcedure] [nvarchar](10) NOT NULL,
[fProcedureDesc] [nvarchar](90) NULL,
[fGroup] [nvarchar](10) NULL,
[fToothEntry] [nvarchar](2) NULL,
[fSurfaces] [tinyint] NULL,
[fMaxQty] [smallint] NULL,
[fProsthesis] [nvarchar](1) NULL,
[fRecallPer] [smallint] NULL,
[fDenthOrhyg] [nvarchar](1) NULL,
[fRVU] [real] NULL,
[fCost] [money] NULL,
[Fadacode] [nvarchar](10) NULL,
[Fbillable] [tinyint] NULL,
[fType] [tinyint] NULL,
[fVisits] [tinyint] NULL,
[fInactive] [tinyint] NULL,
[fDelete] [tinyint] NULL,
[fUpdateBy] [nvarchar](10) NULL,
[fWhen] [smalldatetime] NULL,
[fStamp] [timestamp] NULL,
[fMonID] [int] NULL,
[fMonCount] [real] NULL,
[fSelectionType] [int] NULL,
[fOptional] [tinyint] NULL,
[fAlwaysOpt] [tinyint] NULL,
[fMainPrefOrder] [int] NULL,
[fSmartSurf1] [nvarchar](10) NULL,
[fSmartSurf2] [nvarchar](10) NULL,
[fSmartSurf3] [nvarchar](10) NULL,
[fSmartSurf4] [nvarchar](10) NULL,
[fSmartSurf5] [nvarchar](10) NULL,
[fSmartPosterior] [nvarchar](10) NULL,
[fSmartBicuspid] [nvarchar](10) NULL,
[fSmartAnterior] [nvarchar](10) NULL,
[fOldDACost] [money] NULL,
[RecStatus] [nchar](10) NULL,
CONSTRAINT [PK_ProcedureId] PRIMARY KEY CLUSTERED
(
[ProcedureId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fGroup] DEFAULT (' ') FOR [fGroup]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fToothEntry] DEFAULT (' ') FOR [fToothEntry]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSurfaces] DEFAULT ((0)) FOR [fSurfaces]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fMaxQty] DEFAULT ((1)) FOR [fMaxQty]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fProsthesis] DEFAULT ('N') FOR [fProsthesis]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fRecallPer] DEFAULT ((0)) FOR [fRecallPer]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fDenthOrhyg] DEFAULT ('D') FOR [fDenthOrhyg]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fRVU] DEFAULT ((0)) FOR [fRVU]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fCost] DEFAULT ((0)) FOR [fCost]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_Fadacode] DEFAULT (' ') FOR [Fadacode]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_Fbillable] DEFAULT ((0)) FOR [Fbillable]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fType] DEFAULT ((0)) FOR [fType]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fVisits] DEFAULT ((1)) FOR [fVisits]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fInactive] DEFAULT ((0)) FOR [fInactive]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fDelete] DEFAULT ((0)) FOR [fDelete]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fMonID] DEFAULT ((0)) FOR [fMonID]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSelectionType] DEFAULT ((0)) FOR [fSelectionType]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fOptional] DEFAULT ((0)) FOR [fOptional]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fAlwaysOpt] DEFAULT ((0)) FOR [fAlwaysOpt]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fMainPrefOrder] DEFAULT ((0)) FOR [fMainPrefOrder]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSmartSurf1] DEFAULT (' ') FOR [fSmartSurf1]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSmartSurf2] DEFAULT (' ') FOR [fSmartSurf2]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSmartSurf3] DEFAULT (' ') FOR [fSmartSurf3]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSmartSurf4] DEFAULT (' ') FOR [fSmartSurf4]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSmartSurf5] DEFAULT (' ') FOR [fSmartSurf5]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSmartPosterior] DEFAULT (' ') FOR [fSmartPosterior]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSmartBicuspid] DEFAULT (' ') FOR [fSmartBicuspid]
GO
ALTER TABLE [dbo].[DimProcedure] ADD CONSTRAINT [DF_DimProcedure_fSmartAnterior] DEFAULT (' ') FOR [fSmartAnterior]
GO
ALTER TABLE [dbo].[DimProcedure] ADD DEFAULT ('Current') FOR [RecStatus]
GO
---------------------------------------------------------------------------------------
CREATE TABLE [dbo].[DimCategory](
[DimCategoryID] [int] IDENTITY(1,1) NOT NULL,
[Fcategory] [nvarchar](15) NULL,
[Fbillout] [tinyint] NULL,
[EtlDate] [datetime] NULL,
[RecStatus] [nchar](10) NULL,
CONSTRAINT [PK_DimCategory] PRIMARY KEY CLUSTERED
(
[DimCategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DimCategory] ADD DEFAULT ('Current') FOR [RecStatus]
GO
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 21, 2012 at 4:15 am
Any indexes?
You join on non-key columns, so do you have indexes on them?
Can you also post current execution plan.
December 21, 2012 at 4:40 am
No, I dont have have indexes..
please find the attached current execution plan..
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 21, 2012 at 5:12 am
You can clearly see in your execution plan that SQL performs full table/index scans for all of your tables used in queries, together with using HASH MATCH join which is most expensive join mechanism in SQL, as it's the only one which can be used for joining large non-ordered sets.
Also I can see you have no WHERE clause at all, that your query is based on all your fact records. It's cannot be done really very fast as it will need to grab a lot of data I guess you have in your data warehouse (is it NHS one?).
Your first step would be creating appropriate indexes to help JOIN's, as per following list:
DimLocation(DimLocation)
DimInsurance(FinsCode, dimLocationId)
DimPatient(chartno, Fcategory, fPatient)
DimCategory(Fcategory)
lkpProvider(ProviderCode, ProviderCode)
DimProcedure(fProcedure)
TxPayPlan(Faccount, Finscode)
Actually, I guess your execution plan advises them anyway you can pick up CREATE INDEX script from there.
You may find that creating indexes which will include columns you list select list will help even more, but you will need to discuss it with your DBA as adding indexes affect database size.
December 21, 2012 at 8:12 am
if i cerate index on the columns in list :
DimLocation(DimLocation)
DimInsurance(FinsCode, dimLocationId)
DimPatient(chartno, Fcategory, fPatient)
DimCategory(Fcategory)
lkpProvider(ProviderCode, ProviderCode)
DimProcedure(fProcedure)
TxPayPlan(Faccount, Finscode)
query will gets less time right?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 21, 2012 at 8:16 am
I think it will.
Why you don't have any WHERE condition? Do you really intent to bring all fact records?
You outer join to your dimensions on their legacy keys. So all of these dimensions are Type 1?
If not and you have different version of records in dimension, your query will return all matches...
December 22, 2012 at 1:55 am
But all the columns on which you are suggesting to creating indexes, all are PK in their respective tables and as per my concern when we creating a PK it automatically creates an index on it.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 22, 2012 at 5:31 am
kapil_kk (12/22/2012)
But all the columns on which you are suggesting to creating indexes, all are PK in their respective tables and as per my concern when we creating a PK it automatically creates an index on it.
According to your DDL, your dimension tables have surrogate dimension PK's which are not used in your joins.
You need to create indexes on columns used for joins. PK's are not used in your query!
I have only one correction to my list: DimLocation(flocation)
December 22, 2012 at 5:34 am
thanks,
I will work on this 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply