OSQL extremely slow comparing to Query Analyzer?
-
Hello,
I have a 16Mb sql script which consists of a set of basic deletes and inserts. When I run it using Query Analyzer it takes about 4mins. When I run it with OSQL it takes 50 mins! I thought both were just connecting through odbc and I have absolutely no explanation as to what could be going on.
For info:
The server is running on SQL Server 2000 Entreprise SP3
The OSQL call is:
osql -b -n -S SERVERNAME -d DBNAME -E -q -i D:\generated_script.sql
I tried putting SET NOCOUNT ON to see if the problem comes from prints but it doesn't seem to change anything (now there are only 2 prints in the whole file).
Any ideas would be greatly appreciated !
Thanks
-
IS your script bringing out huge amount of data. If this is possible i too have experienced such a situation with huge amount of data preffered to use QA than osql then.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Well I don't know if 16Mb is huge but the problem is that I cannot use QA because it is an automated task which runs in command line. As far as I know osql is the only command line interface and I can't call it in text mode?
thx
-
I guess the way I'd do this is to send the inserts to one file and the deletes to another instead of having a process generate individual inserts/deletes as a script. Then, then I'd have a stored procedure bulk insert both files into a staging table and do the inserts/deletes from there.
Dunno why you have the time difference between QA and OSQL... I've not run into that problem, yet, probably because I don't use the "generated script" method that you do.
--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) -
I tried testing with a JDBC connection and the things seem to run faster.
Jeff, if I understood well you generate scripts with bits that are stored in a working table. I cannot do that (are you sure generating scripts on the fly from a table is faster ?) because using dynamic sql requires more rights than I can give to the user.
I haven't finished testing yet but Im pretty confused by the fact that JDBC performs faster than ODBC...
-
Hi,
You can refer to the following link, which will provide some ideas about ODBC.
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/diag/part2/75528c10.mspx?mfr=true
http://support.microsoft.com/kb/135533
Regards...Vidhya Sagar
Regards..Vidhya Sagar
SQL-Articles -
Neither OSQL nor Query Analyzer use ODBC, they use the native SQL Library. (Yes, the SQL Server ODBC library also uses the native SQL library, but the difference is that OSQL and QA are not going through the overhead of using ODBC.)
I believe the difference is attributable to the way that OSQL and QA use transactions. Depending on how you are formatting you SQL, you may be running your QA queries in separate transactions, which keeps the logs smaller and more efficient. The OSQL method may be running the entire 16mb batch as one transaction, logging the transactions, applying the updates, then updating the log with the results. Running thousands of insert and delete statements in a single batch will cause slower performance as the log file expands continually and the in-process transactions cause tempdb to grow. If you can, try to wrap the statements in transaction batches of smaller size, say 1000 or so, to relieve this log and tempdb growth.
There is another option other than Osql. Use vbscript and write the statements using SQL-DMO. Under the covers, both Osql and QA (as well as Enterprise Manager) are using SQL-DMO objects. It would not be difficult to write a vbscript to read your text file of TSQL statements and process them. The script file would be constant each time, and you can have your TSQL statement file recreated as needed, and your script program can handle the transactions and error handling much better. I have used this technique, especially for deploying database updates to multiple customer databases for our application. Lookup SQL-DMO in BOL for reference and example code.
Hope this helps.
Mark -
Thank you very much for your replies.
Vidhya: I will have a look at the articles you sent me
Mark: I hoped that putting a "GO" after each statement would make it commit but i was maybe wrong (i dont use any explicit transactions). I'll try the vbs, was considering getting into some scripting for a while now and I guess it's time
Pavle
-
I hoped that putting a "GO" after each statement would make it commit...
I thought you might have, but was not sure if that would be the difference. I thought it should have also, but based on your timings, it either does not recognize the GO (it is on a line by itself, right?), or does not work as we expected, or possibly for some reason, there is an implied "outer batch" that encompasses the entire batch file.
Mark -
Wasn't generating scripts on the fly... thought you were. Was trying to get around doing that. So, I'm confused as to what you are trying to do... thought you had some data you either wanted to insert or delete.
--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) -
Hi, I have exactly the same problem with OSQL.
Does anybody has a solution
-
kubilay husmenoglu (7/31/2008)
Hi, I have exactly the same problem with OSQL.Does anybody has a solution
I'd recommend you start a new thread for this... it'll get more attention.
--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)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply