RE: [sqlite] Sqlite3_reset needed?
Hi Richard, You should be in the habit of calling sqlite3_reset() on each query as soon as that query finishes. Otherwise the query *might* leave a read-lock on the database file and thus prevent subsequent write operations for working correctly. What about if one just uses sqlite3_exec ? The manual says that sqlite3_reset is only needed for SQL that was prepared. Thanks, David. -- --LongSig *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorized and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbos.com http://www.rbsmarkets.com
RE: [sqlite] REGEXP
Hi Ulrich, thanks for the answer but I need to implement a function regexp() for SQLite inside a program in C++. Look at boost: http://www.boost.org/libs/regex/doc/introduction.html Regards, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorized and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbos.com http://www.rbsmarkets.com
RE: [sqlite] Help needed to diagnose NOT IN query
Hello Ulrich, I don't know wether it's faster, but try select a,b,c from d where c in (select c from d except select c from e) Sorry to say but this made no difference :-( I'll try my method next... Thanks for your help, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT 135 Bishopsgate LONDON EC2M 3TP *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorized and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbos.com http://www.rbsmarkets.com
RE: [sqlite] File locking additions
Hi, I definitely like all the things you've proposed. We currently use SQLite over NFS (to a NetApp) and while it mostly works, we do occasionally get locking issues that I'd love to be able to get rid of. Do you run Solaris or Linux? Cheers, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT 135 Bishopsgate LONDON EC2M 3TP Tel: +44 (0) 207 085 1088 *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorized and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbos.com http://www.rbsmarkets.com
RE: [sqlite] Help needed to diagnose NOT IN query
Hi Brett, Many thanks for replying. Well I tried PRAGMA temp_store=memory and that sadly did not help. I'm going to adopt a different approach. Say I've got: select a,b,c from d where c NOT IN (select c from e) What I'm trying is: 1. select a,b,c from d into a temp1 table (as far as I can see SQLite can't do SELECT INTO, so I'll do it for it by creating the table and selecting and inserting the rows - please correct me if there's an easier way). 2. select c from e into a second temp2 table 3. delete from temp1 where temp1.c = temp2.c 4. the results left in temp1 will be the rows where c NOT IN (select c from e) [hopefully :-] I used to do this with Sybase back in the days when Sybase was really bad with NOT IN too - they seemed to improve on things around version 12 or 12.5... Thanks, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT 135 Bishopsgate LONDON EC2M 3TP Tel: +44 (0) 207 085 1088 -Original Message- From: Brett Wilson [mailto:[EMAIL PROTECTED] Sent: 14 March 2006 16:48 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Help needed to diagnose NOT IN query David, I asked a similar question, and here is drh's response to me, adapted to your situation. I think it is probably applicable to you as well. I'm not sure this is the type of query that can be made to go really fast no matter what. I actually haven't gotten around to doing this yet, so I can't comment on how well it works. To implement this, SQLite creates a temporary index that contains all unique values of holiday_list.number. Then it scans through the table and for each entry where holiday.hol_id is not in the temp index. Do make sure that your temp tables are stored in RAM. Compile with -DTEMP_STORE=2. Or at run-time set PRAGMA temp_store=memory; That will put the temporary index in RAM and things will tend to go faster. Brett On 3/14/06, CARTER-HITCHIN, David, FM [EMAIL PROTECTED] wrote: Hi, Hope someone out there can help - I've got this query which runs VERY slowly: SELECTab_tran.ins_num, holiday.hol_id, holiday.ins_num FROM ab_tran, holiday WHERE ab_tran.ins_num = holiday.ins_num AND holiday.hol_id NOT IN ( SELECT number from holiday_list ) All the tables are fairly chunky, except holiday_list which has 89 rows. I just realised that holiday.ins_num is needlessly selected but I doubt very much that would be causing things to slow down massively. I can run this on a similar powered Sybase server and it runs in a couple of minutes - with SQLite, using an in-memory database I've left it all night and it didn't finish! Evidently something is wrong somewhere. I've created indexes on all the columns in the join and the NOT IN clause, but that didn't seem to help. Is there anything else I check? I've run the EXPLAIN (output below), but I'm a novice here, not sure what could be wrong. Any ideas will be highly appreciated. Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:476 - 64 rows extracted in 0.03532 seconds. Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 0 Goto0 60 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 1 Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 2 OpenRead0 3 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 3 SetNumColumns 0 7 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 4 Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 5 OpenRead1 5 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 6 SetNumColumns 1 2 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 7 Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 8 OpenRead3 137675 keyinfo(1,BINARY) Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 9 Rewind 0 56 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 10 Column 0 1 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 11 NotNull -1 14 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 12Pop 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 13 Goto0 55 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 14 MakeRecord 1 0 i Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 15 MemStore0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 16 MoveGe 3 55 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 17 MemLoad 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 18 IdxGE 3 55 + Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 19 RowKey 3 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 20 IdxIsNull 1 54 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 21 IdxRowid3 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 22 MoveGe 1 0
RE: [sqlite] Help needed to diagnose NOT IN query
Hi Ulrich and Jay S. I don't know wether it's faster, but try select a,b,c from d where c in (select c from d except select c from e) Maybe you're lucky and it's faster. Thanks - I'll give IN a go, instead of NOT IN Cheers, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT 135 Bishopsgate LONDON EC2M 3TP *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorized and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbos.com http://www.rbsmarkets.com
[sqlite] Help needed to diagnose NOT IN query
Hi, Hope someone out there can help - I've got this query which runs VERY slowly: SELECTab_tran.ins_num, holiday.hol_id, holiday.ins_num FROM ab_tran, holiday WHERE ab_tran.ins_num = holiday.ins_num AND holiday.hol_id NOT IN ( SELECT number from holiday_list ) All the tables are fairly chunky, except holiday_list which has 89 rows. I just realised that holiday.ins_num is needlessly selected but I doubt very much that would be causing things to slow down massively. I can run this on a similar powered Sybase server and it runs in a couple of minutes - with SQLite, using an in-memory database I've left it all night and it didn't finish! Evidently something is wrong somewhere. I've created indexes on all the columns in the join and the NOT IN clause, but that didn't seem to help. Is there anything else I check? I've run the EXPLAIN (output below), but I'm a novice here, not sure what could be wrong. Any ideas will be highly appreciated. Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:476 - 64 rows extracted in 0.03532 seconds. Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 0 Goto0 60 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 1 Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 2 OpenRead0 3 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 3 SetNumColumns 0 7 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 4 Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 5 OpenRead1 5 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 6 SetNumColumns 1 2 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 7 Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 8 OpenRead3 137675 keyinfo(1,BINARY) Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 9 Rewind 0 56 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 10Column 0 1 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 11NotNull -1 14 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 12Pop 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 13Goto0 55 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 14MakeRecord 1 0 i Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 15MemStore0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 16MoveGe 3 55 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 17MemLoad 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 18IdxGE 3 55 + Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 19RowKey 3 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 20IdxIsNull 1 54 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 21IdxRowid3 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 22MoveGe 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 23MemLoad 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 24If 0 40 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 25MemInt 1 1 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 26OpenVirtual 4 0 keyinfo(1,BINARY) Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 27SetNumColumns 4 1 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 28Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 29OpenRead2 6 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 30SetNumColumns 2 1 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 31Rewind 2 39 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 32Column 2 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 33NotNull -1 36 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 34Pop 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 35Goto0 38 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 36MakeRecord 1 0 i Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 37IdxInsert 4 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 38Next2 32 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 39Close 2 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 40Integer 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 41Column 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 42NotNull -1 46 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 43Pop 2 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 44Null0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 45Goto0 49 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 46MakeRecord 1 0 i Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 47Found 4 49 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 48AddImm -1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 49If 1 54 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 50Column 0 1 Tue Mar 14 12:05:10 2006 DEBUG at
RE: [sqlite] SQLite -- Very Slow -- ??
I had similar issues with inserts and solved them by wrapping the insert
into a transaction.
Regards,
David Carter-Hitchin.
--
Royal Bank of Scotland
Interest Rate Derivatives IT
135 Bishopsgate
LONDON EC2M 3TP
Tel: +44 (0) 207 085 1088
-Original Message-
From: khaldoun kassem [mailto:[EMAIL PROTECTED]
Sent: 13 January 2006 11:00
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite -- Very Slow -- ??
Hi To All
I am new to the list
In fact i had a logging system (to log errors and messages),
for my application using text file, but its very slow.
I tried SQlite, and its slower than the text file, is it normal??
I do the folowing :
//Creating the db file
int rc;
rc = sqlite3_open(sFileName, m_pSqliteDb);
if( rc ){
sqlite3_close(m_pSqliteDb);
m_pSqliteDb=NULL;
return;
}
rc = sqlite3_exec(m_pSqliteDb,CREATE TABLE LOGS(L_ID
INTEGER PRIMARY
KEY,L_PROCESS INTEGER, L_THREAD INTEGER,L_MESSAGE TEXT,L_DATE
TIMESTAMP),
0, 0, 0);
adding a log:
rc = sqlite3_exec(m_pSqliteDb,INSERT INTO
LOGS(L_PROCESS,L_THREAD,L_MESSAGE,L_DATE)
VALUES(nProc,nThread,'sMessage',date('now')), 0, 0, 0);
it works, but its very slow?
is this normal
Thank you
Kh.K
***
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered
Office: 36 St Andrew Square, Edinburgh EH2 2YB.
Authorised and regulated by the Financial Services Authority
This e-mail message is confidential and for use by the
addressee only. If the message is received by anyone other
than the addressee, please return the message to the sender
by replying to it and then delete the message from your
computer. Internet e-mails are not necessarily secure. The
Royal Bank of Scotland plc does not accept responsibility for
changes made to this message after it was sent.
Whilst all reasonable care has been taken to avoid the
transmission of viruses, it is the responsibility of the recipient to
ensure that the onward transmission, opening or use of this
message and any attachments will not adversely affect its
systems or data. No responsibility is accepted by The Royal
Bank of Scotland plc in this regard and the recipient should carry
out such virus and other checks as it considers appropriate.
Visit our websites at:
http://www.rbs.co.uk/CBFM
http://www.rbsmarkets.com
RE: [sqlite] SQLite -- Very Slow -- ??
Yes - although I executed each of those statements individually, e.g.:
Exec SQL1: begin tran
Exec SQL2: insert into...
Exec SQL3: commit tran (if rc good from SQL2) or rollback tran otherwise.
Cheers,
David Carter-Hitchin.
--
Royal Bank of Scotland
Interest Rate Derivatives IT
135 Bishopsgate
LONDON EC2M 3TP
Tel: +44 (0) 207 085 1088
-Original Message-
From: Manfred Bergmann [mailto:[EMAIL PROTECTED]
Sent: 13 January 2006 11:28
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite -- Very Slow -- ??
What do you mean by wrapping into a transaction?
Doing something like this? :
BEGIN TRANSACTION;
exec Sql-Statement
COMMIT TRANSACTION;
regards,
Manfred Bergmann
Am 13.01.2006 um 22:15 schrieb CARTER-HITCHIN, David, FM:
I had similar issues with inserts and solved them by wrapping the
insert
into a transaction.
Regards,
David Carter-Hitchin.
--
Royal Bank of Scotland
Interest Rate Derivatives IT
135 Bishopsgate
LONDON EC2M 3TP
Tel: +44 (0) 207 085 1088
-Original Message-
From: khaldoun kassem [mailto:[EMAIL PROTECTED]
Sent: 13 January 2006 11:00
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite -- Very Slow -- ??
Hi To All
I am new to the list
In fact i had a logging system (to log errors and messages),
for my application using text file, but its very slow.
I tried SQlite, and its slower than the text file, is it normal??
I do the folowing :
//Creating the db file
int rc;
rc = sqlite3_open(sFileName, m_pSqliteDb);
if( rc ){
sqlite3_close(m_pSqliteDb);
m_pSqliteDb=NULL;
return;
}
rc = sqlite3_exec(m_pSqliteDb,CREATE TABLE LOGS(L_ID
INTEGER PRIMARY
KEY,L_PROCESS INTEGER, L_THREAD INTEGER,L_MESSAGE TEXT,L_DATE
TIMESTAMP),
0, 0, 0);
adding a log:
rc = sqlite3_exec(m_pSqliteDb,INSERT INTO
LOGS(L_PROCESS,L_THREAD,L_MESSAGE,L_DATE)
VALUES(nProc,nThread,'sMessage',date('now')), 0, 0, 0);
it works, but its very slow?
is this normal
Thank you
Kh.K
**
*
The Royal Bank of Scotland plc. Registered in Scotland No 90312.
Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.
Authorised and regulated by the Financial Services Authority
This e-mail message is confidential and for use by the
addressee only. If the message is received by anyone other
than the addressee, please return the message to the sender
by replying to it and then delete the message from your
computer. Internet e-mails are not necessarily secure. The
Royal Bank of Scotland plc does not accept responsibility for
changes made to this message after it was sent.
Whilst all reasonable care has been taken to avoid the
transmission of viruses, it is the responsibility of the
recipient to
ensure that the onward transmission, opening or use of this
message and any attachments will not adversely affect its
systems or data. No responsibility is accepted by The Royal
Bank of Scotland plc in this regard and the recipient should carry
out such virus and other checks as it considers appropriate.
Visit our websites at:
http://www.rbs.co.uk/CBFM
http://www.rbsmarkets.com
**
**
___
Telefonate ohne weitere Kosten vom PC zum PC:
http://messenger.yahoo.de
***
The Royal Bank of Scotland plc. Registered in Scotland No 90312.
Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.
Authorised and regulated by the Financial Services Authority
This e-mail message is confidential and for use by the
addressee only. If the message is received by anyone other
than the addressee, please return the message to the sender
by replying to it and then delete the message from your
computer. Internet e-mails are not necessarily secure. The
Royal Bank of Scotland plc does not accept responsibility for
changes made to this message after it was sent.
Whilst all reasonable care has been taken to avoid the
transmission of viruses, it is the responsibility of the recipient to
ensure that the onward transmission, opening or use
RE: [sqlite] SQLite -- Very Slow -- ??
2 - to easily manipulate log files to detect abnormal behaviour
You might want to check out Splunk - we don't run it here, but I've heard
good things about it - it may well be able to do what you're looking for.
www.splunk.com
Cheers,
David Carter-Hitchin.
--
Royal Bank of Scotland
Interest Rate Derivatives IT
135 Bishopsgate
LONDON EC2M 3TP
Tel: +44 (0) 207 085 1088
-Original Message-
From: khaldoun kassem [mailto:[EMAIL PROTECTED]
Sent: 13 January 2006 11:42
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite -- Very Slow -- ??
Hi
What is slow or slower?
When I run the program without logs an action take 1ms, with
logs inserted
in a text file it takes 1.2ms so its SLOW, and with logs
inserted in a
SQLite DB it takes 1.4ms so its SLOWER than the text file.
What is the reason for using database in the first place
(do you need to
select information?)
I Try tu use database for 2 reasons :
1 - because the text file logs was slow, and I thought that
database is
faster than the text file.
2 - to easily manipulate log files to detect abnormal behaviour
but I cannot see how anyone will be able to give you any
useful answer
If you cannot, you cannot said that others cannot.
thank you
On 1/13/06, CARTER-HITCHIN, David, FM
[EMAIL PROTECTED] wrote:
I had similar issues with inserts and solved them by
wrapping the insert
into a transaction.
Regards,
David Carter-Hitchin.
--
Royal Bank of Scotland
Interest Rate Derivatives IT
135 Bishopsgate
LONDON EC2M 3TP
Tel: +44 (0) 207 085 1088
-Original Message-
From: khaldoun kassem [mailto:[EMAIL PROTECTED]
Sent: 13 January 2006 11:00
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite -- Very Slow -- ??
Hi To All
I am new to the list
In fact i had a logging system (to log errors and messages),
for my application using text file, but its very slow.
I tried SQlite, and its slower than the text file, is it normal??
I do the folowing :
//Creating the db file
int rc;
rc = sqlite3_open(sFileName, m_pSqliteDb);
if( rc ){
sqlite3_close(m_pSqliteDb);
m_pSqliteDb=NULL;
return;
}
rc = sqlite3_exec(m_pSqliteDb,CREATE TABLE LOGS(L_ID
INTEGER PRIMARY
KEY,L_PROCESS INTEGER, L_THREAD INTEGER,L_MESSAGE TEXT,L_DATE
TIMESTAMP),
0, 0, 0);
adding a log:
rc = sqlite3_exec(m_pSqliteDb,INSERT INTO
LOGS(L_PROCESS,L_THREAD,L_MESSAGE,L_DATE)
VALUES(nProc,nThread,'sMessage',date('now')), 0, 0, 0);
it works, but its very slow?
is this normal
Thank you
Kh.K
**
*
The Royal Bank of Scotland plc. Registered in Scotland No
90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.
Authorised and regulated by the Financial Services Authority
This e-mail message is confidential and for use by the
addressee only. If the message is received by anyone other
than the addressee, please return the message to the sender
by replying to it and then delete the message from your
computer. Internet e-mails are not necessarily secure. The
Royal Bank of Scotland plc does not accept responsibility for
changes made to this message after it was sent.
Whilst all reasonable care has been taken to avoid the
transmission of viruses, it is the responsibility of the
recipient to
ensure that the onward transmission, opening or use of this
message and any attachments will not adversely affect its
systems or data. No responsibility is accepted by The Royal
Bank of Scotland plc in this regard and the recipient should carry
out such virus and other checks as it considers appropriate.
Visit our websites at:
http://www.rbs.co.uk/CBFM
http://www.rbsmarkets.com
**
**
--
Cordialement
Khaldoun KASSEM
***
The Royal Bank of Scotland plc. Registered in Scotland No 90312.
Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.
Authorised and regulated by the Financial Services Authority
This e-mail message is confidential and for use by the
addressee only. If the message is received by anyone other
than the addressee, please return the message to the sender
by replying to it and then delete the message from your
computer. Internet e-mails are not necessarily secure. The
Royal Bank of Scotland plc does not accept responsibility for
changes made to this message after it was sent
[sqlite] Slowness with in-memory database
Hi,
Really hope someone out there can help me with this.
I've written a C++ cache class which extracts ad-hoc data from a Sybase
database and inserts it into an in-memory SQLite database. This is working
fine but is very slow during the insert data phase. To give some
quantitative idea of what I mean by slow:
a) If I run the extract with the Sybase isql command line utility and dump
that to a file it takes 2/3 minutes (~800,000 rows, 100MB of data).
b) If I run my cache class with the insert into SQLite commented out it
takes 2/3 minutes.
c) Uncommenting the insert statement, the extract/insert takes ~45 mins!
Something is misconfigured as it takes ~20 times as long to insert into an
in-memory db, as it does to select from Sybase and store to a file. I had a
look through the docs and had a google, and came up with some PRAGMA
options, so I tried:
PRAGMA synchronous = OFF
PRAGMA temp_store = MEMORY
Sadly, these made no difference. Does anyone have any idea what is causing
this? Code is running on Solaris 8 on a Sun workstation 1GB of memory 2x
450MHz CPU, SQLite version 3.2.7. I'm opening the database and calling
sqlite3_exec in a pretty standard way:
// Open the database:
sqlite3 *cache;
rc = sqlite3_open(:memory:, cache)
if ( rc )
{
fprintf(stderr, Can't open database: %s\n, sqlite3_errmsg(cache));
sqlite3_close(cache);
exit(1);
} else {
// set the database params
string sql = PRAGMA synchronous = OFF\n;
sql += PRAGMA temp_store = MEMORY\n;
rc = sqlite3_exec(cache, sql.c_str(), NULL, 0, zErrMsg);
if ( rc != SQLITE_OK )
{
fprintf(stderr, SQL error: %s\nSQL Text: %s, zErrMsg,
sql.c_str());
}
}
// Create some tables = called once per table:
rc = sqlite3_exec(cache, create_sql.c_str(), NULL, 0, zErrMsg);
if ( rc != SQLITE_OK )
{
fprintf(stderr, SQL error: %s\nSQL text: %s\n, zErrMsg,
create_sql.c_str());
}
// Then the insert - called 800,000 times for the extract that takes 45
minutes :
rc = sqlite3_exec(cache, populate_sql.c_str(), NULL, 0, zErrMsg);
if ( rc != SQLITE_OK )
{
fprintf(stderr, SQL error: %s\nSQL text: %s\n\n, zErrMsg,
populate_sql.c_str());
}
Any ideas, however slight, will be much appreciated!
Many thanks,
David Carter-Hitchin.
--LongSig
***
The Royal Bank of Scotland plc. Registered in Scotland No 90312.
Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.
Authorised and regulated by the Financial Services Authority
This e-mail message is confidential and for use by the
addressee only. If the message is received by anyone other
than the addressee, please return the message to the sender
by replying to it and then delete the message from your
computer. Internet e-mails are not necessarily secure. The
Royal Bank of Scotland plc does not accept responsibility for
changes made to this message after it was sent.
Whilst all reasonable care has been taken to avoid the
transmission of viruses, it is the responsibility of the recipient to
ensure that the onward transmission, opening or use of this
message and any attachments will not adversely affect its
systems or data. No responsibility is accepted by The Royal
Bank of Scotland plc in this regard and the recipient should carry
out such virus and other checks as it considers appropriate.
Visit our websites at:
http://www.rbs.co.uk/CBFM
http://www.rbsmarkets.com
RE: [sqlite] Slowness with in-memory database
Hi Richard et al., 1. It's not thrashing - I've watched the process grow with prstat -cp `pgrep gti` and it doesn't top 100MB - as I said I've got 1GB of RAM in my workstation, with 2.5GB of free swapspace. 2/3. There are no triggers or indexes on the table - they are very simple and vanilla tables, with simple datatypes - just int's and varchar's. Donald Griggs and Jay Sprenkle have suggested transactions, which I had not been using, and I've wrapped the all 800,000 iterations into one tran and that has reduced the time down to 6 minutes - thanks Donald and Jay!! Problem fixed - it's now taking as much time to ship the data into memory as it does to ship it out of Sybase - I'm happy enough about that. I guess it could be optimised more, but this is ok. I've just read your next post about transactions viz-a-viz in-memory db's - well I see where you're coming from, but the evidence is clearly in favour of transactions here. My other thought is that opening the db with a filehandle of :memory: is perhaps not 100% correct - that it's somehow sneakily going off to disk somewhere - can anyone say? Thanks, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT 135 Bishopsgate LONDON EC2M 3TP Tel: +44 (0) 207 085 1088 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 21 December 2005 14:54 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Slowness with in-memory database CARTER-HITCHIN, David, FM [EMAIL PROTECTED] wrote: Hi, Really hope someone out there can help me with this. I've written a C++ cache class which extracts ad-hoc data from a Sybase database and inserts it into an in-memory SQLite database. This is working fine but is very slow during the insert data phase. To give some quantitative idea of what I mean by slow: a) If I run the extract with the Sybase isql command line utility and dump that to a file it takes 2/3 minutes (~800,000 rows, 100MB of data). b) If I run my cache class with the insert into SQLite commented out it takes 2/3 minutes. c) Uncommenting the insert statement, the extract/insert takes ~45 mins! Something is misconfigured as it takes ~20 times as long to insert into an in-memory db, as it does to select from Sybase and store to a file. Any ideas, however slight, will be much appreciated! Idea 1: You are trashing. What OS are you running and how much memory is installed in the machine. Idea 2: Might you be doing an insert trigger that is taking a long time? Idea 3: You have about 100 different indices on the table that all have to be updated with each insert. FWIW, SQLite normally does about 5 inserts per second, not the 300 you are seeing. So something is clearly wrong. -- D. Richard Hipp [EMAIL PROTECTED] *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorised and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbs.co.uk/CBFM http://www.rbsmarkets.com
