Batch Processing

/**
l'm trying to improve my batch processing routines by
creating a generic method which l can apply on most of
my jobs.Reason Being l'm working with large Data Sets
This is what l'm trying to achieve
1.Get The Rowcount of the Source
2.Split It Into manageable batches
3.Loop through the batches Until the last one is Done
**/
How do l intergrate these two so it can automatically pick up the
batchsizes and process until the last batch is done.
--Outer Loop
Declare @.RowCount Int
Declare @.LoopIteration Int
Declare @.BatchSize Int
--Get The RowCount Of the Table
Select @.RowCount = (Select Count(*) From tbTest)
--
Select @.LoopIteration = @.RowCount/2000000
Select @.BatchSize = @.RowCount/@.LoopIteration
--Select Distinct Client,Convert(Varchar(15),'') As CltNo
-- ,Convert(Int,0) As Status Into tbtest
from tbAddress
--Inner Loop --Works when run Manually
SET ROWCOUNT @.BatchSize--100000 --54 Secs
WHILE 1 = 1
BEGIN
Begin Transaction
Update tbTest
Set CltNo = Client + 1
,Status = 1 Where Status = 0
Commit
if @.@.rowcount = 0
break
END
SET ROWCOUNT 0
--Select * from tbTest Where Status = 1
Message posted via http://www.webservertalk.comWhy do you care what the table size is? You should pick a batch size that
can be accomplished in a short enough time so that it does not interfere
with other users and can have minimal overhead. It usually doesn't matter
if the table is 10M rows or 100K rows. Your batch size should be the same.
What that size is can only be found with proper testing and adjustment.
Usually 10K is a good place to start as it is small enough to finish quickly
and large enough to nibble at the work load. IF you check the @.@.ROWCOUNT
and break when it is 0 then you don't have to worry about how many loops
there will be. Another comment is that there is no need to wrap the Update
in a Begin - Commit. Each update is ATOMIC all by itself. If you update
10K rows in a single update statement all 10K will either work or they wont.
There will not be any middle ground. By adding it in there you now have to
put error checking and handle possible open trans.
Andrew J. Kelly SQL MVP
"Raymond M via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:a911f5b29ab44d0b837051f840966d55@.SQ
webservertalk.com...
> /**
> l'm trying to improve my batch processing routines by
> creating a generic method which l can apply on most of
> my jobs.Reason Being l'm working with large Data Sets
> This is what l'm trying to achieve
> 1.Get The Rowcount of the Source
> 2.Split It Into manageable batches
> 3.Loop through the batches Until the last one is Done
> **/
> How do l intergrate these two so it can automatically pick up the
> batchsizes and process until the last batch is done.
> --Outer Loop
> Declare @.RowCount Int
> Declare @.LoopIteration Int
> Declare @.BatchSize Int
>
> --Get The RowCount Of the Table
> Select @.RowCount = (Select Count(*) From tbTest)
> --
> Select @.LoopIteration = @.RowCount/2000000
> Select @.BatchSize = @.RowCount/@.LoopIteration
>
>
> --Select Distinct Client,Convert(Varchar(15),'') As CltNo
> -- ,Convert(Int,0) As Status Into tbtest
> from tbAddress
> --Inner Loop --Works when run Manually
> SET ROWCOUNT @.BatchSize--100000 --54 Secs
> WHILE 1 = 1
> BEGIN
> Begin Transaction
> Update tbTest
> Set CltNo = Client + 1
> ,Status = 1 Where Status = 0
> Commit
> if @.@.rowcount = 0
> break
> END
> SET ROWCOUNT 0
> --Select * from tbTest Where Status = 1
> --
> Message posted via http://www.webservertalk.com

Batch Jobs ..

Hi Frndz,

Will anyone plz help me in creating simple jobs in Sql Server. And how to run that Job. Actually i Created a job in the AGENT and schedule it daily at 2.30 pm . But it doesn't run automatically . . .

Thnx in Advance . .

Regards

(M. Nedu . . .)Hi,

The simple way to schedule a package is to right click on the package and you will get an option for scheduling. Once you are through it, you would find your package under "Jobs", check for the option which states "Enabled" and it should be "True". Try to run your Job manually and see whether it's executing fine. If it executes fine, then I am sure that it would execute on scheduled time.

Thanks & Regards
Mudasar

Quote:

Originally Posted by nedu

Hi Frndz,

Will anyone plz help me in creating simple jobs in Sql Server. And how to run that Job. Actually i Created a job in the AGENT and schedule it daily at 2.30 pm . But it doesn't run automatically . . .

