RE: [sqlite] Insert statement taking too long
--- Robert Simpson <[EMAIL PROTECTED]> wrote: > You need to create an index on the columns you're > joining. Otherwise I > believe 100,000,000 rows (10k x 10k) in table 2 will > be scanned while SQLite > looks for matches against the 10,000 rows in table > 1. While that makes sense, I suspect there is something else going on. I did a few more tests. For example, if I remove the INSERT but keep the exact same SELECT statement with the joins, it is fast again. So, it seems that it is quite slow when doing the insert's. I was thinking that perhaps the statement was not in a transaction, but I tried that too. Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Insert statement taking too long
Hello, I am seeing an interesting performance issue with INSERT statements. I am using Sqlite 3.3.5 on a Windows XP box. Here is a brief summary of the situation: Insert statements that require no join are quite fast: a) I create an empty table (drop it first if it exists). b) INSERT ... SELECT ... FROM another-table WHERE ... But, if the Insert statement requires a join, the performance degrades drastically: a) I create an empty table (drop it first if it exists). b) INSERT ... SELECT ... FROM table-1 JOIN table-2 WHERE ... Even when the two joined tables have 10,000 records each and those records match one to one, the query goes from taking a second or so in the first case to over 30-40 minutes in the second case. The processing is CPU intensive and pretty much locks down the PC during this process. Is this a common experience or do I need to play around with the configuration options? Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory vs File: speed improvement factor ?
--- John Stanton <[EMAIL PROTECTED]> wrote: > Meier, Roger wrote: > > Hi, > > > > What factor of speed improvement could I expect > with a memory database? > > > Little to none > This is my experience as well, under a variety of conditions that I have tested. It is interesting; at times, it may in fact be slightly slower. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Re: Re: getting the field names
Igor Tandetnik <[EMAIL PROTECTED]> wrote: Michele Simionato wrote: > I forgot to say that I would like a solution working from sqlite3, the > console, not from the > programming interface. PRAGMA table_info(tableName); Igor Tandetnik What happens when your SQL query includes any of these: select 'abc' as my_literal; select 24*7 as my_literal_2 from tab_1; select * from tab_1 , tab_2; select .. Well, I am sure you get the idea :-) Given the current state of the api's, this seems to require full sql parsing on part of the developer, which not realistic, especially from sqlite3 or tcl interfaces. - Be a chatter box. Enjoy free PC-to-PC calls with Yahoo! Messenger with Voice.
[sqlite] Memory databases
I have a couple of questions on :memory: databases: 1) What happens when a table grows in size more than the available RAM? Does sqlite revert to file based mechanism to handle it or would it throw an error? 2) From some of the other posters who have done more testing than I, it seems that there is not much difference in performance between :memory: and regular file based databases. Is that so? in what circumstances would the memory database perform better? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] List of functions
I see references to typecasting functions here in the mailing list. I have not found where they are discussed on the website. I saw some of them in the "expressions" page but seems to cover a subset of them. Is there a page that provides a list of all supported functions? These could be typecasting as well as other mathematical functions. Thanks in advance! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Pragmas
I am a bit confused on how to change the page_size using the pragma command. The documentation says: "The page-size may only be set if the database has not yet been created." But, to be able to issue the pragma command, I need to have a database connection in the first place. If the database I specify does not exist, it gets created automatically as a side effect of the connection. Then it is too late to change the page_size again. Same thing goes for a few other settings, including encoding. I am using the tcl api in case it is relevant. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Update with two tables
Hello, I am trying to update values of some columns in a table with fresh data from another table. The two tables have the same column definitions. Is this possible? Even better would be if the two tables could be the same; then I could use table aliases in the update statement. Here is what I would like to do: UPDATE T1 SET T1.colA = T2.colA, T1.colB = T2.colB FROM T1, T2 WHERE T1.colC = T2.colC Thanks in advance! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] testfixture binaries for Windows
Hello, I do not have Visual Studio or other compilers to build Sqlite3 from the source code on my pc. Is there a Windows binary version of Sqlite3 with the "testfixture" build anywhere? Thanks! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Some advanced questions
--- [EMAIL PROTECTED] wrote: > ... > Most of the interfaces in bind.test are > special-purpose > testing hacks that are only available if you build > the "testfixture". They are not a part of the > standard > TCL API. > > ... > Three things to take away from this overview: > > (1) The TCL API adds new syntax to the TCL > language >(the scripts eval and transactio methods). > This >is an exceedingly powerful mechanism which > cannot >be accomplished with Algol-derived languages > such >as Perl, Python, Ruby, or Java. You have to > look >outside of parsed languages to Lisp and > Scheme >to find this kind of raw power. > > (2) Everything is done at a very high level. No >calls to prepare() or step() or finalize() or >column_text(). Conversions and caching are > all >handled transparently and automatically. You >do not have to think about them. This allows > >you to devote more brain cycles to the > problem >you are trying to solve. Richard, I agree with you 100%. The tcl api looks, no, *is* quite powerful, simple and straightforward. This is really what attracted us to Sqlite in the first place. What I was looking for is a way to test whether a given SQL is valid or not, before executing it. Running the SQL may take too long just to have a OK/not OK test. More importantly, sometimes, the SQL itself has side effects so that it should not be executed twice (like an INSERT statement.) It looks like this functionality is already there as you use it in the test suite. Is there a reason not to make it available generally? At least a version of it that does nothing else or that does not make the full range of promises the regular sqlite3_prepare may do (like setting cursors, setting things up for fetches, etc.). I am just trying to understand the author and inventor's thought processes here more than anything. I am sure you've heard it a lot but thanks a lot for the great software and your generosity in making it public! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Some advanced questions
--- Dennis Cote <[EMAIL PROTECTED]> wrote: > I pulled the following from the bind.test file. It > shows how > sqlite_prepare is used to test the parameter > binding, and how the > sqlite_column_count and sqlite_column_name APIs are > used. > > HTH > Dennis Cote Dennis, This is great! These commands do not seem to be documented at the web page for tcl api. I will check out the tests to see what else is available. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Some advanced questions
--- Dennis Cote <[EMAIL PROTECTED]> wrote: > What you probably want to do is use > sqlite3_prepare() to check if the > statement is syntactically valid. If it is you will > get a success return > code, if not you will get an error return code. > > I understand you are using TCL but I believe all > these API functions are > exposed through the TCL API as well. > Hi Dennis, Thank your very much for your answers! It looks like sqlite3_prepare() is what I need. I have gone through the tcl api and did not find any mappings to it or any command that matched its functionality. Do you have the reference or the command name handy? Thanks again! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Some advanced questions
Hello, I started with Sqlite just a few days ago and gievn how simple its api is, I think I am ready to do some more complex things with it. I am primarily using it from the tcl api so far. I have a few questions: 1) If I have a statement that includes "WHERE 0 = 1", does sqlite optimize the query and return right away or would it execute the query anyway? 2) Is there a way to get column names form a statement without (or before) executing the statement? For example, when a user enters a statement like "select * from ...". 3) I would like to test the validity of sql statements before executing them. So I thought I could use "db complete" command. But now I am not sure what "db complete" command does. In my tests, it returns 0 (false) regardless of the sql statement. In fact the only times I have gotton true is if the statement is empty. The statements are (all are valid and return data): % db complete "select 2 * 2 " % db complete "select a from tab1" % db complete "select a from tab1 where a < 10" % db complete "select a from tab1 where a < 10 order by a" __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Temporary tables
In the application I am working on, it looks like I would be creating some temporary tables as part of complex sql statements. I am thinking of keeping these temp tables in a separate database file so that access to the main tables are not affected. I would appreciate any feedback, whether this would increase performance, or if it would just create extra work for me. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Attach/detach databases
I have a few questions regarding "databases" or "database files." I see how one can divide a particularly large schema structure over several files and then perform attach and detach database commands on them to get the whole thing again. 1) Is this a valid use of this construct? 2) Is there any difference, in terms of performance, between a single database or two or more databases attached to the default one? 3) When doing complex joins against two tables, is there a difference between when the tables are in one database or in two separate databases? I mean syntactically, I would use the database.table name syntax but other than that, is there a difference in terms of reliability or efficiency? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
