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



Scalar(DEFINE:([Expr1006]=[cdp].[SerialNumber]+'-PID-'+Convert([PB].[ProductID])))
Seek(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:([cd].[ClaimDetailID])=([cdb].[ClaimDetailID]))
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetail].[IX_tblClaimDetail_CDCS]
AS [cd]),  WHERE:([cd].[ClaimDetailStatusID]=1))
HASH:([PB].[ProductBundleID])=([cdp].[ProductBundleID]))
Scan(OBJECT:([HPSpifCentral].[dbo].[tblProductBundle].[IX_tblProductBundle8]
AS [PB]))
MERGE:([cdb].[ClaimDetailBundleID])=([cdp].[ClaimDetailBundleID]),
RESIDUAL:([cdp].[ClaimDetailBundleID]=[cdb].[ClaimDetailBundleID]))
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailBundle].[PK_tblClaimDetailBundle]
AS [cdb]),  WHERE:([cdb].[ClaimDetailBundleStatusID]=1) ORDERED FORWARD)
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

Check out UPDATE STATISTICS and see if you get a performance boost.

Show quoteHide quote

Do you have other queries that do return 900,000 rows in less than 4
seconds?

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_tblClaimDetailBundle]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
FK_tblClaimDetailProduct_tblClaimDetailBundle
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblClaimDetailProduct_tblProductBundle]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblClaimDetailProduct] DROP CONSTRAINT
FK_tblClaimDetailProduct_tblProductBundle
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]([ClaimDetailBundleStatusID], [ClaimDetailID],
[DeniedReasonID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [IX_tblClaimDetailBundle_CDB] ON
[dbo].[tblClaimDetailBundle]([ClaimDetailBundleID], [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]([ClaimDetailBundleStatusID]) 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]([ClaimDetailBundleID], [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]([ClaimDetailBundleID], [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_tblLUClaimDetailStatus] 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_tblLUClaimDetailStatus] FOREIGN KEY
(
  [ClaimDetailBundleStatusID]
) REFERENCES [dbo].[tblLUClaimDetailStatus] (
  [ClaimDetailStatusID]
)
GO

ALTER TABLE [dbo].[tblClaimDetailProduct] ADD
CONSTRAINT [FK_tblClaimDetailProduct_tblClaimDetailBundle] FOREIGN KEY
(
  [ClaimDetailBundleID]
) REFERENCES [dbo].[tblClaimDetailBundle] (
  [ClaimDetailBundleID]
),
CONSTRAINT [FK_tblClaimDetailProduct_tblProductBundle] 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 seek 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.


Show quoteHide quote[dbo].[tblClaimDetail]([ClaimID],
[dbo].[tblClaimDetail]([ClaimID],
Show quoteHide quoteN'[tblClaimDetail].[InsertDate]'
N'[tblClaimDetail].[UpdateDate]'
Show quoteHide quote

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 disadvantage 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:
<snip>
Show quoteHide quote

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:
Show quoteHide quote

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.
Show quoteHide quoteScalar(DEFINE:([Expr1006]=[cdp].[SerialNumber]+'-PID-'+Convert([PB].[Product
ID])))
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetail].[IX_tblClaimDetail_CDCS]
Scan(OBJECT:([HPSpifCentral].[dbo].[tblProductBundle].[IX_tblProductBundle8]
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailBundle].[PK_tblClaimDetail
Bundle]
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailProduct].[IX_tblClaimDetai
lProduct_SNo]
Show quoteHide quote

how to count the total rows for a query with group by?
Is there a smart set-based solution?
My table design - where did I go wrong?
Alter table / update... set - strange problem
Newbee question about permissions and sp_OACreate