Query Syntax: tuning

Showing posts with label tuning. Show all posts

Friday, March 23, 2012

Query Tuning.

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_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

Query Tuning problem.

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE939
VGNCCB_ROLE_JT62389
VGNCCB_GROUP_USER_JT1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
Try this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =
ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
pintable[vbcol=seagreen]
take
>
>.
>
|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?
|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
(Please reply only to the newsgroup)

Query Tuning problem.

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
JackTry this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =
ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
pintable[vbcol=seagreen]
take[vbcol=seagreen]
>
>.
>|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
(Please reply only to the newsgroup)

Query Tuning problem.

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
JackTry this query... I think the logic is the same... At least, it might help
you go in the right direction. You had a subquery within a subquery in your
where clause. This means that for every row of the outer table, the query
engine would have to do one subquery on the inner table and one subquery on
the inner table within that subquery for every row of the inner table!
That's a lot of work... Use JOINs instead:
SELECT
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM vign.VGNCCB_ROLE ROLE
JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID = ROLE_JT.ROLE_ID
JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
ON ROLE_JT.USER_NAME='testRole'
OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
AND USER_JT.USER_NAME = 'testRole'))
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fb6901c43e71$19861260$a001280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack|||This did not work.
>--Original Message--
>Try this query... I think the logic is the same... At
least, it might help
>you go in the right direction. You had a subquery within
a subquery in your
>where clause. This means that for every row of the outer
table, the query
>engine would have to do one subquery on the inner table
and one subquery on
>the inner table within that subquery for every row of the
inner table!
>That's a lot of work... Use JOINs instead:
>SELECT
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
>FROM vign.VGNCCB_ROLE ROLE
>JOIN vign.VGNCCB_ROLE_JT AS ROLE_JT ON ROLE.ROLE_ID =ROLE_JT.ROLE_ID
>JOIN vign.VGNCCB_GROUP_USER_JT AS USER_JT
> ON ROLE_JT.USER_NAME='testRole'
> OR (ROLE_JT.GROUP_ID = USER_JT.GROUP_ID
> AND USER_JT.USER_NAME = 'testRole'))
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fb6901c43e71$19861260$a001280a@.phx.gbl...
>> Guys,
>> I'm stumped. While its not pertinent to the
>> matter, we are running a Vignette content management
>> system on Win2k with Sql 2000 Enterprise on a cluster.
>> The server has 2 Gig of RAM , 2 CPU's and the database
>> size is 1.5G.
>> The query below is fired at login. The indexes
>> seem fine based on the query plan. When I look through
>> profiler, the query below takes a very high # of CPU
>> cycles and reads. It consistently takes more than 1.5
>> seconds to execute the query below. I did a dbcc
pintable
>> for ALL the tables in the query and that did not help
>> either. It seemed to make it worse (3 seconds and above)
>> Any idea what could be the issue here? The server
>> is not really heavily taxed.
>> The tables are small. They have very few rows.
>> VGNCCB_ROLE 939
>> VGNCCB_ROLE_JT 62389
>> VGNCCB_GROUP_USER_JT 1364
>>
>> The problem Query:
>> select
>> ROLE_ID,
>> NAME,
>> DESCRIPTION,
>> CREATE_DATE,
>> MODIFIED_DATE
>> FROM
>> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
>> WHERE
>> ROLE_ID in
>> (select ROLE_ID
>> FROM
>> vign.VGNCCB_ROLE_JT -- Non clustered indexes
>> on USER_NAME AND non clustered on GROUP_ID
>> WHERE
>> USER_NAME = 'testRole' or GROUP_ID in (select
>> GROUP_ID
>> FROM
>> vign.VGNCCB_GROUP_USER_JT -- Non clustered
>> index on USER_NAME
>> WHERE
>> USER_NAME = 'testRole'))
>> I'd appreciate it if someone could follow me in this
>> thread to completion. Such a simple query should not
take
>> this long.
>>
>> TIA,
>> Jack
>
>.
>|||"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc0701c43e8b$6948a000$a601280a@.phx.gbl...
> This did not work.
Are you going to elaborate, or is the issue closed?|||Does this query run any better?
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
WHERE RJT.USER_NAME = 'testRole'
UNION
SELECT R.ROLE_ID,
R.NAME,
R.DESCRIPTION,
R.CREATE_DATE,
R.MODIFIED_DATE
FROM vign.VGNCCB_ROLE R
INNER JOIN vign.VGNCCB_ROLE_JT RJT
ON RJT.ROLE_ID = R.ROLE_ID
INNER JOIN vign.VGNCCB_GROUP_USER_JT GUJT
ON GUJT.GROUP_ID = RJT.GROUP_ID
WHERE GUJT.USER_NAME = 'testRole'
Hope this helps,
Gert-Jan
Jack A wrote:
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
--
(Please reply only to the newsgroup)

Query Tuning -- Where Exists?

