Hello all,

I wasn't quite sure where to post this, but I thought I would try here. I do quite a bit of programming with VB.Net to work with SQL databases. I've noticed a performance issue across every application I have written. The very first UPDATE executed is painfully slow. Let me give you an example:

Application opens
On Open, I populate a datagrid with records from an SQL database. This actually runs pretty quick.
I select a record from the datagrid, change something about it, and execute an UPDATE SQL command on that record. It takes upwards of 30 seconds for the record to update.

Now, I select another record, do the same steps, and the record updates in less than 5 seconds. The faster performance happens until the application is closed and re-opened or until SQL is re-started. Then we're back to the first UPDATE slowness.

I've researched this on the web, and have found lots of people reporting the same issue, but haven't found a solution to it. I was wondering if anyone here has ran across this before.

Here's some statistical info for those that are interested:

Operating system: Windows XP Professional SP3
Pentium 4
3.06GHz
2 GB Ram
SQL 2005 Espress
Database size: 1GB
Record count: 355,000

Thanks for any thoughts on this.
Gregg

I haven't noticed this problem with ASQL, using SQL 2008 Express on my laptop. My sample table is quite small though, if that has anything to do with it. (I'm talking about the sqltest4 sample, which creates a table, adds about 9 records to it, then does a few updates and deletes on it.)

It might be interesting to try that test on your database. (It adds its own table to the database, so it wouldn't affect any of the existing data. Then again, maybe the issue is related to the table size, not the database size. Or maybe it's the .Net interface vs. the ODBC interface that ASQL uses with SQL Server.)

Pure speculation here, but it acts as though when you do your first UPDATE, it has read the table you're updating into some kind of cache memory and holds it there. That would explain why the second update goes so much faster. INSERTS into the table happen quickly, no matter what. I'm sure the number of records in the database would make a difference, especially if it is doing what I speculated above

It may also be dependent on your cursor options (concurrency and type), some combinations of which are clearly going to be more efficient than others.

I actually don't declare any cursors at all

I haven't looked at how the .Net interface to SQL Server works, but it seems that whether you declare a cursor type (forward-only, static, dynamic, keyset) and concurrency level (read-only, pessimistic, optimistic, etc.) or not, some kind of defaults must be in effect. There are always trade-offs - memory / concurrency / efficiency, so it might be worth investigating.

As a side issue, you may also want to review the pros and cons of doing all of your SQL operations in VB (external to the A-Shell app) or directly from within A-Shell.

Have you tried executing the update on the server side as a stored procedure? The query is already parsed, compiled and optimized so it should execute more quickly.

Tom,

No, I haven't tried that. I'll check into it and see if that improves anything. Thanks for the suggestion!