Huge table update

Question

Post reply

Huge table update

Viewing 11 posts - 16 through 25 (of 25 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The TOP syntax works for me if you put the TOP between the UPDATE and the name of the affected table.

As in -

UPDATE Top(@rowsperbatch)

GHOSTDATA

SET

Regjimi = 'MI88'

WHERE

LEFT(NR_KEK,2) IN ('08','07','06') AND

Regjimi != 'MI88'

Same with DELETE by the way.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Dugi (3/28/2008)
OK I'm trying but still have problem with these message:
...
Incorrect syntax near the keyword 'Top'.
so this is what I'm asking for ...why this incorrect msg!!!??!?!?
thnx
Dugi

Oops, I should have tested it first. The TOP clause is out of order:

CREATE Procedure spGHOSTDATA_IncrementalUpdate( @RowsPerBatch int )

AS

Update Top (@RowsPerBatch) GHOSTDATA

Set Regjimi = 'MI88'

Where Regjimi <> 'MI88'

And NR_KEK >= '06'

And NR_KEK < '09'

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Also - I could swear that SET ROWCOUNT was "on its way out", i.e. being deprecated. I will have to see if I can dig that up.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Dugi SSCoach Points: 17998 More actions · Answer 4

ookkk now works fine ...thnx from all of you !!!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Matt Miller (3/28/2008)
Also - I could swear that SET ROWCOUNT was "on its way out", i.e. being deprecated. I will have to see if I can dig that up.

It is. I mentioned that in one of the earlier posts: BOL says that the "next" release will deprecate its use for everything except SELECT.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Dugi (3/28/2008)
ookkk now works fine ...thnx from all of you !!!

You're welcome, let us know how it works out Dugi.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Dugi SSCoach Points: 17998 More actions · Answer 7

Hmmm ...it's impossible ok the syntax of the procedure works and has been created but when I execute them both of them again doesn't work there is msg:

Msg 201, Level 16, State 4, Procedure spUpdatepare, Line 0

Procedure or function 'spUpdatepare' expects parameter '@RowsPerBatch', which was not supplied.

as you can see the problem is with parameter " @RowsPerBatch "

so what's now!?

Any idea ???

:w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Did you set you batch size, when you call call like this

EXEC spUpdatepare 25000

this will affect 25000 records in the batch.

Antares686 SSC Guru Points: 125444 More actions · Answer 9

rbarryyoung (3/28/2008)
Matt Miller (3/28/2008)
Also - I could swear that SET ROWCOUNT was "on its way out", i.e. being deprecated. I will have to see if I can dig that up.
It is. I mentioned that in one of the earlier posts: BOL says that the "next" release will deprecate its use for everything except SELECT.

Thanks, hadn't noticed that. Too busy with other things.

Dugi SSCoach Points: 17998 More actions · Answer 10

Antares686 (3/28/2008)
Did you set you batch size, when you call call like this
EXEC spUpdatepare 25000
this will affect 25000 records in the batch.

I confused with this problem so I forget that we have input parameter here I'm trying ...and it works now and I'm seeing what happened with time execution !

thnx Antares

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 11

ok there is the results for my table with over 1 500 000 records:

updateing

25 000 rec for 1:22 (min:sec)

250 000 rec for 7:19

over 500 000 or exactly 676 561 rec for 21:39

there si differences because with simple update it take over 30 min!

NOT BAD!

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]