sql server programming: 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
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 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?

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

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

[quoted text, click to view]

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.

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.
[quoted text, click to view]
Scalar(DEFINE:([Expr1006]=[cdp].[SerialNumber]+'-PID-'+Convert([PB].[Product
ID])))
[quoted text, click to view]
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailBundle].[PK_tblClaimDetail
Bundle]
[quoted text, click to view]
Scan(OBJECT:([HPSpifCentral].[dbo].[tblClaimDetailProduct].[IX_tblClaimDetai
lProduct_SNo]
[quoted text, click to view]

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.


[quoted text, click to view]