Post reply

Suggest me on tables

  • 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..

  • October 7, 2008 at 11:02 am

    #881825

    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.

    .

  • October 7, 2008 at 11:18 am

    #881841

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • October 7, 2008 at 10:43 pm

    #882102

    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.

  • October 8, 2008 at 12:55 pm

    #882524

    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


  • October 8, 2008 at 11:16 pm

    #882700

    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

  • October 9, 2008 at 5:53 am

    #882814

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • October 9, 2008 at 9:26 pm

    #883225

    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)
    Intro to Tally Tables and Functions

  • October 9, 2008 at 9:37 pm

    #883228

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

    .

  • October 9, 2008 at 10:19 pm

    #883237

    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)
    Intro to Tally Tables and Functions

  • October 9, 2008 at 10:51 pm

    #883247

    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.

    .

  • October 9, 2008 at 11:13 pm

    #883252

    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

  • October 9, 2008 at 11:20 pm

    #883255

    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)
    Intro to Tally Tables and Functions

  • October 9, 2008 at 11:23 pm

    #883256

    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)
    Intro to Tally Tables and Functions

  • October 9, 2008 at 11:34 pm

    #883262

    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 *'

    .

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

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