Hello World!
The following query takes more than one hour to complete. Assuming the
tables are properly indexed, if this can be tuned for quicker return?
Besides, I wonder if WHERE EXISTS is always/usually more efficient than
INNER JOIN? Can WHERE EXISTS be implemented in this case and how? Many THANK
S.
SELECT DISTINCT count(*)
FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
ON
Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+LEFT(t.[Date],2))=d
d.DATE_FULL
AND dm.MINUTE_NUMBER_24=t.[Time]
AND dt.HQ_ID=t.HQ_ID
AND dt.Store_ID=t.Store_ID
AND dt.Dept_ID=t.Dept_ID
AND Convert(bigint,fs.Invoice_No)=(
CASE WHEN t.TransactionType='CreditCard' THEN
Convert(bigint,LEFT(t.JoinKey,4) )
ELSE Convert(bigint,t.JoinKey)
END
)
WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) = 1Hi
Have a look at execution plan of the query. Does it use any indexes?
I have my doubt ,because in WHERE condition a LEFT function would not
probably allow to optimyzer to use the index.
How is selective the columns that participate in WHERE condition?
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:35BD39C0-F97C-4A56-AC84-125B55D8F488@.microsoft.com...
> Hello World!
> The following query takes more than one hour to complete. Assuming the
> tables are properly indexed, if this can be tuned for quicker return?
> Besides, I wonder if WHERE EXISTS is always/usually more efficient than
> INNER JOIN? Can WHERE EXISTS be implemented in this case and how? Many
THANKS.
>
> SELECT DISTINCT count(*)
> FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
> ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
> ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
> ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
> ON
>
Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+L
EFT(t.[Date],2))=dd.DATE_FULL
> AND dm.MINUTE_NUMBER_24=t.[Time]
> AND dt.HQ_ID=t.HQ_ID
> AND dt.Store_ID=t.Store_ID
> AND dt.Dept_ID=t.Dept_ID
> AND Convert(bigint,fs.Invoice_No)=(
> CASE WHEN t.TransactionType='CreditCard' THEN
> Convert(bigint,LEFT(t.JoinKey,4) )
> ELSE Convert(bigint,t.JoinKey)
> END
> )
> WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) = 1|||Hi
Have you tried to look at the execution plan? You should get a good idea
where/how its spending its time. Optimizing without the plan is rather
difficult.
You can use the SQL Query Analyzer that comes with the Enterprise
Manager's client-side tool set.
-David
C TO wrote:
> Hello World!
> The following query takes more than one hour to complete. Assuming the
> tables are properly indexed, if this can be tuned for quicker return?
> Besides, I wonder if WHERE EXISTS is always/usually more efficient than
> INNER JOIN? Can WHERE EXISTS be implemented in this case and how? Many THA
NKS.
>
> SELECT DISTINCT count(*)
> FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
> ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
> ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
> ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
> ON
> Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+LEFT(t.[Date],2))
=dd.DATE_FULL
> AND dm.MINUTE_NUMBER_24=t.[Time]
> AND dt.HQ_ID=t.HQ_ID
> AND dt.Store_ID=t.Store_ID
> AND dt.Dept_ID=t.Dept_ID
> AND Convert(bigint,fs.Invoice_No)=(
> CASE WHEN t.TransactionType='CreditCard' THEN
> Convert(bigint,LEFT(t.JoinKey,4) )
> ELSE Convert(bigint,t.JoinKey)
> END
> )
> WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) = 1|||Your query relies on doing calculations to join between tables and to filter
results. Because of this, SQL Server cannot use indexes to do quick lookups
and will instead do a full table scan, running the calculation on each row
to try to get a match. Try taking the calculations from the query and
perhaps using them to produce indexable calculated column(s).
In news:35BD39C0-F97C-4A56-AC84-125B55D8F488@.microsoft.com,
C TO <CTO@.discussions.microsoft.com> said:
> Hello World!
> The following query takes more than one hour to complete. Assuming the
> tables are properly indexed, if this can be tuned for quicker return?
> Besides, I wonder if WHERE EXISTS is always/usually more efficient
> than
> INNER JOIN? Can WHERE EXISTS be implemented in this case and how?
> Many THANKS.
>
> SELECT DISTINCT count(*)
> FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
> ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
> ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
> ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
> ON
>
Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+L
EFT(t.[Date],2))=dd.DATE_FULL
> AND dm.MINUTE_NUMBER_24=t.[Time]
> AND dt.HQ_ID=t.HQ_ID
> AND dt.Store_ID=t.Store_ID
> AND dt.Dept_ID=t.Dept_ID
> AND Convert(bigint,fs.Invoice_No)=(
> CASE WHEN t.TransactionType='CreditCard' THEN
> Convert(bigint,LEFT(t.JoinKey,4) )
> ELSE Convert(bigint,t.JoinKey)
> END
> )
> WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) =
> 1
Stevesql

Query tuning

