[sqlite] SQLite index Idea for DBF

[Manzoor Ilahi Tamimy]

Hi All,

I want  to create index in “dbf” using SQLite index idea.

I will give a little detail of my project. 
I am using SQLite for one of my project. I have dbf files with 30 million or 
50 million records. And at the first stage I tried to  covert the data 
in “db” and then apply different operations. 

But because of the slow performance in case of converting data into dbf to db 
I changed the design and now I did some modifications and I am directly 
reading the data from dbf files.  

So now I want to create index in “dbf” using SQLite index Idea. 

Can anyone guide me how to start.
 
Regards,

Manzoor Ilahi

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLite with Index

[Manzoor Ilahi Tamimy]

Dear All,

I have question about Index.

When we create an index  on a table then how SQLite manage it. Whether SQLite 
uses Hashing or some other technique to handle index.

Can some one guide me where can I find the details working process of SQLIte 
when we create Index. 


Best regards, 

Manzoor Ilahi Tamimy



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Attach db code

[Manzoor Ilahi Tamimy]

Hi All,

Can someone give me the code you use to attach a database.
I have a db file with 5 Million records and have three different tables. 
Index is also created in it. 

I want to load it in memory to apply some queries for testing. 

Best regards, 

Manzoor Ilahi Tamimy

[sqlite] Query Execution speed.

[Manzoor Ilahi Tamimy]

hi All,

I have to use SQLite for one of my project as ":memory:" db.

//
-
Can I get a better speed if I change or omit some macros. 
I saw  " http://www.sqlite.org/compile.html ". the macros defined here can 
only resulting in a smaller compiled library size or they can also improve 
some speed?

we can also Override these macros through PRAGMA statements.
Is there any difference between when we handle these macros directly or 
override through PRAGMA.

which PRAGMA statements can improve the query speed. 
the main queries are, INSERT and SELECT with joins.

I have just tried the following PRAGMA

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); 

can someone guide me which PRAGMA statements are useful for speed improvement 
and what values I need to set for those.
like  "pragma default_cache_size =?"

I have a 4G Physical Memory.
//
-
one last thing, I have tested the code written by "Dennis Cote"
Wed, 03 May 2006 " performance v2 and V3 "

I found that Version 2.8 was much Faster than 3.6 using ":memory:", 30 
Seconds and 60 seconds in case of 2.8 and 3.6 respectively.
can I use 2.8 in my project when i have a huge amount of data to handle.

Thanks 
Regards,

Manzoor Ilahi Tamimy

Re: [sqlite] SQLite performance for 10 Million Records

[Manzoor Ilahi Tamimy]

Hi,

I am really really thankful to all the members of this group. The Discussion 
here was really very helpful for me and also for the others. 

I was not as much experienced as the other members who took part in this 
dicussion, but i worked hard ad spent a lot of time to find out why i am 
getting ":memory:" mode slower or sometime no change in results or sometime 
only little faster may be one second at most. 

Now I think I am in the position to just leave thinking about to use SQLite 
in memory mode. 

I did some tests in the past and also showed results here to you all. in 
which, memory  mode was slower than disk mode. 

now again i did some tests 
1  Disk mode
2  Memory mode
3  ATTACH DB

these tests were as I mentioned earlier, tested at 
Pentium(R) D CPU 3.00GHz
2.00 GB of RAM 
WINXP Professional.

For All these tests I was Using SQLite 3.3.5, but now when I saw that in 
version 3.3.6 there is some improvement in :memory: then I did some tests 
again, but the results are not satisfactory.

As 'Dennis Cote', 'Joe Wilson', 'Martin' mentioned that "there is another 
factor at work here", I wish to participate in this discussion but I can't 
because of my little knowledge about the SQLite working.

But I wish, and I hope to find out the way , where we can see a clear 
difference between DISK and :memory: mode.

Dear Dennis Cote, 

As u suggested me to change my design, so Now I changed it. and its much 
better now.

Again really thanful to to all the members.

but if you all think that still there is some way then I will be happy to 
know about. 

Regards

TAMIMY. 

Re: [sqlite] SQLite performance for 10 Million Records

[Manzoor Ilahi Tamimy]

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 
#include 
#include 
#include 

