Suggest me on tables

Question

Suggest me on tables

mahesh.kumar

Old Hand

Points: 355
More actions
October 7, 2008 at 9:17 am

#99280

Hi all,
I have a table in which updation happens for every minute. This will happen for 24*7. I have a plenty of selection query also running. Kinldy suggest whether i can use index on this table.
Select queriy is follows :
Select * from recordtablewhere registrationno='1234'
updation will hapen for the 4 fields which does not include registrationnno..
WIll indexing on registrationno optimize the query in reading..

Viewing 15 posts - 1 through 15 (of 17 total)

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

It looks to me that an index on registrationno will improve the performance of your SELECT queries, unless you have a large number of records having the same value in registrationno column.

For example, if a query with "registrationno='1234' " returns a large percent of rows from your table, SQL Server might decide to go with a table scan instead of an index-seek and in this case, the index may not give you any benefits. But if the selectivity of the rows is high (less number of records matching the criteria), sql server will use the index and it will give you better performance.

.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

An index on Registrationno will help more if it's a clustered index. If it's a non-clustered index, the SELECT statement will force a key lookup either against whatever clustered index you have or against the table. That can be as costly as the scan, depending on how much data you have in the table.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Prileep Mathan SSC Veteran Points: 207 More actions · Answer 3

Change the data type of the field registrationno from varchar to integer if it requires only numeric value.

Because creating index on integer field will be much faster than varchar fields on updating or inserting records.

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 4

Hi

Index willl surely improve the query performance.

Is this column is unique?

End users always going to use RegistrationNo to query this table?

What is the volume of the data?

Can u think of any column which you can use to partition?

Try answering the above question in order to put the right thing in place. if you have an option to change the datatype of this column then try converting it ino INT/NUMBER.

Thanks -- Vj

http://dotnetvj.blogspot.com

http://oravj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

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

Hi,

Thanks for your reply...

Actually the registrationno is going to be unique and it wil contain char value too hence i cannot use integer. I am having around 30k records and all the records will get updated within a minute. I have another column 'receive_Datetime' which is getting updated for all the records every minute.

I have this type of query also raised from the client side to check whether the record received within 10 min of time....

Select Registrationno,receive_datetime from recordtable where registrationno='kl 01 5432' and

dateadd(mi,-10,getdate())<receive_datetime.

What are the field i should use for indexing to make query run faster.....

Thanks & Regards

Maheshkumar B

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

Based on the query that you've described, I'd say a clustered index on the Registration Number. You should probably experiment with a clustered index on the registration number and the date field too.

Understand, that's just a (partially) educated guess. Without structures, queries, application intent, data distribution... this is largely just speculation. Don't take any of the suggestions at face value. Test them, carefully, against your system, it's queries and data. Then you can be sure.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Actually, the SELECT * is pretty much going to blow any chances of real performance because the index will never do a seek.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jacob sebastian SSChampion Points: 11822 More actions · Answer 8

Well, if the selectivity is high, query optimizer might use an 'index seek' even if the query contains "select *".

.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

True, but if it's not a Clustered Index, you'll also get an RID Lookup or a KEY Lookup. Now, on small tables, that doesn't mean much. But on a million row table, it means the difference between 3 logical reads and 6. LOL... yeah, I know... doesn't sound like a heck of a lot, but take a just 100 such "hits" per second and you end up with an extra 300 logical reads per second as well as the extra duration and it's just not necessary.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jacob sebastian SSChampion Points: 11822 More actions · Answer 10

Agreed. But in such cases, having an index (even if it is NC) might be better than not having an index (even if there is an RID lookup). An index seek and RID lookup together may be much desirable than a table scan. That was the point I want to emphasis.

.

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

Hi,

I have a updation on a datefiled for everyminute in all the records hence if i create a index on datefield wont it give a problem in writing..

Thanks & Regards

maheshkumar B

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

jacob sebastian (10/9/2008)
Agreed. But in such cases, having an index (even if it is NC) might be better than not having an index (even if there is an RID lookup). An index seek and RID lookup together may be much desirable than a table scan. That was the point I want to emphasis.

Yes, that's absolutely true... but my point was that you shouldn't use SELECT *. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

mahesh.kumar (10/9/2008)
Hi,
I have a updation on a datefiled for everyminute in all the records hence if i create a index on datefield wont it give a problem in writing..
Thanks & Regards
maheshkumar B

Yes... all indexes make inserts just a little slower. But they're worth it on SELECTs. That's why there's a couple of good DBCC commands to reorganize indexes after a lot of inserts or updates.

If your index is on a date field that indicates when the row was inserted, you won't notice much even on big INSERTs because the index won't frag.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jacob sebastian SSChampion Points: 11822 More actions · Answer 14

Yes, that's absolutely true... but my point was that you shouldn't use SELECT *. 🙂

I agree with you Jeff. I am not in favor of using 'SELECT *'

.