ADO.NET performance expectations?

  • Thread starter Thread starter John Spiegel
  • Start date Start date
J

John Spiegel

Hey all,

I'm working on a winform that does an update on a local VFP database using
OleDb. Doing this natively in VFP is about two seconds, while doing so in a
..NET form takes 25 seconds. Any idea where I can get some speedup?

cmdPrepaidCard.CommandText =
"UPDATE PrepaidBatch SET TransactionKey = 100 " +
"WHERE EMPTY(Created) AND TransactionKey = 0 AND " +
"BatchType = 'I' AND Sequence - 5 < " +
"(SELECT MIN( Sequence ) " +
"FROM PrepaidBatch " +
"WHERE EMPTY( Created ) AND BatchType = 'I' " +
"AND TransactionKey = 0 )";

TIA,

John
 
Follow-up: I knew that it was faster to use a stored proecedure, but not
this different! I changed the code to call a stored procedure and it now
runs in about two seconds. Should it normally save 22 seconds in such a
case?
 
Check the query plan in SQL and look at the time difference, I am sure your
parsing and generating the query plan for each execution would be slow but I
agree not a performance difference like that. Also you should use the query
hint NOLOCK on your subquery to avoid locking.

Happy holidays
 
Thanks for jumping in. This one is a VFP database not MSSQL. There's no
execution plan to check nor query hint in this environment.
 
Back
Top