Batch applying large data diffs - SQLCMD slower than OSQL

mcdrewskimcdrewski Posts: 5
edited November 24, 2006 8:08PM in SQL Data Compare Previous Versions
Apologies for using the forum, this is only tangentially related to SQL Data compare, but I'm hoping someone out there has seen this problem.

I'm trying to track down why applying a largish Data Diff .SQL file executes in about 2min using OSQL and about 12min using SQLCMD on SQL2005.

To explain, internally we're using RedGate DB Diff and Data Diff tools to generate 'update' scripts to deploy data from our development databases. These are then checked into source code control and applied to 'live' databases using an internally developed tool. In the case of a 'full' deploy our data scripts end up being in the order of 100Mb or so in size (so quite small really). We (automagically) add 'GO' commands every 500 lines or so to make sure the batches are applied regularly.

I'm currently changing our patch scripts to use SQLCMD instead of OSQL (for a number of reasons not least being UTF8 data support), but have found that at the moment the same exact file is applied roughly six times slower with SQLCMD than using OSQL.

The commands I'm using are:

sqlcmd -o log.txt -S XX-SQL-DEV -U xx -P xx -d XX_DEV_XXXX -b -x -i "C:\xx\scripts\version_25\build_00_DATA.sql"

osql -o log.txt -S XX-SQL-DEV -U xx -P xx -n -d XX_DEV_XXXX -i "C:\xx\scripts\version_25\build_00_DATA.sql"

The sql file is 68.5 MB (almost 300,000 lines). The authentication is using SQL authentication rather than windows/trusted, and refers to a 'superuser' account who has all admin "uber" permissions. I have tried increasing the sqlcmd packet size (-a 16383) but have not seen any noticable difference at all. Note that I am running both commands on my workstation as the SQLServer console is not accessible.

I'm confused at why such an order-of-magnitude performance drop hasn't been noticed by anyone else... so I *must* be doing something wrong and am looking for ideas as to what?

Cheers

-Drew Mayo

ObDisclaimer : Cross posted from here - where nobody seems to know/care.
OBDisclaimer2 : We are currently using v3.3 of the redgate tools, but as the issue doesn't seem to be version specific and we are looking to upgrade to v5 in the next little while, I've posted this here. Apologies in advance if it's inappropriate.
· Share on Twitter

Comments

Sign In or Register to comment.