Thnx in Advance . .

Regards

(M. Nedu . . .)

Batch Jobs

Hi Folks,

I am running batch jobs(Nearly 50 queries) on Production server.It is taking nearly 5 or 6 hours(some times 10 hours also) to get the results.The problem is If I loose VPN connection I am not able to save data.I need to save the data upto the query executed before I loose VPN connection Please help me

You're not saying what those batchjobs are doing, but would it be possible to have each job or query save it's intermediate result in a table or as files..? That may be a way to 'catch up' if you loose connection.

It probably needs som adjusting in the jobs/queries also in order to keep track of done/left to do steps, but perhaps an idea?

/Kenneth

|||hey one suggestion i can give to speed up your batch process is to switch the recovery model of your database to Bulk-Logged. Do this only if you can afford to rerun ur bulk process in an event of failure. Also do a log backup before and after switching to/from Bulk-Logged mode|||

Thanks KeWin

|||

Thanks Mitesh

|||

Hi KeWin,

I am saving values in tables but I have one problem .

Example

I am running 30 queries as batch.I lost VPN connection at 15 query.Upto 14th query I have values in table.After establishing VPN connection I need start from 15 query.Could you please help me how to solve this problem.

|||

Well, your app need some way to know which queries are done, and where to start again if interrupted.

It probably don't need to be anything fancy, but I believe you're the one who can best decide how that should be done.
Your app also need the ability to be able to start at a given query, say the 15th..

I don't know anything more about how this is done from your side, so it's hard to come up with any particular suggestion.

/Kenneth

Batch Jobs

Is there a forum, documentation, link, or anything that talks about batch jobs, what they are, how to create them, run them, etc? Basically, everything I need to know about batch jobs?

thx,

Kat

What do you mean by batch jobs ? Do you mean Jobs that are executed by SQL Server Agent ? If so, I would recommend to get a book about SQL Server administration which dives deeper into the sense and the functionality of SQL Server Agent.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens,

I've been working with version 2000. Running a batch job to me is different for instance than running a proc (I think)... typically a batch job runs overnight because it takes a long time. I'm not sure what the definition of a batch job is. I believe it is a script that is run that takes a long time. Any ideas?

Kat

|||

Hi there - Whenever most DBAs talk about "batch" they usually just mean a set of SQL statements. If you save these in a file, they are also called a "script". If you run these scripts automatically, either using the SQL Server Agent (in what is called a "job") it's called a "batch job". To be even more confusing, some DBAs refer to a transaction (which is one statement alone or separated with a GO) to mean "batch".

Tell me what you're having issues with and I'll try to help.

Buck Woody

http://www.buckwoody.com

|||

Hi Buck,

This was just a question of definition and you explained it perfectly, thanks much!

Kat

|||If you don't need anything else on this thread, feel free to mark the answer as complete.

batch jobs

I'm looking for the definition of a batch job, what is it? How is it run? How does it differ from other jobs?

thx,

Kat

If i remember right

in query analyzer, a batch is a series of tsql statement

separated by keyword Go

select * from table1

select * from table 2

go

select * from table3

select * from table4

this query consist of two batch

|||

batch as compared to stored procedure

is characterized by many roundtrips to the server

as compared to one round trip in SP

SP is enjoy cached execution as compared to

several process of parsing, compiling,optimizing and executing

a batch process

Batch Jobs

Is there a forum, documentation, link, or anything that talks about batch jobs, what they are, how to create them, run them, etc? Basically, everything I need to know about batch jobs?

thx,

Kat

What do you mean by batch jobs ? Do you mean Jobs that are executed by SQL Server Agent ? If so, I would recommend to get a book about SQL Server administration which dives deeper into the sense and the functionality of SQL Server Agent.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens,

I've been working with version 2000. Running a batch job to me is different for instance than running a proc (I think)... typically a batch job runs overnight because it takes a long time. I'm not sure what the definition of a batch job is. I believe it is a script that is run that takes a long time. Any ideas?

Kat

|||

Hi there - Whenever most DBAs talk about "batch" they usually just mean a set of SQL statements. If you save these in a file, they are also called a "script". If you run these scripts automatically, either using the SQL Server Agent (in what is called a "job") it's called a "batch job". To be even more confusing, some DBAs refer to a transaction (which is one statement alone or separated with a GO) to mean "batch".

Tell me what you're having issues with and I'll try to help.

Buck Woody

http://www.buckwoody.com

|||

Hi Buck,

This was just a question of definition and you explained it perfectly, thanks much!

Kat

|||If you don't need anything else on this thread, feel free to mark the answer as complete.