I have a table of around 15 columns, with a few hundred records, potentially going to thousands. I present the user with a "search" form, so that they can select a record to work with. Using a ReadOnlyListBase/ReadOnlyBase combination, I populate a BindingSource, and bind that to a listbox.
The problem I have is this: it takes around 15 seconds for the ReadOnlyListBase to populate itself with the 300 or so records? Performing a direct select on the table in SQL Management Studio of course takes milliseconds. Here is a snippet of the ROB:
internal TableInfo(WMSSafeDataReader dr) { Fetch(dr); } #endregion #region Data Access private void Fetch(WMSSafeDataReader dr) { this.Id = dr.GetInt32("ID"); this.Code = dr.GetString("CODE"); this.Description = dr.GetString("DESCRIPTION"); }
and the ROLB:
private void DataPortal_Fetch(Criteria criteria) { RaiseListChangedEvents = false; using (var ctx = ConnectionManager.GetManager(connectionString, false)) { using (IDbCommand cm = ctx.Connection.CreateCommand()) { cm.CommandText = "SELECT ID, CODE, DESCRIPTION FROM TABLE "; if (criteria.ThisId != 0) { cm.CommandText += " WHERE "; cm.CommandText += " THIS_ID = @THIS_ID "; cm.Parameters.Add(CreateParm("THIS_ID", criteria.ThisId)); } using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader())) { IsReadOnly = false; while (dr.Read()) { Add(new TableInfo(dr)); } IsReadOnly = true; } } } RaiseListChangedEvents = true; }
Is there anything I can do differently to speed this up? 5 seconds per 100 records in absolutely unacceptable?! Thanks.
When you put a breakpoint in just before the select statement is sent to the db, how long does it take when you press f10 and run that line of code?
I ask because comparing your code to the time it takes in sql server management studio is a first step at best in getting to the bottom of it. All it proves is how fast the query *could* run, not actually how fast it's running in your code.
You're not necessarily comparing oranges to oranges that way.
You need to see first off if there is a delay of that length in your code where it executes the statement.
Don't assume any delay is where you *think* it is, prove it first.
(What is firstexpr there for, it's unclear to me and looks completely redundant?)
When you put a breakpoint in just before the select statement is sent to the db, how long does it take when you press f10 and run that line of code?
The data is retrieved very quickly from the database. The delay is inside "while (dr.Read())", namely in the call to Add().
(What is firstexpr there for, it's unclear to me and looks completely redundant?)
I just forgot to edit that out of the code snippet while anonymizing it. Also, the ID is needed, as this is a list of base objects. We want the list to "find" the ID we wish to view/edit.
Also isn't fetching the ID redundant since it's a known parameter in advance of the query executing?
private void Fetch(WMSSafeDataReader dr) { this.Id = dr.GetInt32("ID"); this.Code = dr.GetString("CODE"); this.Description = dr.GetString("DESCRIPTION"); }
Assuming that you are using managed properties, using one of the below methods should help
The constant checking of if a user can write to a property can slow things done in a list significantly, especially when loading.
Assuming that you are using managed properties, using one of the below methods should help
The constant checking of if a user can write to a property can slow things done in a list significantly, especially when loading.
Here is how the property declaration is done:
public int Id { get; private set; }
I'll try your suggestion, and see if it helps.
Well, unfortunately nothing I try speeds things up any. I experimented trying your suggestion to use managed fields and LoadProperty. I then tried passing in an array of object instead of SafeDataReader, to see if perhaps that was a cause of the slowdown (which didn't make sense anyway, as it should just be in the same server memory space).
Another interesting point here, is that when this is processing, my CPU is pinned the whole time. At least one core is, anyway. Why on earth would it need so much processing power to do this? In the time it would take this to process 72000 records, I could encode a bloody H.264 movie.
Are you using an application server?
I can assure you that that ROL's are not normally this slow, we have several lists that load a thousand or so entries in the time it is taking to load 300 for you. We will get to the bottom your problem, with enough time and patience :)
Run your code with a CLR Profiler. This will pinpont more exectly which lines are consuming so much time.
VS2010 Premium/Ultimate has a builtin profiler or you can use the DotTrace og Ants profiler trial version to test it.
Thanks for your help guys. I'll try the profiler, and see what it yields.
Thanks @Johnny, score! I just found a HUGE performance issue with my custom database access code. Thank you!
Copyright (c) Marimer LLC