Re: MSDE Slow in executing Stored Procedures

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 05/07/04 

Date: Fri, 7 May 2004 09:50:51 +0200

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: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)
  • Re: Why use stored procedure in C#?
    ... the reasons of using stored procedure is ... because they are stored as compiled form in sql server. ... Untrue, stored procedures aren't stored in compiled form, they're ... re-compiled every time IF there's no execution plan in the cache, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: very strange stored procedure behavior
    ... The execution plan for the sp you get with "Show Execution Plan" ... >im having trouble with a stored procedure. ... >to run the query directly (outside the stored proc) and got a much faster ... >that sql server compiles stored procedures and keeps that execution plan im ...
    (microsoft.public.sqlserver.programming)
  • 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 Procedures vs DTS vs Jobs
    ... A stored procedure is complied code meaning that SQL Server has already ... > execution time, ...
    (microsoft.public.sqlserver.dts)
Loading