sqlite vs ms access 에 sqlite 더 빠른게 사실인가요?

[김현수]

안녕하세요

 

저는 일반직장인인데요 자료를 관리하다 보니느  sqlite를 알게되었습니다.

 

현재 MS-Access에 컬럼은 49개, record 수는 2백만개 정도로 하나의 테이블로 운영하고 있습니다.

.

주요 사용방법은 엑셀에서 ODBC를 이용해서 sql 문으로 단수조회문 ( select문) 으로 사용중입니다.

sqlite가 더 빠르다고 해서 sqlite에 이관해서 테스트 해보니 MS-Access 보다 빠르다는 생각이 전혀 들지 않습니다..

 

단순히 하나의 테이블에서 where 문만 고쳐서  사용하는 sqlite가 더 느려지는 이유가 무엇일까요?

 

  1. setup 사항을 빼먹은게 있는건인지?

  2. 아니면 같은 MS 계열이라서 그런건지?

  3. 테이블 하나만으로 대용량을 사용하는 경우에는 해당되는게 아닌지?

 

궁금합니다.  좋은 고견 부탁드립니다.

 

 

 

인터네셍서 sqlite 성능 평가

 

 

MySQL (MyODBC / ByteFx), SQLite (V3), Access (2003), MSSQL (2000Sp3) between the performance test

Tag: string, insert, access, database Category: Database Author: gaohadai Date: 2010-08-12

Wrote a simple test program for the purpose of understanding their current use of the performance of the database, and can adjust better results.

The following are the results of several tests:

Test environment: Windows2003, NET Framework 1.1, no AntiVirus, 1GB Memory

Test database: SQLite V3, MySQL Type = InnoDB, SQL Server 2000 Sp3, Access 20003

Set the parameters as follows:

MySQL (default * 2)

max_allowed_packet = 16M

key_buffer_size = 16M

table_cache = 128

sort_buffer_size = 4M

The remaining options are default values.

Database connection string:

Private MSSQLConnStr As String = "initial catalog = CodeLib; Connect Timeout = 20; data source = (local); integrated security = SSPI; persist security info = False;"

Private MySQLODBCConnStr As String = "DRIVER = {MySQL ODBC 3.51 Driver}; DATABASE = CodeLib; OPTION = 18475; SERVER = localhost; UID =; PASSWORD =;"

Private MySQLByteFXConnStr As String = "Database = CodeLib; Data Source = localhost; User Id =; Password ="

Private OLEDBConnStr As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = 1.mdb"

Private SQLiteConnStr As String = "Data Source =; New = True; Version = 3"

 

INSERT with Transaction 10000 records:

00.6089 (SQLite) <01.1677 (MSSQL) <02.5346 (ByteFx) <08.9000 (Access) <13.9242 (MyODBC) 0 ~ 10000

00.5476 (SQLite) <01.2679 (MSSQL) <02.4982 (ByteFx) <08.6672 (Access) <13.5140 (MyODBC) 10000 ~ 20000

00.5245 (SQLite) <01.1508 (MSSQL) <02.5150 (ByteFx) <08.7101 (Access) <13.6411 (MyODBC) 20000 ~ 30000

 

INSERT with Transaction 50000 records:

03.4739 (SQLite) <09.4173 (MSSQL) <13.6408 (ByteFx) <44.3165 (Access) <71.6529 (MyODBC) 50000

INSERT without the Transaction 300 records:

00.0908 (MSSQL) <00.3520 (Access) <10.3717 (MyODBC) <11.5118 (ByteFx) <25.8230 (SQLite) 30000 ~ 30300

00.0907 (MSSQL) <00.3686 (Access) <08.7148 (ByteFx) <11.3625 (MyODBC) <25.1898 (SQLite) 30300 ~ 30600

00.1027 (MSSQL) <00.3595 (Access) <08.4664 (ByteFx) <08.7934 (MyODBC) <25.3364 (SQLite) 30600 ~ 31200

SELECT 31200 pen records:

00.4161 (SQLite) <00.8362 (MSSQL) <00.8688 (Access) <02.3565 (MyODBC) <02.4857 (ByteFx)

00.3519 (SQLite) <00.5707 (Access) <00.7055 (MSSQL) <02.5555 (MyODBC) <02.6173 (ByteFx)

00.3472 (SQLite) <00.6037 (MSSQL) <00.6707 (Access) <02.5439 (ByteFx) <02.6328 (MyODBC)

 

SELECT 50000 pen records:

00.6129 (SQLite) <01.0132 (MSSQL) <01.2942 (Access) <04.0064 (ByteFx) <04.4904 (MyODBC)

00.6741 (SQLite) <00.9719 (MSSQL) <01.2956 (Access) <03.7336 (ByteFx) <04.6592 (MyODBC)

Delete 31200 pen records:

00.0091 (Access) <00.0247 (ByteFx) <00.1960 (SQLite) <00.6901 (MSSQL) <01.2297 (MyODBC)

 

Delete 50000 pen records:

00.0092 (Access) <00.0301 (ByteFx) <00.3305 (SQLite) <01.9281 (MyODBC) <03.0269 (MSSQL)

Be seen from the above results:

SQLite3 does not perform line transaction processing is not very satisfactory, but the the SELECT speed is quite fast.

MSSQL / Access whether to perform the transaction performance is pretty good.

Difference of the MySQL ByteFx MyODBC on the Insert / Delete speed slightly.

The above data for reference only, If you have any better suggestions on the results, please let me know.

SQLite not open transaction is a tragedy, because it's multi-access support on the basis of exclusive and shared file.

SQLite and other database performance can be found in the official website:

 

원본 위치 <http://www.databaseskill.com/1054843/>