Re: Help with Stored Procedure Execution Speed - Delphi 6


"Eric Stewart" <stewarte@xxxxxxxxxxxxxxxxxx> wrote in message
news:43612c1e@xxxxxxxxxxxxxxxxxxxxxxxxx
>I have a stored procedure - MS SQL 2000 - that runs in less than 3 minutes
> in query analyzer and returns around 13000 records. However, in my Delphi
> 6
> application, I call the stored procedure using a TADOQuery passing the
> appropriate parameters and it times out. I have been increasing the time
> out
> setting just to see how long it would take. I currently have it set at 16
> minutes and I still time out. I have tried using DisableControls on the
> query because I think I read somewhere that that should help.

Ado connections normally has default time out after 30 seconds. The timeout
may come from several reasons such as your query takes longer than timeout
or poor network performance. If you query through ADO you can use the
Profiler to see how much time it takes for your query when you do it from
ADO. And you also can use Performance monitor tool to see where is causes of
the timeout; for example, bottle neck from your network, disk accessing,
RAM, CPU or etc.

You can improve your performance and get rid of the timeout by.
1. Edit the query timeout as you've already done.
2. You need to make sure you have the right indexes on the table and you
query by using those indexes too. (You may need to rebuild your indexes, let
see in SQL server help)
3. Be careful about Locking.
4. In some cases, if you have a heap of data, upgrade hardware might help
you too.

Hope may help

Regards
Kittipong Kiatcheeranun


.

Relevant Pages

  • Re: sp timeout capture
    ... " If you set a query timeout for a query in Query Analyzer and the query ... you'll see an Attention event in Profiler" ... I thought i used sql profiler very extensively, ...
    (microsoft.public.sqlserver.tools)
  • Re: Problem using Access or Query Designer to run queries in SQL S
    ... The need for the group of financial analysts I support, is some ad-hoc query ... Therefore stored procedures called via pass-thru queries ... for their Access/Excel queries to fetch data from SQL server is still a huge ... I can instruct them to set the ODBC timeout ...
    (microsoft.public.sqlserver.odbc)
  • Re: Not In Operator for Sub query too slow
    ... showed that he was restricting the Not In clause to only the ... as "Timeout using CurrentDB.Execute". ... Albert i cant restrict my records to one record. ... David i tried your way and it worked now the query runs and gives me ...
    (microsoft.public.access.queries)
  • Re: TIMEOUT Driving me nuts !
    ... optimized your queries. ... Connection timeout only handles the connection times not query times. ... > HELP HELP HELP! ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Query timeout
    ... Isn't there an ADO property that manages query timeouts for your ... Whatever object you are using to execute the query, ... see if there is a timeout in this object to be set. ... Pro SQL Server 2000 Database Design - ...
    (microsoft.public.sqlserver.programming)