Re: [sqlite] How do I optimize a query in this situation?
> > Consider replacing this query with a programmatic loop using blob API: > > http://sqlite.org/c3ref/blob_open.html > > or simply running a query like this: > > SELECT bin_content FROM FILE_CONTENT WHERE id = ?; > > with a new Id on every loop iteration. > > Igor Tandetnik > Thank you for the pointers. It looks like both options require me to implement the loop within C. So is it true that running this loop inside C would be faster than SQLite? Or, is SELECT * FROM TABLE WHERE id in (1,2,3); slower than three times of: SELECT * FROM TABLE WHERE id = ?; ? >From what I understand, the only difference is the number-to-string-to-number conversion. Are there any other factors? In general, should I always keep the query string short, regardless of the fact that it's "an easy query"? I only begin to worry because the query string can be up to 5KB, or even 20KB if the user continues to use the program. Is that too long for the engine to perform good? Thanks again! Best regards, He Shiming ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How do I optimize a query in this situation?
Hi, I'm working on this project. It puts a lot of files into a single sqlite database (including the actual file content, the program is designed to store the actual content) for search. When a search command is issued, I run a SELECT command on the FILE_INFO table to get stuff like file name, size and date. Then I run a background thread to obtain the actual file content from FILE_CONTENT table. The syntax of the query to obtain the file content is quite silly. Since I have all the row IDs in the first query, I ran a query like this: SELECT bin_content FROM FILE_CONTENT WHERE id in (1,2,3,4...); The query is built by sprintf with a for loop, obviously. Sometimes, there can be several hundreds or thousands of numbers in those brackets. I couldn't help but thinking there might be a way to improve this query syntax. I could mix the two query into one using LEFT JOIN. But that'll slow things down, and I wanted some quick response before seeing the actual content. It looks like other options will also slow down the second query. So I would like to know if it's okay to have queries running this way? Is it typically done this way or can I improve it? Many thanks in advance, He Shiming ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help on sql syntax, left join and group_concat
> First, note that left join is a red herring here, since you don't in fact > have any records in T1 without a matching record in T2. > > Try this: > > select NAME, group_concat(COUNT), group_concat(TYPE) > from ( > select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE > from T1 left join T2 on T1.ID=T2.REFID > order by T2.COUNT); > > Igor Tandetnik > > Well, in fact, it's possible that a record in T1 doesn't have a matching record in T2. So I have to use left join. I'm sorry for not clearing that up. Thanks for the hint. It works, but I noticed the query takes much longer to complete (5 times actually in my smaller testing db). My real scenario is much more complicated and the performance is even worse. So, any alternatives? Can it be done without a sub-select? About performance, it is important in my scenario. That's why I used group_concat. I could use additional queries to fetch the data from T2. But I figured as long as it's done in a single query instead of multiple, the performance shall be better. Please correct me if I'm wrong here, because I'm not so sure if I'm heading in the right direction. Thanks, He Shiming ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help on sql syntax, left join and group_concat
Hi, I need some help on a particular sql statement syntax. Consider the following tables: T1: ID, NAME 1, John T2: REFID, COUNT, TYPE 1, 9, B 1, 5, U 1, 8, T I have the following statement: select T1.NAME, group_concat(T2.COUNT), group_concat(T2.TYPE) from T1 left join T2 on T1.ID=T2.REFID; And the result is: John, 9,5,8, B,U,T What I want is to make the join part sorted by T2.COUNT, so that the result goes: John, 5,8,9, U,T,B I can't think of a way to make it happen. I tried putting in an additional ORDER BY T2.COUNT but it has no effect. Any hints? Thanks in advance, He Shiming ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] group_concat for binary?
Well, I didn't think BLOB stores text more efficiently. It's just that my data is actually in binary. To store it in text I'll have to encode it, which thus require twice storage as large. Anyway, I've managed to create my own custom aggregate function to accomplish this. The initial results look fine. I'll post again if I ran into troubles. Best regards, He Shiming -- From: "John Stanton" <[EMAIL PROTECTED]> Sent: Monday, March 10, 2008 12:24 PM To: "General Discussion of SQLite Database" Subject: Re: [sqlite] group_concat for binary? > Why do you think a BLOB stores text more efficiently? Do you compress it? > > He Shiming wrote: >> Hi, >> >> I would like to know if there is some workaround to use group_concat on >> binary columns. >> >> For performance consideration, I need to query two tables and accomplish >> data retrieval in a single SELECT. There is this BLOB column, originally >> designed as BLOB rather than text so that the storage is efficient. So >> can I >> use group_concat to join binary columns? I don't know if the >> implementation >> actually treats the output column as a string, or it will determine the >> actual length, in which case, it might be possible. >> >> What choices do I have? Is it a good idea to add another column that >> contains the encoded text representation of the BLOB? >> >> Thanks in advance, >> He Shiming >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] group_concat for binary?
Hi, I would like to know if there is some workaround to use group_concat on binary columns. For performance consideration, I need to query two tables and accomplish data retrieval in a single SELECT. There is this BLOB column, originally designed as BLOB rather than text so that the storage is efficient. So can I use group_concat to join binary columns? I don't know if the implementation actually treats the output column as a string, or it will determine the actual length, in which case, it might be possible. What choices do I have? Is it a good idea to add another column that contains the encoded text representation of the BLOB? Thanks in advance, He Shiming ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question - Multiple Users, Multiple files
Sorry about the previous mail, I hit tab in a gmail and it automatically sent it. Anyway, back to my question I'm planning out a program right now - web based - that would require each user of the application to be able to create their own independent data store with its own schema and modifications etc. In a perfect world, sqlite would be great for this as I could just give each individual user their own sqlite database and let them manipulate tables and schema as they see fit before they start to store information into the db. My concern is scalability and the performance hit of having let's say 5000 users potentially, accessing 5000 databases at the same time on the server. Based on my readings around the web there are ways to optimize sql queries (transactions, in memory dbs and all that) but even with that, would it be feasible or better yet advisable? The main issue here is the ability of the users to create and modify their own schemas which may leave my only other option to be xml Thanks. Jason It of course depends on the nature of your application, i.e., how the user will be using the database. Are they going to perform inserts or updates of large data set all the time? There are some common techniques for sqlite to improve its performance, such as use PRAGMA synchronous = OFF;, use transactions to wrap up inserts and updates whenever possible, and use indexes. My experience and the tests done here: http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison indicated that indexes do improve performance greatly. If your users were to input a CREATE TABLE statement, there's nothing much you can do. However, if you only let the user design a table, when you generate the CREATE TABLE statements, you might also want to take care of index creations. Another thing you should look into is the concurrency. SQLite can't write (inserts and updates), when another connection is reading (selects) from the same database file. It looks like each user having their own database file is the way to go, but just make sure you handle busy status properly. My 2 cents. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Order BY
Thanks shiming we have tried this method as u suggested , but creating an index on a 5 million records take a long time, nearly 4 seconds. And in our case the Database contain 5 Million to 30 or 50 million records. so if I try to create additional indices on these tables it takes too much time. any other suggestions if u have please. Best regards, Manzoor Ilahi I don't understand. Index creation is a one time deal, it's like tables (there are temporary indexes though, which are also similar to tables). You don't have to recreate indexes before queries. It only cost you 4 seconds, but future queries will be a lot faster. Newly inserted rows will be automatically indexed. Did you compare the performance before and after the creation of indexes? In my experience, indexes do improve performance a lot as long as you've indexed the correct columns, i.e. the columns appearing in where clause and order by clause. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Order BY
Dar All, Can someone guide me, why the ORDER BY in SQLite is too slow. we observed that if the change in data values is small then ORDER BY works better . but if the change is big then the performance is very slow. Even sometime I can not finish a query execution, and wait and may be sometime need to kill the process. also another point that i observed is , when ..order by fieldName , if fieldName is varchar,the excutiion is faster ,and if fieldName is float,the execution can't be finished! Any Idea Please thanks Manzoor Ilahi Queries that we tested and the Schema is given as under. //-- select * from hvh5m,itm where hvh5m.Column4=itm.Icol1 order by Column8 this SQl can't be finished //-- select * from hvh5m,itm where hvh5m.column4=itm.Icol1 order by itm.Icol1; this sql can be finished! //-- hvh5m: column1 varchar(8 ); column2 varchar(4); Column3 varchar(2); Column4 varchar(4)£» column5 varchar(7); column6 varchar(8 ); column7 varchar(1); Column8float(10£¬2); itm£º Icol1 varchar(4); itemname varchar(20); Icol3 varchar(1); Icol4 varchar(1); Icol5 varchar(1); Icol6 varchar(1); Icol7 varchar(1); Icol8 varchar(1); Icol 9 varchar(1); Icol 10 varchar(1); Icol 11 varchar(1); Icol 12 varchar(1); Icol 13 varchar(1); Icol 14 varchar(1); Icol 15 varchar(1); Icol 16 varchar(1); There is one index on itm.Icol1. You can try creating two additional indexes, one on hvh5m.Column4 and the other on hvh5m.Column8. They should speed things up. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] results of SELECT contains carriage returns
Hi, I have a database where some values contain carriage returns. I am using the command line to execute sqlite commands, eg: sqlite3 Disney.db "SELECT * FROM Characters" When I get the result of a SELECT statement, the output has columns separated by pipe characters and rows by new lines. So, if a value contains a return, it prematurely starts a new line, and messes up my output result. What's the best approach to deal with this? I guess I could use the command: .mode csv to change the output to csv (which wraps newlines in values within quotes). But it doesn't hold from one sqlite3 command to the next. And I can't see how to do this in a single command line, and there'd be too much overhead to write the ".mode" and SELECT commands to a temporary file to then invoke through a sqlite3 command. I hope I'm missing something simple. Can anyone help, please? Thanks, Tom Well, you didn't say which midware or platform were you using. I'll talk in the perspective of C API. In reality, we never parse query results directly from the "table" output from command line shell. I think, usually, the command line shell is provided for the convenience of quickly evaluating some of the details of the database system. But when you were to develop an application, you simply don't use the database through a command line shell. You are supposed to use a combination of sqlite3_open, sqlite3_prepare, sqlite3_step, which are the APIs provided by sqlite, to access the database. And use APIs like sqlite3_column_* to get the content of the query. It certainly doesn't matter if there are return characters in the results. It won't matter even if characters are unreadable binaries. That's why we have a BLOB type. You are not supposed to parse them, you can get them directly through these APIs. You might want to learn more about a database management system, after all, sqlite is one. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding sqlite3_exec
Hi List, If I use sqlite3_exec to query a database, How can I know that the results in the data base got over. For example If I am expecting a 10 results in some for loop and actually there are only five results , How can I get a notification or return value that the results completed or Is there any way I can get SQLITE_DONE through sqlite3_Exec. What return value I will get If I query an empty table. Thanks and Regards, Vivek R SQLite didn't provide a "get number of rows" function for the result set. It is mentioned in the document that sqlite3_exec is actually a wrapper for sqlite3_prepare and sqlite3_step. It is in my opinion that sqlite3_exec should only be used when the result of the query isn't that important. For instance, a pragma query. For the record, sqlite3_exec did provide a callback function in which you can count and get the number of rows in a resultset. The optimal way is that you prepare the statement, fetch and count the results with sqlite3_step. Another thing I noticed from your question is that you might not want to "expect 10 results". It's not very wise to design a hard loop such as for(i=0;i<10;i++) when comes to a database query resultset. A better way would be to use an array to store the result set they way you could understand, and process them later. Then you'll have for(i=0;i Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] Regarding aborting a query
Actually it implements such syntax. See LIMIT and OFFSET at http://www.sqlite.org/lang_select.html Filip Right, I missed that. Sorry for the confusion. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding aborting a query
Hi List, Let us assume there are 10 results for a Query. If I want to abort the Query after 5 results How can I do that? What notification I will get once I got all the results and of if there are any errors after 6th result. How to specify a primary Key, foreign key while creating a table. Thanks and Regards, Vivek R At the present time, sqlite hadn't implemented syntax such as LIMIT 0,5. So you have to do it manually. It's fairly easy if you use prepared statements. The steps are as follows: 1. Call sqlite3_prepare with your query string 2. Call sqlite3_bind_* to bind parameters if applicable 3. Call sqlite3_step and sqlite3_column_* to fetch results and keep count, since sqlite3_step only returns one row at a time, you can decide from its return values, if it's SQLITE_DONE, there's probably less than 5 results, if it's still SQLITE_ROW when your counter reaches 5, you break the while loop. 4. Call sqlite3_reset or sqlite3_finalize, and yes you can reset or finalize a query when it's not done. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding Performance and removing create view
Hi List, we have ported SQLite to one of our consumer products. We require some minimal applications only like creating and deleting table and Inserting , Querying, deleting rows in a table. So, We are planning to remove some of the features like create view and etc , as we are facing some memory problems and we want to increase the performance of SQLite. Could you please suggest me what are the things to be done to remove the features like views and others ( which we usually not required) and How to increase the performance of SQLite. Thanks and Regards, Vivek R Generally, you can use transaction to wrap up your inserts and updates to improve writing performance. The details are discussed here: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations . Additionally, PRAGMA synchronous = OFF; will force sqlite to rely on operating system cache when writing. It's a great time saver. Check http://www.sqlite.org/pragma.html for its details and safety concerns. After all, sqlite is a database management system. So to improve reading (selecting) speed, you'll need to create indexes on those columns appears often in your where clause. Sometimes it's necessary to do some benchmarks and use queries such as EXPLAIN QUERY PLAN SELECT * ... to find out whether these indexes are improving the performance. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple Updates
Hi All, Is it possible to do multiple updates of blobs using the bind variables, I was doing them 1 at a time but it was a little slow. For example :- rc = sqlite3_prepare(objects_db, "UPDATE table SET proprietary_data = ? WHERE device_id = ? and instance = ?", -1, &pStmt, 0); for (i= 0; i <10;i++) { sqlite3_bind_blob(pStmt, 1, proprietary_data, proprietary_data_len, SQLITE_STATIC); sqlite3_bind_int(pStmt, 2, object->device_id); sqlite3_bind_int(pStmt, 3, object->objectIdentifier.instance); rc = sqlite3_step(pStmt); } if (sqlite3_finalize(pStmt)) Regards, Chris There is a pretty simple answer to all these kinds of questions. Use transactions. You can see the details about transactions and performance here: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations . In my experience, performance do improve a lot when transaction is used for such an update or insert iteration. If you don't know it yet, another idea is to use PRAGMA synchronous = OFF; . This way, sqlite will rely on operating system disk cache, which is an even greater improvement in performance. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Locking problems
Hi ! I'm getting a lots of "database is locked" (code:5). My app keeps giving up on one machine, it might be that there is a network problem but I not 100% sure. Anyway, when the app hangs all the other machines including the machine where the database file is get the "database is locked" (code:5) error. To get rid of the error I have to close my app on all machines (five total) and sometimes reboot the machine with the database. As far as I can tell there are no processes still running. The database is as far as I know not corrupted and seems ok after "restarting the network". Why do I get "database is locked" (code:5) and what do I have to do to avoid it ? I can try to ensure that there is a try/catch block but I'm unsure how to unlock the database, Is it enough to do a sqlite3_close() or do I need to ROLLBACK TRANSACTION and sqlite3_finalize() before I sqlite3_close() ? Windows XP, SQLite 3.3.4. /Martin ma1999ATjmaDOTse Usually, the SQLITE_LOCKED means that you tried to prepare or execute a new SQL statement without resetting or finalizing a previous one on a single connection. The statements should be prepared and executed this way: sqlite3_prepare sqlite3_bind_* sqlite3_step // more sqlite3_steps if there are more statements sqlite3_finalize You can't put another sqlite3_prepare in the middle. If you did, you'll get the database is locked error. And if any error encountered during the process, you should call sqlite3_reset to reset the statement. I suggest that you check your code very carefully to see if any statements are not finalized or resetted. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Wish to store a C structure in sqlite column
Dear Friends, I am in the process of forming a Generic API,(sql oriented and BerkelyDB and sister databases). In the process of integration ,i like to store a Structure in Sqlite. as far as my knowledge SQLITE allows me to declare the column types suppoted by the programming languare or say i am using blob . My requirement is i wish to store a structure in the SQLite column. I am unable to form a sql statement to store the structure ,i am also not clear with whether i can have a strucure as column type. suggestions will be really helpful. Thanking you, B.Narendran A C struct is already a blob. Inserting it to a table is quite straightforward. I assume you already created your tables, and have your connection open. You can try these to insert a struct: struct MyStruct { long nSomeStuff[1024]; }; MyStruct thisStruct = {0}; sqlite3* db; // already opened sqlite3_stmt* pStmt = NULL; const char* pszUnused; sqlite3_prepare (db, "INSERT INTO TABLE (BLOBCOLUMN) VALUES (?);", -1, &pStmt, &pszUnused); sqlite3_bind_blob (pStmt, 1, &thisStruct, sizeof(MyStruct), SQLITE_STATIC); sqlite3_step (pStmt); sqlite3_finalize (pStmt); Blob data must be prepared using a wildcard (?) and be bound later. Remember that when binding, the index of the first column is 1, not 0. And you have to check return values for each of the sqlite3_* functions, they may fail or return busy. There's no need to do any memory copy. If you will destroy the struct before sqlite3_step is called, then change SQLITE_STATIC to SQLITE_TRANSIENT. This way, sqlite will make an internal copy when sqlite3_bind_blob is called. When retrieving data, the size of the column is determined by sqlite3_column_bytes. You use the value returned by this function to decide how much memory you needed to copy from the pointer returned by sqlite3_column_blob to your own struct. I'm not sure if it'll help you to understand, in the eye of a database system, a C struct doesn't have any difference to the data in a block of memory buffer, or something like long long nVars[100];. They only need two things to get started, a pointer, and the size. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Disabling locking?
I want to use SQLite with only one user executing queries sequentially , so I don't need locking. Is there a simple way to completely disabling locking? Specific instructions if possible please, thanks... There are two kinds of locks. First one is a file lock, as in functions returning SQLITE_BUSY. From what I understand, if you do execute queries sequentially, or serialize the queries from threads yourself. You won't be experiencing any file lockings. The second is the database lock, as in functions returning SQLITE_ERROR. This could only happen when you didn't write your code correctly. For instance, if you forgot to finalize one prepared INSERT statement, and tried to prepare another INSERT statement right behind, you'll get SQLITE_ERROR, while sqlite3_reset will return SQLITE_LOCKED. Queries must be executed sequentially on a single sqlite connection. It's not your choice. If you need to execute parallel queries, you have to open another connection and handle busy status. Locking is not a feature for you to disable. It's a fact that you can't read or write before another writing process is finished. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Quotation handling bug?
- Original Message - From: "Robert Simpson" <[EMAIL PROTECTED]> To: Sent: Tuesday, September 19, 2006 2:19 PM Subject: RE: [sqlite] Quotation handling bug? Single quotes are supposed to be used for string literals, and double quotes/brackets for identifiers such as table names and column names. SELECT 'ID' FROM 'MYTABLE' is selecting the literal string 'ID', not the column. I am not positive, but I think if you use single quotes around something, SQLite will first try and treat it like a literal -- and if the SQL parser is expecting an identifier where you've placed a literal, it will try and re-evaluate it as an identifier instead. So since the statement CREATE TABLE 'MYTABLE' ('ID' INTEGER PRIMARY KEY) contains literals in places identifiers are expected, SQLite treats them as identifiers instead of literals. Conversely, SELECT 'ID' FROM 'MYTABLE' is ambiguous in that 'ID' could mean the literal string 'ID' or could mean an identifier. In a SELECT clause the string is first evaluated as a literal, and since literals are allowed in the return columns of a SELECT, the literal code path is taken and there is no need to try and evaluate it as an identifier. The FROM 'MYTABLE' portion is parsed later, but literals aren't allowed as a target in a FROM clause, so 'MYTABLE' is treated as an identifier. In short ... Don't use single-quotes around identifiers. Use single-quotes for string literals, and use double-quotes or brackets around identifiers so your code is more readable and explicit. Robert That's very helpful. Thanks. Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Quotation handling bug?
Hi, I think I found a bug in sqlite version 3.3.7. The steps to reproduce it is as follows. I've tested it on Windows only. C:\Something>sqlite3 newdb.db CREATE TABLE 'MYTABLE' ('ID' INTEGER PRIMARY KEY); INSERT INTO 'MYTABLE' ('ID') VALUES(1); INSERT INTO 'MYTABLE' ('ID') VALUES(2); INSERT INTO 'MYTABLE' ('ID') VALUES(3); This is pretty straightfoward. But when I try to fetch the data out... SELECT 'ID' FROM 'MYTABLE' WHERE 'ID'=2; // no result SELECT 'ID' FROM 'MYTABLE' WHERE ID=2; // result is ID SELECT ID FROM 'MYTABLE' WHERE ID=2; // result is 2 SELECT 'MYTABLE'.'ID' FROM 'MYTABLE' WHERE 'MYTABLE'.'ID'=2; // result is 2 I guess, to make it safer, I'll have to use the last one. However, the behavior or the first one and the second one looks like malfunctioning. The four queries should produce completely equivalent results, which is "2". Or is it something I did wrong? Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -