I am bored today, I have made a performance test of SQL Server, everyone knows SQL Server has six versions (Enterprise Edition, Standard Edition, Personal Edition, Development, Desktop Engine, SQL Server CE, Enterprise Evaluation) I often dizzy the use of it is suitable for me, today I finally decided to tell Microsoft! Test SQL mainly examine the write operation, read operation, index operation of the database server, if you are interested in your machine, run this script The result is posted?
----------------------------------------------- -- I Machine configuration (company machine) ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------- Celeron 4 1.7 - 256M memory - ASUS 845D motherboard - NVIDA GeForce2 MX / MX 400 32M memory - MAXTOR 6E040L0 40G, 7200 Turn - English Windows 2000 Professional with SP4 ---- ------------------------------------- ------------------------------------------ Test SQL (note SQL A @Imax variable) --------------------------------------------------------------------------------------------------------------------------------- ---- IF EXISTS (Select * from dbo.sysObjects where id = Object_ID (n '[dbo]. [allabak]') And ObjectProperty (ID, n'susertable ') = 1) Truncate Table [DBO]. [Areabak ] Go
--Drop table [dbo]. [allabak] if not exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [allabak]') And ObjectProperty (id, n'susertable ') = 1) beginCREATE TABLE [dbo]. [AreaBak] ([AreaCode] bigint not null, [AreaName] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT nULL, [LocaName] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT nULL) ON [PRIMARY]
Alter Table [DBO]. [Areabak] with nocheck address [pk_areabak] Primary Key Clustered ([AreaCode]) on [primary] EndGo
Declare @IMAX bigint - <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< set @imax = 1000 - << Modify this IMAX (10000000 20000, 100000) to compare the test results <<<< - <<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< declare @i bigintdeclare @tbegin datetimedeclare @tend DateTimedeclare @fcout floatset @tbegin = getdate () - Test write performance set @i = 1WHILE @i <= @ iMaxBegin Insert Into Areabak Values (@i, RTRIM (Cast (@i as char (12))) 'XX', RTRIM (Cast (@i as char (12))) 'XXXX') Set @ i = @ i 1nd
- Test Index Performance Set @i = @ImaxWhile @i> = (@Imax -50) Begin Select * from area @ @i set @ i = @ i-1
- Test a lot of data read performance select * from areaabakset @tend = getdate () set @fcout = convert (@ TEND - @ Tbegin)) --24 * 60 * 60 exchange set @fcout = @ fcout * 24.0 * 60.0 * 60.0Print @fcout -------------------------------------------------------------------------------------------------------------------- -----
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MSDE (WITHOUT SP) test results (seconds) ----------------------------------------- --------- IMAX = 1000: 0.873333 | 0.88 | 0.87 - iMax = 10000: 8.45333 | 8.75333 | 8.32-- IMAX = 100000: 90.9533 | 82.4467 | 83.59 ---------------------------------------------------------------------------------------------------------------------------- --------------------------------------- English MSDE (with SP3) Test Results (Seconds) ------------------------------------- iMax = 1000: 0.93 | 0.93333 | 0.93-- IMAX = 10000: 8.5 | 8.74333 | 8.38333 - IMAX = 20000: 17.0733 | 16.4633 | 17.8067 - iMax = 100000: 82.9 | 82.2167 | 86.3533 ------- -------------------------------------------------- ------------------------------------- WITHOUT SP test results (seconds) - ----------------------------------------------- iMax = 1000: 0.863333 | 0.87 | 0.8.34333 | 8.25 | 8.30333 - iMax = 20000: 16.4633 | 16.5933 | 16.6633-- IMAX = 100000: 81.27 | 86.9367 | 81.4567 ---------------- --------------------------------------- English Personal Edition (with SP3) Test Results (Second ) ------------------------------------- IMAX = 1000: 0.913333 | 0.92 | 0.93 | 0.91333- iMax = 10000: 8.46 | 8.39333 | 8.87333 | 8.25333 | 8.29 | - IMAX = 20000: 16.9767 | 16.1933 | 16.3433 | 16.2433 | 16.2867 - iMax = 100000: 80.4867 | 81.8767 | 80.9167 | 82.09 | 81.91
- IMAX = 1000: 0.673333 / 0.79 / 0.65 (Memory is adjusted to 102 ~ 255m) - IMAX = 10,000: 8.29333 / 8.21 / 8.16333 (Memory is adjusted to 10255m) - IMAX = 100000: 80.2267 / 80.1033 (Memory is adjusted to 102 ~ 255m)
- IMAX = 100000: 79.0433 / 82.24 (Memory is adjusted to 0 ~ 5m) ------------------------------- ----------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Development (WITHOUT SP) test results (seconds) --------------------------------------- ---------- IMAX = 1000: 0.863333 | 0.86 | 0.85 - iMax = 10000: 8.14 | 8.60333 | 8.26333 - IMAX = 20000: 17.1433 | 17.0567 | 16.3333 - iMax = 100000: 86.3833 | 85.7833 | 86.5333 ------------------------------------------------ - English Development Edition (with sp3) test results (seconds) ------------------------------------- ------------ IMAX = 1000: 0.92 | 0.92 | 0.923333 - iMax = 10000: 8.17333 | 8.39333 | 8.28 - iMax = 20000: 16.2733 | 17.3033 | 16.2333 - iMax = 100000: 86.9967 | 85.8333 | 85.2233 -------------------------------------------------------------------------------------------------------------------------- - Because the operating system does not test the standard and enterprise version of SQL Server, if you only develop SQL Server applications, you don't have to install the Enterprise Edition on your machine (if you don't have to use special features. ). In fact, the above test results show that the individual version of SP3's stand-alone (in line with our development environment) is the best.
This test did not perform concurrent connection processing performance, according to Microsoft officially said: When compatible with more than 5 batch, the concurrent workload controller in SQL Server 2000 and SQL Server 2000 Desktop Engine will limit performance.
When using the SQL Server 2000 Desktop Engine or Microsoft Data Engine (MSDE) 1.0, the size of the database cannot exceed 2GB. (I personally often have a database greater than 2GB, and Desktop Engine is installed, but also adds a plurality of graphical management tools.
Standard and Enterprise Edition requires Windows2000 Server version to be installed. Hey, I just don't like the Server version of Windows 2000! Microsoft Memory (RAM) suggestions: Enterprise Edition 64 MB of Minimum, 128 MB Recommended Standard Edition 64 MB of minimum Personal Edition 64 MB on Windows 2000, 32 MB on all other operating systems (memory consumption is relatively small) Developer Edition 64 MB of minimum Desktop Engine 64 MB minimum on Windows 2000, 32 MB on all other operating systems a final conclusion : I decided to use SQL Server personal version SP3 in the development, and I need to consider the size and financial resources of customers.
