April 26, 2012 at 7:32 am
hi guys,
I have a table with 110M rows in it, 33 fields per row, mostly varchars of varing lengths with no clustered index (20GB)
I am copying the table to another database on the same server but due to log file contraints have to move it one day's activity at a time. I created a non-clustered index on the transaction date, but the query plan is showing 98% cost for the RID lookup.
If I was to add the rest of the columns to the index as a covering index, this would eliminate the RID lookup, but how long to create and how much space would the index then take up
At the rate it is going, it will take another 36 hours to copy the data, it is now about half way through.
once we have copied this table the database will be dropped.
Is it worth creating the index or should I just put up with it.
April 26, 2012 at 7:41 am
aaron.reese (4/26/2012)
hi guys,I have a table with 110M rows in it, 33 fields per row, mostly varchars of varing lengths with no clustered index (20GB)
I am copying the table to another database on the same server but due to log file contraints have to move it one day's activity at a time. I created a non-clustered index on the transaction date, but the query plan is showing 98% cost for the RID lookup.
If I was to add the rest of the columns to the index as a covering index, this would eliminate the RID lookup, but how long to create and how much space would the index then take up
At the rate it is going, it will take another 36 hours to copy the data, it is now about half way through.
once we have copied this table the database will be dropped.
Is it worth creating the index or should I just put up with it.
Is there a key on the table at all? Could you put a clustered index on the key or unique set of columns? You could certainly then break down the copy in different ways and get better performance than what you are getting now.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 26, 2012 at 7:46 am
David,
same question applies really - A clustered index will re-write the data on the disk so there will still be a huge overhead for creating the index. As I am going to drop the table after doing this one job, will it take longer to create the index and copy the data WITH it, than copy the data WITHOUT it.
April 26, 2012 at 7:49 am
No, I would highly doubt that the creation of the index + the copy would take as long as without the index and allowing the copy to continue. However, you may run into log file size issues....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply