Re: MSDE Slow in executing Stored Procedures

From: Lau Weng Tat (lwt_at_ghl.com)
Date: 05/07/04 

Date: Fri, 7 May 2004 16:55:31 +0800

Sorry for the confusion. It's my typo error. Allow me to describe the
problem I am facing. I have an application (App A) that accesses the
database. Stored procedures are used to select/insert/delete data.
Results obtained were:
AppA
                                            MSDE 2000 SQL 2000
Stored Procedure 1500 ms 70ms

*Time above is the average time taken for the application to complete its
processing, that includes some business logic processing,
inserting/select/update data etc.

When I checked my application log files, most of the time taken was used in
database processing, i.e. retrieving data, inserting data etc.

In order to isolate where the bottleneck is, I have created a very simple VB
App (App B) that creates an ADO connection object and execute a simple
stored procedure and its equivalent insert statement.
E.g.
Insert Statement
INSERT [MylDB].[dbo].[My_Table] (Col1, Col2, Col3,.....) VALUES ('1', '2',
'3', ........)

Stored Procedure
EXEC [MylDB].[dbo].[My_SP] '1', '2', '3', ......

where the Stored Proc is
CREATE PROCEDURE [dbo].[My_SP]
 ( @sz_Col1 varchar (2),
   @sz_Col2 varchar (2),
   @sz_Col3 varchar (2),
   .....
)
AS
INSERT [MylDB].[dbo].[My_Table] (Col1, Col2, Col3,.....) VALUES (@sz_Col1,
@sz_Col2, @sz_Col3, ........)
GO

Results obtained were:
AppB
                                            MSDE 2000 SQL 2000
Insert Statement 0~30ms 0~10ms
Stored Procedure 200~300ms 0~10ms

The insert statement inserts 18 columns of data into a row. Similarly, the
stored procedure takes in 18 parameters. The VB App (App B) hard-codes all
the data values. The Profiler results are comparing Insert Statement and
Stored Procedure using App B in MSDE 2000.

So, I'm wondering what is making the execution of stored procedure on
MSDE2000 so slow......
Thanks!

regards,
Lau

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%2319SFgANEHA.3192@TK2MSFTNGP11.phx.gbl...
> I'm afraid that I'm not following you. In your earlier post, you showed
two figures where I/O and duration
> differs a lot. And you now say that the perf difference is negliable.
>
> In your original post, you also mention both MSDE (is that MSDE 2000 or
MSDE 7.0) vs. SQL Server 2000.
>
> Perhaps you can summarize you'd findings (MSDE vs. SQL Server and INSERT
vs. stored procedure)?
>
> If your general case is that the stored procedure is slower than a
straight INSERT statement, then it can be
> due to things like parameter sniffing, using constants vs. parameter vs.
variables in the WHERE clause etc.
> But we can't comment on this without seeing the stored procedure vs. the
INSERT statement.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
>
> "Lau Weng Tat" <lwt@ghl.com> wrote in message
news:uSdr3EANEHA.1196@TK2MSFTNGP11.phx.gbl...
> > Well, I've ran SQL Query Analyzer and executed both stored procedure and
sql
> > insert statement. The execution plan looks the same.
> > If I ran the same stored procedure and insert statement against a SQL 2K
> > Server, the difference in performance is negligible.
> >
> > regards,
> > Lau
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> > message news:OE8mQW$MEHA.3404@TK2MSFTNGP10.phx.gbl...
> > > Again, the first step is the check the execution plans and compare
them.
> > Then you have to take it from there
> > > and try to understand why the plans differ (if they do).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > >
> > >
> > > "Lau Weng Tat" <lwt@ghl.com> wrote in message
> > news:%23M%23J7A%23MEHA.3312@tk2msftngp13.phx.gbl...
> > > > Thanks for the tip. But I think it's not the cause of the slowdown.
> > > > I've used profiler to trace the execution for both stored procedure
and
> > > > insert statement.
> > > > Results were
> > > > Stored procedure
> > > > CPU : 0
> > > > Reads : 95
> > > > Writes : 8
> > > > Duration : 179
> > > >
> > > > Insert Statement
> > > > CPU : 0
> > > > Reads : 2
> > > > Writes : 0
> > > > Duration : 16
> > > >
> > > > Seems that the number of reads and writes is larger for stored
> > procedures.
> > > > Any ideas how to speed things up?
> > > > Btw, the stored procedure and insert statement is identical (insert
a
> > single
> > > > record with a one column primary key)
> > > > Thanks!
> > > >
> > > > regards,
> > > > Lau
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
wrote
> > in
> > > > message news:OkdkRozMEHA.2484@TK2MSFTNGP09.phx.gbl...
> > > > > Autoclose for the database is one possible reason. Also check and
> > compare
> > > > the execution planes for the
> > > > > queries.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > >
> > > > >
> > > > > "Lau Weng Tat" <lwt@ghl.com> wrote in message
> > > > news:%23SHrKJyMEHA.1312@TK2MSFTNGP12.phx.gbl...
> > > > > > Hi All,
> > > > > >
> > > > > > I'm having this performance issue problem with MSDE 2000. I
have an
> > app
> > > > > > that accesses MSDE database using stored procedures (select,
edit,
> > > > delete)
> > > > > > I found out that the queries are extremely slow compared to SQL
> > Server
> > > > 2000.
> > > > > > E.g. 70 ms (MSDE) vs 1500ms (SQL 2K) for the entire operations.
> > > > > > To figure out what was going wrong, i wrote a simple VB
application
> > that
> > > > > > executes just a simple SQL insert operation, both using stored
> > procedure
> > > > and
> > > > > > a SQL insert statement. Results were : 200ms (Stored Proc) vs
20ms
> > (SQL
> > > > > > Insert Statement)
> > > > > > Does anyone have any idea why stored procedures is so slow? Is
there
> > > > anyway
> > > > > > to speed things up?
> > > > > > Thanks!
> > > > > >
> > > > > > regards,
> > > > > > Lau
> >
> >
>
>

Relevant Pages

  • Re: Deadlock between Distribution Agent and Distribution Agent Cle
    ... stored procedure at the subscriber will have. ... replication to the subscriber and the possibility of moving the ... > obtains will be released at the end of its execution. ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Stored procedure doesnt return dataset unless recompiled
    ... Have you changed any of the objects called by the stored procedure? ... SQL Server will not allow you to change the ... "Dewey" wrote in message ... >I have a web app that calls a SP. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored Procedures vs DTS vs Jobs
    ... A stored procedure is complied code meaning that SQL Server has already ... > execution time, ...
    (microsoft.public.sqlserver.dts)
  • Re: tool for stored proc. (written in oracle/sql) execution?
    ... > obtained after execution of stored procedure in oracle and sql? ... I have migrated my stored procedure written in sql 2000 to ... use a simple file compare utility (like the fc.exe command line utility ... from the SQL Server into a table with a unique index on all columns and try ...
    (microsoft.public.sqlserver.tools)
  • Re: MSDE Slow in executing Stored Procedures
    ... the first step is the check the execution plans and compare them. ... > I've used profiler to trace the execution for both stored procedure and ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.msde)