ADO.NET performance expectations?

[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

[John Spiegel]

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?

"John Spiegel" <js******@YETANOTHERSPAMHATERc-comld.com> wrote in message
news:eu**************@TK2MSFTNGP12.phx.gbl...

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

[BeanTownBizTalkGuru]

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

"John Spiegel" wrote:

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?

"John Spiegel" <js******@YETANOTHERSPAMHATERc-comld.com> wrote in message
news:eu**************@TK2MSFTNGP12.phx.gbl...

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

[John Spiegel]

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.
"BeanTownBizTalkGuru" <Be*****************@discussions.microsoft.com> wrote
in message news:50**********************************@microsof t.com...

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

"John Spiegel" wrote:

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?

"John Spiegel" <js******@YETANOTHERSPAMHATERc-comld.com> wrote in message news:eu**************@TK2MSFTNGP12.phx.gbl...

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