Hi

I've been having a range of performance problems and would really like to get to the bottom of them.

I was initially having timeouts and deadlocks because everything I did was within a transaction. So, following advice I took out the transactions (which has cured all deadlocks and timeouts). I have also made some other changes following other advice which has helped a lot. But I am now left with performance problems...

I ran this test yesterday and noticed that LLBL performed worse than using .Net Sql code. Also, I noticed that using transactions dramatically improves performance so I am wondering if the advice was wrong! Creating a transaction around this code reduces the time it takes by at least half.

So the code is as follows:

**** LLBL Code. Takes 4 to 7 seconds to execute:

DataAccessAdapter adapter = new DataAccessAdapter("Data Source=.;Initial Catalog=ImlHostDBElection;Integrated Security=True"); adapter.OpenConnection(); adapter.StartTransaction(IsolationLevel.ReadCommitted, "A"); IdentityEntity ident;

for (i = 0; i < 5000; i++) { ident = new IdentityEntity(); ident.EffectiveFrom = DateTime.Now; ident.IdentityTypeId = "HANDSET"; ident.Identity = "0000000000"; // NOTE: I have tried adding to EntityCollection and saving collection, but no difference adapter.SaveEntity(ident, false); } adapter.CloseConnection();

**** .Net SQL Code: Takes 1 to 2 second to execute:

IDbConnection conn = new SqlConnection("Data Source=.;Initial Catalog=ImlHostDBElection;Integrated Security=True"); conn.Open(); IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = "insert into base.[identity] ([identity], [identitytypeid], isactive) " + " values (@Identity, @IdentityTypeId, 1)";

IDbTransaction trans = conn.BeginTransaction(); cmd.Transaction = trans;

IDataParameter param = cmd.CreateParameter(); param.ParameterName = "@Identity"; param.SourceColumn = "Identity"; param.DbType = DbType.String; cmd.Parameters.Add(param);

param = cmd.CreateParameter(); param.ParameterName = "@IdentityTypeId"; param.SourceColumn = "IdentityTypeId"; param.DbType = DbType.String; cmd.Parameters.Add(param);

param = cmd.CreateParameter(); param.ParameterName = "@EffectiveFrom"; param.SourceColumn = "EffectiveFrom"; param.DbType = DbType.DateTime; cmd.Parameters.Add(param);

for (i = 0; i < 5000; i++) { ((IDataParameter)cmd.Parameters["@IdentityTypeId"]).Value = "HANDSET"; ((IDataParameter)cmd.Parameters["@EffectiveFrom"]).Value = DateTime.Now; ((IDataParameter)cmd.Parameters["@Identity"]).Value = "0000000000"; cmd.ExecuteNonQuery(); }

trans.Commit();

So... what should I do? I guess I need to put a transaction around this (as in the example) for performance problems, but as soon as I do this I get other problems. Am I using LLBL correctly? I think much of my trouble is that lots of the data that comes in to the system is in batches (it is responses from handsets). The system will be idle for much of the time, then suddenly I will get a huge batch of data (up to 20000 rows to be inserted), and sometimes I will only get 1 or 2 rows of data (so it is quite unpredictable). It is also being performed in a thread since I need to continuously monitor what is going on and immediately update the DB. I think the timeouts occur when I get a batch of data to be processed and other threads are trying to run queries.

Any ideas on what I can do to get the best performance and avoid timeouts, etc?

buckbuchanan wrote:

Hi

I've been having a range of performance problems and would really like to get to the bottom of them.

What's the llblgen pro version you're using, and runtime build nr (see guidelines of this forum)

I was initially having timeouts and deadlocks because everything I did was within a transaction. So, following advice I took out the transactions (which has cured all deadlocks and timeouts). I have also made some other changes following other advice which has helped a lot. But I am now left with performance problems...

I ran this test yesterday and noticed that LLBL performed worse than using .Net Sql code. Also, I noticed that using transactions dramatically improves performance so I am wondering if the advice was wrong! Creating a transaction around this code reduces the time it takes by at least half.

The thing with transactions is: it could speed up performance, but it also could degrade performance. Let's look at your code.

So the code is as follows:

**** LLBL Code. Takes 4 to 7 seconds to execute:

DataAccessAdapter adapter = new DataAccessAdapter("Data Source=.;Initial Catalog=ImlHostDBElection;Integrated Security=True"); adapter.OpenConnection(); adapter.StartTransaction(IsolationLevel.ReadCommitted, "A"); IdentityEntity ident;

for (i = 0; i < 5000; i++) { ident = new IdentityEntity(); ident.EffectiveFrom = DateTime.Now; ident.IdentityTypeId = "HANDSET"; ident.Identity = "0000000000"; // NOTE: I have tried adding to EntityCollection and saving collection, but no difference adapter.SaveEntity(ident, false); } adapter.CloseConnection();

As you don't specify 'false' for recurse, it will default to 'true' for recurse and will recurse over the graph for every entity. This costs performance as you're doing it per entity. Specify 'false' for recurse will solve this.

Saving entities in bulk can be done best in a collection. This will produce the save queue once and will traverse the graphs once.

**** .Net SQL Code: Takes 1 to 2 second to execute:

  IDbConnection conn = new SqlConnection("Data Source=.;Initial Catalog=ImlHostDBElection;Integrated Security=True");
  conn.Open();
  IDbCommand cmd = conn.CreateCommand();
  cmd.CommandText = "insert into base.[identity] ([identity], [identitytypeid], isactive) " +
                " values (@Identity, @IdentityTypeId, 1)";

  IDbTransaction trans = conn.BeginTransaction();
  cmd.Transaction = trans;

  IDataParameter param = cmd.CreateParameter();
  param.ParameterName = "@Identity";
  param.SourceColumn = "Identity";
  param.DbType = DbType.String;
  cmd.Parameters.Add(param);

  param = cmd.CreateParameter();
  param.ParameterName = "@IdentityTypeId";
  param.SourceColumn = "IdentityTypeId";
  param.DbType = DbType.String;
  cmd.Parameters.Add(param);

  param = cmd.CreateParameter();
  param.ParameterName = "@EffectiveFrom";
  param.SourceColumn = "EffectiveFrom";
  param.DbType = DbType.DateTime;
  cmd.Parameters.Add(param);

  for (i = 0; i < 5000; i++)
  {
    ((IDataParameter)cmd.Parameters["@IdentityTypeId"]).Value = "HANDSET";
    ((IDataParameter)cmd.Parameters["@EffectiveFrom"]).Value = DateTime.Now;
    ((IDataParameter)cmd.Parameters["@Identity"]).Value = "0000000000";
    cmd.ExecuteNonQuery();
  }

  trans.Commit();

Did the transaction log increase during the llblgen pro code run? The code isn't equal to the LLBLGen pro code btw. All you do here is changing parameters and save, but that's way less than what's done with the entities.

So... what should I do? I guess I need to put a transaction around this (as in the example) for performance problems, but as soon as I do this I get other problems. Am I using LLBL correctly? I think much of my trouble is that lots of the data that comes in to the system is in batches (it is responses from handsets). The system will be idle for much of the time, then suddenly I will get a huge batch of data (up to 20000 rows to be inserted), and sometimes I will only get 1 or 2 rows of data (so it is quite unpredictable). It is also being performed in a thread since I need to continuously monitor what is going on and immediately update the DB. I think the timeouts occur when I get a batch of data to be processed and other threads are trying to run queries.

Any ideas on what I can do to get the best performance and avoid timeouts, etc?

If you want to insert bulks of data, the best is to use bulk insert of sqlserver. That's way faster than any sql solution.

nevertheless, using non-recursive saves in this will speed up performance.

The transaction around the insert will block readers on the same table. If I recall correctly, you were using a polling mechanism on a table?

Thanks for the reply Otis.....

Otis wrote:

What's the llblgen pro version you're using, and runtime build nr (see guidelines of this forum)

v 2.0.0 final 04/09/2006

The thing with transactions is: it could speed up performance, but it also could degrade performance. Let's look at your code.

Ok... fair enough!

As you don't specify 'false' for recurse, it will default to 'true' for recurse and will recurse over the graph for every entity. This costs performance as you're doing it per entity. Specify 'false' for recurse will solve this.

I have just tried that but didn't make a difference in this case.

Saving entities in bulk can be done best in a collection. This will produce the save queue once and will traverse the graphs once.

Yes.... this is definately an improvement, but still only half the speed of sql.

Did the transaction log increase during the llblgen pro code run?

Don't know... how do I check this?

The code isn't equal to the LLBLGen pro code btw. All you do here is changing parameters and save, but that's way less than what's done with the entities.

Yes... I am guessing that this is the price to pay for the convenience of LLBL. I just think that for the few places I have batching like this I need a different strategy. I hoped you might notice something that I was doing or not doing which is very obviously wrong and would boost the performance.

If you want to insert bulks of data, the best is to use bulk insert of sqlserver. That's way faster than any sql solution.

Am I right in thinking that this involves making a file to bulk load? If so not sure this is an option as I might not have access to the DB server (i'm possibly miles off track so please correct me if so!).

The transaction around the insert will block readers on the same table. If I recall correctly, you were using a polling mechanism on a table?

Yes... this was the problem. I am continuously monitoring several tables to notify clients of changes.

buckbuchanan wrote:

Otis wrote:

What's the llblgen pro version you're using, and runtime build nr (see guidelines of this forum)

v 2.0.0 final 04/09/2006

Ok. There are newer versions, though for this particular case it's not that important.

As you don't specify 'false' for recurse, it will default to 'true' for recurse and will recurse over the graph for every entity. This costs performance as you're doing it per entity. Specify 'false' for recurse will solve this.

I have just tried that but didn't make a difference in this case.

Hmm, that's odd.

Saving entities in bulk can be done best in a collection. This will produce the save queue once and will traverse the graphs once.

Yes.... this is definately an improvement, but still only half the speed of sql.

True, the pipeline to follow is much longer than with a loop which just changes parameters. We profiled the living daylights out of the api, so there's not much room left for improvement unfortunately, allthough v2.5 is faster than v2.0. But not that much faster that it will become close to the raw sql code you posted. Btw, if you call 'Prepare()' once on that loop (the sql code) it will be even faster.

Did the transaction log increase during the llblgen pro code run?

Don't know... how do I check this?

In sqlserver enterprise manager/management studio. The files of the database / catalog should have enough room. If they're near capacity, inserting data will cause the db to increase the size which is very slow compared to the insert itself.

The code isn't equal to the LLBLGen pro code btw. All you do here is changing parameters and save, but that's way less than what's done with the entities.

Yes... I am guessing that this is the price to pay for the convenience of LLBL. I just think that for the few places I have batching like this I need a different strategy. I hoped you might notice something that I was doing or not doing which is very obviously wrong and would boost the performance.

Only the recursive switch which is still switched on. So if you change it to false, it will be faster, but not twice as fast, the graph sorter is pretty quick.

If you want to insert bulks of data, the best is to use bulk insert of sqlserver. That's way faster than any sql solution.

Am I right in thinking that this involves making a file to bulk load? If so not sure this is an option as I might not have access to the DB server (i'm possibly miles off track so please correct me if so!).

Sqlserver has a couple of ways to do bulk inserts You have the BULKINSERT comand in T-SQL. This indeed requires reading from a file. you can also use the command line tool 'bcp'. And there's a third, the SqlBulkCopy class . that last one is I think the best option here. You can prepare a datatable to write to the DB, and then call SqlBulkCopy.WriteToServer() to write the table's contents to the server using bulkinsert. This is very fast as it bypasses the SQL engine, so it will take only a small period of time.

It's .NET 2.0+, check System.Data.SqlClient.SqlBulkCopy in the MSDN for details.

Thanks for the advice! I'll have a go at the bulk copy thing...