Re: Stored Procedure is running slow!

From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 07/18/04 

Date: Sun, 18 Jul 2004 23:18:46 +0000 (UTC)

[posted and mailed, please reply in news]

Mario Splivalo (majk@fly.srk.fer.hr) writes:
> I have created a stored procedure that returns a single value, a
> calculation for stock items manipulation over some period of time. When
> I run stored procedure from QA, it takes some 15 seconds to execute.
> But, when I run it via ADO, using Microsoft Fox Pro, even when I set
> .CommandTimeout property to 240 seconds (4 minutes), I still get
> 'connection timeout' messages. I call dozens of stored procedures wich
> take around 5-10 seconds to execute when called from QA, and 90-180
> seconds when called from MS Fox Pro, via ADO.
>
> Has anyone experienced something like this, to give me some guidelines,
> I spent a night here, have no clue why is it happenig like so. Yes, I
> could se the .CommandTimeout property of an ADO connection object to
> something like 5000 seconds, but, I'd like to know why is it taking so
> much time to execute SP from fox.

Try this in Query Analyzer:

  SET ARITHABORT OFF
  go
  EXEC tr_SNV

My prediction is that the query will run for just as long as it would
do from ADO, if the command timeout had not set in.

By default Query Analyzer runs with ARITHABORT ON, but OLE DB and ODBC
connections runs with this seting off by default. Therefore you get
different query plans, but if you change the set in QA, you will use
the bad query plan for the user-defined function.

On the other hand, if you pass SET ARITHABORT ON from ADO, this may
improve things.

Now, we only need to understand the reason for the difference... A shot
in the dark is that you have an index on a computed column or an indexed
view somewhere. For the optimizer to consider these indexes, ARITHABORT
must be ON.

Another way to turn on ARITHABORT, is to use the ALTER DATABASE statement,
so it is on by default for the datbase. 

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Relevant Pages

  • Re: Returns a names of columns from a query in stored procedure
    ... through ADO, there is .Name property for each ordinal column returned in ... Mike Epprecht, Microsoft SQL Server MVP ... How I can return the name of columns from a query that was carried through ... With stored procedure "sp_columns" I can return the columns of a table, ...
    (microsoft.public.sqlserver.programming)
  • Re: Very bad response time from stored procedure call via ADO
    ... Turn on the Show Query Plan and show statistics option in SQL Server ... VB6 application for display via ADO. ... The stored procedure structure is as follows: ...
    (microsoft.public.data.ado)
  • RE: ODBC query in VB code Need HELP
    ... Everything for ADO is in the first 2 messages that I gave you. ... Since your pass-through query already exists (including the ... Dim STRSQL As String ...
    (microsoft.public.access.formscoding)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... > Noiseword varcharNot Null ... > the data from the noise file to the noise_words table. ... >> A clause of the query contained only ignored words. ... >> into query analyzer before starting the stored procedure. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Connecting to Query
    ... practice examples of the ADO Command, Parameters, etc. you explained below. ... Second, with the selected territory ... >> combo box to select a product from a query based primarily on a link ...
    (microsoft.public.access.formscoding)