Does SQL Express limit the speed at which it will pull data?

I just converted my Access tables into SQL Express 2005. I also made some
views that are equivalent to some queries I have in Access. When I run one
of my views (in SQL Server Management Studio Express) it will take 20 seconds
for all 80,000 records to finish loading. When I run the same query in
Access (using the original Access tables, not SQL), the same query runs in
1-2 seconds. If I just open a table in SQL with 70,000 records it takes
15-20 seconds to load all of the records (this installation is all local, no
network involved). Opening the same table in Access only takes 1 second.

Am I doing something wrong or is there a restriction on SQL Express that
limits how many records it will process per clock cycle?

No it does not limit performance but you have to understand how SQL Server
in general and Express specifically works. If it was the first time you
accessed it there will be a lag while it loads the data from disk into the
cache. But more than likely you are getting a lag as the db is opened due to
the way Express works. Looking at a whole table is not a good test for
performance anyway since this is something that your app should rarely or
never do. You should be issuing selects that only return the row or rows you
want. With proper indexes this will be fast. Have a look at these links to
see how express works.



SQLExpress
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp
Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
User Instances
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/emsqlexcustapp.asp
Embedding Express in Apps
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express


--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

In addition to what Andrew had to say, more importantly, SQL Server, even
Express Edition, is a nearly RDBMS, MS Access is not!

MS JET (the db engine behind the Access GUI) is an ISAM engine. It does not
fully support ATOMICITY, nor does it follow the WAL transaction protocol.
However, because it (JET) is ISAM based, if it is local, and you are the
only user, it can be extremely fast, faster than SQL Server in many cases.

The problem is that JET does not scale-well, in addition to its
transactional guarantee problems.

This is a very important point: ISAM/VSAM file-base engines can be faster,
but are limited, and do not provide transaction guarantees.

Keep this in mind, an RDBMS is not built for SPEED alone. The most
important feature of a transactional based db engine is its Constraint and
Transaction support, optimized for speed AFTER the primary purpose has been
satisfied.

If SPEED is the ONLY issue (better think twice about this one), then
file-based (sometimes called indexed based) engines are going to provide you
with better throughput.

At the risk of data loss on the downside however.

Sincerely,


Anthony Thomas

--

[quoted text, click to view]