Post reply

Creating Index on Table which containing Millions of rows

  • April 9, 2009 at 3:34 pm

    #75454

    Hi,

    I am working on one of the Database which has more then 30 tables. One of the table containing Millions of records which I transfer over another table for monthly basis for calculating staff time and attendance Duration and charges.

    Let say Table_A got 100,000,00 rows and transfer to Table_B 500,000,0 rows every month. Table_B always clear out (Truncate) when calculating the data. But when I tried to work on Table_B, it took more then 6 hours to get the job done. This table got no index on it.

    Now, I created on Table_B - 1 cluster index and 8 normal index before I insert the data, then I transfer 500,000,0 rows to Table_B. It took 1hours and 30 mins to insert that record but it took only 30 mins before I created the index on table.

    So, my questions is -

    1. Shall I remove all index and copy the 500,000,0 rows first then create the index on it? I think that will make the process slow too, isn't it?

    2. When I do the calculation, it took nearly 6 hours to finish the job,How to make processing faster?

    Please help.

    Thanks.

    Leo

  • April 9, 2009 at 4:47 pm

    #975496

    Remove all indexes but clustered.

    Recreate dropped indexes after populating the table.

    But actually moving of such a chunk of data away for processing is not such a brilliant idea.

    There must be the way to work it out from Table A without killing your disks.

    _____________
    Code for TallyGenerator

  • April 10, 2009 at 9:11 am

    #975821

    Mr. Journeyman

    there are several things to consider here, One the table may not be the only issue, you have to look at the store procedures and tune the store procedures to get the fastest result. take a look at the way the store procedures are calling the table, such as is there a date invoulved, are there GUID called, what is the most often called field, do we have an index on that field, what is the method the store procedure using to calculate the resultset. do we have temp tables are we using any or do we need them, are using Memory to calculate. we do not go indexing tables for the sake of indexing, you gain nothing and you may end up loosing performance.

    see if this help

    "We never plan to Fail, We just fail to plan":)

  • April 11, 2009 at 2:38 pm

    #976189

    Hello,

    Thanks for two of your suggestion. I tried with 1 cluster index and 1 normal index(which contain 6 fields in it), that make it bit faster. But it didn't show better result. It took few minutes less then normal process. But not much.

    Shall I tried with 1 cluster index and 6 indexes (split up those 6 fields and set by individual indexes) ?

    Regards,

    Leo

  • April 11, 2009 at 6:07 pm

    #976227

    If it's possible try to order the data before it's being inserted, in SELECT part.

    _____________
    Code for TallyGenerator

  • April 11, 2009 at 8:15 pm

    #976234

    I agree... it would probably be better to not move the data before processing. And, if that's still somehow necessary, I've had great success with just dropping the table and using SELECT/INTO to move the data. Of course, that depends if you're doing some calculations on the way in or not and whether the source of information is actually a table or an aggregated view.

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

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

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