0

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.

Adam A
  • 413
  • 7
  • 14
  • Possible duplicate: http://stackoverflow.com/questions/9974/query-times-out-from-web-app-but-runs-fine-from-management-studio or http://stackoverflow.com/questions/2465887/why-would-set-arithabort-on-dramatically-speed-up-a-query or http://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-stu – Aaron Bertrand Sep 01 '11 at 16:03
  • 1
    Have you compared the execution plans? – Justin Sep 01 '11 at 16:20
  • Might be worth pointing out it's not an ADO problem. If you trace your query from .net, you'll see it's executed inside a call to sp_executesql. If you take this query and run it in SSMS, it's just as slow as running from ADO. – Daz Sep 24 '14 at 15:06

2 Answers2

0

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
-1

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.

tsells
  • 2,751
  • 1
  • 18
  • 20
  • -1 Because a batch cannot contains GO. GO is a batch *delimiter* and is not part of the T-SQL grammar. You could use somehting like http://code.google.com/p/dbutilsqlcmd/ if you want to run entire .sql files and separate the batches before issuing the command, but SqlCommand will not understand GO and will send it to the server, resulting in error. – Remus Rusanu Sep 01 '11 at 19:45
  • I disagree. We do it all the time. We generate multiple SQL statements into a single string and then send that string as the command text of the sql command being executed against the database. – tsells Sep 01 '11 at 20:48
  • Separated by GO? I know for a fact that will trigger error when parsed on the server, so you must be doing something else. – Remus Rusanu Sep 01 '11 at 21:07
  • Yes. We have our customized DAL setup to do just this. We pass multiple statements this way. We do this for multi inserts, deletes, etc. You don't need all of them inside a single "batch". Each statement is autonomous from the next - they just all need to be executed. – tsells Sep 02 '11 at 00:26