Upgrading SQLTookit App - Performance issues

JoshCrosby

Hello,
I wrote/copied code from the old toolkit (I think 3/4 can't remember) and have never had a chance or reason to upgrade it because it worked great. The app is a console app, I schedule as a SQL Job to take a snapshot and save to a file share to be used just in case. Like I said, worked great and fast.

Problem: I'm upgrading to SQL 2005, and the app blows chunks. I know it is because of the old toolkit assemblies, so I am now using the new tookit and I'm finding major performance issues. This app took seconds to snapshot an entire database, and now it takes hours. Can you help me to see what is wrong with my code, or what needs to be updated?

       /// <summary>
        /// Public method to create a Red Gate SQL Compare file. This file can then be used as a compare file within the Red Gate's SQL Compare Tool.
        /// </summary>
		public void CreateSnapShot()
		{
			try 
			{
				// set the server name
				string serverName = (string) parameters["server"];
				// set the location
				string locationName = (string) parameters["saveto"];

				// put the databases into an array
				string[] databaseArray = Utils.GetDatabases(serverName, true,"", "", true);
				
				foreach (string db in databaseArray)
				{
					// call method to save the snapshot
					System.Console.WriteLine("{0} is being processed", db);	
					LoadAndSaveASnapshot(serverName,db,locationName);
				}

			}
			catch (Exception e)
			{
				System.Console.WriteLine("Error: {0}", e.ToString());
			}
		}


        /// <summary>
        /// Loads the and save a RedGate SQL Compare snapshot.
        /// </summary>
        /// <param name="serverName">Name of the server.</param>
        /// <param name="databaseName">Name of the database.</param>
        /// <param name="location">The file location.</param>
		private void LoadAndSaveASnapshot(string serverName, string databaseName, string location)
		{				
			// Set up the connection
			ConnectionProperties connectProperties = new ConnectionProperties(serverName, databaseName);
			// set integrated security
			connectProperties.IntegratedSecurity = true;

			//register a database using integrated security
			Database db1=new Database();
			
			// hook into an event handler to pass back information
			db1.Status += new StatusEventHandler(StatusCallback);

			// register the database
            db1.Register(connectProperties, Options.Default);// | Options.CaseSensitiveObjectDefinition);

			// build the location to save
			string pathToSnapshots = CreateDirectory(serverName, databaseName, location);

			// build the filename
			StringBuilder sb = new StringBuilder();
			sb.Append(pathToSnapshots);
			sb.Append(@"\Snap_");
			sb.Append(databaseName);
			sb.Append("_");
			sb.Append(DateTime.Now.ToString("MM-dd-yyyy"));
			sb.Append(".snp");
 
			//save the database to disk
			db1.SaveToDisk(sb.ToString());

			//dispose of the objects
			db1.Dispose();
			
		}

I realize that Utils.GetDatabases should be replaced by Shared.SQLServers but this code can't be found, see an earlier post in the forum about that issue. It seems to really take the most time at

            db1.Register(connectProperties, Options.Default);// | Options.CaseSensitiveObjectDefinition);

Brian Donahue

Hi Josh,

There isn't a tremendous performance difference between versions 3 and 5. This must be down to the database being registered.

It could also be that you have attached a debugger to your application, and that may be spending oodles of time examining handled exceptions. db.Register takes up to 10 times longer to run when you are debugging it!

JoshCrosby

I will give it shot and let you know how it goes. Thanks for the response.

JoshCrosby

Major overhead in debugging. Thanks for the help

Brian Donahue

My pleasure!