G
Guest
I have a question to which I hope someone has the answer. We have developed
an application that queries a database a great deal and speed is crucial. We
do all database operations utilizing stored procedures and return the
information in parameters since there would be only one row anyway.
We have noticed some interesting timing that is causing us problems in the
overlying process this applciation supports. By using Windows high
performance timers to measure the performance we have learned that sometimes
we'll have long delays (When I say long delay I mean between 150 to 600 mS.)
waiting for the ExecuteNonQuery to return. Other times (a little more rare)
we would have a long delay getting the parameters back after the
ExecuteNonQuery returned. Of course we try to retrieve the SQLParameters as
soon as the ExecuteNonQuery returns. That delay is measured from the time
the ExecuteNonQuery returns and the first SQLParameter is read out of ADO.
The time needs to stay below 100 mS. The delay doesn't happen very often
about once in every 100 or so attempts.
We are putting performance timers in SQL Server to determine the times the
stored procedures uses for processing and examining when the server
recompiles its stored procedures. So now for the question.
We are concerned with the delay ADO is giving us when we attempt to call the
SQLParameters collection. Do the parameters return to ADO on a background
thread that is spawned when the ExecuteNonQuery returns complete from the
stored procedure? If not does anyone know what might account for these
infrequent delays that we are seeing?
We are using Framework 1.1 SP 1.
Any help will is greatly appreciated.
Rob
an application that queries a database a great deal and speed is crucial. We
do all database operations utilizing stored procedures and return the
information in parameters since there would be only one row anyway.
We have noticed some interesting timing that is causing us problems in the
overlying process this applciation supports. By using Windows high
performance timers to measure the performance we have learned that sometimes
we'll have long delays (When I say long delay I mean between 150 to 600 mS.)
waiting for the ExecuteNonQuery to return. Other times (a little more rare)
we would have a long delay getting the parameters back after the
ExecuteNonQuery returned. Of course we try to retrieve the SQLParameters as
soon as the ExecuteNonQuery returns. That delay is measured from the time
the ExecuteNonQuery returns and the first SQLParameter is read out of ADO.
The time needs to stay below 100 mS. The delay doesn't happen very often
about once in every 100 or so attempts.
We are putting performance timers in SQL Server to determine the times the
stored procedures uses for processing and examining when the server
recompiles its stored procedures. So now for the question.
We are concerned with the delay ADO is giving us when we attempt to call the
SQLParameters collection. Do the parameters return to ADO on a background
thread that is spawned when the ExecuteNonQuery returns complete from the
stored procedure? If not does anyone know what might account for these
infrequent delays that we are seeing?
We are using Framework 1.1 SP 1.
Any help will is greatly appreciated.
Rob