[sqlite] HELP! DDD & SQLite
Hello I'm trying to debug SQLite (to understand the code). But e when I attach the process sqlite3, the sqlite3 terminal hangs (ie would not accept any user inputs) till I detach. Can someone please tell me what I'm doing wrong Thanks Uma Cory Nelson <[EMAIL PROTECTED]> wrote: On 9/7/07, Yves Goergen wrote: > Hi, > > in a scenario when multiple operations need to be transactionally > synchronised, I have a file that must be deleted when the database > records are added successfully, but the database operations must be > rolled back, if the file cannot be deleted. > > I'm currently using a transaction for this on the database side and > rolling it back if the file cannot be deleted. But what if the file is > gone and then SQLite says it doesn't accept my records? Since we're > inside a transaction, integrity checks should be deferred until a > COMMIT. Is there a way to tell whether the COMMIT will succeed under the > current conditions so that I can safely delete the file? My understanding is that if your first insert succeeds you hold a write lock on the table and barring any exceptional errors a commit should always succeed. -- Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can I simulate a COMMIT?
On 9/7/07, Yves Goergen <[EMAIL PROTECTED]> wrote: > Hi, > > in a scenario when multiple operations need to be transactionally > synchronised, I have a file that must be deleted when the database > records are added successfully, but the database operations must be > rolled back, if the file cannot be deleted. > > I'm currently using a transaction for this on the database side and > rolling it back if the file cannot be deleted. But what if the file is > gone and then SQLite says it doesn't accept my records? Since we're > inside a transaction, integrity checks should be deferred until a > COMMIT. Is there a way to tell whether the COMMIT will succeed under the > current conditions so that I can safely delete the file? My understanding is that if your first insert succeeds you hold a write lock on the table and barring any exceptional errors a commit should always succeed. -- Cory Nelson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Can I simulate a COMMIT?
Hi, in a scenario when multiple operations need to be transactionally synchronised, I have a file that must be deleted when the database records are added successfully, but the database operations must be rolled back, if the file cannot be deleted. I'm currently using a transaction for this on the database side and rolling it back if the file cannot be deleted. But what if the file is gone and then SQLite says it doesn't accept my records? Since we're inside a transaction, integrity checks should be deferred until a COMMIT. Is there a way to tell whether the COMMIT will succeed under the current conditions so that I can safely delete the file? Would that work with nested transactions or are integrity checks also deferred to the most outer transaction? I never used nested transactions so I have no experience with it. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New Operator Support
On Fri, 07 Sep 2007 17:16:39 +0800, Ragha wrote: >Hi, > >Just to get more hands on Sqlite i want to >write a custom operator. Pls suggest how i can do it. > >For example >select * from tablex where column1 ~ '123'; > >I want implement it similar to '='. Can anyone help me >what all steps,files i need to change? > >regards >ragha This should get you started: http://www.sqlite.org/docs.html more specifically: http://www.sqlite.org/arch.html http://www.sqlite.org/vdbe.html http://www.sqlite.org/opcode.html http://www.hwaci.com/sw/lemon/ http://www.sqlite.org/datatype3.html Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL-92 Syntax Question
On 9/7/07, Brad Stiles <[EMAIL PROTECTED]> wrote:
>
> Anyway, if I understand what you're saying, I believe what you want is an
> OUTER JOIN.
>
> SELECTT1.COLUMN, T2.COLUMN
> FROM TABLE2 T2
> outer join TABLE1 T1 on T2.COLUMN = T1.COLUMN
>
> That will get all rows from T2, and matching rows from T1. If no row in
> T1 exists, then T1.COLUMN will be null.
Thanks for pointing me in the right direction. I created a test database
and tables, and was able to figure it out. Turns out LEFT JOIN does exactly
what I was looking for. I'm pasting in the text below just in case anyone
else finds it useful (the concept more than the SQL syntax).
DROP TABLE ACCOUNT;
DROP TABLE IMPORT;
CREATE TABLE ACCOUNT(PKEY VARCHAR(10));
CREATE TABLE IMPORT(PKEY VARCHAR(10));
INSERT INTO ACCOUNT VALUES('E1');
INSERT INTO ACCOUNT VALUES('E2');
INSERT INTO ACCOUNT VALUES('E3');
INSERT INTO ACCOUNT VALUES('E4');
INSERT INTO ACCOUNT VALUES('E5');
INSERT INTO IMPORT VALUES('E1');
INSERT INTO IMPORT VALUES('E6');
INSERT INTO IMPORT VALUES('E3');
INSERT INTO IMPORT VALUES('E7');
INSERT INTO IMPORT VALUES('E5');
SELECT T1.PKEY, T2.PKEY FROM IMPORT T2 LEFT JOIN ACCOUNT T1 ON T2.PKEY =
T1.PKEY;
Thanks again for the tip.
Re: [sqlite] New Operator Support
Its like a newbie exploration nothing more, for example if someone want to write a linux kernel module there is some fixed guidelines,once a person is accustomed to it,he/she would hv better understanding. So is there any guidline ther than tracing every line of code and then finding it out. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Dwight Ingersoll <[EMAIL PROTECTED]> Date: Friday, September 7, 2007 10:48 pm Subject: Re: [sqlite] New Operator Support > On 9/7/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > > > > Hi, > > > > Its to get involved in the development of sqlite.If possible > > move to contrib section.a dream. > > > If that's the case, I think the first step is to post your > proposal, and get > input from the SQLite community as to whether the modification > would be > useful enough to be included in the base distribution. > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New Operator Support
On 9/7/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > > Hi, > > Its to get involved in the development of sqlite.If possible > move to contrib section.a dream. If that's the case, I think the first step is to post your proposal, and get input from the SQLite community as to whether the modification would be useful enough to be included in the base distribution.
RE: [sqlite] New Operator Support
Wouldn't it be a lot easier to just create a custom function? What's the advantage (other than pretty syntax) of using a custom operator? Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Sent: Friday, September 07, 2007 5:17 AM To: sqlite-users@sqlite.org Subject: [sqlite] New Operator Support Hi, Just to get more hands on Sqlite i want to write a custom operator. Pls suggest how i can do it. For example select * from tablex where column1 ~ '123'; I want implement it similar to '='. Can anyone help me what all steps,files i need to change? regards ragha - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ANSI order by
Andre du Plessis wrote:
Sorry if this is actually a noob question, how do I do an ansi style
order by in sqlite
For example
A
b
a
B
Would be sorted as
A
B
a
b
but what you want is
a
A
b
B
I can do order by upper(column)
But then things like
__new__
Goes to the bottom and should go to the top
Thanks.
You should use lower(column) instead. This will convert uppercase to
lowercase and sort *most* punctuation characters before the alphabetic
characters. Note that '{', '|', '}', and '~' will still sort after all
the alphabetic characters.
If you want the lower case columns to come before the uppercase values
(as you show in your example) you will also have to sort by the original
column values in descending order (so 'a' 0x61 comes before 'A' 0x41).
order by lower(a_column), a_column desc
Or you could write and register your own collation function which does
exactly what you want. :-)
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQL-92 Syntax Question
> SELECTT1.COLUMN, T2.COLUMN > FROM TABLE1 T1, TABLE2 T2 > WHERE T1.COLUMN *= T2.COLUMN Sorry about the fist one. New web mail client... Anyway, if I understand what you're saying, I believe what you want is an OUTER JOIN. SELECTT1.COLUMN, T2.COLUMN FROM TABLE2 T2 outer join TABLE1 T1 on T2.COLUMN = T1.COLUMN That will get all rows from T2, and matching rows from T1. If no row in T1 exists, then T1.COLUMN will be null. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL-92 Syntax Question
> The SELECT I have is: > > SELECTT1.COLUMN, T2.COLUMN > FROM TABLE1 T1, TABLE2 T2 > WHERE T1.COLUMN *= T2.COLUMN > > In SQL Server, the *= indicates a forced inner join which would cause > a record to be generated regardless if it existed in the T1 table or > not. If I understand what you correctly, what you want is an The result set generated (if I remember correctly) would set the > T1.COLUMN to null if it did not appear in the T1 table and both > columns would be populated if the record existed in both tables. > Changing the *= to =* caused the reverse of this. > > So my questions are how would this be expressed in SQL-92 (or more to > the point SQLite) syntax, and am I mistating the result set that would > be returned by this (there was another post in the mailing list on how > to determine whether a record existed in a primary table or not, and I > didn't want to spread bad advice). > > Thanks in advance. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL-92 Syntax Question
I learned SQL before the SQL-92 standard was in place. The database engines I used (SQL Server and Oracle) have their own proprietary methods for indicating forced inner outer joins. I haven't looked at this code in over 10 years, so I'm a little unclear about exactly what the result set returned was, and I don't have a database in place to play with these as yet. The SELECT I have is: SELECTT1.COLUMN, T2.COLUMN FROMTABLE1T1, TABLE2T2 WHERE T1.COLUMN *= T2.COLUMN In SQL Server, the *= indicates a forced inner join which would cause a record to be generated regardless if it existed in the T1 table or not. The result set generated (if I remember correctly) would set the T1.COLUMN to null if it did not appear in the T1 table and both columns would be populated if the record existed in both tables. Changing the *= to =* caused the reverse of this. So my questions are how would this be expressed in SQL-92 (or more to the point SQLite) syntax, and am I mistating the result set that would be returned by this (there was another post in the mailing list on how to determine whether a record existed in a primary table or not, and I didn't want to spread bad advice). Thanks in advance. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL approach to Import only new items, delete other items
On 9/6/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > Im importing data > > The data has a unique value, call it MD5 for now that could be a unique > value for the data. > > > > Each record that gets imported is converted to MD5, a lookup is done on > the table for that MD5, > > if found it must leave it alone, if not found it must insert a new > record... <> A SQL approach to this would be to force a left inner join creating a result set which would indicate which records currently exist/does not exist in the primary table. So in the result set, if both columns have values, then the record exists in the primary table and needs to be updated, if the column from the primary table is null, then it doesn't exist and must be inserted. To determine which records need to be deleted, force a right outer join, and use similar logic to determine which records need to be deleted. Unfortunately the SQL dialects I learned were prior to SQL-92, and my examples use the database engine proprietary syntax to indicate forced inner/outer joins, so I can't give you an example. I'm going to post another message asking what the correct syntax would be in SQL-92 terms.
Re: [sqlite] More on Column types
Andre du Plessis wrote: What is the difference between: MYCOLUMN NUMERIC Vs MYCOLUMN INTEGER See http://www.sqlite.org/datatype3.html for a complete description of the difference. Or does it really mean nothing, I remember somewhere the default column type could be integer, after that it defaults to whatever you insert. But I was wondering... what would the preferred type be to A: Store Int32 - Int64 values B: Floating point values. A: use a declared type of INTEGER B: use a declared type of REAL Is there a way to alter SQLite so that a select would always return a column as a type you want? Either in the DB file or as part of the select. Yes, only insert data of the declared type into the column. If you declare the column as an integer, and only insert integer values, you will always get integer results when selecting the column. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ANSI order by
Sorry if this is actually a noob question, how do I do an ansi style order by in sqlite For example A b a B Would be sorted as A B a b but what you want is a A b B I can do order by upper(column) But then things like __new__ Goes to the bottom and should go to the top Thanks.
RE: [sqlite] SQLite or MS Access
Yeah I have on many more than one occasion in MSAccess lost information when the program is terminated, that is why so many people hate it and don't want to go near it for any important data. I have heard however that MSAccess from 2000 onwards is based on the SQL server codebase so it is more stable, but apparently not designed to be very durable, once again I could be totally wrong but just a rumour I have heard. SQL Server on the other hand should be ACID by default?, and remains fast even if you commit on each insert, however when I say fast probably as fast as SQLite doing the same thing, but when SQLite is batched in transactions it becomes exponentially faster, where as SQLServer and other giants the speed is much closer to eachother whether you do many in one transaction or one at a time per transaction. However once again you never no how much system and disk resources are taken by things like SQLServer that helps them to do faster commits, im not sure. However nothing can get away from the fact that at some point disk buffers have to be flushed and that is up to hardware, I think many programmers are probably oblivious to how ACID like their transactions are and have probably simply not experienced such critical failure that their 'cached' inserts was never committed. Im assuming that SQL server probably keeps open logfiles that obviously can be written to much faster and have separate processes/threads that commit those to disk, SQLite does not create more processes or threads that I know of and I think many who are looking for fast lightweight solutions prefer this. SQLite however does get a lot faster when turning the synchronous off and not flushing buffers, but then again do you want to take that gamble. -Original Message- From: Nuno Lucas [mailto:[EMAIL PROTECTED] Sent: 07 September 2007 02:46 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite or MS Access On 9/7/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: [..] > What I don't understand is how Access, and other DB's are able to still > operate much faster (maybe not as fast), and still flush file buffers to > disk, is beyond me. Maybe it really still caches it, I would not be > surprised if you pull the plug from an MS access db you may end up with > missing records even if you committed, I could be wrong... MS Access has several modes of operation, but the default one is not ACID, so it seems fast. There are ways to set the transaction mode to be ACID (which I don't recall what/how they are), which will make the performance suffer behind any hope, making the comparison more fair. I don't recall now, but I seem to remember the default mode doesn't even guarantee the data is on disc when finished using it, which makes inserting a single record an order of magnitude slower on SQLite (because it waits for the data to get to the disk controller) than for Access (which just gives the data to the OS, not caring if it goes to disk or not). In a nutshell, benchmarks are not easy... Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite or MS Access
On 9/7/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: [..] > What I don't understand is how Access, and other DB's are able to still > operate much faster (maybe not as fast), and still flush file buffers to > disk, is beyond me. Maybe it really still caches it, I would not be > surprised if you pull the plug from an MS access db you may end up with > missing records even if you committed, I could be wrong... MS Access has several modes of operation, but the default one is not ACID, so it seems fast. There are ways to set the transaction mode to be ACID (which I don't recall what/how they are), which will make the performance suffer behind any hope, making the comparison more fair. I don't recall now, but I seem to remember the default mode doesn't even guarantee the data is on disc when finished using it, which makes inserting a single record an order of magnitude slower on SQLite (because it waits for the data to get to the disk controller) than for Access (which just gives the data to the OS, not caring if it goes to disk or not). In a nutshell, benchmarks are not easy... Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New Operator Support
May be, first want to try with the same impl as any other operator like =. If successful, then try providing some mech which make use of indexes where they are actually not allowed for. If u see one of my earlier post, related to pread,Made use of custom pread to avoid disk access, now in 3.5 it is a default feature. Just want to experiment. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: [EMAIL PROTECTED] Date: Friday, September 7, 2007 5:56 pm Subject: Re: [sqlite] New Operator Support > It still might be useful to explain what the ~ should do. > > RBS > > > Hi, > > > > Its to get involved in the development of sqlite.If possible > > move to contrib section.a dream. > > > > regards > > ragha > > > > > **> > This email and its attachments contain confidential information from > > HUAWEI, which is intended only for the person or entity whose > address is > > listed above. Any use of the information contained herein in any way > > (including, but not limited to, total or partial disclosure, > > reproduction, or dissemination) by persons other than the intended > > recipient(s) is prohibited. If you receive this e-mail in error, > please> notify the sender by phone or email immediately and delete it! > > > *> > > - Original Message - > > From: [EMAIL PROTECTED] > > Date: Friday, September 7, 2007 5:26 pm > > Subject: Re: [sqlite] New Operator Support > > > >> Couldn't tell you as I don't know C, but what > >> will the ~ do? Can't the same be done with the > >> available operators? > >> > >> RBS > >> > >> > > >> > Hi, > >> > > >> > Just to get more hands on Sqlite i want to > >> > write a custom operator. Pls suggest how i can do it. > >> > > >> > For example > >> > select * from tablex where column1 ~ '123'; > >> > > >> > I want implement it similar to '='. Can anyone help me > >> > what all steps,files i need to change? > >> > > >> > regards > >> > ragha > >> > > >> > > >> > > >> > **>>> > This email and its attachments contain confidential information from > >> > HUAWEI, which is intended only for the person or entity whose > >> address is > >> > listed above. Any use of the information contained herein in > any way > >> > (including, but not limited to, total or partial disclosure, > >> > reproduction, or dissemination) by persons other than the > intended>> > recipient(s) is prohibited. If you receive this e- > mail in error, > >> please> notify the sender by phone or email immediately and > delete it! > >> > > >> > *>>> > > - > >> > >> > To unsubscribe, send email to [EMAIL PROTECTED] > >> > -- > --- > >> > >> > > >> > > >> > > >> > >> > >> > >> > >> > --- > >> -- > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> > --- > >> -- > >> > >> > > > > - > > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > > > > > > > --- > -- > To unsubscribe, send email to [EMAIL PROTECTED] > --- > -- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New Operator Support
It still might be useful to explain what the ~ should do. RBS > Hi, > > Its to get involved in the development of sqlite.If possible > move to contrib section.a dream. > > regards > ragha > > ** > This email and its attachments contain confidential information from > HUAWEI, which is intended only for the person or entity whose address is > listed above. Any use of the information contained herein in any way > (including, but not limited to, total or partial disclosure, > reproduction, or dissemination) by persons other than the intended > recipient(s) is prohibited. If you receive this e-mail in error, please > notify the sender by phone or email immediately and delete it! > > * > > - Original Message - > From: [EMAIL PROTECTED] > Date: Friday, September 7, 2007 5:26 pm > Subject: Re: [sqlite] New Operator Support > >> Couldn't tell you as I don't know C, but what >> will the ~ do? Can't the same be done with the >> available operators? >> >> RBS >> >> > >> > Hi, >> > >> > Just to get more hands on Sqlite i want to >> > write a custom operator. Pls suggest how i can do it. >> > >> > For example >> > select * from tablex where column1 ~ '123'; >> > >> > I want implement it similar to '='. Can anyone help me >> > what all steps,files i need to change? >> > >> > regards >> > ragha >> > >> > >> > >> **> >> This email and its attachments contain confidential information from >> > HUAWEI, which is intended only for the person or entity whose >> address is >> > listed above. Any use of the information contained herein in any way >> > (including, but not limited to, total or partial disclosure, >> > reproduction, or dissemination) by persons other than the intended >> > recipient(s) is prohibited. If you receive this e-mail in error, >> please> notify the sender by phone or email immediately and delete it! >> > >> *> >> > - >> >> > To unsubscribe, send email to [EMAIL PROTECTED] >> > - >> >> > >> > >> > >> >> >> >> >> --- >> -- >> To unsubscribe, send email to [EMAIL PROTECTED] >> --- >> -- >> >> > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New Operator Support
Hi, Its to get involved in the development of sqlite.If possible move to contrib section.a dream. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: [EMAIL PROTECTED] Date: Friday, September 7, 2007 5:26 pm Subject: Re: [sqlite] New Operator Support > Couldn't tell you as I don't know C, but what > will the ~ do? Can't the same be done with the > available operators? > > RBS > > > > > Hi, > > > > Just to get more hands on Sqlite i want to > > write a custom operator. Pls suggest how i can do it. > > > > For example > > select * from tablex where column1 ~ '123'; > > > > I want implement it similar to '='. Can anyone help me > > what all steps,files i need to change? > > > > regards > > ragha > > > > > > > **> > This email and its attachments contain confidential information from > > HUAWEI, which is intended only for the person or entity whose > address is > > listed above. Any use of the information contained herein in any way > > (including, but not limited to, total or partial disclosure, > > reproduction, or dissemination) by persons other than the intended > > recipient(s) is prohibited. If you receive this e-mail in error, > please> notify the sender by phone or email immediately and delete it! > > > *> > > - > > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > > > > > > > --- > -- > To unsubscribe, send email to [EMAIL PROTECTED] > --- > -- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New Operator Support
Couldn't tell you as I don't know C, but what will the ~ do? Can't the same be done with the available operators? RBS > > Hi, > > Just to get more hands on Sqlite i want to > write a custom operator. Pls suggest how i can do it. > > For example > select * from tablex where column1 ~ '123'; > > I want implement it similar to '='. Can anyone help me > what all steps,files i need to change? > > regards > ragha > > > ** > This email and its attachments contain confidential information from > HUAWEI, which is intended only for the person or entity whose address is > listed above. Any use of the information contained herein in any way > (including, but not limited to, total or partial disclosure, > reproduction, or dissemination) by persons other than the intended > recipient(s) is prohibited. If you receive this e-mail in error, please > notify the sender by phone or email immediately and delete it! > > * > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] New Operator Support
Hi, Just to get more hands on Sqlite i want to write a custom operator. Pls suggest how i can do it. For example select * from tablex where column1 ~ '123'; I want implement it similar to '='. Can anyone help me what all steps,files i need to change? regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite or MS Access
Transactions are the main thing to speed this up but there are others such as the various Pragma settings. If you search in this group for slow insert you will find them. RBS > The problem was transactions > > Thanks all > > -Message d'origine- > De : Andre du Plessis [mailto:[EMAIL PROTECTED] > Envoyé : vendredi 7 septembre 2007 10:25 > À : sqlite-users@sqlite.org > Objet : RE: [sqlite] SQLite or MS Access > > Well here are my test results (im using Delphi for this one) > > This is my insert statement: > > INSERT INTO TEST (TEST_ID_NO_IDX, NO_INDEX, TEST_DATA, TEST_ID) values > (%d, %s, %s, %d) > This table deliberately has NO index. > > > 1000 inserts took: > Inserting MS Access - 4,043.273 ms > Inserting SQLite - 249.329 ms > > In my sample the key is doing the following > > BEGIN TRANSACTION > Loop inside transaction: > Do inserts > > COMMIT TRANSACTION > > I'm suspecting that you are falling into the trap of not doing most of > your work in a transaction, in SQLite that is BAD, sqlite tries to be as > durable as possible, so it writes to the journal and flushes file > buffers each time a transaction commits, if you don't specify one, then > this will automatically happen on each insert and will kill your speed. > > > What I don't understand is how Access, and other DB's are able to still > operate much faster (maybe not as fast), and still flush file buffers to > disk, is beyond me. Maybe it really still caches it, I would not be > surprised if you pull the plug from an MS access db you may end up with > missing records even if you committed, I could be wrong... > > Used correctly SQlite should be the fastest there is, obviously for more > single user (desktop db) style operations not multiuser. > > Ive done Inserts up to the 40-50K/ sec in sqlite with the right kind of > tweaks. > > > > -Original Message- > From: Michael Martin [mailto:[EMAIL PROTECTED] > Sent: 07 September 2007 10:06 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] SQLite or MS Access > > Hi All, > > > > I've done some benchmarks tests and I wonder where I've made a mistake. > > > > In C# code with SQLite.NET.2.0.1 wrapper with sqlite 3.0: 1000 inserts > in a table of two columns -> 168 seconds > > > > In C# code with Jet.Oledb.4.0 with MS Access: 1000 inserts in a table > of two columns -> 1.14 seconds > > > > Could someone help me please > > > > Thanks in advance > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite or MS Access
The problem was transactions Thanks all -Message d'origine- De : Andre du Plessis [mailto:[EMAIL PROTECTED] Envoyé : vendredi 7 septembre 2007 10:25 À : sqlite-users@sqlite.org Objet : RE: [sqlite] SQLite or MS Access Well here are my test results (im using Delphi for this one) This is my insert statement: INSERT INTO TEST (TEST_ID_NO_IDX, NO_INDEX, TEST_DATA, TEST_ID) values (%d, %s, %s, %d) This table deliberately has NO index. 1000 inserts took: Inserting MS Access - 4,043.273 ms Inserting SQLite - 249.329 ms In my sample the key is doing the following BEGIN TRANSACTION Loop inside transaction: Do inserts COMMIT TRANSACTION I'm suspecting that you are falling into the trap of not doing most of your work in a transaction, in SQLite that is BAD, sqlite tries to be as durable as possible, so it writes to the journal and flushes file buffers each time a transaction commits, if you don't specify one, then this will automatically happen on each insert and will kill your speed. What I don't understand is how Access, and other DB's are able to still operate much faster (maybe not as fast), and still flush file buffers to disk, is beyond me. Maybe it really still caches it, I would not be surprised if you pull the plug from an MS access db you may end up with missing records even if you committed, I could be wrong... Used correctly SQlite should be the fastest there is, obviously for more single user (desktop db) style operations not multiuser. Ive done Inserts up to the 40-50K/ sec in sqlite with the right kind of tweaks. -Original Message- From: Michael Martin [mailto:[EMAIL PROTECTED] Sent: 07 September 2007 10:06 AM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite or MS Access Hi All, I've done some benchmarks tests and I wonder where I've made a mistake. In C# code with SQLite.NET.2.0.1 wrapper with sqlite 3.0: 1000 inserts in a table of two columns -> 168 seconds In C# code with Jet.Oledb.4.0 with MS Access: 1000 inserts in a table of two columns -> 1.14 seconds Could someone help me please Thanks in advance - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite or MS Access
Well here are my test results (im using Delphi for this one) This is my insert statement: INSERT INTO TEST (TEST_ID_NO_IDX, NO_INDEX, TEST_DATA, TEST_ID) values (%d, %s, %s, %d) This table deliberately has NO index. 1000 inserts took: Inserting MS Access - 4,043.273 ms Inserting SQLite - 249.329 ms In my sample the key is doing the following BEGIN TRANSACTION Loop inside transaction: Do inserts COMMIT TRANSACTION I'm suspecting that you are falling into the trap of not doing most of your work in a transaction, in SQLite that is BAD, sqlite tries to be as durable as possible, so it writes to the journal and flushes file buffers each time a transaction commits, if you don't specify one, then this will automatically happen on each insert and will kill your speed. What I don't understand is how Access, and other DB's are able to still operate much faster (maybe not as fast), and still flush file buffers to disk, is beyond me. Maybe it really still caches it, I would not be surprised if you pull the plug from an MS access db you may end up with missing records even if you committed, I could be wrong... Used correctly SQlite should be the fastest there is, obviously for more single user (desktop db) style operations not multiuser. Ive done Inserts up to the 40-50K/ sec in sqlite with the right kind of tweaks. -Original Message- From: Michael Martin [mailto:[EMAIL PROTECTED] Sent: 07 September 2007 10:06 AM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite or MS Access Hi All, I've done some benchmarks tests and I wonder where I've made a mistake. In C# code with SQLite.NET.2.0.1 wrapper with sqlite 3.0: 1000 inserts in a table of two columns -> 168 seconds In C# code with Jet.Oledb.4.0 with MS Access: 1000 inserts in a table of two columns -> 1.14 seconds Could someone help me please Thanks in advance - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite or MS Access
have you try this on usb key ? i've very bad benchmark on this, try in hard disk in this case Sylko Zschiedrich a écrit : Do all insert's in one transaction and it will be done in 1 second or less. Begin transaction 1..1000 insert into table Commit transaction Ciao Sylko -Ursprüngliche Nachricht- Von: Michael Martin [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 7. September 2007 10:06 An: sqlite-users@sqlite.org Betreff: [sqlite] SQLite or MS Access Hi All, I've done some benchmarks tests and I wonder where I've made a mistake. In C# code with SQLite.NET.2.0.1 wrapper with sqlite 3.0: 1000 inserts in a table of two columns -> 168 seconds In C# code with Jet.Oledb.4.0 with MS Access: 1000 inserts in a table of two columns -> 1.14 seconds Could someone help me please Thanks in advance - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite or MS Access
Do all insert's in one transaction and it will be done in 1 second or less. Begin transaction 1..1000 insert into table Commit transaction Ciao Sylko -Ursprüngliche Nachricht- Von: Michael Martin [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 7. September 2007 10:06 An: sqlite-users@sqlite.org Betreff: [sqlite] SQLite or MS Access Hi All, I've done some benchmarks tests and I wonder where I've made a mistake. In C# code with SQLite.NET.2.0.1 wrapper with sqlite 3.0: 1000 inserts in a table of two columns -> 168 seconds In C# code with Jet.Oledb.4.0 with MS Access: 1000 inserts in a table of two columns -> 1.14 seconds Could someone help me please Thanks in advance - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite or MS Access
Hi All, I've done some benchmarks tests and I wonder where I've made a mistake. In C# code with SQLite.NET.2.0.1 wrapper with sqlite 3.0: 1000 inserts in a table of two columns -> 168 seconds In C# code with Jet.Oledb.4.0 with MS Access: 1000 inserts in a table of two columns -> 1.14 seconds Could someone help me please Thanks in advance
