Tuesday, October 04, 2005

Parameters.Refresh might slow your application big time (Delphi + ADO)

I recently found that that by calling someADOStoredProcedure.Parameters.Refresh you might be slowing your application quite a lot, I use this method to retrieve the stored procedures parameters very often, I have used it for many years with no problems, then last week I came accross this problem where my application would work very well when executed against the local database, but really slow when executed against a remote server in another office.

I went through the "trouble" of adding counters to measure the time each part of my program was taking, to determine where exactly the problem was, and it did tell me what block of code was causing the problem, but I still couldn't see anything obvious, it was simple code to execute a stored procedure, I was still confused, because other parts of code also executed procedures, and so by looking very closely I saw that the other procedures weren't using the Parameters.Refresh; still not convinced that that was the problem, I decided to give it a try, and sure enough it fixed the problem, it dropped the execution time from 300+ milliseconds to 30+

so there you have it, be careful when using this method to retrieve your parameters, while it can be very useful, it might kill your application if it runs against a remote database

my solution to completly fix the problem, was to call this method once, and after that, make sure I populate all the parameters values, because that also can cause issues, if you don't call the .Refresh method, make sure that you update all the parameters values, or you'll end up with parameters data from your previous call, which can really mess up your data

No comments: