By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,888 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

ADO.NET performance expectations?

P: n/a
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
Jul 21 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Jul 21 '05 #2

P: n/a

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


Jul 21 '05 #3

P: n/a
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


Jul 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.