Hi,
I have the following query which takes 12- 15 seconds to return almost
900,000 rows. I would like the query to return in less than 4 seconds
(1-3). I have added indexes where needed but was not successful. I am
wondering if this query can be rewritten in anyway to respond in lesser
time. Well the question every one will ask is why are you returning so many
rows?. Thats something we cant do away at present but may be later we might.
The query and query plan are as follows:
SELECT cdp.SerialNumber, PB.ProductId , c.ClaimID, cd.ClaimDetailID, 'spif'
AS Program,
cdp.SerialNumber + '-PID-' + convert(varchar,PB.ProductId) As
SerialNumberProductId
FROM dbo.tblClaimDetailProduct cdp (NOLOCK)
INNER JOIN dbo.tblClaimDetailBundle cdb (NOLOCK)
ON cdp.ClaimDetailBundleID = cdb.ClaimDetailBundleID
AND cdb.ClaimDetailBundleStatusID = 1
INNER JOIN dbo.tblClaimDetail cd (NOLOCK)
ON cdb.ClaimDetailID = cd.ClaimDetailID
AND cd.ClaimDetailStatusID = 1
INNER JOIN dbo.tblClaim c (NOLOCK)
ON cd.ClaimID = c.ClaimID
AND c.ClaimStatusID IN (1, 2, 5, 6, 7, 8, 9, 10, 11,12,14)
INNER JOIn dbo.tblProductBundle PB (NOLOCK)
ON PB.ProductBundleId = cdp.ProductBundleId
|--Compute
Scalar(DEFINE:([Expr1006]=[cdp].[SerialNumber]+'-PID-'+Convert([PB].[ProductID])))
|--Hash Match(Inner Join, HASH:([c].[ClaimID])=([cd].[ClaimID]))
|--Index
S
(OBJECT:([HPSpifCentral].[dbo].[tblClaim].[IX_tblClaim_1] AS [c]),
SEEK:([c].[ClaimStatusID]=1 OR [c].[ClaimStatusID]=2 OR
[c].[ClaimStatusID]=5 OR [c].[ClaimStatusID]=6 OR [c].[ClaimStatusID]=7 OR
[c].[ClaimStatusID]=8 OR [c].[Cla
|--Hash Match(Inner Join,
HASH:([cd].[ClaimDetailID])=([cdb].[ClaimDetailID]))
|--Index
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetail].[IX_tblClaimDetail_CDCS]
AS [cd]), WHERE:([cd].[ClaimDetailStatusID]=1))
|--Hash Match(Inner Join,
HASH:([PB].[ProductBundleID])=([cdp].[ProductBundleID]))
|--Index
Scan(OBJECT:([HPSpifCentral].[dbo].[tblProductBundle].[IX_tblProductBundle8]
AS [PB]))
|--Merge Join(Inner Join,
MERGE:([cdb].[ClaimDetailBundleID])=([cdp].[ClaimDetailBundleID]),
RESIDUAL:([cdp].[ClaimDetailBundleID]=[cdb].[ClaimDetailBundleID]))
|--Index
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailBundle].[PK_tblClaimDetailBundle]
AS [cdb]), WHERE:([cdb].[ClaimDetailBundleStatusID]=1) ORDERED FORWARD)
|--Index
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailProduct].[IX_tblClaimDetailProduct_SNo]
AS [cdp]), ORDERED FORWARD)
I would be happy to provide the table schemas as well.
Any help is greatly appreciated.
Thanks
MCheck out UPDATE STATISTICS and see if you get a performance boost.
"Sagar" <mmsagar@.hotmail.com> wrote in message
news:eWEVxpTlFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have the following query which takes 12- 15 seconds to return almost
> 900,000 rows. I would like the query to return in less than 4 seconds
> (1-3). I have added indexes where needed but was not successful. I am
> wondering if this query can be rewritten in anyway to respond in lesser
> time. Well the question every one will ask is why are you returning so
> many rows?. Thats something we cant do away at present but may be later we
> might. The query and query plan are as follows:
> SELECT cdp.SerialNumber, PB.ProductId , c.ClaimID, cd.ClaimDetailID,
> 'spif' AS Program,
> cdp.SerialNumber + '-PID-' + convert(varchar,PB.ProductId) As
> SerialNumberProductId
> FROM dbo.tblClaimDetailProduct cdp (NOLOCK)
> INNER JOIN dbo.tblClaimDetailBundle cdb (NOLOCK)
> ON cdp.ClaimDetailBundleID = cdb.ClaimDetailBundleID
> AND cdb.ClaimDetailBundleStatusID = 1
> INNER JOIN dbo.tblClaimDetail cd (NOLOCK)
> ON cdb.ClaimDetailID = cd.ClaimDetailID
> AND cd.ClaimDetailStatusID = 1
> INNER JOIN dbo.tblClaim c (NOLOCK)
> ON cd.ClaimID = c.ClaimID
> AND c.ClaimStatusID IN (1, 2, 5, 6, 7, 8, 9, 10, 11,12,14)
> INNER JOIn dbo.tblProductBundle PB (NOLOCK)
> ON PB.ProductBundleId = cdp.ProductBundleId
>
> |--Compute
> Scalar(DEFINE:([Expr1006]=[cdp].[SerialNumber]+'-PID-'+Convert([PB].[ProductID])))
> |--Hash Match(Inner Join, HASH:([c].[ClaimID])=([cd].[ClaimID]))
> |--Index
> S
(OBJECT:([HPSpifCentral].[dbo].[tblClaim].[IX_tblClaim_1] AS [c]),
> SEEK:([c].[ClaimStatusID]=1 OR [c].[ClaimStatusID]=2 OR
> [c].[ClaimStatusID]=5 OR [c].[ClaimStatusID]=6 OR [c].[ClaimStatusID]=7 OR
> [c].[ClaimStatusID]=8 OR [c].[Cla
> |--Hash Match(Inner Join,
> HASH:([cd].[ClaimDetailID])=([cdb].[ClaimDetailID]))
> |--Index
> Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetail].[IX_tblClaimDetail_CDCS]
> AS [cd]), WHERE:([cd].[ClaimDetailStatusID]=1))
> |--Hash Match(Inner Join,
> HASH:([PB].[ProductBundleID])=([cdp].[ProductBundleID]))
> |--Index
> Scan(OBJECT:([HPSpifCentral].[dbo].[tblProductBundle].[IX_tblProductBundle8]
> AS [PB]))
> |--Merge Join(Inner Join,
> MERGE:([cdb].[ClaimDetailBundleID])=([cdp].[ClaimDetailBundleID]),
> RESIDUAL:([cdp].[ClaimDetailBundleID]=[cdb].[ClaimDetailBundleID]))
> |--Index
> Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailBundle].[PK_tblClaimDetailBundle]
> AS [cdb]), WHERE:([cdb].[ClaimDetailBundleStatusID]=1) ORDERED FORWARD)
> |--Index
> Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailProduct].[IX_tblClaimDetailProduct_SNo]
> AS [cdp]), ORDERED FORWARD)
>
> I would be happy to provide the table schemas as well.
> Any help is greatly appreciated.
> Thanks
> M
>|||Do you have other queries that do return 900,000 rows in less than 4
seconds?|||Without DDL and index information this is hard to say. It looks as if
you have no clustered indexes, and that the indexes that you do have
could be better placed.
So yes, the table schema is very welcome, because that is the place to
improve this query's performance.
As a general rule: make sure each table has a Primary Key (which will
automatically result in a unique index) and index all foreign key
constraints. It is generally a good idea for each table to have
clustered index. If there are several indexing on a table, then make
sure the clustered index is a narrow index.
HTH,
Gert-Jan
Sagar wrote:
> Hi,
> I have the following query which takes 12- 15 seconds to return almost
> 900,000 rows. I would like the query to return in less than 4 seconds
> (1-3). I have added indexes where needed but was not successful. I am
> wondering if this query can be rewritten in anyway to respond in lesser
> time. Well the question every one will ask is why are you returning so man
y
> rows?. Thats something we cant do away at present but may be later we migh
t.
> The query and query plan are as follows:
> SELECT cdp.SerialNumber, PB.ProductId , c.ClaimID, cd.ClaimDetailID, 'spif
'
> AS Program,
> cdp.SerialNumber + '-PID-' + convert(varchar,PB.ProductId) As
> SerialNumberProductId
> FROM dbo.tblClaimDetailProduct cdp (NOLOCK)
> INNER JOIN dbo.tblClaimDetailBundle cdb (NOLOCK)
> ON cdp.ClaimDetailBundleID = cdb.ClaimDetailBundleID
> AND cdb.ClaimDetailBundleStatusID = 1
> INNER JOIN dbo.tblClaimDetail cd (NOLOCK)
> ON cdb.ClaimDetailID = cd.ClaimDetailID
> AND cd.ClaimDetailStatusID = 1
> INNER JOIN dbo.tblClaim c (NOLOCK)
> ON cd.ClaimID = c.ClaimID
> AND c.ClaimStatusID IN (1, 2, 5, 6, 7, 8, 9, 10, 11,12,14)
> INNER JOIn dbo.tblProductBundle PB (NOLOCK)
> ON PB.ProductBundleId = cdp.ProductBundleId
> |--Compute
> Scalar(DEFINE:([Expr1006]=[cdp].[SerialNumber]+'-PID-'+Convert([PB].[ProductID])))
> |--Hash Match(Inner Join, HASH:([c].[ClaimID])=([cd].[ClaimID]))
> |--Index
> S
(OBJECT:([HPSpifCentral].[dbo].[tblClaim].[IX_tblClaim_1] AS [c]),
> SEEK:([c].[ClaimStatusID]=1 OR [c].[ClaimStatusID]=2 OR
> [c].[ClaimStatusID]=5 OR [c].[ClaimStatusID]=6 OR [c].[ClaimStatusID]=7 OR
> [c].[ClaimStatusID]=8 OR [c].[Cla
> |--Hash Match(Inner Join,
> HASH:([cd].[ClaimDetailID])=([cdb].[ClaimDetailID]))
> |--Index
> Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetail].[IX_tblClaimDetail_CDCS]
> AS [cd]), WHERE:([cd].[ClaimDetailStatusID]=1))
> |--Hash Match(Inner Join,
> HASH:([PB].[ProductBundleID])=([cdp].[ProductBundleID]))
> |--Index
> Scan(OBJECT:([HPSpifCentral].[dbo].[tblProductBundle].[IX_tblProductBundle8]
> AS [PB]))
> |--Merge Join(Inner Join,
> MERGE:([cdb].[ClaimDetailBundleID])=([cdp].[ClaimDetailBundleID]),
> RESIDUAL:([cdp].[ClaimDetailBundleID]=[cdb].[ClaimDetailBundleID]))
> |--Index
> Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailBundle].[PK_tblClaimDetailBundle]
> AS [cdb]), WHERE:([cdb].[ClaimDetailBundleStatusID]=1) ORDERED FORWARD)
> |--Index
> Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailProduct].[IX_tblClaimDetailProduct_SNo]
> AS [cdp]), ORDERED FORWARD)
> I would be happy to provide the table schemas as well.
> Any help is greatly appreciated.
> Thanks
> M|||Are you sure that the problem is the query and not communication/application
related? To test this, change the select to a select into #tempTable and
then select from #tempTable in your app/sp. That way you can separate the
amount of time the query takes to build the information from the time that
it takes to transfer/read out the result set in a trace.
"Sagar" <mmsagar@.hotmail.com> wrote in message
news:eWEVxpTlFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have the following query which takes 12- 15 seconds to return almost
> 900,000 rows. I would like the query to return in less than 4 seconds
> (1-3). I have added indexes where needed but was not successful. I am
> wondering if this query can be rewritten in anyway to respond in lesser
> time. Well the question every one will ask is why are you returning so
many
> rows?. Thats something we cant do away at present but may be later we
might.
> The query and query plan are as follows:
> SELECT cdp.SerialNumber, PB.ProductId , c.ClaimID, cd.ClaimDetailID,
'spif'
> AS Program,
> cdp.SerialNumber + '-PID-' + convert(varchar,PB.ProductId) As
> SerialNumberProductId
> FROM dbo.tblClaimDetailProduct cdp (NOLOCK)
> INNER JOIN dbo.tblClaimDetailBundle cdb (NOLOCK)
> ON cdp.ClaimDetailBundleID = cdb.ClaimDetailBundleID
> AND cdb.ClaimDetailBundleStatusID = 1
> INNER JOIN dbo.tblClaimDetail cd (NOLOCK)
> ON cdb.ClaimDetailID = cd.ClaimDetailID
> AND cd.ClaimDetailStatusID = 1
> INNER JOIN dbo.tblClaim c (NOLOCK)
> ON cd.ClaimID = c.ClaimID
> AND c.ClaimStatusID IN (1, 2, 5, 6, 7, 8, 9, 10, 11,12,14)
> INNER JOIn dbo.tblProductBundle PB (NOLOCK)
> ON PB.ProductBundleId = cdp.ProductBundleId
>
> |--Compute
>
Scalar(DEFINE:([Expr1006]=[cdp].[SerialNumber]+'-PID-'+Convert([PB].[Product
ID])))
> |--Hash Match(Inner Join, HASH:([c].[ClaimID])=([cd].[ClaimID]))
> |--Index
> S
(OBJECT:([HPSpifCentral].[dbo].[tblClaim].[IX_tblClaim_1] AS [c]),
> SEEK:([c].[ClaimStatusID]=1 OR [c].[ClaimStatusID]=2 OR
> [c].[ClaimStatusID]=5 OR [c].[ClaimStatusID]=6 OR [c].[ClaimStatusID]=7 OR
> [c].[ClaimStatusID]=8 OR [c].[Cla
> |--Hash Match(Inner Join,
> HASH:([cd].[ClaimDetailID])=([cdb].[ClaimDetailID]))
> |--Index
>
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetail].[IX_tblClaimDetail_CDCS]
> AS [cd]), WHERE:([cd].[ClaimDetailStatusID]=1))
> |--Hash Match(Inner Join,
> HASH:([PB].[ProductBundleID])=([cdp].[ProductBundleID]))
> |--Index
>
Scan(OBJECT:([HPSpifCentral].[dbo].[tblProductBundle].[IX_tblProductBundle8]
> AS [PB]))
> |--Merge Join(Inner Join,
> MERGE:([cdb].[ClaimDetailBundleID])=([cdp].[ClaimDetailBundleID]),
> RESIDUAL:([cdp].[ClaimDetailBundleID]=[cdb].[ClaimDetailBundleID]))
> |--Index
>
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailBundle].[PK_tblClaimDetail
Bundle]
> AS [cdb]), WHERE:([cdb].[ClaimDetailBundleStatusID]=1) ORDERED FORWARD)
> |--Index
>
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailProduct].[IX_tblClaimDetai
lProduct_SNo]
> AS [cdp]), ORDERED FORWARD)
>
> I would be happy to provide the table schemas as well.
> Any help is greatly appreciated.
> Thanks
> M
>|||Thanks for all your replies. I appreciate it. Well Here is the table schema:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblClaimDetail_tblClaim]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetail] DROP CONSTRAINT
FK_tblClaimDetail_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblClaimInvoice_tblClaim]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimInvoice] DROP CONSTRAINT
FK_tblClaimInvoice_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblClaimNote_tblClaim]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimNote] DROP CONSTRAINT FK_tblClaimNote_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblClaimStatusHistory_tblClaim]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimStatusHistory] DROP CONSTRAINT
FK_tblClaimStatusHistory_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblUserRequest_tblClaim]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblUserRequest] DROP CONSTRAINT
FK_tblUserRequest_tblClaim
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblClaimDetailBundle_tblClaimDetail]
') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetailBundle] DROP CONSTRAINT
FK_tblClaimDetailBundle_tblClaimDetail
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblClaimDetailProduct_tblClaimDetail
Bundle]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
FK_tblClaimDetailProduct_tblClaimDetailB
undle
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblClaimDetailProduct_tblProductBund
le]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
FK_tblClaimDetailProduct_tblProductBundl
e
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblClaim]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblClaim]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblClaimDetail]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblClaimDetail]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblClaimDetailBundle]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblClaimDetailBundle]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblClaimDetailProduct]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblClaimDetailProduct]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblProductBundle]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblProductBundle]
GO
CREATE TABLE [dbo].[tblClaim] (
[ClaimID] [int] IDENTITY (10000, 1) NOT NULL ,
[ClaimDate] [datetime] NOT NULL ,
[PromotionID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[UserEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HistoricalOutletID] [int] NULL ,
[HistoricalCompanyID] [int] NULL ,
[ClaimStatusID] [int] NOT NULL ,
[ClaimStatusReason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ClaimExpirationDate] [datetime] NULL ,
[FaxDate] [datetime] NULL ,
[FaxTime] [int] NULL ,
[PaymentTypeID] [int] NULL ,
[PaymentDate] [datetime] NULL ,
[PaymentNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaymentAmount] [money] NULL ,
[ClaimSourceTypeID] [int] NULL ,
[BatchID] [int] NULL ,
[ExpiryEmailSentDate] [datetime] NULL ,
[FraudAuditStatusID] [int] NOT NULL ,
[InDepthAudit] [bit] NULL ,
[TicketId] [int] NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblClaimDetail] (
[ClaimDetailID] [int] IDENTITY (1, 1) NOT NULL ,
[ClaimID] [int] NOT NULL ,
[BundleID] [int] NOT NULL ,
[ClaimInvoiceID] [int] NULL ,
[BenefitDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[BenefitAmount] [money] NULL ,
[ClaimDetailStatusID] [int] NOT NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblClaimDetailBundle] (
[ClaimDetailBundleID] [int] IDENTITY (1, 1) NOT NULL ,
[ClaimDetailID] [int] NOT NULL ,
[BundleID] [int] NOT NULL ,
[ClaimDetailBundleStatusID] [int] NOT NULL ,
[DeniedReasonID] [int] NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblClaimDetailProduct] (
[ClaimDetailProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductBundleID] [int] NOT NULL ,
[ClaimDetailBundleID] [int] NOT NULL ,
[SerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BenefitAmount] [money] NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblProductBundle] (
[ProductBundleID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductID] [int] NULL ,
[ProductCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PCProductID] [int] NULL ,
[BundleID] [int] NOT NULL ,
[IsSNRequired] [bit] NOT NULL ,
[BenefitAmount] [money] NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblClaim] WITH NOCHECK ADD
CONSTRAINT [PK_tblClaim] PRIMARY KEY CLUSTERED
(
[ClaimID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tblClaimDetail] ON
[dbo].[tblClaimDetail]([ClaimID], [BundleID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tblClaimDetailBundle] ON
[dbo].[tblClaimDetailBundle]([ClaimDetailID], [BundleID],
[ClaimDetailBundleStatusID], [DeniedReasonID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tblProductBundle] ON
[dbo].[tblProductBundle]([BundleID], [ProductID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
ALTER TABLE [dbo].[tblClaim] WITH NOCHECK ADD
CONSTRAINT [DF_tblClaim_PaymentTypeID] DEFAULT (3) FOR [PaymentTypeID],
CONSTRAINT [DF_tblClaim_SourceType] DEFAULT (1) FOR [ClaimSourceTypeID],
CONSTRAINT [DF_tblClaim_FraudAuditStatusID] DEFAULT (0) FOR
[FraudAuditStatusID],
CONSTRAINT [DF_tblClaim_InDepthAudit] DEFAULT (0) FOR [InDepthAudit]
GO
ALTER TABLE [dbo].[tblClaimDetail] WITH NOCHECK ADD
CONSTRAINT [PK_tblClaimDetail] PRIMARY KEY NONCLUSTERED
(
[ClaimDetailID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblClaimDetailBundle] WITH NOCHECK ADD
CONSTRAINT [PK_tblClaimDetailBundle] PRIMARY KEY NONCLUSTERED
(
[ClaimDetailBundleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblClaimDetailProduct] WITH NOCHECK ADD
CONSTRAINT [PK_tblClaimDetailProduct] PRIMARY KEY NONCLUSTERED
(
[ClaimDetailProductID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProductBundle] WITH NOCHECK ADD
CONSTRAINT [PK_tblProductBundle] PRIMARY KEY NONCLUSTERED
(
[ProductBundleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaim] ON [dbo].[tblClaim]([PromotionID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaim_Date] ON [dbo].[tblClaim]([ClaimDate]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [tblClaim6] ON [dbo].[tblClaim]([BatchID], [ClaimID],
[PromotionID], [HistoricalOutletID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [tblClaim_CS] ON [dbo].[tblClaim]([ClaimStatusID],
[PromotionID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaim_CP] ON [dbo].[tblClaim]([ClaimID],
[PromotionID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaim_1] ON [dbo].[tblClaim]([ClaimStatusID],
[ClaimID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_1714821171_3A_1A] ON [dbo].[tblClaim]
([PromotionID], [ClaimID]) ')
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_6A_7A] ON
[dbo].[tblClaimDetail] ([ClaimDetailID], [ClaimID], [BenefitAmount],
[ClaimDetailStatusID]) ')
GO
CREATE INDEX [tblClaimDetail_ClaimDetail] ON
[dbo].[tblClaimDetail]([ClaimDetailID], [ClaimID], [BenefitAmount],
[ClaimDetailStatusID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [tblClaimDetai_BAmount] ON [dbo].[tblClaimDetail]([ClaimID],
[ClaimDetailStatusID], [BenefitAmount]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_7A] ON [dbo].[tblClaimDetail]
([ClaimDetailID], [ClaimID], [ClaimDetailStatusID]) ')
GO
CREATE INDEX [IX_tblClaimDetail_CCD] ON [dbo].[tblClaimDetail]([ClaimID],
[ClaimDetailID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetail_CDCS] ON
[dbo].[tblClaimDetail]([ClaimDetailID], [ClaimDetailStatusID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_165575628_1A_7A_2A] ON [dbo].[tblClaimDetail]
([ClaimDetailID], [ClaimDetailStatusID], [ClaimID]) ')
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_7A_6A] ON
[dbo].[tblClaimDetail] ([ClaimDetailID], [ClaimID], [ClaimDetailStatusID],
[BenefitAmount]) ')
GO
CREATE INDEX [IX_tblClaimDetailBundle_NCX] ON
[dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eStatusID], [ClaimDetailID],
[DeniedReasonID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailBundle_CDB] ON
[dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eID], [ClaimDetailID],
[ClaimDetailBundleStatusID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailBundle_CDIT] ON
[dbo].[tblClaimDetailBundle]([ClaimDetailID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [tblClaimDetailBundle13] ON
[dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eStatusID]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_181575685_2A_4A_5A] ON
[dbo].[tblClaimDetailBundle] ([ClaimDetailID], [ClaimDetailBundleStatusID],
[DeniedReasonID]) ')
GO
CREATE INDEX [IX_tblClaimDetailProduct_1] ON
[dbo]. [tblClaimDetailProduct]([ClaimDetailBund
leID], [ProductBundleID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailProduct] ON
[dbo].[tblClaimDetailProduct]([SerialNumber]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailProduct_SNo] ON
[dbo]. [tblClaimDetailProduct]([ClaimDetailBund
leID], [ProductBundleID],
[SerialNumber]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblClaimDetailProduct3] ON
[dbo]. [tblClaimDetailProduct]([ProductBundleID
], [SerialNumber]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_1627152842_1A_2A] ON [dbo].[tblProductBundle]
([ProductBundleID], [ProductID]) ')
GO
CREATE INDEX [IX_tblProductBundle8] ON
[dbo].[tblProductBundle]([ProductBundleID], [ProductID]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaim].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]', N'[tblClaim].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]', N'[tblClaim].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaim].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]', N'[tblClaim].[UpdateUser]'
GO
setuser
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaimDetail].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetail].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]',
N'[tblClaimDetail].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaimDetail].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetail].[UpdateUser]'
GO
setuser
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetailBundle].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetailBundle].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]',
N'[tblClaimDetailBundle].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetailBundle].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetailBundle].[UpdateUser]'
GO
setuser
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetailProduct].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetailProduct].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]',
N'[tblClaimDetailProduct].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetailProduct].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblClaimDetailProduct].[UpdateUser]'
GO
setuser
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblProductBundle].[InsertDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblProductBundle].[InsertUser]'
GO
EXEC sp_bindefault N'[dbo].[RecordStatus]',
N'[tblProductBundle].[RecordStatus]'
GO
EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblProductBundle].[UpdateDate]'
GO
EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
N'[tblProductBundle].[UpdateUser]'
GO
setuser
GO
ALTER TABLE [dbo].[tblClaimDetail] ADD
CONSTRAINT [FK_tblClaimDetail_tblBundle] FOREIGN KEY
(
[BundleID]
) REFERENCES [dbo].[tblBundle] (
[BundleID]
),
CONSTRAINT [FK_tblClaimDetail_tblClaim] FOREIGN KEY
(
[ClaimID]
) REFERENCES [dbo].[tblClaim] (
[ClaimID]
),
CONSTRAINT [FK_tblClaimDetail_tblClaimInvoice] FOREIGN KEY
(
[ClaimInvoiceID]
) REFERENCES [dbo].[tblClaimInvoice] (
[ClaimInvoiceID]
),
CONSTRAINT [FK_tblClaimDetail_tblLUClaimDetailStatu
s] FOREIGN KEY
(
[ClaimDetailStatusID]
) REFERENCES [dbo].[tblLUClaimDetailStatus] (
[ClaimDetailStatusID]
)
GO
ALTER TABLE [dbo].[tblClaimDetailBundle] ADD
CONSTRAINT [FK_tblClaimDetailBundle_tblBundle] FOREIGN KEY
(
[BundleID]
) REFERENCES [dbo].[tblBundle] (
[BundleID]
),
CONSTRAINT [FK_tblClaimDetailBundle_tblClaimDetail]
FOREIGN KEY
(
[ClaimDetailID]
) REFERENCES [dbo].[tblClaimDetail] (
[ClaimDetailID]
),
CONSTRAINT [FK_tblClaimDetailBundle_tblLUClaimDetai
lStatus] FOREIGN KEY
(
[ClaimDetailBundleStatusID]
) REFERENCES [dbo].[tblLUClaimDetailStatus] (
[ClaimDetailStatusID]
)
GO
ALTER TABLE [dbo].[tblClaimDetailProduct] ADD
CONSTRAINT [FK_tblClaimDetailProduct_tblClaimDetail
Bundle] FOREIGN KEY
(
[ClaimDetailBundleID]
) REFERENCES [dbo].[tblClaimDetailBundle] (
[ClaimDetailBundleID]
),
CONSTRAINT [FK_tblClaimDetailProduct_tblProductBund
le] FOREIGN KEY
(
[ProductBundleID]
) REFERENCES [dbo].[tblProductBundle] (
[ProductBundleID]
)
GO
ALTER TABLE [dbo].[tblProductBundle] ADD
CONSTRAINT [FK_tblProductBundle_tblBundle] FOREIGN KEY
(
[BundleID]
) REFERENCES [dbo].[tblBundle] (
[BundleID]
)
GO
As far as the other questions, I did update statistics and it did not give
me a performance boot. I also did DBCC DBREINDEX which did not help me
either. Mowgli, I cant answer your question at this time since this is the
only table I have 900,000 rows. others are like 1000 rows and they do return
data in less than a second (milli seconds). I have also verified that the
problem is the query itself taking time and not communication. When I do a
profiler trace i see most of the time is spent processing the query .(i did
insert into temp as well but the insert is longer because the 900,00 rows
returned and inserted is taking time). After the insert is done. the select
* from temp is faster.
Any other things i need to look into?.
Thanks
M|||Sagar
I would like to know (if possible) how long the much simpler query
below takes to return the data...
SELECT TOP 900000
cdp.SerialNumber
, cdp.ClaimDetailBundleID
, cdp.ProductBundleId
, 'spif' AS Program
, cdp.SerialNumber + '-PID-' AS SerialNumberProductId
FROM dbo.tblClaimDetailProduct cdp
If this takes longer than 4 seconds then I think that you may have to
revise your performance expectations.|||Why aren't the clustered indexes on the primary keys? The key of a
clustered index should be as small as possible. In SQL Server 2000, if a
table has a clustered index, then instead of record pointers, nonclustered
indexes contain the key value from the clustered index. (Record pointers
are only used if the table is a heap.) Thus, the clustered index (if it
exists) is always used to locate a row. This differs from previous versions
of SQL Server. This means that every join of a table that has a
nonclustered primary key requires an additional index s
per row. This is
documented in BOL. I suggest you read up on it, create a testing database
with clustered primary keys, and try it out. I think you'll be pleasantly
surprised.
"Sagar" <mmsagar@.hotmail.com> wrote in message
news:eUWSqTVlFHA.3756@.TK2MSFTNGP15.phx.gbl...
> Thanks for all your replies. I appreciate it. Well Here is the table
schema:
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblClaimDetail_tblClaim]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimDetail] DROP CONSTRAINT
> FK_tblClaimDetail_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblClaimInvoice_tblClaim]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimInvoice] DROP CONSTRAINT
> FK_tblClaimInvoice_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblClaimNote_tblClaim]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimNote] DROP CONSTRAINT FK_tblClaimNote_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblClaimStatusHistory_tblClaim]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimStatusHistory] DROP CONSTRAINT
> FK_tblClaimStatusHistory_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblUserRequest_tblClaim]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblUserRequest] DROP CONSTRAINT
> FK_tblUserRequest_tblClaim
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo]. [FK_tblClaimDetailBundle_tblClaimDetail]
') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimDetailBundle] DROP CONSTRAINT
> FK_tblClaimDetailBundle_tblClaimDetail
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo]. [FK_tblClaimDetailProduct_tblClaimDetail
Bundle]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
> FK_tblClaimDetailProduct_tblClaimDetailB
undle
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo]. [FK_tblClaimDetailProduct_tblProductBund
le]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
> FK_tblClaimDetailProduct_tblProductBundl
e
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblClaim]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblClaim]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblClaimDetail]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[tblClaimDetail]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblClaimDetailBundle]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblClaimDetailBundle]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblClaimDetailProduct]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblClaimDetailProduct]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblProductBundle]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblProductBundle]
> GO
> CREATE TABLE [dbo].[tblClaim] (
> [ClaimID] [int] IDENTITY (10000, 1) NOT NULL ,
> [ClaimDate] [datetime] NOT NULL ,
> [PromotionID] [int] NOT NULL ,
> [UserID] [int] NOT NULL ,
> [UserEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HistoricalOutletID] [int] NULL ,
> [HistoricalCompanyID] [int] NULL ,
> [ClaimStatusID] [int] NOT NULL ,
> [ClaimStatusReason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ClaimExpirationDate] [datetime] NULL ,
> [FaxDate] [datetime] NULL ,
> [FaxTime] [int] NULL ,
> [PaymentTypeID] [int] NULL ,
> [PaymentDate] [datetime] NULL ,
> [PaymentNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [PaymentAmount] [money] NULL ,
> [ClaimSourceTypeID] [int] NULL ,
> [BatchID] [int] NULL ,
> [ExpiryEmailSentDate] [datetime] NULL ,
> [FraudAuditStatusID] [int] NOT NULL ,
> [InDepthAudit] [bit] NULL ,
> [TicketId] [int] NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblClaimDetail] (
> [ClaimDetailID] [int] IDENTITY (1, 1) NOT NULL ,
> [ClaimID] [int] NOT NULL ,
> [BundleID] [int] NOT NULL ,
> [ClaimInvoiceID] [int] NULL ,
> [BenefitDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [BenefitAmount] [money] NULL ,
> [ClaimDetailStatusID] [int] NOT NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblClaimDetailBundle] (
> [ClaimDetailBundleID] [int] IDENTITY (1, 1) NOT NULL ,
> [ClaimDetailID] [int] NOT NULL ,
> [BundleID] [int] NOT NULL ,
> [ClaimDetailBundleStatusID] [int] NOT NULL ,
> [DeniedReasonID] [int] NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblClaimDetailProduct] (
> [ClaimDetailProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProductBundleID] [int] NOT NULL ,
> [ClaimDetailBundleID] [int] NOT NULL ,
> [SerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BenefitAmount] [money] NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblProductBundle] (
> [ProductBundleID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProductID] [int] NULL ,
> [ProductCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [PCProductID] [int] NULL ,
> [BundleID] [int] NOT NULL ,
> [IsSNRequired] [bit] NOT NULL ,
> [BenefitAmount] [money] NULL ,
> [InsertDate] [datetime] NOT NULL ,
> [InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [UpdateDate] [datetime] NULL ,
> [UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblClaim] WITH NOCHECK ADD
> CONSTRAINT [PK_tblClaim] PRIMARY KEY CLUSTERED
> (
> [ClaimID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tblClaimDetail] ON
> [dbo].[tblClaimDetail]([ClaimID], [BundleID]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tblClaimDetailBundle] ON
> [dbo].[tblClaimDetailBundle]([ClaimDetailID], [BundleID],
> [ClaimDetailBundleStatusID], [DeniedReasonID]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tblProductBundle] ON
> [dbo].[tblProductBundle]([BundleID], [ProductID]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblClaim] WITH NOCHECK ADD
> CONSTRAINT [DF_tblClaim_PaymentTypeID] DEFAULT (3) FOR [PaymentTypeID],
> CONSTRAINT [DF_tblClaim_SourceType] DEFAULT (1) FOR [ClaimSourceTypeID],
> CONSTRAINT [DF_tblClaim_FraudAuditStatusID] DEFAULT (0) FOR
> [FraudAuditStatusID],
> CONSTRAINT [DF_tblClaim_InDepthAudit] DEFAULT (0) FOR [InDepthAudit]
> GO
> ALTER TABLE [dbo].[tblClaimDetail] WITH NOCHECK ADD
> CONSTRAINT [PK_tblClaimDetail] PRIMARY KEY NONCLUSTERED
> (
> [ClaimDetailID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblClaimDetailBundle] WITH NOCHECK ADD
> CONSTRAINT [PK_tblClaimDetailBundle] PRIMARY KEY NONCLUSTERED
> (
> [ClaimDetailBundleID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblClaimDetailProduct] WITH NOCHECK ADD
> CONSTRAINT [PK_tblClaimDetailProduct] PRIMARY KEY NONCLUSTERED
> (
> [ClaimDetailProductID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblProductBundle] WITH NOCHECK ADD
> CONSTRAINT [PK_tblProductBundle] PRIMARY KEY NONCLUSTERED
> (
> [ProductBundleID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaim] ON [dbo].[tblClaim]([PromotionID]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaim_Date] ON [dbo].[tblClaim]([ClaimDate]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [tblClaim6] ON [dbo].[tblClaim]([BatchID], [ClaimID],
> [PromotionID], [HistoricalOutletID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [tblClaim_CS] ON [dbo].[tblClaim]([ClaimStatusID],
> [PromotionID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaim_CP] ON [dbo].[tblClaim]([ClaimID],
> [PromotionID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaim_1] ON [dbo].[tblClaim]([ClaimStatusID],
> [ClaimID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_1714821171_3A_1A] ON [dbo].[tblClaim]
> ([PromotionID], [ClaimID]) ')
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_6A_7A] ON
> [dbo].[tblClaimDetail] ([ClaimDetailID], [ClaimID], [BenefitAmount],
> [ClaimDetailStatusID]) ')
> GO
> CREATE INDEX [tblClaimDetail_ClaimDetail] ON
> [dbo].[tblClaimDetail]([ClaimDetailID], [ClaimID], [BenefitAmount],
> [ClaimDetailStatusID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [tblClaimDetai_BAmount] ON
[dbo]. [tblClaimDetail]([ClaimID],d">
> [ClaimDetailStatusID], [BenefitAmount]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_7A] ON
[dbo].[tblClaimDetail]
> ([ClaimDetailID], [ClaimID], [ClaimDetailStatusID]) ')
> GO
> CREATE INDEX [IX_tblClaimDetail_CCD] ON
[dbo]. [tblClaimDetail]([ClaimID],d">
> [ClaimDetailID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetail_CDCS] ON
> [dbo].[tblClaimDetail]([ClaimDetailID], [ClaimDetailStatusID]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_165575628_1A_7A_2A] ON
[dbo].[tblClaimDetail]
> ([ClaimDetailID], [ClaimDetailStatusID], [ClaimID]) ')
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_165575628_1A_2A_7A_6A] ON
> [dbo].[tblClaimDetail] ([ClaimDetailID], [ClaimID], [ClaimDetailStatusID],
> [BenefitAmount]) ')
> GO
> CREATE INDEX [IX_tblClaimDetailBundle_NCX] ON
> [dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eStatusID], [ClaimDetailID],
> [DeniedReasonID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailBundle_CDB] ON
> [dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eID], [ClaimDetailID],
> [ClaimDetailBundleStatusID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailBundle_CDIT] ON
> [dbo].[tblClaimDetailBundle]([ClaimDetailID]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [tblClaimDetailBundle13] ON
> [dbo]. [tblClaimDetailBundle]([ClaimDetailBundl
eStatusID]) WITH FILLFACTOR
=
> 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_181575685_2A_4A_5A] ON
> [dbo].[tblClaimDetailBundle] ([ClaimDetailID],
[ClaimDetailBundleStatusID],ed">
> [DeniedReasonID]) ')
> GO
> CREATE INDEX [IX_tblClaimDetailProduct_1] ON
> [dbo]. [tblClaimDetailProduct]([ClaimDetailBund
leID], [ProductBundleID])
WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailProduct] ON
> [dbo].[tblClaimDetailProduct]([SerialNumber]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailProduct_SNo] ON
> [dbo]. [tblClaimDetailProduct]([ClaimDetailBund
leID], [ProductBundleID],
> [SerialNumber]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblClaimDetailProduct3] ON
> [dbo]. [tblClaimDetailProduct]([ProductBundleID
], [SerialNumber]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_1627152842_1A_2A] ON
[dbo].[tblProductBundle]
> ([ProductBundleID], [ProductID]) ')
> GO
> CREATE INDEX [IX_tblProductBundle8] ON
> [dbo].[tblProductBundle]([ProductBundleID], [ProductID]) WITH FILLFACTOR
=
> 90 ON [PRIMARY]
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaim].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]', N'[tblClaim].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]', N'[tblClaim].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]', N'[tblClaim].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]', N'[tblClaim].[UpdateUser]'
> GO
> setuser
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetail].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetail].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]',
> N'[tblClaimDetail].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
N'[tblClaimDetail].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetail].[UpdateUser]'
> GO
> setuser
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblClaimDetailBundle].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetailBundle].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]',
> N'[tblClaimDetailBundle].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblClaimDetailBundle].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetailBundle].[UpdateUser]'
> GO
> setuser
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblClaimDetailProduct].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetailProduct].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]',
> N'[tblClaimDetailProduct].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblClaimDetailProduct].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblClaimDetailProduct].[UpdateUser]'
> GO
> setuser
> GO
> setuser
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblProductBundle].[InsertDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblProductBundle].[InsertUser]'
> GO
> EXEC sp_bindefault N'[dbo].[RecordStatus]',
> N'[tblProductBundle].[RecordStatus]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModified]',
> N'[tblProductBundle].[UpdateDate]'
> GO
> EXEC sp_bindefault N'[dbo].[LastModifiedBy]',
> N'[tblProductBundle].[UpdateUser]'
> GO
> setuser
> GO
> ALTER TABLE [dbo].[tblClaimDetail] ADD
> CONSTRAINT [FK_tblClaimDetail_tblBundle] FOREIGN KEY
> (
> [BundleID]
> ) REFERENCES [dbo].[tblBundle] (
> [BundleID]
> ),
> CONSTRAINT [FK_tblClaimDetail_tblClaim] FOREIGN KEY
> (
> [ClaimID]
> ) REFERENCES [dbo].[tblClaim] (
> [ClaimID]
> ),
> CONSTRAINT [FK_tblClaimDetail_tblClaimInvoice] FOREIGN KEY
> (
> [ClaimInvoiceID]
> ) REFERENCES [dbo].[tblClaimInvoice] (
> [ClaimInvoiceID]
> ),
> CONSTRAINT [FK_tblClaimDetail_tblLUClaimDetailStatu
s] FOREIGN KEY
> (
> [ClaimDetailStatusID]
> ) REFERENCES [dbo].[tblLUClaimDetailStatus] (
> [ClaimDetailStatusID]
> )
> GO
> ALTER TABLE [dbo].[tblClaimDetailBundle] ADD
> CONSTRAINT [FK_tblClaimDetailBundle_tblBundle] FOREIGN KEY
> (
> [BundleID]
> ) REFERENCES [dbo].[tblBundle] (
> [BundleID]
> ),
> CONSTRAINT [FK_tblClaimDetailBundle_tblClaimDetail]
FOREIGN KEY
> (
> [ClaimDetailID]
> ) REFERENCES [dbo].[tblClaimDetail] (
> [ClaimDetailID]
> ),
> CONSTRAINT [FK_tblClaimDetailBundle_tblLUClaimDetai
lStatus] FOREIGN KEY
> (
> [ClaimDetailBundleStatusID]
> ) REFERENCES [dbo].[tblLUClaimDetailStatus] (
> [ClaimDetailStatusID]
> )
> GO
> ALTER TABLE [dbo].[tblClaimDetailProduct] ADD
> CONSTRAINT [FK_tblClaimDetailProduct_tblClaimDetail
Bundle] FOREIGN KEY
> (
> [ClaimDetailBundleID]
> ) REFERENCES [dbo].[tblClaimDetailBundle] (
> [ClaimDetailBundleID]
> ),
> CONSTRAINT [FK_tblClaimDetailProduct_tblProductBund
le] FOREIGN KEY
> (
> [ProductBundleID]
> ) REFERENCES [dbo].[tblProductBundle] (
> [ProductBundleID]
> )
> GO
> ALTER TABLE [dbo].[tblProductBundle] ADD
> CONSTRAINT [FK_tblProductBundle_tblBundle] FOREIGN KEY
> (
> [BundleID]
> ) REFERENCES [dbo].[tblBundle] (
> [BundleID]
> )
> GO
> As far as the other questions, I did update statistics and it did not give
> me a performance boot. I also did DBCC DBREINDEX which did not help me
> either. Mowgli, I cant answer your question at this time since this is the
> only table I have 900,000 rows. others are like 1000 rows and they do
return
> data in less than a second (milli seconds). I have also verified that the
> problem is the query itself taking time and not communication. When I do a
> profiler trace i see most of the time is spent processing the query .(i
did
> insert into temp as well but the insert is longer because the 900,00 rows
> returned and inserted is taking time). After the insert is done. the
select
> * from temp is faster.
> Any other things i need to look into?.
> Thanks
> M
>|||M,
A note about your schema. It seems you have a policy of adding an
Identity column to each table, and calling it the Primary Key. However,
you haven't named the natural key, since there are no Unique constraints
(or unique indexes).
Take table ClaimDetails as an example. Maybe (ClaimID, BundleID) is the
natural key. If it is, then it is a good practice to either make it the
Primary Key, or at least create a Unique Constraint for it. You can make
the Unique constraint clustered if you like. The point is, that it will
get a unique index, and that is important for the query optimizer.
The di
vantage of adding a surrogate key like this Identity column, is
that it makes it harder to efficiently join tables if selection is done
based on the natural key (especially if it is not unique, as noted
before). For example, if the primary key of table ClaimDetails was
indeed (ClaimID, BundleID), then table tblClaimDetailBundle would have a
ClaimID column (and no ClaimDetailID) and the join between
tblClaimDetailBundle - tblClaimDetail - tblClaim would be much more
efficient, since it would not require a hash.
Unfortunately, redesigning your schema will have a lot of impact, and
might not be possible. But it would make the decision about the
clustered index easier, because if you don't have to choose between the
surrogate key and the natural key then it will always be the Primary Key
that you want to have clustered.
You could try the following tips, and see if they help:
- make the index IX_tblClaimDetailProduct_1 on table
tblClaimDetailProduct clustered
- for all indexes that will contain only unique values, please create
the index with the Unique keyword
- if you have an SMP machine, then make sure parallelism is available,
because your query is likely to benefit from parallelism (because of the
many hashes).
I guess it won't help much. Although I must say that I think SQL-Server
is doing a pretty good job (given the circumstances).
Gert-Jan
Sagar wrote:
> Thanks for all your replies. I appreciate it. Well Here is the table schema:[/colo
r]
<snip>
> As far as the other questions, I did update statistics and it did not give
> me a performance boot. I also did DBCC DBREINDEX which did not help me
> either. Mowgli, I cant answer your question at this time since this is the
> only table I have 900,000 rows. others are like 1000 rows and they do retu
rn
> data in less than a second (milli seconds). I have also verified that the
> problem is the query itself taking time and not communication. When I do a
> profiler trace i see most of the time is spent processing the query .(i di
d
> insert into temp as well but the insert is longer because the 900,00 rows
> returned and inserted is taking time). After the insert is done. the selec
t
> * from temp is faster.
> Any other things i need to look into?.
> Thanks
> M
