slow performance on select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

slow performance on select

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-01-22 : 11:49:22

i am making a select on a tables with index defined.
on of the colum with an index (not an index of its own but with 2 other column) is a colum that is defined as bigint and holds datetime in this structure :
yyyymmddhhmmss
when i make a select on this tables which has about 6 millions rows
its performance very slow (i get result of 300 rows in 2 minutes)
why is that?
beacuse that column isnt defined with ists own key?
or maybe beacuse the indexed should be rebuild?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-22 : 12:23:02

1. you should store dates in datetime columns, not bigint.
2. post ddl for tables and indexes.
3. post sample data for your table.
4. post your query that is running slow.

then someone will have a chance to help you.

www.elsasoft.org

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-01-22 : 12:50:54

1 TOO LATE CANT FIX SOMETHING that works for 3 years and to change all the code now (by the way is a search on dates faster then on big int? assuming i do thesame query for both and same amount of rows???)
2.




CREATE TABLE [dbo].[tbl1] (
	[Company] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[App] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Cm] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BLM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[StartDate] [bigint] NOT NULL ,
	[Sender] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Recipient] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Content] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ET] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FinalDate] [bigint] NULL ,
	[MS] [int] NULL ,
	[IP] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EL] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Reason] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Group] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[User] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Charge] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ChargeP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[IsCharged] [bit] NULL ,
	[RB] [bit] NULL ,
	[Code] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OP] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ServiceName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [January2007]
GO

ALTER TABLE [dbo].[tbl1] ADD 
	CONSTRAINT [PK_tbl1] PRIMARY KEY  NONCLUSTERED 
	(
		[BLM],
		[Recipient],
		[StartDate]
	) WITH  FILLFACTOR = 90  ON [January2007] ,
	CONSTRAINT [CK_tbl1] CHECK ([StartDate] >= '20070101000000' and [StartDate] <= '20070131235959')
GO

 CREATE  INDEX [INDX_COMPANY_APP_EVT] ON [dbo].[tbl1]([Company], [EVT]) ON [January2007]
GO

 CREATE  INDEX [INDX_RECIPIENT] ON [dbo].[tbl1]([Recipient]) ON [January2007]
GO

 CREATE  INDEX [INDX_SENDER] ON [dbo].[tbl1]([Sender]) ON [January2007]
GO

 CREATE  INDEX [INDX_SERVICE_CODE] ON [dbo].[tbl1]([Code]) ON [January2007]
GO

 CREATE  INDEX [INDX_SERVICE_NAME] ON [dbo].[tbl1]([ServiceName]) ON [January2007]
GO

select startdate from tbl1 where startdate >=20000121232323 and 
startdate <=20000122232323

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-22 : 13:54:20

try putting a clustered index on final date.

CREATE CLUSTERED INDEX [INDX_FinalDate] ON [dbo].[tbl1]([finalDate]) ON [January2007]
GO

edit: my bad. put it on start date.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-22 : 15:25:51

exactly. Your query is hitting the startdate column but you have no indexes, clustered or otherwise, on this column.

Either a clustered or nonclustered index on startdate will help your query enormously.

However I can tell you right now your query as written won't return any rows because the range in your where clause is not compatible with your check constraint on the startdate column.

and about bigint vs datetime: the reason to use datetime is because the value you are storing is a date. with a bigint, you can't use all the date related functions in sql (such as dateadd, etc). also, you are more susceptible to bad data - how would you interpret negative values for example? these would be allowed by a bigint column but certainly not a datetime column.

of course I understand you can't change it now though. in the future, you can use datetime for dates.

www.elsasoft.org

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-01-23 : 01:51:37

the quesy wont return anything beacuse i have types values from head by on my query on thwe sql server i fixed it and it works but slow.

whats the diffrence between clustered and non clustered index?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2007-01-23 : 02:39:55

Do you need all those columns to be Nvarchar, rather than Varchar? That won't help performance either ... particularly on a table as large as 6,000,000 rows. The widths seem to be mostly 50 - might be worth reviewing the MAX(LEN(BLMJ)) [and other columns similarly] to see if they actually need to be that long.

Kristen

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-23 : 10:42:38

quote:
Originally posted by pelegk2

whats the diffrence between clustered and non clustered index?

clustered physically arranges the data around the index -- in other words, data is physically stored in order specified by index. this is why u can have only one clustered index

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-23 : 17:05:21

for your query, a clustered index on startdate will be faster than non-clustered. This is because the server won't have to do any bookmark lookups to find the actual data once it has found it in the index on a seek, since the data and index are "one and the same" for a clustered index.

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources