Re: [sqlite] store result
ok this i can do, but the problem is i need to select a set of records and i want to manipulate with each record then only i need to delete that record Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 1/19/06, manoj marathayil wrote: > > hi, > is there is any way so that i can store the result > of > a select query and execute a delete query on the > same > table with out using any other buffer? How about: select id into temp_table from source_table where somecondition = true; delete from source_table where id in ( select id from temp_table ); Send instant messages to your online friends http://in.messenger.yahoo.com
Re: [sqlite] Slow query after reboot
We have the same issue, to get around it we fire a thread when the program starts, intelligently "touching" every table that the user is likely to access (As Michael Sizaki already mentioned a select count(last_column) from big_table; will do it) Since a user is very unlikely to run a program and start doing anything complex within the first 10 to 20 seconds, by the time they DO try and run a query, it is the "second time" the query has been run and this we get the benefit of the Windows XP cache and thus the fast speed > I have created a client application that is always running on a users > desktop. The application accepts user input and then uses SQLite to > perform a few simple queries against a single db file that contains 4 > tables. The performance is fantastic after the initial install and > normal usage. When the user leaves for the night and tries a query in > the morning, the app hangs for 20 seconds and then finally comes back > with the results. If the user then duplicates the query immediately > afterward, the query is almost instantaneous. In addition, if at any > point the user reboots the machine and then retries the query, the same > delay happens. The time is spent in the SQLiteDataReader.read() > method. Does anybody have any thoughts on why this is happening? > Thanks for any help. > >
Re: [sqlite] Fwd: Question about string concatenation
malcom wrote: Hello, Anyone can translate this SQL query to sqlite? I know that to concatenate two string you should to use || but I can't port this query to sql :( UPDATE table_name SET column = CONCAT(column, " other text to add") WHERE confront_column_key = "something_key" Thanks UPDATE table_name SET column = column || ' other text to add' WHERE confront_column_key = 'something_key'
[sqlite] Re: Question about string concatenation
malcom wrote: Anyone can translate this SQL query to sqlite? I know that to concatenate two string you should to use || but I can't port this query to sql :( UPDATE table_name SET column = CONCAT(column, " other text to add") WHERE confront_column_key = "something_key" UPDATE table_name SET column = column || ' other text to add' WHERE confront_column_key = 'something_key' Igor Tandetnik
[sqlite] Fwd: Question about string concatenation
Hello, Anyone can translate this SQL query to sqlite? I know that to concatenate two string you should to use || but I can't port this query to sql :( UPDATE table_name SET column = CONCAT(column, " other text to add") WHERE confront_column_key = "something_key" Thanks
Re: [sqlite] Problem with import
I created your schema and saved the single line you provided to a file and it all imported fine: sqlite> .nullvalue NULL sqlite> .separator ; sqlite> .import text.txt Cliente sqlite> select * from cliente; 1|2005-02-13 00:00:00|FRANCISCO EDNAN SABOIA PONTES |0|R|NEWTON PARENTE|1161|PRO XIMO AO COMETA|JANGURUSSU|FORTALEZA|CE||78928958334|94006024023|327419 66 |32769280 /34724873 - PUBLICO||1977-08-17 00:00:00|NAOCONVENIADO|NORMAL|SUPERVIS OR|2005-12-29 00:00:00|1|PRE - PAGO LANCAMENTO24 HORAS|2005-04-30 00:00:00|0||0| 0|NULL|.|CELULAR-MARIAROSIMEIRE|TIO- CELSO||8861-5632|3276- 1949||CONTRATO DE LOCACAO||0|.|NULL|NULL|0|NULL|NULL|.|.|||NULL|NULL|||NULL| NULL||2005 -12-29 00:00:00|1899-12-30 17:08:25|ADMINISTRAÃ+O|ANGELA|ALTERACAO sqlite> The raw data you provided contains text. The strings are not encased in quotes or anything like that so while the example record was fine, if any of your other strings have a ; in them, I imagine that'll break your import Oen thing, your line ".import datafile.txt Cliente ";" "NULL"" didn't work for me, I used separate commands, .nullvalue and .separator Not sure why, I don't regularly use the command line importer > PLEASE, read my e-mail, i really need help ;-) > > > I am trying to import data from a file using sqlite3 command line, and > the tcl bind. But I aways get error about the Number of Columns. Sqlite > always says that I am trying to put less columns than the number of > columns defined in the table.
Re: [sqlite] Slow query after reboot
Geoff Simonds wrote: The app is running on Windows XP machines Is it possible that indexing services are enabled and XP is trying to index the database file?
Re: [sqlite] Decimal conversion
nbiggs wrote: When I try executing the command I get 'SQL error: near "as": syntax error'. Let me explain what I am trying to do, there might be a better way to do it. I have a table of weights as one of the columns. I am trying to return data so that I can create a histogram of the data. My query is as follows: Select round(field, 1), count(*) from table group by round(field, 1); Nathan, What version of SQLite are you using? The cast syntax was added fairly recently. I'm using version 3.2.7 to test. With a current version of SQLite this should work. select cast (field * 10 as integer) / 10.0 as bin, count(*) from table group by bin; Note, you wont get a result row for any bin values that would have had a count of zero. I.e. if there are no rows with a value of 49.8?? then there will not be a result row in the histogram for that value with a count of zero. The histogram output only has rows where the count was 1 or more. This may or may not be what you want. HTH Dennis Cote
Re[2]: [sqlite] Slow query after reboot
Hello Clark, I don't use a virus scanner and observe the same slow initial performance. I do believe it's possible it's the disk cache though, my disks are quite fast (15K SCSI). It acts more like some initialization phase in the DB than it does disk IO delay. C Thursday, January 19, 2006, 1:57:57 PM, you wrote: CC> Also possible, maybe even likely, is the user's anti-virus CC> software is scanning the DB file when the app is first opened. CC> 35MB is a big file for A-V to have to scan. CC> You or they may be able to configure the A-V to ignore the DB file. CC> -Clark CC> - Original Message CC> From: Geoff Simonds <[EMAIL PROTECTED]> CC> To: sqlite-users@sqlite.org CC> Sent: Thursday, January 19, 2006 7:52:55 AM CC> Subject: Re: [sqlite] Slow query after reboot CC> My table contains about 500,000 rows and 4 columns, not all that much CC> data. The overall size of the db file is 35 mb. Does 15 - 20 seconds CC> sound right to load from disk into memory? CC> Robert Simpson wrote: >> - Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> >> >>> >> >>> The app is running on Windows XP machines and I assume that disk >>> files are cached. The strange thing is that the time it takes for >>> the initial read into RAM after install and first use is >>> significantly shorter than after a reboot. For example, if you just >>> installed the app and start it, the first time you do a query you see >>> results in about 2 seconds. Subsequent queries come back much almost >>> instantaneously. If the user reboots the machine or waits until the >>> next day and performs the same query, it now takes about 15 seconds. >>> After the 15 seconds, results come back and subsequent queries are >>> instantaneous. I am not sure if this has anything to do with it but >>> the app is a Deskband that lives in the taskbar on windows. >> >> >> >> That's not so strange, really. When the app is installed (along with >> the database), the Windows disk cache probably has at least part of >> the database file cached -- afterall it just got finished writing it. >> >> Robert >> >> >> >> >> -- Best regards, Tegmailto:[EMAIL PROTECTED]
RE: [sqlite] Decimal conversion
When I try executing the command I get 'SQL error: near "as": syntax error'. Let me explain what I am trying to do, there might be a better way to do it. I have a table of weights as one of the columns. I am trying to return data so that I can create a histogram of the data. My query is as follows: Select round(field, 1), count(*) from table group by round(field, 1); -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 1:31 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Decimal conversion nbiggs wrote: >How do I convert the number 49.991 to just 49.9 in a select statement? >Using the round(weight, 1) returns 50.0. > >Is there a truncate function? > > Nathan, You can use: select cast ((field * 10) as integer) / 10.0 If this is something you do a lot of it might make sense to define a custom function. HTH Dennis Cote
Re: [sqlite] Slow query after reboot
Also possible, maybe even likely, is the user's anti-virus software is scanning the DB file when the app is first opened. 35MB is a big file for A-V to have to scan. You or they may be able to configure the A-V to ignore the DB file. -Clark - Original Message From: Geoff Simonds <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, January 19, 2006 7:52:55 AM Subject: Re: [sqlite] Slow query after reboot My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right to load from disk into memory? Robert Simpson wrote: > - Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> > >> > >> The app is running on Windows XP machines and I assume that disk >> files are cached. The strange thing is that the time it takes for >> the initial read into RAM after install and first use is >> significantly shorter than after a reboot. For example, if you just >> installed the app and start it, the first time you do a query you see >> results in about 2 seconds. Subsequent queries come back much almost >> instantaneously. If the user reboots the machine or waits until the >> next day and performs the same query, it now takes about 15 seconds. >> After the 15 seconds, results come back and subsequent queries are >> instantaneous. I am not sure if this has anything to do with it but >> the app is a Deskband that lives in the taskbar on windows. > > > > That's not so strange, really. When the app is installed (along with > the database), the Windows disk cache probably has at least part of > the database file cached -- afterall it just got finished writing it. > > Robert > > > > >
Re: [sqlite] Decimal conversion
nbiggs wrote: How do I convert the number 49.991 to just 49.9 in a select statement? Using the round(weight, 1) returns 50.0. Is there a truncate function? Nathan, You can use: select cast ((field * 10) as integer) / 10.0 If this is something you do a lot of it might make sense to define a custom function. HTH Dennis Cote
Re: [sqlite] Slow query after reboot
Thanks for the info and suggestions Michael. I will give this a try. Michael Sizaki wrote: Geoff Simonds wrote: My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right to load from disk into memory? Yes it does. The problem is, that your query is probably not reading sequentially from disk. Therefore the disk head has to jump forth and back. Once the entire database in in the OS disk cache, queries are fast, because it's only CPU bound and not disk bound anymore. To speedup the initial access, you can: - read the entire file once before you start your query - run the following query (once) select count(last_column) from big_table; this will touch each record in a kind of optimal order - if that is still slow, try VACUUM on your database. This brings the records in a natural order. I have an application that deals also with about 500,000 and the database size is about 100mb. Queries on a "cold" database are extremely slow Michael Robert Simpson wrote: - Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> The app is running on Windows XP machines and I assume that disk files are cached. The strange thing is that the time it takes for the initial read into RAM after install and first use is significantly shorter than after a reboot. For example, if you just installed the app and start it, the first time you do a query you see results in about 2 seconds. Subsequent queries come back much almost instantaneously. If the user reboots the machine or waits until the next day and performs the same query, it now takes about 15 seconds. After the 15 seconds, results come back and subsequent queries are instantaneous. I am not sure if this has anything to do with it but the app is a Deskband that lives in the taskbar on windows. That's not so strange, really. When the app is installed (along with the database), the Windows disk cache probably has at least part of the database file cached -- afterall it just got finished writing it. Robert
Re: [sqlite] Problem with import
Daniel de Matos Alves wrote: PLEASE, read my e-mail, i really need help ;-) I am trying to import data from a file using sqlite3 command line, and the tcl bind. But I aways get error about the Number of Columns. Sqlite always says that I am trying to put less columns than the number of columns defined in the table. I created the table using the following SQL: CREATE TABLE Cliente ( Chave_ClienteINTEGER, "Data Cadastro"TEXT, NomeTEXT, SexoINTEGER, Chave_LogradouroTEXT, EnderecoTEXT, NumeroTEXT, ComplementoTEXT, BairroTEXT, CidadeTEXT, EstadoTEXT, CepTEXT, CPFTEXT, RGTEXT, "Telefone Trabalho"TEXT, "Telefone Particular"TEXT, EmailTEXT, "Data Nascimento"TEXT, Chave_ConvenioTEXT, Chave_SituacaoTEXT, UsuarioQueAutorizouTEXT, "Ultima Locacao"TEXT, "Pre pago"BOOLEAN, Chave_PrePagoTEXT, "Validade Pre Pago"TEXT, "Encerrou Pre Pago"BOOLEAN, "Composicao Pre Pago"TEXT, "Quantidade Tiquete"INTEGER, "Consumido Tiquete"INTEGER, "Ultima Compra Tiquete"TEXT, "Limite Debito"TEXT, Referencia1TEXT, Referencia2TEXT, Referencia3 TEXT, "Fone Referencia1"TEXT, "Fone Referencia2"TEXT, "Fone Referencia3"TEXT, Chave_TextoTEXT, ObservacaoTEXT, AbertaLCBOOLEAN, LimiteLCTEXT, DataAberturaLCTEXT, DataEncerramentoLCTEXT, DiaVencimentoLCINTEGER, DataVencimentoLCTEXT, DataUltPgtoLCTEXT, ValorUltPgtoLCTEXT, SaldoProximoPgtoLCTEXT, GrupoAbriuLCTEXT, UsuarioAbriuLCTEXT, DataAbriuLCTEXT, HoraAbriuLCTEXT, GrupoEncerrouLCTEXT, UsuarioEncerrouLCTEXT, DataEncerrouLCTEXT, HoraEncerrouLCTEXT, MotivoEncerramentoLCTEXT, "Data de Movimentacao"TEXT, "Hora de Movimentacao"TEXT, "Grupo usuario"TEXT, "Usuario de grupo"TEXT, TipoManutencaoTEXT ); I used this tcl code to import db copy datafile ";" "NULL" or in sqlite3 command interface .import datafile.txt Cliente ";" "NULL" and Here we have one line of the data file: 1;2005-02-13 00:00:00;FRANCISCO EDNAN SABOIA PONTES ;0;R;NEWTON PARENTE ;1161;PROXIMO AO COMETA ;JANGURUSSU;FORTALEZA;CE;;78928958334;94006024023;32741966;32769280 /34724873 - PUBLICO;;1977-08-17 00:00:00;NAO CONVENIADO;NORMAL;SUPERVISOR;2005-12-29 00:00:00;1;PRE - PAGO LANCAMENTO 24 HORAS;2005-04-30 00:00:00;0;;0;0;NULL;.;CELULAR-MARIA ROSIMEIRE;TIO- CELSO;;8861-5632;3276-1949;;CONTRATO DE LOCACAO;;0;.;NULL;NULL;0;NULL;NULL;.;.;;;NULL;NULL;;;NULL;NULL;;2005-12-29 00:00:00;1899-12-30 17:08:25;ADMINISTRAÇÃO;ANGELA;ALTERACAO Daniel, When you are using the ".import" command in the shell, you should supply only two arguments; the filename and the table name. You have given four. I believe you are trying to set the separator character to ";", but that needs to be done with the ".separator" command before the ".import". Note, the ".separator" command does not need quotes around separator character (and if they are used, they become part of the string SQLite looks for to delimit values). It also appears that you are trying to set a string to use for NULL values. This is not possible with the ".import" command. Since it imports all values as text there are no NULL values. The closest thing you get is an empty string (i.e when two separator characters occur back to back). In summary try this sequence commands instead. create table table_name .separator ; .import datafile.txt table_name To verify the values that were imported use: .mode line select * from table_name; HTH Dennis Cote
Re: [sqlite] Re: store result
Igor Tandetnik wrote: Dennis Cote wrote: But I have to wonder why you suggest creating the temp table at all when this does the same thing: delete from source_table where id in (select id from source_table where some_condition = true); Or even delete from source_table where some_condition = true; Igor Tandetnik Good point.
Re: [sqlite] Slow query after reboot
Geoff Simonds wrote: My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right to load from disk into memory? Yes it does. The problem is, that your query is probably not reading sequentially from disk. Therefore the disk head has to jump forth and back. Once the entire database in in the OS disk cache, queries are fast, because it's only CPU bound and not disk bound anymore. To speedup the initial access, you can: - read the entire file once before you start your query - run the following query (once) select count(last_column) from big_table; this will touch each record in a kind of optimal order - if that is still slow, try VACUUM on your database. This brings the records in a natural order. I have an application that deals also with about 500,000 and the database size is about 100mb. Queries on a "cold" database are extremely slow Michael Robert Simpson wrote: - Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> The app is running on Windows XP machines and I assume that disk files are cached. The strange thing is that the time it takes for the initial read into RAM after install and first use is significantly shorter than after a reboot. For example, if you just installed the app and start it, the first time you do a query you see results in about 2 seconds. Subsequent queries come back much almost instantaneously. If the user reboots the machine or waits until the next day and performs the same query, it now takes about 15 seconds. After the 15 seconds, results come back and subsequent queries are instantaneous. I am not sure if this has anything to do with it but the app is a Deskband that lives in the taskbar on windows. That's not so strange, really. When the app is installed (along with the database), the Windows disk cache probably has at least part of the database file cached -- afterall it just got finished writing it. Robert
[sqlite] Decimal conversion
How do I convert the number 49.991 to just 49.9 in a select statement? Using the round(weight, 1) returns 50.0. Is there a truncate function? Nathan Biggs
[sqlite] SQLITE Wrappers
Hello, I have created some wrappers to call the sqlite3.dll. I just wanted to make sure that I have all the wrappers I need to use sqlite. It is working, I just wanted to make sure that I am making all the calls in the correct order. To Open: sqlite3_open To Execute: sqlite3_prepare sqlite3_column_count sqlite3_column_name (if set to return column names) sqlite3_step sqlite3_column_text sqlite3_reset sqlite3_finalize To Close: sqlite3_close For Errors: sqlite3_errmsg Nathan Biggs Computerway Food Systems (336) 841-7289
[sqlite] Problem with import
PLEASE, read my e-mail, i really need help ;-) I am trying to import data from a file using sqlite3 command line, and the tcl bind. But I aways get error about the Number of Columns. Sqlite always says that I am trying to put less columns than the number of columns defined in the table. I created the table using the following SQL: CREATE TABLE Cliente ( Chave_ClienteINTEGER, "Data Cadastro"TEXT, NomeTEXT, SexoINTEGER, Chave_LogradouroTEXT, EnderecoTEXT, NumeroTEXT, ComplementoTEXT, BairroTEXT, CidadeTEXT, EstadoTEXT, CepTEXT, CPFTEXT, RGTEXT, "Telefone Trabalho"TEXT, "Telefone Particular"TEXT, EmailTEXT, "Data Nascimento"TEXT, Chave_ConvenioTEXT, Chave_SituacaoTEXT, UsuarioQueAutorizouTEXT, "Ultima Locacao"TEXT, "Pre pago"BOOLEAN, Chave_PrePagoTEXT, "Validade Pre Pago"TEXT, "Encerrou Pre Pago"BOOLEAN, "Composicao Pre Pago"TEXT, "Quantidade Tiquete"INTEGER, "Consumido Tiquete"INTEGER, "Ultima Compra Tiquete"TEXT, "Limite Debito"TEXT, Referencia1TEXT, Referencia2TEXT, Referencia3 TEXT, "Fone Referencia1"TEXT, "Fone Referencia2"TEXT, "Fone Referencia3"TEXT, Chave_TextoTEXT, ObservacaoTEXT, AbertaLCBOOLEAN, LimiteLCTEXT, DataAberturaLCTEXT, DataEncerramentoLCTEXT, DiaVencimentoLCINTEGER, DataVencimentoLCTEXT, DataUltPgtoLCTEXT, ValorUltPgtoLCTEXT, SaldoProximoPgtoLCTEXT, GrupoAbriuLCTEXT, UsuarioAbriuLCTEXT, DataAbriuLCTEXT, HoraAbriuLCTEXT, GrupoEncerrouLCTEXT, UsuarioEncerrouLCTEXT, DataEncerrouLCTEXT, HoraEncerrouLCTEXT, MotivoEncerramentoLCTEXT, "Data de Movimentacao"TEXT, "Hora de Movimentacao"TEXT, "Grupo usuario"TEXT, "Usuario de grupo"TEXT, TipoManutencaoTEXT ); I used this tcl code to import db copy datafile ";" "NULL" or in sqlite3 command interface .import datafile.txt Cliente ";" "NULL" and Here we have one line of the data file: 1;2005-02-13 00:00:00;FRANCISCO EDNAN SABOIA PONTES ;0;R;NEWTON PARENTE ;1161;PROXIMO AO COMETA ;JANGURUSSU;FORTALEZA;CE;;78928958334;94006024023;32741966;32769280 /34724873 - PUBLICO;;1977-08-17 00:00:00;NAO CONVENIADO;NORMAL;SUPERVISOR;2005-12-29 00:00:00;1;PRE - PAGO LANCAMENTO 24 HORAS;2005-04-30 00:00:00;0;;0;0;NULL;.;CELULAR-MARIA ROSIMEIRE;TIO- CELSO;;8861-5632;3276-1949;;CONTRATO DE LOCACAO;;0;.;NULL;NULL;0;NULL;NULL;.;.;;;NULL;NULL;;;NULL;NULL;;2005-12-29 00:00:00;1899-12-30 17:08:25;ADMINISTRAÇÃO;ANGELA;ALTERACAO
Re: [sqlite] store result
> I don't believe there is an option to "select into" in SQLite. So your > first statement should probably be a "create as". Also, you should > delete the temp table after the delete. Your solution should read like this: Oops! Too many database syntax records in my head at the same time! > > create temp temp_table as > select id from source_table where some_condition = true; > delete from source_table > where id in (select id from temp_table); > drop temp_table; > > But I have to wonder why you suggest creating the temp table at all when > this does the same thing: He said he wanted to do some processing before deleting the records. I do something similar when printing letters from a live system. I take a snapshot into a temp table, export the data to the printing process, and then if that suceeds I mark the records as printed using the temp table. If I didn't use a snapshot records added while the process runs will be marked as printed when they really are not.
[sqlite] Re: store result
Dennis Cote wrote: But I have to wonder why you suggest creating the temp table at all when this does the same thing: delete from source_table where id in (select id from source_table where some_condition = true); Or even delete from source_table where some_condition = true; Igor Tandetnik
Re: [sqlite] Slow query after reboot
- Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right to load from disk into memory? I can't tell you that until the following are answered: 1. What's the query? 2. What's the database schema? Indexes? 3. Are you calling prepare/step/finalize to load the data or using some other method? 4. How are you storing the data in memory? linked lists? Pre-allocated array? The first two are the most important factors. Robert
Re: [sqlite] store result
Jay Sprenkle wrote: is there is any way so that i can store the result of a select query and execute a delete query on the same table with out using any other buffer? How about: select id into temp_table from source_table where somecondition = true; delete from source_table where id in ( select id from temp_table ); Jay, I don't believe there is an option to "select into" in SQLite. So your first statement should probably be a "create as". Also, you should delete the temp table after the delete. Your solution should read like this: create temp temp_table as select id from source_table where some_condition = true; delete from source_table where id in (select id from temp_table); drop temp_table; But I have to wonder why you suggest creating the temp table at all when this does the same thing: delete from source_table where id in (select id from source_table where some_condition = true); Dennis Cote
Re: [sqlite] Slow query after reboot
My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right to load from disk into memory? Robert Simpson wrote: - Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> The app is running on Windows XP machines and I assume that disk files are cached. The strange thing is that the time it takes for the initial read into RAM after install and first use is significantly shorter than after a reboot. For example, if you just installed the app and start it, the first time you do a query you see results in about 2 seconds. Subsequent queries come back much almost instantaneously. If the user reboots the machine or waits until the next day and performs the same query, it now takes about 15 seconds. After the 15 seconds, results come back and subsequent queries are instantaneous. I am not sure if this has anything to do with it but the app is a Deskband that lives in the taskbar on windows. That's not so strange, really. When the app is installed (along with the database), the Windows disk cache probably has at least part of the database file cached -- afterall it just got finished writing it. Robert
Re: [sqlite] Slow query after reboot
- Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> The app is running on Windows XP machines and I assume that disk files are cached. The strange thing is that the time it takes for the initial read into RAM after install and first use is significantly shorter than after a reboot. For example, if you just installed the app and start it, the first time you do a query you see results in about 2 seconds. Subsequent queries come back much almost instantaneously. If the user reboots the machine or waits until the next day and performs the same query, it now takes about 15 seconds. After the 15 seconds, results come back and subsequent queries are instantaneous. I am not sure if this has anything to do with it but the app is a Deskband that lives in the taskbar on windows. That's not so strange, really. When the app is installed (along with the database), the Windows disk cache probably has at least part of the database file cached -- afterall it just got finished writing it. Robert
Re: [sqlite] Slow query after reboot
The app is running on Windows XP machines and I assume that disk files are cached. The strange thing is that the time it takes for the initial read into RAM after install and first use is significantly shorter than after a reboot. For example, if you just installed the app and start it, the first time you do a query you see results in about 2 seconds. Subsequent queries come back much almost instantaneously. If the user reboots the machine or waits until the next day and performs the same query, it now takes about 15 seconds. After the 15 seconds, results come back and subsequent queries are instantaneous. I am not sure if this has anything to do with it but the app is a Deskband that lives in the taskbar on windows. Jay Sprenkle wrote: On 1/19/06, Geoff Simonds <[EMAIL PROTECTED]> wrote: I have created a client application that is always running on a users desktop. The application accepts user input and then uses SQLite to perform a few simple queries against a single db file that contains 4 tables. The performance is fantastic after the initial install and normal usage. When the user leaves for the night and tries a query in the morning, the app hangs for 20 seconds and then finally comes back with the results. If the user then duplicates the query immediately afterward, the query is almost instantaneous. In addition, if at any point the user reboots the machine and then retries the query, the same delay happens. The time is spent in the SQLiteDataReader.read() method. Does anybody have any thoughts on why this is happening? Thanks for any help. Does your system cache disk files? The first time it reads the file into cache and after that it's read from RAM.
Re: [sqlite] Slow query after reboot
On 1/19/06, Geoff Simonds <[EMAIL PROTECTED]> wrote: > I have created a client application that is always running on a users > desktop. The application accepts user input and then uses SQLite to > perform a few simple queries against a single db file that contains 4 > tables. The performance is fantastic after the initial install and > normal usage. When the user leaves for the night and tries a query in > the morning, the app hangs for 20 seconds and then finally comes back > with the results. If the user then duplicates the query immediately > afterward, the query is almost instantaneous. In addition, if at any > point the user reboots the machine and then retries the query, the same > delay happens. The time is spent in the SQLiteDataReader.read() > method. Does anybody have any thoughts on why this is happening? > Thanks for any help. Does your system cache disk files? The first time it reads the file into cache and after that it's read from RAM.
Re: [sqlite] store result
On 1/19/06, manoj marathayil <[EMAIL PROTECTED]> wrote: > > hi, > is there is any way so that i can store the result > of > a select query and execute a delete query on the > same > table with out using any other buffer? How about: select id into temp_table from source_table where somecondition = true; delete from source_table where id in ( select id from temp_table );
[sqlite] Slow query after reboot
I have created a client application that is always running on a users desktop. The application accepts user input and then uses SQLite to perform a few simple queries against a single db file that contains 4 tables. The performance is fantastic after the initial install and normal usage. When the user leaves for the night and tries a query in the morning, the app hangs for 20 seconds and then finally comes back with the results. If the user then duplicates the query immediately afterward, the query is almost instantaneous. In addition, if at any point the user reboots the machine and then retries the query, the same delay happens. The time is spent in the SQLiteDataReader.read() method. Does anybody have any thoughts on why this is happening? Thanks for any help.
Re: [sqlite] Sqlite and Java
Nilo Paim wrote: My point is: if I use another sql engine that is written in Java then I just inverted the problem: how to access the databases without writing a bridge in native code that allows me to access the database from C or C++ or... ? Use a database server with client interfaces then. -- Gerhard
Re: [sqlite] Sqlite and Java
Jonathan Ballet wrote: Noel Frankinet wrote: Nilo Paim wrote: Hi Noel, Maybe slower, maybe memory hungry... but not less portable in my opinion. Java is machine independent, unless when using native code. On that scenario ( native code ) Java is not portable. My point is: I would like to have access to sqlite databases from java without worrying me about native code, dll's, .so'es, etc..., just using only pure java code, without lossing the normal access to the same databases, using the "normal" way, via C, C++ or any other compiled language. Thanks for your quick response. Cheers, Nilo Porto Alegre - Brasil Hello Nilo, I think there a IBM open source sql engine in pure Java. But I don't rember the name right now. May be you could use that ? Regards I think you're talking of http://hsqldb.org/, used among other project by OpenOffice ... I think Noël may be talking about Apache Derby, formerly IBM Cloudscape: http://db.apache.org/derby/ Regards, Ulrik Petersen
Re: [sqlite] Sqlite and Java
Kervin L. Pierre wrote: Cloudscape, which was given to Apache foundation and is now the Apache Derby Project. http://db.apache.org/derby/ Yes this is it ! -- Noël Frankinet Gistek Software SA http://www.gistek.net
Re: [sqlite] Sqlite and Java
Cloudscape, which was given to Apache foundation and is now the Apache Derby Project. http://db.apache.org/derby/ Jonathan Ballet wrote: I think you're talking of http://hsqldb.org/, used among other project by OpenOffice ...
Re: [sqlite] Sqlite and Java
Noel Frankinet wrote: Nilo Paim wrote: Hi Noel, Maybe slower, maybe memory hungry... but not less portable in my opinion. Java is machine independent, unless when using native code. On that scenario ( native code ) Java is not portable. My point is: I would like to have access to sqlite databases from java without worrying me about native code, dll's, .so'es, etc..., just using only pure java code, without lossing the normal access to the same databases, using the "normal" way, via C, C++ or any other compiled language. Thanks for your quick response. Cheers, Nilo Porto Alegre - Brasil Hello Nilo, I think there a IBM open source sql engine in pure Java. But I don't rember the name right now. May be you could use that ? Regards Hi, Noel. Oh, I got your point now.;-) Yes, there are some free sql engines in pure Java. The most well known is called HSQLDB. My point is: if I use another sql engine that is written in Java then I just inverted the problem: how to access the databases without writing a bridge in native code that allows me to access the database from C or C++ or... ? I'm not searching for a specific solution for a specific project. I'm trying to got a solution that I could use many times. And to be honest, I like Sqlite a lot... 8-) Thanks for your interest. []'s Nilo Porto Alegre - Brasil
Re: [sqlite] Sqlite and Java
If I am not mistaken, the following thread might be relevant: http://www.mail-archive.com/sqlite-users@sqlite.org/msg11005.html Ran On 1/19/06, Nilo Paim <[EMAIL PROTECTED]> wrote: > > Hi all, > > Does anybody here knows something about a port of sqlite to java? > > Please, note that I'm not talking about java calling sqlite via JNI, but > about a real rewrite of sqlite using java. Obviously, a second step > would be the writing of a JDBC driver. > > Would be useful that port? > > Comments? Suggestions? > > Thanks to all. > > Nilo > Porto Alegre - Brasil >
Re: [sqlite] Sqlite and Java
Noel Frankinet wrote: > Nilo Paim wrote: > >>> >> Hi Noel, >> >> Maybe slower, maybe memory hungry... but not less portable in my >> opinion. Java is machine independent, unless when using native code. >> On that scenario ( native code ) Java is not portable. >> >> My point is: I would like to have access to sqlite databases from >> java without worrying me about native code, dll's, .so'es, etc..., >> just using only pure java code, without lossing the normal access to >> the same databases, using the "normal" way, via C, C++ or any other >> compiled language. >> >> Thanks for your quick response. >> >> Cheers, >> >> Nilo >> Porto Alegre - Brasil >> > Hello Nilo, > > I think there a IBM open source sql engine in pure Java. But I don't > rember the name right now. > May be you could use that ? > > Regards > I think you're talking of http://hsqldb.org/, used among other project by OpenOffice ...
Re: [sqlite] Sqlite and Java
Nilo Paim wrote: Hi Noel, Maybe slower, maybe memory hungry... but not less portable in my opinion. Java is machine independent, unless when using native code. On that scenario ( native code ) Java is not portable. My point is: I would like to have access to sqlite databases from java without worrying me about native code, dll's, .so'es, etc..., just using only pure java code, without lossing the normal access to the same databases, using the "normal" way, via C, C++ or any other compiled language. Thanks for your quick response. Cheers, Nilo Porto Alegre - Brasil Hello Nilo, I think there a IBM open source sql engine in pure Java. But I don't rember the name right now. May be you could use that ? Regards -- Noël Frankinet Gistek Software SA http://www.gistek.net
Re: [sqlite] Sqlite and Java
Noel Frankinet wrote: Nilo Paim wrote: Hi all, Does anybody here knows something about a port of sqlite to java? Please, note that I'm not talking about java calling sqlite via JNI, but about a real rewrite of sqlite using java. Obviously, a second step would be the writing of a JDBC driver. Would be useful that port? Comments? Suggestions? Thanks to all. Nilo Porto Alegre - Brasil Hello Nilo, What would be the point ? A slower, memory hungry, less portable sqlite ? Cheers, Hi Noel, Maybe slower, maybe memory hungry... but not less portable in my opinion. Java is machine independent, unless when using native code. On that scenario ( native code ) Java is not portable. My point is: I would like to have access to sqlite databases from java without worrying me about native code, dll's, .so'es, etc..., just using only pure java code, without lossing the normal access to the same databases, using the "normal" way, via C, C++ or any other compiled language. Thanks for your quick response. Cheers, Nilo Porto Alegre - Brasil
Re: [sqlite] Sqlite and Java
Nilo Paim wrote: Hi all, Does anybody here knows something about a port of sqlite to java? Please, note that I'm not talking about java calling sqlite via JNI, but about a real rewrite of sqlite using java. Obviously, a second step would be the writing of a JDBC driver. Would be useful that port? Comments? Suggestions? Thanks to all. Nilo Porto Alegre - Brasil Hello Nilo, What would be the point ? A slower, memory hungry, less portable sqlite ? Cheers, -- Noël Frankinet Gistek Software SA http://www.gistek.net
[sqlite] Sqlite and Java
Hi all, Does anybody here knows something about a port of sqlite to java? Please, note that I'm not talking about java calling sqlite via JNI, but about a real rewrite of sqlite using java. Obviously, a second step would be the writing of a JDBC driver. Would be useful that port? Comments? Suggestions? Thanks to all. Nilo Porto Alegre - Brasil
[sqlite] store result
hi, is there is any way so that i can store the result of a select query and execute a delete query on the same table with out using any other buffer? T h a n k s & R e g a r d s , Manoj M | Webyog | Bangalore | Voice: 91 9886171984, 91 80 51216434(off) "A winner is not one who never fails, but one who never quits!" Send instant messages to your online friends http://in.messenger.yahoo.com
[sqlite] problem porting to arm/embos
hi, after porting sqlite3 to arm9/embos i seem to have problems with sqlparsing. for example trying to create a simple table with sql using CREATE TABLE test(id integer); returns a sqliteErr "near "id": syntax error" - i already stepped through the whole parsing but didnt get it. maybe somebody had a similar problem? thomas
