Re: Very bad response time from stored procedure call via ADO
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Thu, 10 Aug 2006 07:16:40 -0400
Craig wrote:
Hi,
We have a stored procedure that returns some statistical information
to a VB6 application for display via ADO. SQL Server is version 2000
SP4 and is clustered (2 nodes)
The stored procedure structure is as follows:
Create procedure upLstXYZ
( @Parm varchar(5)
)
as
begin
set nocount on
select 'Result col 1' as Heading, dbo.fn1(@Parm) as Value
union all
select 'Result col 2', dbo.fn2(@Parm)
union all
select 'Result col 3', dbo.fn3(@Parm)
union all
select 'Result col 4', dbo.fn4(@Parm)
end
In total there are 14 rows returned (14 function calls).
When run in SQL Query Analyser the typical duration is 400ms.
When run from the VB6 application via ADO the duration increases to
over 60000ms!
I've captured a profiler trace when the stored procedure is run via
ADO. The extra time is occurring in some of the function calls. The
duration is increased in the order of between 150 and 400 times!
In our development environment, which is not clustered, there is
neglible difference in reponse time between running the stored
procedure in SQL Query Analyser and the via ADO in the VB application.
What would you suggest I do to resolve this?
The usual culprit when there is a difference in performance between testing
a procedure in QA and using the procedure in production is a behavior called
"parameter sniffing". See:
http://tinyurl.com/h7aa
and
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EEAA
If this does not turn out to be the problem, then I suggest you post a repro
to m.p.sqlserver.programming. In the repro, include the DDL and sample data
required to allow someone to install the tables, prcs and functions on his
own server and test them (www.aspfaq.com/5006)
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
.
- Prev by Date: Re: How to select rows from a mapped protected .MDB and insert into local table
- Next by Date: Re: Prevent Commands in SQL Update
- Previous by thread: Re: Transform DataTables
- Next by thread: Re: Very bad response time from stored procedure call via ADO
- Index(es):
Relevant Pages
|
