SQL indexes

[jepatte]

I have a table with 2.5 mil records and one field is a varchar 50 which I have an index on. Although, I need to run a query to append some text on the values in this field.

But this takes over an hour to run. Is this normal?

 

[gmmastros]

I don't think that's normal.

You may get better performance by removing the index, updating the data, and then re-creating the index. It's worth a try.

-George

"the screen with the little boxes in the window." - Moron

 

[SQLDenis]

>>But this takes over an hour to run. Is this normal?

Do what george said, or try updating in batches of 50000

fragmentation level of the index also will play a role of how long it will take

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jepatte]

is there a special way to do in batches or do you mean just
add a where clause to do 50,000 based on the indentity seed or something like that?

 

[nice95gle]

Just to add to what George and Denis stated. Seeing that you are working with 2.5 mil records, make sure you have enough space to hold a copy of that data for re-creating the index if it's a clustered index. SQL Serevr will create a copy of the table then sort it then the original table dropped.

Good Luck



Well Done is better than well said
- Ben Franklin

 

[jepatte]

We have plenty of space, although, its not a clustered index. Should it be clustered?

 

[gmmastros]

>> Should it be clustered?

Probably not. Clustered indexes have better performance than non-clustered indexes. Also, you can only have 1 clustered index per table. Therefore, it makes the most sense to have the clustered index on the column(s) that improve overall performance the most. Usually, clustered indexes are put on the primary key column(s) of a table.

-George

"the screen with the little boxes in the window." - Moron

 

[nice95gle]

No, leave it as is. If it was clustered this is something you would have to take into consideration but it is not, so don't worry about it.

Well Done is better than well said
- Ben Franklin