Doubt in indexing and slow queries

Question

Doubt in indexing and slow queries

mahesh.kumar

Old Hand

Points: 355
More actions
October 9, 2008 at 12:09 am

#99311

Dear all,
I have a table which have frequent insertion happening around. We are inserting 2000 records per minute through ado.net(unfortunately we cannot use bulk copy here). This process is running for 24*7. This table will get record 14,00,000 record in a day.
we cannot able to create index also because of huge insertion happening around hence the query in retrieval proces became slow. To minimize the data growth in the table we are keeping only the last 2 days records present in the 'recordtable' table and the rest of the records will be moved to archive database by every night using SSIS. Now what happens is after a periodic acces for more than a month the query was running very slow. So i moved all the current records to new table and rename the new table to current one. After that the query to the table is fast.
Is this anything because of defragmentation?( happens because frequent deletion of the records)
I am using the below query to read the records from the table. Kindly suggest me whether i can use index for my situation....
select registrationno,latitude,longitude,ignition,speed,gpsdatetime from gpsdata_history where registrationno='kl 09 4567' and gpsdatetime between '01/09/2008 14:00' and '01/09/2008 16:00'
The table structure is folllows
CREATE TABLE [dbo].[recordtable](
[SNo] [bigint] IDENTITY(1,1) NOT NULL,
[REGISTRATIONNO] [varchar](50) NULL,
[UNITNO] [varchar](50) NULL,
[LATITUDE] [float] NULL,
[LONGITUDE] [float] NULL
[SPEED] [numeric](5, 0) NULL,
[GPSDATETIME] [datetime] NULL,
[DIRECTION] [varchar](10) NULL,
[ODOMETER] [numeric](12, 2) NULL,
[IGNITION] [char](1) NULL,
[LOCATION] [varchar](200) NULL,
[BasestationName] [varchar](50) NULL,
[logic_state] [varchar](20) NULL,
[SlNo] [bigint] NULL,
[EngineTime] [nvarchar](10) NULL,
[AContime] [nvarchar](10) NULL,
[SpeedLimitTime] [nvarchar](10) NULL,
[IdleTime] [nvarchar](10) NULL
)

Viewing 9 posts - 1 through 8 (of 8 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

Dont you have any indexes on the table. Without proper indexes data retrival queries will be slow. You have to be very sure of the queries that hit the table before creating a index. In most cases even if the data insertion is huge its best to have indexes specially a clustered index.

Clustered index is best created on columns whose values are very distinct ex:- Identity columns. However column(s) on which clustered index is created should be frequently used in queries.

With the info that you have given us i would make a guess that clustered index on registration number would be a good bet. Although its a varchar column i would say its going to hold unique data. Another column can be the date column if its used in almost all your queries.

You would also have to plan periodical index maintenance.

"Keep Trying"

mahesh.kumar Old Hand Points: 355 More actions · Answer 2

Hi,

First of all thanks for your reply..

We have around 1000 vehicles and for every minute all the vehicles status will be inserted with datetime, igntiion ,speed etc.. So in this table registrationno will not be unique and for every minute each vehicles data been inserted...

Frequent insertion is happening in the table. In this case creating indexing wont affect the writing performance?. Kindly suggest me if am going wrong.

Thanks & Reply

Maheshkumar B

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

A clustered index on the GPSDATETIME and REGISTRATIONNO seems like the best we can do. Having clustered index increases requires some resources for index maintenance whenver a insert operation/delete operation occurs. But its only a little bit and its use-fullnes (in queries) far outweighs this. Other than what Wandrag has suggested i would look to keep a lower fill factor around 40-50.

"Keep Trying"

mahesh.kumar Old Hand Points: 355 More actions · Answer 4

I have a frequent insertion and updation is going on so defragmentation is a problem in my current system?

Thanks & Regards

Maheshkumar

mahesh.kumar Old Hand Points: 355 More actions · Answer 5

Hi,

Registrationno and date filed are not unique fields. Shall i create a index for datfield first then registrationno?

Huge insertion and deletion is going in my field. Is defragmentation be a problem in my current system apart from non index issue?

Thanks & Regards

Maheshkumar B

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

Hi

Is the combination of RegistrationNo and the datefield a unique combination. I would think so. So you are better off creating a clustered index on both of these fields together, not separate indexes. Anyway you can have only 1 clustered index.

As regards defragmentation its happening now without clustered index on your table and will happen even after creating a clust index. The thing is after having clustered index you will be able to manage it better. You will need to have maintenance plans for this.

Create the clust index in a test environment and test properly.

"Keep Trying"

mahesh.kumar Old Hand Points: 355 More actions · Answer 7

Any chance of making this index maintanance plan automatically...

ChiragNS One Orange Chip Points: 26137 More actions · Answer 8

ChiragNS

One Orange Chip

Points: 26137

October 13, 2008 at 12:32 am

#883978

Yes use the maintenance plan wizard.

"Keep Trying"