In one of applications written in our company we are trying to use dotConnect SQLite provider as data provider for OLAP cube generator. The provider is delivered to generator by specifying DbProviderFactory (in this case SQLiteFactory) and appropriate connection string (containing data source, set utf-16 encoding flag, and cache size parameter = 20000).
Apart connection string and provider factory, there is no other way to control database during cube generation. Entire process is controlled by Analysis Services (database queries, transactions, how database is opened). Here we've encountered great performance problem. SQLite turned out to be over 10 times slower than SQL Server.
Cube generation:
- dotConnect provider: 6:05
- SQL Server: 0:25
In both cases database structure is the same. It's star-shaped: 5 independent tables (each has unique indexed integer column as primary key) and 1 table that describes relations between tables (it contains all foreign keys referenced from other 5 tables). The table which describes relations has about 1 million rows.
The difference in execution time is to great to consider it as normal.
Is there anybody who experienced with similar problems? Do You have any suggestions what should we do speed up cube generation?
Any help, suggestions, ideas are much appreciated.
SQLite + Analysis Services - very slow
Microsoft SQL Server and SQLite have different structure and are applied for different purposes. SQL server is a specific implementation of a relational DBMS. Unlike client-server database management systems, the SQLite engine is not a standalone process with which the program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the program. SQLite has less performance than SQL Server, because of its implementation.