30 minutes and still running, getting worried

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

SQL Server 2000 Forums

SQL Server Development (2000)

30 minutes and still running, getting worried

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-08 : 09:50:49

I have a very slow query.

I'm not surprised that it is slow.

But I would appreciate any help anyone can give to help me speed it up.

I dont think the indexes can be improved upon much. There are non-clustered indexes on the two columns for the join.

OldTable.ContactNote TEXT
EventHistory.EVH_Note VARCHAR 4000
EventHistory.Old_ColumnVal NULL more than 70% of the time

Records in EventHistory: 7312759
Records in OldTable: 2226317

UPDATE T
SET T.EVH_Note = S.ContactNote
FROM EventHistory T
INNER JOIN MyLinkedServer.MyDatabase.dbo.OldTable S ON T.Old_ColumnVal = S.Record_Pointer
WHERE DATALENGTH(S.ContactNote) <= 4000

Need to know anything else?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 09:54:17

how many rows are you updateing?

Go with the flow & have fun! Else fight the flow

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-08 : 10:01:14

Most of them
2222954

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-08 : 10:09:07

Inter-server statements can be real slow. It can be difficult if not impossible to speed it up. You may want to consider replicating OldTable to the server with EventHistory since your update uses close to the entire OldTable table anyway. Everytime the update statement runs, sql is pulling all that data over anyway. You may as well keep a copy local.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 10:11:01

well i remember reading somewhere that sql server processes about 1000 record per second in an update or insert.
try disabling the indexes.

Go with the flow & have fun! Else fight the flow

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-06-08 : 10:11:10

Can you do the updates in batches?....ie attempt to minimise logging.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-08 : 10:49:44

I waited 40 minutes and gave up the first time i tried. then I put the indexes on to help the join (not sure that would have helped much as query optimizer generally prefers the clustered index). so either way it's slow.
I'll try batching 1st and then I'll try moving the table to the same server if it's still slow.
Will putting this statement in a transaction speed it up?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-08 : 11:27:44

What's a good batch size for 2million+ records?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 11:33:34

5000 - 10000 rows

Go with the flow & have fun! Else fight the flow

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-08 : 11:53:24

Batching: 14 Minutes later I hit the stop button before I would have been driven to slit my throwt.
So now I'll copy the table to the same server and hope for the best. Else the SQL depression will kick in.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 11:56:01

emmm yeah linked servers can do that to you...
when working with linked servers the rule of thumb is to transfer as few rows as possible over the network.
hope that helps...

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-08 : 12:27:20

And you definitely DO NOT want to perform JOINs across servers, especially for large tables.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-08 : 12:47:31

Well the table is now in the same database and is still running 20 minutes later. I might be making improvements - but I'm not seeing the kind of performance improvement I was after - yet.
This is how the statement looks at the mo:

SET ROWCOUNT 100000
DECLARE @intRowCount int
SELECT @intRowCount=1
WHILE @intRowCount > 0
BEGIN

UPDATE T
SET T.EVH_Note = S.ContactNote
FROM EventHistory T
INNER JOIN OldTable S ON T.Old_ColumnVal = S.Record_Pointer
WHERE DATALENGTH(S.ContactNote) <= 4000

SELECT @intRowCount = @@ROWCOUNT
END

Thanks for all that advice guys. I DO appreciate it. What else can I try?
What's slowing it down? The JOIN? the nulls in one of the join columns? DATALENGTH? String comparison?
The size of the tables? The hardware?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-08 : 13:01:05

My first guess would be the condition:
WHERE DATALENGTH(S.ContactNote) <= 4000

checking the datalength of that many values can't be easy. Perhaps a calculated column on OldTable.ContactNote would speed things up.

ie: column def:
ContactNoteLength as DATALENGTH(ContactNote)

new criteria:
WHERE ContactNoteLength <= 4000

Be One with the Optimizer
TG

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-08 : 13:05:51

clever, i'll try that tomorrow. Thanks TG
the sun is shining, everyone else has gone home & I need a bear

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 13:09:15

hmmmm.... something seems wrong to me in that while of yours...
how do you know which 10000 records are you updating and
that you're not updateing already updated records?

Go with the flow & have fun! Else fight the flow

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-09 : 03:31:53

how else do you do batching then?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-09 : 03:45:03

quote:
Originally posted by coolerbob

clever, i'll try that tomorrow. Thanks TG
the sun is shining, everyone else has gone home & I need a bear

?

Damian
"Foolish consistency is the hobgoblin of little minds." - Emerson

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-09 : 04:40:38

well you have to have a column with values that differ.
for instance:


SET ROWCOUNT 100000
DECLARE @intRowCount int
SELECT @intRowCount=1
WHILE @intRowCount > 0
BEGIN

UPDATE T
SET T.EVH_Note = S.ContactNote,
SomeColumn = 'some value you need'
FROM EventHistory T
INNER JOIN OldTable S ON T.Old_ColumnVal = S.Record_Pointer
WHERE DATALENGTH(S.ContactNote) <= 4000 
      AND SomeColumn is null

SELECT @intRowCount = @@ROWCOUNT
END

i guess that SomeColumn can also be T.EVH_Note
but for anything better ... DDL, DML, sample data

this way you'll only update columns that aren't already updated.
you're still thinking of procedural code

rewire a part of your brain to set based

Go with the flow & have fun! Else fight the flow

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-09 : 07:50:50

Merkin, merkin, merkin
well spotted. you got a couple of us in stiches here with that one!

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-09 : 07:58:20

thanks spirit 1
will try that

Saying "rewrite" is one thing. Doing it is another. It is taking me a while. But I think it's fare to say that it's not a quick trip for most people.

** I love bears **

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources