I have the same table with the same data on 2 databases (SQL Server Express 2022 and MS Access 2016 with a .MDB 2002-2003), containing about 100,000 records. Both tables are indexed (index is named ID).
I connect to them using ADO. Connection type for SQL Server is "shared memory" (the fastest method) since scripts and databases reside on the same machine. Databases were installed by using the default configuration, I didn't tweak any parameter. Here are the connection strings I'm using:
MSACCESS: PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=xxxxx
SQL SERVER: Provider=MSOLEDBSQL;DataTypeCompatibility=80;Data Source=.\SQLEXPRESS;Initial Catalog=xxxxx;User ID=yyyyy;Password=zzzzz;Use Encryption for Data=False;Persist Security Info=True;TrustServerCertificate=True
For benchmarking, I'm using this simple query:
SELECT value FROM [fad] WHERE userid=[random] AND module=1 AND course=1
After running this query for 2,000 times, here are the results:
- SQL Server: 17.04 sec
- MS Access: 3.92 sec
Based on what I read on page https://www.diericx.net/post/benchmark-embedded-dotnet-databases/ (in particular in this graph: https://www.diericx.net/images/embeddeddb4.png ), I have very different results. By looking this graph, it seems that SQL Server should be at least 2.5 times faster than MS Access.
I tried changing the connection string, no success so far. I ran the benchmarch on both Windows 10 Pro and on a Windows 2016 server, with similar results.
How could I increase the performance of SQL Server Express? Thank you
Edit: this is the code I used for benchmarking (vbscript - ASP classic):
<%
server.scripttimeout=9000000
gotest(1) ' MSSQL
gotest(0) ' MSACCESS
function gotest(DbMSSQL)
response.write "DATABASE TYPE: <b>"
Set Conn = Server.CreateObject("ADODB.Connection")
conn.CursorLocation=3 ' adUseClient
select case DbMSSQL
case "0": response.write "MSAccess"
PathDB="c:\inetpub\wwwroot\accessdb\accessdb.mdb"
Conn.Open ("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&PathDB&"")
case "1": response.write "MSSQL"
PathDB="sqldb"
conn.open("Provider=MSOLEDBSQL19;DataTypeCompatibility=80;Data Source=.\SQLEXPRESS;Initial Catalog="&pathdb&";User ID=sa;Password=Pnzip1xt-;Use Encryption for Data=False;Persist Security Info=True;TrustServerCertificate=True")
set RS=Conn.Execute("SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;")
if not RS.eof then response.write "</b><br>Connection type: <b>"&RS("net_transport")&"<br>"
end select
response.write "</B><br>ADO Version: <b>"&Conn.Version&"</b><br><br>PERFORMANCE TEST IN PROGRESS...<br>"
response.flush
t1=timer
randomize timer
for dummy=1 to 2000
iduten=(Int((53700-13123)*Rnd+13122))
set RS=conn.execute("SELECT value FROM [fad] WHERE userid="&iduten&" AND module=1 AND course=1")
if dummy=1 then
response.write "<br>Cursor type: "&RS.cursortype
response.write "<br>Cursor location: "&RS.cursorlocation
response.write "<br>Cursor lock type: "&RS.LockType&"<br><br>"
end if
if dummy mod 200=0 then
response.write "X"
response.flush
end if
if dummy mod 1000=0 then
response.write " "&dummy&"<br>"
response.flush
end if
RS.close
set RS=nothing
if not response.isclientconnected then response.end
next
response.write "<br><br>TEST HAS ENDED. TIME TAKEN (sec): "
response.write timer-t1
response.write "<br><br>"
Conn.close
set conn=nothing
end function
%>
1条答案
按热度按时间jutyujz01#
Access in general, and it been WIDE knowledge for the last 20+ years?
If you are pulling data on the SAME machine as the data? Access easy runs 2x or mote times faster then a server based system.
I don't think there should be ANY surprise here.
For one, the Access JET (now called ACE) data engine can and does directly read the file, and "scraps" the data right off the disk.
With SQL server? You STILL going though a network layer, so you have tc/ip (network connection). You have a transaction based data engine, you have a separate software process and service, and a server based process in which the software has to "talk" to, and this is NOT a in-line software execution of code like you have with ms-access.
So, you have a Mount Everest of additional software layers between your software and the data file. You STILL using a socket type of connection to SQL server.
Quite much ZERO surprise that a direct disk file based system like Access will run circles around such a setup, and it always been that way.
However, having stated the above?
The INSTANT you start to have multiple users, then that is the instant that you ALREADY now will have a network connection involved. And that then tends to shift performance in favor of the server based system.
And using a server based system means that disk reads of data DOES NOT occur OVER the network.
And that means you can "send" the work to the server, let it beat up the disk drive (local on server), and THEN send the results.
With a file based system (like Access), then you have ZERO means to "send" the commands to the other end, since htere is NOT some other end. All processing occures client side. It just that in this example, both data and "process" are on the same server!
However, the instant that you go beyond the 1 user, that is ALSO the same instant that you now have a network involved, and that then is the very instant that a server based database system will start to show advantages.
As a typlical "genreal" statement?
if you migrate a access database to sql server, in MOST cases, you see a slow down if no network connection is between you and the data.
However, the instant that a network is placed between you and the database, then hands down, SQL server CAN run faster (note the word "can"!!! - it is not a automatic given that JUST adopting SQL server will make the Access front end application run faster).
In fact, Stackoverflow has been FILLED for the last 20+ years of posts of people who migrated their Access data to SQL server, and found things now run slower. So, this effect is NOT ANY kind of surprise here.
However, even one on one - say local computer?
You can still often find that SQL server will run faster then ACE/JET based queries - and this often is the result of the SQL server being able to "figure out" a better query plan then what JET/ACE will cook up. So, SQL server has received significant more R&D dollars over the years, and thus if you thow at SQL server some VERY bad formed quires, or some VERY poor SQL? SQL server will OFTEN still find a MUCH better way to execute that SQL due to a FAR better and more intelligent query plan builder.
So, for a simple table - raw data read?
No surprise, file based systems, be it reading a text file, or using access to scrape data from the disk drive? Such file systems are faster at raw read speeds, since there is FAR LESS systems and layers of software between your code and the hard drive with data.
A great example?
Take your test code, and re-write the code as a stored procedure, and then run it 2000 times. it will run as fast (actually probably faster) then the ACE/jet sql, since we now removing the network bottle neck of transfer of data to the client.