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

MSDE sharing on peer-to-peer network

We have a Front End/Back End database. The Back End is stored in a shared
folder on a per-to-peer network.
1. Can we use MSDE to back-end the database and have up to five users access
it from each of their workstations if we change the front-end to an Access
project using ADO?
2. If this can be done how will the performance compare? There are up to 40
tables containing up to 150MB of data.
Thanks
Terry
hi Terry,
Terry wrote:
> We have a Front End/Back End database. The Back End is stored in a
> shared folder on a per-to-peer network.
> 1. Can we use MSDE to back-end the database and have up to five users
> access it from each of their workstations if we change the front-end
> to an Access project using ADO?
you should not store SQL Server/MSDE databases in a network share, nor
should they be on a remote one..
users only needs to access MSDE, and thus data throught it's service and do
not need physical access to the database os files, both for security reasons
and technical perspective...
regarding Access/ADO, you actually do not need this path as you could go
with other model (c#/ADO.Net or whatever), but it's ok..

> 2. If this can be done how will the performance compare? There are up
> to 40 tables containing up to 150MB of data.
it dependes on your code quality and logic... avoid SELECT * FROM
myMillionTable and follow standard code quality and best practice for
database applications
you have to test your scenario...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Andrea
Thanks for the information.
I think I worded the question badly in terms of sharing - I understand where
you are coming from (I think). Can a user on, say Workstation 2 access the
data in an MSDE database on Workstation 1?
Terry
=================================
"Andrea Montanari" wrote:

> hi Terry,
> Terry wrote:
> you should not store SQL Server/MSDE databases in a network share, nor
> should they be on a remote one..
> users only needs to access MSDE, and thus data throught it's service and do
> not need physical access to the database os files, both for security reasons
> and technical perspective...
> regarding Access/ADO, you actually do not need this path as you could go
> with other model (c#/ADO.Net or whatever), but it's ok..
>
> it dependes on your code quality and logic... avoid SELECT * FROM
> myMillionTable and follow standard code quality and best practice for
> database applications
> you have to test your scenario...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Terry,
The answer is yes, giving that MSDE (SQL Server) is running on
workstation 1 (it is a program, not just a database "file") and that the
user on Workstation 2 has the necessary "rights" to access the database.
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
Terry wrote:[vbcol=seagreen]
> Hello Andrea
> Thanks for the information.
> I think I worded the question badly in terms of sharing - I understand where
> you are coming from (I think). Can a user on, say Workstation 2 access the
> data in an MSDE database on Workstation 1?
> Terry
> =================================
> "Andrea Montanari" wrote:
>
sql

MSDE no access on database objects

I have installed MSDE 2000 A, but I can't edit the database objects (tables,
stored procedures, etc.) in Access 2000. If I try, the window appears only
for a moment.
Any suggestions to solve the problem?
Frank
Hi Frank,
"FAT" <FAT@.discussions.microsoft.com> ha scritto nel messaggio
news:D74106D3-FDF3-4359-9AFA-1DB9A25C809E@.microsoft.com
> I have installed MSDE 2000 A, but I can't edit the database objects
> (tables, stored procedures, etc.) in Access 2000. If I try, the
> window appears only for a moment.
> Any suggestions to solve the problem?
> Frank
I think you've probably better resort on alternate tools.. perhaps Acces
2003, or other 3rd party tools...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||ehm........
please have a look at http://www.microsoft.com/sql/msde/partners/default.asp
and/or http://www.aspfaq.com/show.asp?id=2442 ... google for others..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE free for commercial use?

also, how can i retrieve data ? no interface for that?
does it have stored procedure function?Here is an FAQ on appropriate use of MSDE.

It has stored procedures and user defined functions. It does not have a user interface like Enterprise Manager, but if you have legit access to Enterprise Manager, it will work against MSDE. You can purchase the developer edition, for instance, and that will give you the client tools.|||::also, how can i retrieve data ? no interface for that?

I really have to disappoint you, but OI know nooone who is using a user interface to retrieve data or manipulate data in a database besides for development / admin purposes.

Databases are tools for developers. YOUR APPLICATION is the user interface for data retrieval.

To the other question: yes, free for commercial use, if you can legally redistribute it - there are some preconsditions, but easy to meet.