crazy slow update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

crazy slow update

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-28 : 10:08:16

So, in running a query to do an update to 65,536 rows, it's taken 45 minutes to get through HALF of those rows. The update looks like this:
update mnione set housenu='1010' where uniquekey='2prci8T{z9KfjZpq{iTath'
update mnione set housenu='1010' where uniquekey='24XmArj9j3ZxxDlZZWtP6s'
update mnione set housenu='1010' where uniquekey='0cxMtM2Pr1ygMa7J3MeNuO'
update mnione set housenu='1010' where uniquekey='0tLFOfZ{r3GB4KGQV2D6xz'
update mnione set housenu='1010' where uniquekey='1ZU3dgisvALenPV0pdpjAz'
update mnione set housenu='1020' where uniquekey='3VqTFhdSHDghtamG3zVjGB'
update mnione set housenu='1455' where uniquekey='1U2e7Mjaj1B8cniFmTduZa'
update mnione set housenu='1810' where uniquekey='0v{ysi{GH8vvMqCK94tv0Y'
update mnione set housenu='1090' where uniquekey='0t5JjFu8T5uA8k6m{C02B6'
update mnione set housenu='2010' where uniquekey='1vbxsElfTEOfJeEi1g{apH'

I have 8 such scripts with equal lines to each. It should NOT be taking this long.

Any clues as to how i can speed this up?
Sql 2008R2
3.4 GHz quad core processor
8GB RAM
64 Bit OS and SQL
no other significant apps running.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-28 : 10:17:52

Got an index on the column uniquekey?

--
Gail Shaw
SQL Server MVP

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-28 : 10:26:08

Nope. Someone else was suggesting that. Never dealt with indexes before. Nonetheless, i've since dumped the entire script process above and am going this route instead:

update mnione set housenuone =LEFT(addressline1,CASE WHEN CHARINDEX(' ',addressline1)> 0 THEN CHARINDEX(' ',addressline1) ELSE PATINDEX('%[A-Za-z]%',addressline1) END)
WHERE addressline1 LIKE '[0-9]%'

UPDATE MNIone SET housenu=SUBSTRING(Housenuone,1,5)

It pulls the string from the existing address data and finds the necessary characters at the beginning of the string, puts them in a temp column and then updates the necessary column as a substring thereof. All 1.4M rows done in a few seconds.

If you can, please explain how i would put an index on future columns for the unavoidable use of the other script.

thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 10:59:11

You should get to know INDEXES. your future success with relational databases will depend on them.

An index is just like in a book. It lets the engine go straight to the page without having to read the whole book first. (scans).

Please read this to get started:
http://www.sqlteam.com/article/sql-server-indexes-the-basics

TO create an index on the column in question you would issue this statement


CREATE INDEX IX_mnione_uniquekey ON mnione ([uniquekey])

This will build an index (called IX_mnione_uniquekey ) on the table for the column in question. Any query comaring against that column will be able to SEEK the index rather than scanning the table. Speed will improve - probably by many hundreds of times.

You *NEED* to get familiar with indexes.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-28 : 11:13:46

thanks. The concept and implementation seems very straightforward. I'll read the article fully as soon as i can, but a couple of questions. First, does the index that i place on a column remain after i close my connection or session? Is there a valid reason to "drop" the indexes that i might place for the purposes of inserts for a data conversion? I guess i'm wondering how costly they are in terms of system resources. It would seem that, since, resources are a zero-sum-game, anything that makes life better for me is taking a piece of the pie away from someone else.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 11:26:22

indexes persist. they are tree structures which enable quick seeking to data pages.

the downside to indexes is that they slow down inserts (in general) as an update or an insert needs to also maintain the index. However, you probably wont notice. In the end the answer is always "It Depends" -- if your reads heavily outweigh your writes then you will tend to index heavily. If not then you will index sparingly.

However, this means, that you should choose indexes carefully to match queries, not that you shouldn't have them.

In particular indexes between common JOIN criteria for tables are very, very, very, very, very, helpful.

Also -- they use up some disk space.

but read the article. You will be a much, much improved developer for it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-28 : 11:38:24

thanks again.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-28 : 11:55:08

Take a read through these:

[url]http://www.sqlservercentral.com/articles/Indexing/68439/[/url]
[url]http://www.sqlservercentral.com/articles/Indexing/68563/[/url]
[url]http://www.sqlservercentral.com/articles/Indexing/68636/[/url]

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources