MSDE Slow in executing Stored Procedures

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
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
>
|||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...[vbcol=seagreen]
delete)[vbcol=seagreen]
2000.[vbcol=seagreen]
and[vbcol=seagreen]
anyway
>
|||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...
> the execution planes for the
> news:%23SHrKJyMEHA.1312@.TK2MSFTNGP12.phx.gbl...
> delete)
> 2000.
> and
> anyway
>
|||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.. .[vbcol=seagreen]
procedures.[vbcol=seagreen]
single[vbcol=seagreen]
in[vbcol=seagreen]
compare[vbcol=seagreen]
app[vbcol=seagreen]
Server[vbcol=seagreen]
that[vbcol=seagreen]
procedure[vbcol=seagreen]
(SQL[vbcol=seagreen]
|||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...
> Then you have to take it from there
> news:%23M%23J7A%23MEHA.3312@.tk2msftngp13.phx.gbl.. .
> procedures.
> single
> in
> compare
> app
> Server
> that
> procedure
> (SQL
>
|||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...[vbcol=seagreen]
sql[vbcol=seagreen]
in[vbcol=seagreen]
them.[vbcol=seagreen]
and[vbcol=seagreen]
a[vbcol=seagreen]
wrote[vbcol=seagreen]
have an[vbcol=seagreen]
edit,[vbcol=seagreen]
application[vbcol=seagreen]
20ms[vbcol=seagreen]
there
>
|||Finally I found the culprit!
Tibor, you are correct. It is the Auto-close option in the database
settings. Initially I thought the default database option settings for
autoclose and autoshrink is set to off. But apparently on SQL Personal
Edition (i.e. MSDE), these 2 options are turned on as default, whereas SQL
2K is turned off.
Once I've set both options to off, the performance increased tremendously..
Thanks Tibor for your help. Really appreciate it.
regards,
Lau
"Lau Weng Tat" <lwt@.ghl.com> wrote in message
news:ugIh6DBNEHA.1104@.TK2MSFTNGP10.phx.gbl...
> 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[vbcol=seagreen]
> message news:%2319SFgANEHA.3192@.TK2MSFTNGP11.phx.gbl...
> two figures where I/O and duration
> MSDE 7.0) vs. SQL Server 2000.
> vs. stored procedure)?
> straight INSERT statement, then it can be
> variables in the WHERE clause etc.
> INSERT statement.
> news:uSdr3EANEHA.1196@.TK2MSFTNGP11.phx.gbl...
and[vbcol=seagreen]
> sql
2K[vbcol=seagreen]
wrote[vbcol=seagreen]
> in
> them.
slowdown.[vbcol=seagreen]
procedure[vbcol=seagreen]
> and
(insert[vbcol=seagreen]
> a
> wrote
and[vbcol=seagreen]
> have an
> edit,
SQL[vbcol=seagreen]
operations.
> application
> 20ms
> there
>
|||hi Lau,
"Lau Weng Tat" <lwt@.ghl.com> ha scritto nel messaggio
news:ObFFEcCNEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Finally I found the culprit!
> Tibor, you are correct. It is the Auto-close option in the database
> settings. Initially I thought the default database option settings for
> autoclose and autoshrink is set to off. But apparently on SQL Personal
> Edition (i.e. MSDE), these 2 options are turned on as default, whereas SQL
> 2K is turned off.
> Once I've set both options to off, the performance increased
tremendously..
> Thanks Tibor for your help. Really appreciate it.
you are right... MSDE and Personal Edition always set autoclose and
autoshrink options to true...
this is by design in order to prevent database corruption for users tha are
not aware of the presence of MSDE is installed on their pc..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply