5

I'm using SMO to execute a batch SQL script. In Management Studio, the script executes in about 2 seconds. With the following code, it takes about 15 seconds. 

var connectionString = GetConnectionString();
// need to use master because the DB in the connection string no longer exists  
// because we dropped it already
var builder = new SqlConnectionStringBuilder(connectionString) 
{ 
    InitialCatalog = "master" 
};

using (var sqlConnection = new SqlConnection(builder.ToString()))
{
    var serverConnection = new ServerConnection(sqlConnection);
    var server = new Server(serverConnection);

    // hangs here for about 12 -15 seconds
    server.ConnectionContext.ExecuteNonQuery(sql);  
}

The script creates a new database and inserts a few thousand rows across a few tables. The resulting DB size is about 5MB.

Anyone have any experience with this or have a suggestion on why this might be running so slowly with SMO?

gunr2171
  • 16,104
  • 25
  • 61
  • 88
Jeff
  • 35,755
  • 15
  • 108
  • 220
  • I would say this isn't something to worry about, 15 seconds to create a database seems reasonable. Besides when you execute the query in SSMS are you already connected to the server? Different client machine? – MrEdmundo Nov 10 '10 at 08:55
  • Same client machine. Opening the connection doesn't seem to take so long, just the ExecuteNonQuery call. The concern is that this step is part of the AssemblyInitialize method for our Unit Tests, so having it take an extra 15 seconds just to start up the unit tests is a real annoyance. – Jeff Nov 10 '10 at 15:38
  • Anyone? I'd really like to use these scripts for our unit testing. – Jeff Nov 22 '10 at 20:38

2 Answers2

4

SMO does lots of weird .. stuff in the background, which is a price you pay for ability to treat server/database objects in an object-oriented way.
Since you're not using the OO capabilites of SMO, why don't you just ignore SMO completely and simply run the script through normal ADO?

Pavel Urbančík
  • 1,466
  • 9
  • 6
  • Would certainly do that, but bear in mind this is a SQL Management Studio generated data script (ie. multiple GO statements). Is there a way I can run the script with normal ADO? – Jeff Nov 28 '10 at 22:25
  • Yes, simply replace all "GO" with ";" and you're good to go :) – Pavel Urbančík Nov 29 '10 at 18:39
  • 3
    I normally split the string using "GO" and execute each substring as sql. – Keith John Hutchison Nov 29 '10 at 20:12
  • Nice, simple answer. Good stuff. – Jeff Nov 29 '10 at 20:37
  • I figured doing it in batch would have a performance IMPROVEMENT...go figure. – Jeff Nov 29 '10 at 20:45
  • Following @csmu suggestion, in normal conditions, I think something like `Regex.Split(text, @"(?mi)^\s*(?:GO|;)\s*$")` should suffice. This assumes `GO` or `;` are not within comment block, and are in their own lines - yeah, not very smart. – jweyrich May 29 '15 at 20:57
0

The best and fastest way to upload records into a database is through SqlBulkCopy.
Particularly when your scripts are ~1000 records plus - this will make a significant speed improvement.
You will need to do a little work to get your data into a DataSet, but this can easily be done using the DataSet xml functions.

blorkfish
  • 21,800
  • 4
  • 33
  • 24
  • I would definitely use bulk copy, but these are scripts generated in Management Studio via the Generate Scripts functionality. Is there a way I could still use these scripts with bulky copy? – Jeff Nov 12 '10 at 19:55
  • Unfortunately not. SQLBulkCopy requires a dataset as input. – blorkfish Nov 15 '10 at 03:40