Manzoor Ilahi Tamimy wrote:
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
Tamimy,
It's nice to see my test program making the rounds. ;-)
This is a modified version of the test code I posted to show that there
was a small but definite SLOWDOWN when using :memory: databases compared
to a database in a file on disk. It seems strange, but it is true. Use a
disk file for best speed.
You have a couple of problems with your modifications that make your
results a little misleading.
First, you are including the execution time of the pragmas and the
create table statements in your timing of the inserts. The bgn=time()
statement should be moved until it is just before the "begin
transaction" is executed.
Second, you stop timing the inserts before you have done the commit. The
commit is where the I/O operations are completed (i.e. buffers are
flushed and the journal file is deleted). This time should be included,
so you should move your end=time() statement after the "commit
transaction" is executed.
Finally, you are not actually retrieving any data from the database
during your select. You have not passed a callback function pointer to
sqlite3_exec(), so it never returns any results to your application. If
you are not going to read any results you might as well not perform the
select, and the execution time will be zero.
I would recommend replacing the sqlite3_exec() call with a
prepare/step/finalize loop in which you retrieve the result columns into
local variables. The loop below should work (note, this code is untested
and so may contain errors).
sqlite3_stmt *select;
int rc;
// vars to hold results.
int a;
double b;
char *c = NULL;
int c_sz = 0;
int d;
double e;
char *f = NULL;
int f_sz = 0;
double g;
char *h = NULL;
int h_sz = 0;
bgn = time(NULL);
sqlite3_prepare(db, "select * from t", -1, &select, NULL);
do {
rc = sqlite3_step(select);
if (rc == SQLITE_ROW) {
// retrieve results
a = sqlite3_column_int(select, 1);
b = sqlite3_column_double(select, 2);
read_string(select, 3, &c, &c_sz);
d = sqlite3_column_int(select, 4);
e = sqlite3_column_double(select, 5);
read_string(select, 6, &f, &f_sz);
g = sqlite3_column_double(select, 7);
read_string(select, 8, &h, &h_sz);
}
} while (rc != SQLITE_DONE);
sqlite3_finalize(select);
end = time(NULL);
release_string(&c, &c_sz);
release_string(&f, &f_sz);
release_string(&h, &h_sz);
It uses the following functions to read the string values into
dynamically allocated variable sized buffers, and to free those buffers.
void read_string(sqlite3_stmt *qry, int col, char **str, int *len)
{
// ensure buffer is large enough to hold data
int size = sqlite3_column_bytes(qry, col);
if (size >= *len) {
*str = realloc(*str, size + 1); // extra space for terminal nul
if (!*str) {
fprintf(stderr, "Out of memory.\n");
exit();
}
*len = size + 1;
}
// copy the data
memcpy(*str, sqlite3_column_text(qry, col), size);
// add terminal nul
(*str)[size] = 0;
}
void release_string(char **str, int *len)
{
free(*str);
*str = NULL;
*len = 0;
}
One last thing. You said "I have to display this huge data in Grid". I
don't think you really want to put 50 million records into a grid for
display to a user. There is no way they can make use of that data. You
may want to display small subsets of the data, but 50 million rows are
simply too much for a human to deal with. You should probably review
your design in this regard.
HTH
Dennis Cote