#include "sqlite3.h"
int main(int argc, char *argv[])
{
char *database = "backup.db";
//  char *database = ":memory:";
sqlite3 *db;
sqlite3_stmt *insert;
int samples = 300;
int i;
time_t bgn, end;
double t;

remove(database);
bgn = time(NULL);
sqlite3_open(database, );
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, 
, 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 

Re: [sqlite] SQLite performance for 10 Million Records

[Manzoor Ilahi Tamimy]

Here Is The Schema For these Tables. 

CREATE TABLE HVH (
Field1 VARCHAR(8),  IDC  VARCHAR(4), 
Field3 VARCHAR(2),  Field4 VARCHAR(4), 
Field5 VARCHAR(7),  Field6 VARCHAR(8), 
Field7 VARCHAR(1),  Field8 FLOAT);

CREATE TABLE ITM(
IDC  VARCHAR(4),ITEMNAME VARCHAR(20),
COLUMN3  VARCHAR(1),COLUMN4   VARCHAR(1),
COLUMN5  VARCHAR(1),COLUMN6   VARCHAR(1),
COLUMN7  VARCHAR(1),COLUMN8   VARCHAR(1),
COLUMN9  VARCHAR(1),COLUMN10  VARCHAR(1),
COLUMN11 VARCHAR(1),COLUMN12  VARCHAR(1),
COLUMN13 VARCHAR(1),COLUMN14  VARCHAR(1),
COLUMN15 VARCHAR(1),COLUMN16  VARCHAR(1));

CREATE INDEX index1 ON ITM (IDC);


//
TEST 1  ( Disk DB )

Table Names itm , HVH   
Number of Records :  itm   100,000 and HVH   less than 10,000

QUERY:
Select count(*) from itm, HVH where  itm .IDC = HVH.IDC
Elapse Time  : 54.359  (Sec)

//

TEST 2 ( Disk DB )

Table Names itm , HVH   
Number of Records :  itm   5 Million  and HVH   less than 
10,000

QUERY:
create index index1 on itm(IDC) 
Elapse Time  : 0.356  (Sec)


Select count(*) from itm,HVH where  itm.IDC=HVH.IDC
Elapse Time  : 64   (Sec)

//

TEST 3 ( Disk DB )

Table Names itm 
Number of Records :  itm   20 Million  

QUERY:
Select count(*) from itm 
Elapse Time  : 133  (Sec)


//

Where need chnge?.


Regards

TAMIMY


On Mon, 19 Jun 2006 12:38:32 -0400, Derrell.Lipman wrote
> "Manzoor Ilahi Tamimy" <[EMAIL PROTECTED]> writes:
> 
> > //
> > TEST 1  ( Disk DB )
> >
> > Table Names itm , HVH   
> > Number of Records :  itm -->  100,000 and HVH -- >  less than 10,000
> >
> > QUERY:
> > Select count(*) from itm, HVH where  itm .IDC = HVH.IDC
> > Elapse Time  : 54.359  (Sec)
> 
> The count() function is always slow, I think, because no index can 
> be used; it must actually count the number of records.  I suspect, 
> though, that your problem is missing indexes.  Is there an implicit 
> or explicit index on itm.IDC?  Is there an implicit or explicit 
> index on HVH.IDC?  With query times like you're seeing, I'd assume not.
> 
> If you post your schema along with the queries you want to issue,
>  many people here will likely provide suggestions for improvement.
> 
> Derrell

Re: [sqlite] SQLite performance for 10 Million Records

[Manzoor Ilahi Tamimy]

Dear ALL,

I am really thankful to  Bill King, Micha Bieber , Derrell for your valuable 
suggestions.

I was really confused that which way should I follow now, because I was sure 
that SQLite will work much better. when I got the suggestion about Firebird 
then again I went to the comparison page of SQLite and found that Firebird 
was also slow in many cases compared to SQLite. 

>From the Derrell’s reply again I got hope that yes I can achieve that using 
SQLite.

Now I want to add some more information about my work may be you will add 
some suggestions. 

//
TEST 1  ( Disk DB )

Table Names itm , HVH   
Number of Records :  itm   100,000 and HVH   less than 10,000

QUERY:
Select count(*) from itm, HVH where  itm .IDC = HVH.IDC
Elapse Time  : 54.359  (Sec)

//

TEST 2 ( Disk DB )

Table Names itm , HVH   
Number of Records :  itm   5 Million  and HVH   less than 10,000

QUERY:
create index index1 on itm(IDC) 
Elapse Time  : 0.356  (Sec)


Select count(*) from itm,HVH where  itm.IDC=HVH.IDC
Elapse Time  : 64   (Sec)

//

TEST 3 ( Disk DB )

Table Names itm 
Number of Records :  itm   20 Million  

QUERY:
Select count(*) from itm 
Elapse Time  : 133  (Sec)


//
 
Now Please have look at these results. What should I do now to improve the 
performance.
 Thanks and regards 
Manzoor Ilahi Tamimy



On Mon, 19 Jun 2006 09:47:03 -0400, Derrell.Lipman wrote
> Bill King <[EMAIL PROTECTED]> writes:
> 
> > Manzoor Ilahi Tamimy wrote:
> >
> >> We are Using SQLite for one of our project.
> >>
> >>The Database Size is  more than 500 MB.
> >>It contain one table and about 10 million Records.
> 
> > Err, for that size, I'd recommend going something heavier, like
> > firebird. This is not sqlite's solution domain in the slightest.
> 
> I'd have to differ on opinion here.  I have an sqlite database 
> that's at 6.9GB with about 40 million records, and it's working just 
> fine.  My query speed has not changed substantially as the database 
> has grown.
> 
> With sqlite, you *do* need to hand-optimize your queries.  Determine 
> which queries are slow, and consider splitting them into multiple 
> queries which generate temporary tables.  By doing this, you can 
> create indexes, as appropriate, on the temporary tables that make 
> the overall time to accomplish your goal much less than cramming it 
> all into a single query that is not so highly optimized.
> 
> Oh, and I'm using the old sqlite 2.8 series.  I expect I'd be 
> getting even better speed if I used the newer 3 series.
> 
> Cheers,
> 
> Derrell

[sqlite] SQLite performance for 10 Million Records

[Manzoor Ilahi Tamimy]

Hello All,

We are Using SQLite for one of our project. 

The Database Size is  more than 500 MB.
It contain one table and about 10 million Records.

We are facing Problem in the select with single Join. The join is between a 
big table and a small table. The small table contain records not more than 
10,000. 

We are using a disk base db. But the performance is very slow.

Any body can guide me, which mode should I use to improve performance for a 
huge amount of records. 

If  memory mode is a better choice then , please guide me a little that how 
to use it. 

Any special parameters for using SQLite in memory mode.

Thanks.

Manzoor ILahi

RE: [sqlite] Pragmas

[Manzoor Ilahi Tamimy]

Dear Anish

I am using the same
---
 sqlite3_open(database, );
 sqlite3_exec(db, "PRAGMA page_size=4096", NULL, NULL, NULL);
sqlite3_exec(db, "create table t...
---

and its working the only file included is (#include "sqlite3.h")

regards,

TAMIMY


- Original Message - 
From: "Anish Enos Mathew" <[EMAIL PROTECTED]>
To: 
Sent: Monday, May 22, 2006 3:49 PM
Subject: RE: [sqlite] Pragmas


>
> Hi,
>   I used the following command for setting the page size.
>
>   PRAGMA page_size = 4096; before the table is created.
>
> But it is showing an error, "PRAGMA undeclared". What could be the
> problem? Do I need to include any header file for it?
>
> -Original Message-
> From: Nemanja Corlija [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 22, 2006 1:12 AM
> To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> Subject: Re: [sqlite] Pragmas
>
> On 5/21/06, Unit 5 <[EMAIL PROTECTED]> wrote:
>> I am a bit confused on how to change the page_size
>> using the pragma command.  The documentation says:
>>
>> "The page-size may only be set if the database has not
>> yet been created."
>
> The database is created when you create first table in it. Run the
> pragma before your first CREATE TABLE query.

[sqlite] HELP for SQLite MEMORY test.

[Manzoor Ilahi Tamimy]

DEAR All ,
   
   I want to use SQLite for our project, the main thing is that the 
   database contains millions of Records. So for the faster 
 operations   on the db I want to use the SQLite as in-memory database.
 
   I have compared the results of SQLite as Disk db and as Memory db 
   but I am not getting much difference. I am surprised that there 
  must  be some difference between memory mode and disk mode.
 
   Tests were run on 2.4GHz Sempron with 1GB of RAM and running 
  Windows  XP + SP2 with all updates applied. Test 20 : 100 
  INSERTs CREATE  TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100)); 
  INSERT INTO t1  VALUES(1,13153,'thirteen thousand one hundred fifty 
  three');
   
   In Memory 37.51 Sec 
   DISK BASED 39.76 Sec 
   Disk Space consumed 61.6 MB 
   
 
   Test 21`: 300 INSERTs
   In Memory 141.79 Sec 
   DISK BASED 111.906000 Sec 
   Disk Space consumed 185 MB
   
 
   Test 22: 500 INSERTs
 
   In Memory 279.42 Sec
   DISK BASED 201.266000 Sec 
   Disk Space consumed 308 MB
   Test 23: 1000 INSERTs
   
 
   In Memory 784.797000 Sec 
   DISK BASED 399.846000 Sec
   Disk Space consumed 617 MB
   
 
   I am testing it in the following way
   
 
   dwStart = GetTickCount();
   db.execDML("begin transaction;");
   db.execDML("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))
 ;") ; for( int i=1;i<=1000;i++) db.execDML("INSERT INTO t1 
 VALUES(1, 298361,'two hundred ninety eight  thousand three hundred')
 ");  db.execDML("commit Transaction") ;  dwStop = GetTickCount();
 
   
   I think I am missing something or some necessary parameters. I 
  spent  a lot of time to find out the problem. Please Guide me. I 
  will be  really thankful.
 
   Regards,
 
   MANZOOR ILAHI
--
COMSATS Institute of Information Technology (http://www.ciit.edu.pk)