How fast is osql ?

Question

How fast is osql ?

Peter Teunissen

Grasshopper

Points: 12
More actions
March 4, 2006 at 11:52 am

#167258

Here's my situation: my DB has about 4000 stored procedures. The source of each SP is in one file (so there are 4000 little .sql files). During the DB creation we run a batch file with 4000 osql calls and capture the result of each call to see if we had any problems.
Now here's the first question. Would it be much faster (I mean MUCH faster) to concatenate all these files into one .sql file and do one osql call? Does the invocation of each osql realy have that much overhead? I asume it does a DB logon for each call, so you would be gaining there. If I do one large osql call I do loose some traceability of which SP creation would fail.
Here's another thought: could I write some code using ODBC/ADO/??? to start a connection, and run each script over the same connection? I tried this a little, but the syntax of our .sql files has logic in it to see of the SP exists, if so drop it, and re-create it. It seems ADO does not like the T-SQL syntax? Is there a programmatic interface to osql? Any other suggestions to make this fast and still preserve traceability?
Thanks,
-Peter

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 1

Yes, it would be faster to use a single file... but, then you'd also loose the granularity you need to check the creation of each stored procedure for errors...

I'm not sure why you are doing it this way, though... why not use MDF/LDF file from a "golden" database and just attach it? Then, there's no chance for error so you won't need to check for error and you won't need to use OSQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Peter Teunissen Grasshopper Points: 12 More actions · Answer 2

Interesting thought! But how would this work for upgrades? Can you detach the 'old' SPs and attach the new SPs?

SteveHall SSChasing Mays Points: 633 More actions · Answer 3

If you want to update an SQL source the best way is to drop it first and then re-insert it. The easiest way to do this is to include the following code at the start of each SP:

IF EXISTS (select * from sysobjects where id = object_id('sp-name') and sysstat & 0xf = 4)

drop procedure sp_name

GO

Then continue with the SP code:

CREATE PROC sp_name...

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 4

No. It wouldn't work for upgrades because the whole database is changed... and that brings up another point... if your "harmless, transparent upgrade" fails for some reason, where does that leave the user? Are you renaming the old procs as originalprocname_OLD or something?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)