[sqlite] Help me in SQL
Hi sqlite-users@sqlite.org, I use sqliteODBC 0.68 + ADO and SQLite 3.3.6. I try executing sql: select t1.field1 as field1, t2.field2 as field2 from table1 t1 left join table2 t2 on (t1.id=t2.refid) and I receive the message on a mistake "no such column: t1.field1(1)" -- Компьютеры помогают быстро и эффективно решать проблемы, появившиеся с изобретением компьютеров С наилучшими пожеланиями, blins mailto:[EMAIL PROTECTED]
RE: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison
Daniel: Thanks for the suggestion. I wasn't aware that the prepare statement gained you that much for one-table select queries. I use it for multi-100k inserts (along with trans.) and it saves quite a bit of time. This is my sql for the present problem: select * from (select f1, f2, f3, f4, f5 from Table where f2 = 'abc' and f3 = 2563351070 and f4 >= '2004-01-01'and f4 <='2006-01-01' ) order by f1 limit 32 offset 900; Do you think that prepare would be helpful here? Regards, Michael
RE: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison
Brannon: Thank you for your thoughts. To be clear, the 'optimize for speed' setting in MY release is actually slower than MY debug version - I know nothing about Ralf's settings. That issue is separate from SQLiteSpy - I didn't mean to conflate them. And the issue is not which version of VS I'm using. If I simply put a 'return 0' in my callback, the time is within 2%. Thus, I am timing the dll only and I am using the pre-compiled version, not a VS 6 compiled version. Ralf is compiling his own version, in Borland, and gets 2.5 times my speed. Therefore, I believe the speed difference lies in the differences between the two compiled versions. Michael
Re: [sqlite] count(*)
Brannon King wrote: select rowid from table limit 1 offset -1; Two ways to do this: SELECT rowid FROM table ORDER BY rowid DESC LIMIT 1; SELECT max(rowid) FROM table; Yes, but neither one of those would be as fast as this query, true? SELECT rowid FROM table LIMIT 1 I guess I was thinking to avoid the sort overhead. Brannon, The rowid column is the key field for the btree that stores the table data. It is effectively always indexed. So neither of the queries suggested by Richard will do any sorting. They will use the ordering provided by this index to directly select the maximum value of the rowid column, or the rowid of the last column in the table in rowid order (which is the same thing). These queries will locate the last record in O(log N) time using the rowid btree. HTH Dennis Cote
Re: [sqlite] finding the groups which have some sort of mising transaction
On Thu, 20 Jul 2006 15:44:32 +1000, you wrote: >HI, all > >The following 2 queries (have the same result) are about to find some groups >which have some sort of missing transactions. Please advise which one would >have better performance. Thank you in advance! Why not measure both cases with: time sqlite yourdatabase SELECT docketno >FROM cntt_sales2 >GROUP BY docketno >HAVING MAX(CASE WHEN datacmd = 'RCP' THEN 1 ELSE 0 END) = 0 >ORDER BY docketno > > >SELECT docketno FROM >(SELECT docketno, MAX(case when datacmd = 'RCP' then 1 else 0 end)AS opendoc >FROM cntt_sales2 GROUP BY docketno) >WHERE opendoc = 0 What is the table structure? >Jack -- ( Kees Nuyt ) c[_]
Re: [sqlite] How to calculate the sum up to a row better than O(n^2)?
On 7/20/06, Michael Sizaki <[EMAIL PROTECTED]> wrote: Hi, Suppose I have a database: CREATE TABLE data (timestamp INTEGER, amount INTEGER); INSERT INTO data VALUES(1,10); INSERT INTO data VALUES(2,20); INSERT INTO data VALUES(3,5); INSERT INTO data VALUES(4,2); ... Now I want to see the sum up to the timestamp: SELECT timestamp,(SELECT sum(amount) FROM data as d WHERE d.timestamp<=data.timestamp) FROM data ORDER BY timestamp; This works fine for small data sets. But it is obviously a quadratic problem. Is there a more efficient way to do the same thing? Perhaps you can calculate the results as you insert the data and store it in the database? It's not faster, but it moves the calculations to a time when someone isn't staring at the screen waiting for them! -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com
RE: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison
The VC6 compiler is from 1998. The VC 7.1 or 8.0 compilers produce better compilations. I'm certain any Borland or GNU compiler of the past 3 years would also produce better assembly than VC6. And if somebody has their hands on a PathScale or Intel compiler, please post some benchmarks! > I contacted Ralf who informed me that he knew of this speed > advantage and mentioned that he compiled his dll separately > using Borland. > An additional mystery is why using the 'optimize for speed' > compiler option actually slows down the result set processing. So that issue "optimize for speed" was with VC6 or with the Borland compiler? If it's for VC6, well, upgrade already. If it's for Borland's compiler, it is probably due to bugs in the memory management that were evident in BCB5/6. In that case use FastMM (or some equivalent) for the memory manager and see if that makes a difference. > I am using VC 6 C++ and SQLite 3.34. > My timing test uses the time reported my SQLite as reported > on the status bar, and for my code, I time only the sql_exec > stmt completion callbacks: > 'rc = sqlite3_exec(db, pSQL, callback1, 0, );'. > My callback merely stuffs 5 columns into a char array with no > processing. > > Thanks for your help. > Michael >
[sqlite] How to calculate the sum up to a row better than O(n^2)?
Hi, Suppose I have a database: CREATE TABLE data (timestamp INTEGER, amount INTEGER); INSERT INTO data VALUES(1,10); INSERT INTO data VALUES(2,20); INSERT INTO data VALUES(3,5); INSERT INTO data VALUES(4,2); ... Now I want to see the sum up to the timestamp: SELECT timestamp,(SELECT sum(amount) FROM data as d WHERE d.timestamp<=data.timestamp) FROM data ORDER BY timestamp; This works fine for small data sets. But it is obviously a quadratic problem. Is there a more efficient way to do the same thing? Michael
RE: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison
> I've been using the SQLiteSpy sql browser tool from Ralf > Junker at The Delphi Inspiration to test and time sql. > Unfortunately for my assumptions, it appears that SQLiteSpy > runs queries about 2.5x faster than using the SQLite dll in my code. > Does anyone know how to speed up SQLite running as a dll? > I contacted Ralf who informed me that he knew of this speed > advantage and mentioned that he compiled his dll separately > using Borland. > An additional mystery is why using the 'optimize for speed' > compiler option actually slows down the result set processing. > > I am using VC 6 C++ and SQLite 3.34. > My timing test uses the time reported my SQLite as reported > on the status bar, and for my code, I time only the sql_exec > stmt completion callbacks: > 'rc = sqlite3_exec(db, pSQL, callback1, 0, );'. > My callback merely stuffs 5 columns into a char array with no > processing. > > Thanks for your help. > Michael > > Michael I would suggest that SQLiteSpy is using sqlite3_prepare, sqlite3_step and sqlite3_reset rather than sqlite3_exec and this accounts for the difference. Daniel. == Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ==
Re: [sqlite] Importing Oracle 8.1.7 data into SQLite
On 7/20/06, Vinod Inamdar <[EMAIL PROTECTED]> wrote: Dear All, I am a newbie to SQlite and I require the above mentioned functionality in the subject line for a specific project. Is it possible to import Oracle 8.1.7 data into SQLite. Also is it possible to export data from SQLite to Oracle 8.1.7 Oracle has a data export program. I wrote a similar program for sqlite. if you want to try it there's a link in my email sig. The command line program that comes with sqlite can also import data -- -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com
Re: [sqlite] Order of columns within a CREATE TABLE statement
w b uttered: Hi all, Just had a quick question with regards to the order of the columns within a create table statement I have a few tables that use the BLOB type for storing various lengths of binary data and I was wondering if its better (more efficient) to always declare columns of this type last within the create table statement or not. I'm not searching on these columns but they do have the chance of being updated with more or less binary data than was originally in them, so wondered if there is any penalty difference for having them at the end or in the middle of of a list of other columns within the table. Or does it not really matter given that any column can handle any data type ? Put longer and not searched for columns at the end of the column list. SQLite will put a minimum of 4 rows in a single page, which for 1K pages, results in something like <240 bytes per row of room after meta information has been used. For rows bigger than this, SQLite builds an overflow list of pages, into which the rest of the data is written. This overflow list is slow to traverse, so it is better to have indexed and/or commonly used columns in the first couple of hundred bytes of the row to avoid having to traverse the overflow pages. Thanks Wayne Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Importing Oracle 8.1.7 data into SQLite
Write it out in SQL and read in the SQL. A bit clumsy, but simple. Vinod Inamdar wrote: Dear All, I am a newbie to SQlite and I require the above mentioned functionality in the subject line for a specific project. Is it possible to import Oracle 8.1.7 data into SQLite. Also is it possible to export data from SQLite to Oracle 8.1.7 Regards, Vinod Inamdar __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] sqlite
Sqlite is driven by SQL, so you just use SQL like any other SQL database. sandhya wrote: Hi, Is there any possibility to import files from the local file system to the sqlite DB.And Is there any export option is there just to check whether the loaded file and exported file consists of same data or not. Is it possible in sqlite? If possible,How it will stores files in tables?In which format? Please explain me how can i do it. Thank you Sandhya
[sqlite] import in sqlite
> Hi, > > Is there any possibility to import files from the local file system to the > sqlite DB.And Is there any export option is there just to check whether the > loaded file and exported file consists of same data or not. > Is it possible in sqlite? > If possible,How it will stores files in tables?In which format? > Please explain me how can i do it. > > Thank you > Sandhya > >
Re: [sqlite] Resources required and Lock & recovery mechanisms
Thank you John. Regards, Vivek R On 7/20/06, John Stanton <[EMAIL PROTECTED]> wrote: Sqlite requires few resources. Locking is achieved through regular file locks which lock the entire database since it is a file. Flow control is not applicable. You may use semaphores etc in your application for synchronization, but they are not used by Sqlite. Maintenance of an Sqlite database uses regular SQL. Backups and loading are just file copies since an Sqlite database is a single file. Vivek R wrote: > Hi Everybody, > I have the following doubt... > > 1. what are the resources required by SQLLite - they can be RAM/ROM, > semaphores, mail boxes, task requirements; > 2. How do we have flow control? > 3. what are the Lock mechanisms provided by the engine (row lock, table > lock..)? Any additional lock mechanism we need to build. > > 3. How to create a service component that creates these tables on HDD ( > Hard > disk on Consumer products like DVD or Set top box ) before it leave the > factory. > 4. recovery mechanisms (in case DB crash how do we recover/reconstruct > data?) > > > Thanks and Regards, > Vivek R >
[sqlite] Importing Oracle 8.1.7 data into SQLite
Dear All, I am a newbie to SQlite and I require the above mentioned functionality in the subject line for a specific project. Is it possible to import Oracle 8.1.7 data into SQLite. Also is it possible to export data from SQLite to Oracle 8.1.7 Regards, Vinod Inamdar __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] sqlite
Hi, Is there any possibility to import files from the local file system to the sqlite DB.And Is there any export option is there just to check whether the loaded file and exported file consists of same data or not. Is it possible in sqlite? If possible,How it will stores files in tables?In which format? Please explain me how can i do it. Thank you Sandhya
