ADO .NET vs. SQL Server Management Studio – ADO performs worse – This article will take you through the common SQL errors that you might encounter while working with c#, sql, sql-server. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I’m running the same commands in ADO.NET C# and Sql Server Management studio. The SQL that runs via C# performs significantly worse – memory usage is worse (using up all available memory) and thus causing the database executing time to increase. The management studio isn’t perfect (it too causes sql server to use up memory) but it’s not as bad as via ADO.NET.
I am running: Windows 7, Sql Server 2008 R2, 10.50.1600. C# .NET 3.5. Sql Server management Studio 2008 R2. All programs and databases are on my local dev machine.
The SQL I am running is 40 create view’s and 40 create unique indexes on 2 database’s. I need to do this on the fly as we are running a database compare between 2 databases (for reasons that aren’t relevant we need to compare views and not tables). And since performance is an issue we cannot leave the views and indexes around all the time.
The SQL looks like this:
create view [dbo].[view_datacompare_2011106] with schemabinding as (
SELECT t.[ID], t.[Column1], t.[Column2], t.[Column3], FROM dbo.Table t WHERE t.[ID] in ('1','2','3','4') )
go
create unique clustered index [index_datacompare_2011106] on [dbo].[view_datacompare_2011106] (ID)
go
...
The only difference is that the C# code does not call Go. Each create cmd is wrapped up in a using statement and called via ExecuteNonQuery() e.g.
using (SqlCommand cmd = new SqlCommand(sql, this.connectionActualDb))
{
cmd.CommandTimeout = Int32.Parse(SqlResources.TimeoutSeconds);
cmd.ExecuteNonQuery();
}
P.S. SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views.
Solution :
Use Waits and Queues methodology to investigate the performance bottleneck. You’ll find the root cause and then we can advice accordingly. Most likely your C# application runs into concurrency due to locks, very likely held by the application itself. Typically one blames plan changes due to parameter sniffing, as in Slow in the Application, Fast in SSMS, but with DDL statements this is unlikely.
Why don’t you put all the commands into a single string separated by GO and send the one string to the database?
It’s called SQL Batching.
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.