I have tested my Code with the following PRAGMA and still not getting any
change in time.
//--------------------------------------------------------------
sqlite3_exec(db, "PRAGMA temp_store=2", NULL, NULL, NULL);
sqlite3_exec(db, "PRAGMA synchronous=0", NULL, NULL, NULL);
sqlite3_exec(db, "PRAGMA count_changes=OFF", NULL, NULL, NULL);
sqlite3_exec(db, "pragma default_cache_size =65536", NULL, NULL, NULL);
sqlite3_exec(db, "pragma cache_size = 8192", NULL, NULL, NULL);
//--------------------------------------------------------------
Actually I have a data in DBF files each file contain 10,30, 50 Million
Records. What I am doing is First I have developed an Application to convert
data from DBF to DB for using SQLite for fast data access.
The Requirment of the project is like this, because every time i will get
data in DBF. so I will link my applcation with another application and will
first convert it into DB and then I will run different Select Queries.
What I want is Fast Insert And Fast Select. I have to display this hige data
in Grid. I am Using MFC to develop this application.
Now I am thinking to Use SQLite in Memory Mode , may be it help me to reduce
the Time.
I don't know much that How to use it memory mode. because the methode I am
using is taking more time than DISK mode. here is a small Application in
Which I was testing a DISK mode and Memory mode for INSERTION and SELECT.
can you guide me how to use it properly in memory mode.
I have tested this code on
P4, 3Ghz, 2 GB RAM , Windows XP Professional.
//******************************************************
#include "stdafx.h"
#include <conio.h>
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include "sqlite3.h"
int main(int argc, char *argv[])
{
char *database = "backup.db";
// char *database = ":memory:";
sqlite3 *db;
sqlite3_stmt *insert;
int samples = 3000000;
int i;
time_t bgn, end;
double t;
remove(database);
bgn = time(NULL);
sqlite3_open(database, &db);
sqlite3_exec(db, "PRAGMA temp_store=2", NULL, NULL, NULL);
sqlite3_exec(db, "PRAGMA synchronous=0", NULL, NULL, NULL);
sqlite3_exec(db, "PRAGMA count_changes=OFF", NULL, NULL, NULL);
sqlite3_exec(db, "pragma default_cache_size =65536", NULL, NULL,NULL);
sqlite3_exec(db, "pragma cache_size = 8192", NULL, NULL, NULL);
sqlite3_exec(db, "create table t (a integer, b float, c text,d integer,
e float, f text, g float, h text)", NULL, NULL, NULL);
sqlite3_exec(db, "begin transaction", NULL, NULL, NULL);
sqlite3_prepare(db, "insert into t values (?, ?, ?, ?, ?, ?, ? , ?)", -1,
&insert, NULL);
for (i = 0; i < samples; i++) {
sqlite3_bind_int(insert, 1, 44);
sqlite3_bind_double(insert, 2, 444.4);
sqlite3_bind_text(insert, 3, "two hundred", -1, SQLITE_STATIC);
sqlite3_bind_int(insert, 4, 55);
sqlite3_bind_double(insert, 5, 5.5);
sqlite3_bind_text(insert, 6, "two hundred", -1, SQLITE_STATIC);
sqlite3_bind_double(insert, 7, 66.6);
sqlite3_bind_text(insert, 8, "two hundred", -1, SQLITE_STATIC);
sqlite3_step(insert);
sqlite3_reset(insert);
}
// Insert Time check
//*********************************************************************
end = time(NULL);
sqlite3_exec(db, "commit transaction", NULL, NULL, NULL);
t = difftime(end, bgn);
printf("Executed %d inserts in %.0f seconds, %.0f inserts/sec\n",
samples, t, samples / t);
getch();
//*********************************************************************
// Select Time check
//*********************************************************************
bgn = time(NULL);
sqlite3_exec(db, "Select * from t", NULL, NULL, NULL);
end = time(NULL);
t = difftime(end, bgn);
printf("Select in %.0f seconds", t);
getch();
//*********************************************************************
sqlite3_close(db);
}
//******************************************************
DISK MODE
3000 000 INSERTS 31 Seconds 96774 INSERTS / Sec
"SELECT * from t" 5 Seconds.
MEMORY MODE
3000 000 INSERTS 53 Seconds 56604 INSERTS / Sec
"SELECT * from t" 5 Seconds.
Can I reduce the TIME of DISK mode or this is the limit.
WHY the Time in MEMORY mode is higher than DISK in case of INSERTION.
For the memory MODE I am just changing one statement is this the way to use
memory mode.
I changed char *database = "backup.db"; into
char *database = ":memory:";
I really Need Help.
Thanks in Advance.
TAMIMY
---------------------------------------------------------------------------
> I think that Derrell has already answered your question. What you
> are trying to do is inherently slow. You are doing a cross join of
> two large tables and counting the results. Depending upon which
> indexes you have defined (i.e. an index on IDC in either HVH or ITM
> tables), SQLite needs to scan one table from start to finish, and
> for each row in that table it uses an index to locate the matching
> rows in the other table.
>
> You can look at the output of the "explain query plan" command to
> see how your tables and indexes are being scanned.
>
> I suspect that this is a contrived tests case, and that it is not
> what you really need to get done. Can you explain what you are
> trying to do in more detail? For example, it looks like IDC is, or
> should be, the primary key for the ITM table, and that IDC is a
> foreign key in the HVH table. Is that true? How many records in the
> HVH table match each record in the ITM table; one, a few, or many?
> What information do you need to get from these tables (i.e. Is a
> count of the results what you are really after)?
>
> If you can explain what you are trying to do in more detail, someone
> here can probably help you to generate a more suitable query.
>
> HTH
> Dennis Cote
