VBScript running from command line

Question

Post reply

VBScript running from command line

Steve DiDomenico

Ten Centuries

Points: 1138
More actions
June 10, 2003 at 2:14 pm

#82126

Is VBScript executed from cscript or wscript worth using for end-of-day database processing? Things like processing a RecordSet of 100,000 records is taking 24 hours to complete. I know performance is a tricky issue and more information would be needed, but does Windows command-line scripting generally run slow?
Thanks in advance for your time

Viewing 8 posts - 1 through 7 (of 7 total)

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

mworku Ten Centuries Points: 1311 More actions · Answer 1

You would see a performance gain if you put your data processing logic

in a stored proc. After the initial execution the Stored proc. is cached and SQL server

uses a pre-compiled or existing execution plan whenver the proc is executed. This saves

time b/c vb script has to be interpreted by the vb script engine and each sql statement

batch is compiled and optimized before execution.

I don't know the kind of processing you're doing but 24 hrs for

100,000 records is extremely slow. You may need indexes or revise logic (just a suggestion don't take it personal) to boost performance.

MW

Steve DiDomenico Ten Centuries Points: 1138 More actions · Answer 2

Thanks MW.

I'm using stored procedures and the logic looks simple enough. Maybe 24 hours is as good as it gets.

Steve DiDomenico Ten Centuries Points: 1138 More actions · Answer 3

What a difference an index will make. Using a suggestion from a DBA, I added one

non-clustered index to one table and the VBScript processed a Recordset of 97,454 records in 26:47. Before the index was added, the estimated completion time for this Recordset would have been about

26 hours!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi sdidomenico,

quote:
non-clustered index to one table and the VBScript processed a Recordset of 97,454 records in 26:47. Before the index was added, the estimated completion time for this Recordset would have been about
26 hours!

excuse me, if I hook right in here, but how many thousands operations are you performing that your script runs about half an hour (although much better than running 24 hours)?

Maybe it will be helpful if you post what you want to do?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Steve DiDomenico Ten Centuries Points: 1138 More actions · Answer 5

Hi Frank, Please feel free to hook into any of my posts. I'm a developer with 20 years experience, but only 6 months of experience of SQL Server/ASP/ADO/VBScript/HTML and all that jazz. I'm a recovering Mainframe COBOL Flat File programmer.

Our application is for Fund Raising via Direct Mailing.

We are using VBScript from the NT command-line to run nightly scheduled processing. The script in question here is to select our most current donors, calculate and average donation amount, create a .csv file of names and addresses, and insert a contact record for each donor selected into the database. The Server is NT 4.0 SP6a, SQL Server 7.0 SP3, PIII 1 Ghz Server class machine with IDE Drives, no RAID.

If you need some real detail, I can email you the command-line VBScript.

For me, less than 30 minutes is more than acceptable.

Thanks for your interest.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hi sdidomenico,

quote:
Hi Frank, Please feel free to hook into any of my posts. I'm a developer with 20 years experience, but only 6 months of experience of SQL Server/ASP/ADO/VBScript/HTML and all that jazz. I'm a recovering Mainframe COBOL Flat File programmer.

hmm,... an app that needs 30 minutes to run on a mainframe could ce consider a very expensive one!

quote:
For me, less than 30 minutes is more than acceptable.

..and what could be better.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 7

quote:
Thanks MW.
I'm using stored procedures and the logic looks simple enough. Maybe 24 hours is as good as it gets.

100,000 rows is not a lot. And to comment on if 24hours is resonable I would say no. However, crcumstances with what you are doing and your specific data may mean otherwise. If you could post the code and a sampling of the data (can be altered) along with an idea of the expected results someone will generally take a stab at what may be done to speed things up.