How do you guys go about compare the efficiency of two queries?
What I usually do is run the "Display estimated execution plan" in the query
analyser and see each query cost (relative to the batch) and pick one that
gives lower percentage. Is this a good way to compare queries? Am I
missing something just by looking at that number?
Do I actually have to check IO costs, CPU running time, run the profile (and
look for what)?
Correction, tips and suggestion of best practice will be appreciated.
ThanksJustin
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
Yes , sure , as well as looking at EXECUTION PLAN of the both queries
http://www.sql-server-performance.c...performance.asp
"Justin" <nospam@.nospam.com> wrote in message
news:Oe3YBjflGHA.4244@.TK2MSFTNGP02.phx.gbl...
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the
> query analyser and see each query cost (relative to the batch) and pick
> one that gives lower percentage. Is this a good way to compare queries?
> Am I missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>|||Justin wrote:
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the que
ry
> analyser and see each query cost (relative to the batch) and pick one that
> gives lower percentage. Is this a good way to compare queries? Am I
> missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile (a
nd
> look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>
The estimated plan is a good place to start, you can identify the most
expensive parts of the query from that. You should also look at the I/O
stats and the actual execution plan. From the I/O stats, you can
identify the tables that are hit the hardest, and focus on potential
indexes, etc for those tables. Looking at the actual execution plan
will help you identify potential new indexes, improved joins, sorts, etc..

Query Tuning

Hi,
I have a query that selects 4 fields. One is of type Varchar(500). When I
execute the query, the response is about 9 seconds (very slow). When I
comment out the varchar field, it returns in less than 1 second.
It took me a while to figure out that it's not a missing index, i can't
figure this one out.
please advise.
rafaelHow many records are being returned?
"Rafael Chemtob" wrote:

> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael
>
>|||10
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>
When I|||> How many records are being returned?
..And what is the average length of the data in those rows?
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
> How many records are being returned?
> "Rafael Chemtob" wrote:
>|||ok, sorry for not being very detailed.
4 fields
id_rating INT
summary VARCHAR(500)
dt_rating smalldatetime
id_user INT
these are the 4 fields. The record count that's returned is 11 rows.
Hope that helps
thanks
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> ..And what is the average length of the data in those rows?
>
> Thomas
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:6F062F1B-91A3-45B6-AB3E-F08DF4A37831@.microsoft.com...
When I
>|||Is the performance discrepancy consistent? i.e., have you tested this a
number of times? How many records are in the table?,
and finally, is there an index on the table that contains all the other
three columns from the table, but not the varchar(500) column?
Also, please post the DDL for the tables, and the actual Query.
Charly
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I mean, is there an index on the table which includes columns
(id_rating, dt_rating, id_user), but not Column summary ?
"Rafael Chemtob" wrote:

> ok, sorry for not being very detailed.
> 4 fields
> id_rating INT
> summary VARCHAR(500)
> dt_rating smalldatetime
> id_user INT
> these are the 4 fields. The record count that's returned is 11 rows.
> Hope that helps
> thanks
>
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:#45wbrTRFHA.2384@.tk2msftngp13.phx.gbl...
> When I
>
>|||I query the table using id_rating (which is the PK).
and this is consistent. I comment out the varchar field and i get the
results MUCH quicker.
rafael
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> I mean, is there an index on the table which includes columns
> (id_rating, dt_rating, id_user), but not Column summary ?
>
> "Rafael Chemtob" wrote:
>
When I
can't|||As I asked above, one possible reason for this is if there's an index that
includes the columns (id_rating, dt_rating, id_user), but NOT the summary
column. If that were the case, the query processor could use the index alon
e
for the query without Summary, but would be forced to do a table scan when
you include summary.. Is there such an index?
"Rafael Chemtob" wrote:

> I query the table using id_rating (which is the PK).
> and this is consistent. I comment out the varchar field and i get the
> results MUCH quicker.
> rafael
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:456CEB9F-6A05-464C-9EE5-93F4FF0B7DB3@.microsoft.com...
> When I
> can't
>
>|||Hi Rafael,
For performance questions like these, it is very important to post all
relevant DDL (so including indexes, constraints, etc.) and the exact
query.
So just a wild guess for now: make sure you have a clustered index on
the table. If the table does not have a clustered index, and you delete
many rows, then querying the table can become very slow.
HTH,
Gert-Jan
Rafael Chemtob wrote:
> Hi,
> I have a query that selects 4 fields. One is of type Varchar(500). When
I
> execute the query, the response is about 9 seconds (very slow). When I
> comment out the varchar field, it returns in less than 1 second.
> It took me a while to figure out that it's not a missing index, i can't
> figure this one out.
> please advise.
> rafael

Friday, March 23, 2012

Query Tuning.

Query Tuning problem.

Query Tuning problem.

Query Tuning problem.

Query Tuning -- Where Exists?

Query tuning

Query Tuning

Query Syntax

Blog Archive

About Me