Select statement running very slow on a table

Question

Post reply

Select statement running very slow on a table

Kiran Kumar MP

SSC Enthusiast

Points: 176
More actions
November 6, 2008 at 9:42 pm

#75358

Hi,
I have a table when I run a select statements it is taking lot of time and returning 17000 row.
Please find the table structure here.
CREATE TABLE [ContractHistory] (
[ContractNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Branch] [tinyint] NOT NULL ,
[CcyCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WHCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[MaturityDate] [smalldatetime] NOT NULL ,
[DealDate] [smalldatetime] NOT NULL ,
[DealTime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EODDate] [smalldatetime] NOT NULL ,
[Action] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SubProductId] [smallint] NULL ,
[Asset] [bit] NULL ,
[BaseNo] [bigint] NULL ,
[CustomerName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DealerCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Principal] [numeric](28, 16) NULL ,
[IssueDate] [smalldatetime] NULL ,
[FirstStartDate] [smalldatetime] NULL ,
[FinalMaturityDate] [smalldatetime] NULL ,
[DealRate] [float] NULL ,
[CoverRate] [float] NULL ,
[InterestType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InterestSchedule] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InterestMethod] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AmortisationSchedule] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AmortisationAmount] [numeric](28, 16) NULL ,
[RateCurveId] [tinyint] NULL ,
[ContractType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccruedDays] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CouponRate] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuthorizeDate] [smalldatetime] NULL ,
[DailyAccrual] [numeric](28, 16) NULL ,
[ToDateAccrual] [numeric](28, 16) NULL ,
[ProjectedDailyAccrual] [numeric](28, 16) NULL ,
[InterestAmount] [numeric](28, 16) NULL ,
[LastUpdatedOn] [smalldatetime] NULL ,
[BondType] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ContractHistory] PRIMARY KEY CLUSTERED
(
[ContractNo],
[Branch],
[CcyCode],
[WHCode],
[StartDate],
[MaturityDate],
[EODDate],
[Action]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
//Constraint
ALTER TABLE [dbo].[ContractHistory] ADD CONSTRAINT [PK_ContractHistory] PRIMARY KEY CLUSTERED
(
[ContractNo],
[Branch],
[CcyCode],
[WHCode],
[StartDate],
[MaturityDate],
[EODDate],
[Action]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
//Index
CREATE INDEX [IX_ContractHistory] ON [dbo].[ContractHistory]([ContractNo], [Branch], [CcyCode], [WHCode], [EODDate] DESC , [Action], [RateCurveId]) ON [PRIMARY]
GO
//select stament
select * from dbo.ContractHistory where eoddate between '20080902'
and '20080903' and (action='delete' or action='')
Please help us to Increase the performance. because this table used in other SP and that SP's performance is very poor.
Thanx
Kiran

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Why create a second index almost identical as your composite primary key?

N 56°04'39.16"
E 12°55'05.25"

Kiran Kumar MP SSC Enthusiast Points: 176 More actions · Answer 2

Kiran Kumar MP

SSC Enthusiast

Points: 176

November 7, 2008 at 5:08 am

#895425

Please ignore that part

CrazyMan SSChampion Points: 12560 More actions · Answer 3

Hi Kiran

How many records are there in this table that you have created? have u got an execution plan for his query??

Cheers

🙂

Kiran Kumar MP SSC Enthusiast Points: 176 More actions · Answer 4

there are 3lakh files.

select cost 6%

cintracthistory cost 94%

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 5

try like:

select * from dbo.ContractHistory with (nolock) (index = IX_ContractHistory)

where eoddate between '20080902' and '20080903' and

(action = 'delete' or isnull (action, '') = '')

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

CREATE INDEXIX_ContractHistory ON dbo.ContractHistory

(

EODDate DESC,

Action,

ContractNo,

Branch,

CcyCode,

WHCode,

RateCurveID

) ON [PRIMARY]

SELECT*

FROMdbo.ContractHistory

WHEREeodDate >= '20080902'

AND eodDate < '20080903'

AND (Action = 'delete' OR Action = '')

N 56°04'39.16"
E 12°55'05.25"