Re: [sqlite] problem with sqlite velocity the revenge
Kurt Welgehausen wrote: >> ... only the first index is opened ... > > That's right. SQLite uses only one index, and it doesn't keep > statistics, so it may pick the wrong index if it has to choose. > Try forcing it to use the other index by dropping the one that > it's using now. > > Changing 'table1, table2 where' to 'table1 join table2 on' is > not going to help. > You can also get it to use the other index by reversing the order the tables are joined. Ie. replace parole JOIN mega with mega JOIN parole The logic of this statement is basically to scan through every record in one of the tables and find all the records in the other table that have the same value for the word field. The index is used to locate the macthing values in the second table. Every pair of matching values is returned. If the same words are repeated in the parole table (with 6M records) and are unique in the mega table (with 200K records) it should make no difference. If each parole record matches to one mega record, then there should be an average of 30 parole records for each mega record. So you have 6M outer loops with 1 indexed lookup each, or 200K outer loops with an average of 30 indexed lookups each, ie. 6M lookups either way. It might be faster to scan the smaller mega table and use the index to find matching records in the larger parole table. Do you really want 6M result records? If my assumptions are not correct, you should order the join so that the size of the first table multiplied by the average number of matching records in the other table is as small as possible.
Re: [sqlite] Re: *** Please help ***
Thank you for that Miguel, but this is not good. DRH, if we don't get the type information returned for Views, what is the point of getting it back for SELECTs? Views are completely useless to SQLite users who rely on the type information to interpret the result sets. Is this something you can fix soon? Miguel Angel Latorre Díaz wrote: Read the documentation: "The first parameter is a prepared SQL statement. If this statement is a SELECT statement, the Nth column of the returned result set of the SELECT is a table column then the declared type of the table column is returned. If the Nth column of the result set is not at table column, then a NULL pointer is returned." - Original Message - From: "EzTools Support" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 20, 2004 11:43 PM Subject: [sqlite] Re: *** Please help *** Can someone please please answer the question, does sqlite3_column_decltype NOT return the column type for Views? I don't see how this wouldn't considered as a major bug. EzTools Support wrote: I found out why Views were returning all NULLs. I have made a modified version of sqlite3_exec. Mine checks for NULL returned from sqlite3_column_decltype, for which Sqlite3 now returns NULL. Am I right that this is so? My COM wrapper depends on having the column data types returned for Views. Can you please make it work again, as this is vital. thanks -brett EzTools Support wrote: Hello. Can someone please help with this issue. It may turn out to be a bug in Sqlite 3. I have the following fairly basic SQL View that works fine with Sqlite 2.x: CREATE VIEW Invoices AS SELECT Customers.CustomerID, Customers.CompanyName,Orders.OrderID, OrderDetails.ProductID, OrderDetails.Quantity FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID When I create and run this view in 3.x, I get all of the rows back, but all values are NULL! If I run the SELECT statement apart from the view, it I get the data back fine. But if I select * FROM Invoices, I get all of the rows back, but all values are NULL. Another interesting thing is that when selecting from the View, the column names are without the table prefix. That is, CustomerID, CompanyName, OrderID, et. But if I select from the tables directly, the column names have the table prefix, Customers.CustomerID, Customers.CompanyName, Orders.OrderID I have put the database up on my website here (Zip file): www.eztools-software.com/downloads/northwind.zip This is happening in all version 3.0.5-3.0.7. Can someone please download this DB and investigate. If you want to test the same view and data with a v2.x database, you can get the SqlitePlus28 download from here: www.eztools-software.com/downloads/sqliteplus.exe TIA -Brett Goodman
Re: [sqlite] Re: *** Please help ***
Read the documentation: "The first parameter is a prepared SQL statement. If this statement is a SELECT statement, the Nth column of the returned result set of the SELECT is a table column then the declared type of the table column is returned. If the Nth column of the result set is not at table column, then a NULL pointer is returned." - Original Message - From: "EzTools Support" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 20, 2004 11:43 PM Subject: [sqlite] Re: *** Please help *** > Can someone please please answer the question, does > sqlite3_column_decltype NOT return the column type for Views? I don't > see how this wouldn't considered as a major bug. > > > EzTools Support wrote: > > > > > I found out why Views were returning all NULLs. I have made a > > modified version of sqlite3_exec. Mine checks for NULL returned from > > sqlite3_column_decltype, for which Sqlite3 now returns NULL. Am I > > right that this is so? My COM wrapper depends on having the column > > data types returned for Views. Can you please make it work again, as > > this is vital. > > > > thanks > > -brett > > > > > > EzTools Support wrote: > > > >> Hello. Can someone please help with this issue. It may turn out to > >> be a bug in Sqlite 3. > >> > >> I have the following fairly basic SQL View that works fine with > >> Sqlite 2.x: > >> > >> CREATE VIEW Invoices AS > >> SELECT Customers.CustomerID, Customers.CompanyName,Orders.OrderID, > >> OrderDetails.ProductID, OrderDetails.Quantity FROM Customers INNER > >> JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN > >> OrderDetails ON Orders.OrderID = OrderDetails.OrderID > >> > >> When I create and run this view in 3.x, I get all of the rows back, > >> but all values are NULL! > >> > >> If I run the SELECT statement apart from the view, it I get the data > >> back fine. But if I select * FROM Invoices, I get all of the rows > >> back, but all values are NULL. > >> > >> Another interesting thing is that when selecting from the View, the > >> column names are without the table prefix. That is, CustomerID, > >> CompanyName, OrderID, et. But if I select from the tables directly, > >> the column names have the table prefix, Customers.CustomerID, > >> Customers.CompanyName, Orders.OrderID > >> > >> I have put the database up on my website here (Zip file): > >> > >>www.eztools-software.com/downloads/northwind.zip > >> > >> This is happening in all version 3.0.5-3.0.7. Can someone please > >> download this DB and investigate. > >> > >> If you want to test the same view and data with a v2.x database, you > >> can get the SqlitePlus28 download from here: > >> > >>www.eztools-software.com/downloads/sqliteplus.exe > >> > >> TIA > >> -Brett Goodman > >> > >> > > >
[sqlite] Re: *** Please help ***
Can someone please please answer the question, does sqlite3_column_decltype NOT return the column type for Views? I don't see how this wouldn't considered as a major bug. EzTools Support wrote: I found out why Views were returning all NULLs. I have made a modified version of sqlite3_exec. Mine checks for NULL returned from sqlite3_column_decltype, for which Sqlite3 now returns NULL. Am I right that this is so? My COM wrapper depends on having the column data types returned for Views. Can you please make it work again, as this is vital. thanks -brett EzTools Support wrote: Hello. Can someone please help with this issue. It may turn out to be a bug in Sqlite 3. I have the following fairly basic SQL View that works fine with Sqlite 2.x: CREATE VIEW Invoices AS SELECT Customers.CustomerID, Customers.CompanyName,Orders.OrderID, OrderDetails.ProductID, OrderDetails.Quantity FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID When I create and run this view in 3.x, I get all of the rows back, but all values are NULL! If I run the SELECT statement apart from the view, it I get the data back fine. But if I select * FROM Invoices, I get all of the rows back, but all values are NULL. Another interesting thing is that when selecting from the View, the column names are without the table prefix. That is, CustomerID, CompanyName, OrderID, et. But if I select from the tables directly, the column names have the table prefix, Customers.CustomerID, Customers.CompanyName, Orders.OrderID I have put the database up on my website here (Zip file): www.eztools-software.com/downloads/northwind.zip This is happening in all version 3.0.5-3.0.7. Can someone please download this DB and investigate. If you want to test the same view and data with a v2.x database, you can get the SqlitePlus28 download from here: www.eztools-software.com/downloads/sqliteplus.exe TIA -Brett Goodman
Re: [sqlite] Version 3.0.7
Jakub Adamek, dando pulos de alegria, escreveu : That's GREAT ! Nuno, are you going to merge Sqlite-Wince STABLE ? I'll do that during this week. Regards, ~Nuno Lucas
Re: [sqlite] sqlite3 DEF file
Monday, September 20, 2004, 5:11:48 AM, Mike wrote: > this file is incomplete, and has been for some time. here is the correct > version. would somebody check it in, since I dont have CVS access handy: The only additional symbol I see is sqlite3_version which is not a function. The sqlite3_libversion symbol is a function, and provides access to the version info. Accessing data directly from a DLL is fraught with peril since different development environments have different rules about the levels of indirection to the data. So, we use functional access only to sqlite. e
[sqlite] SQLITE OMIT VACUUM-Macro
Hi, I'm not sure whether it's a problem with my english or the SQLite code. SQLite in version 2 and 3 knows several macros which defined or not add or remove certain functionalities to eventually reduce the memory footprint of the SQLite library. SQLiteInt.h introduces: /* ** When building SQLite for embedded systems where memory is scarce, ** you can define one or more of the following macros to omit extra ** features of the library and thus keep the size of the library to ** a minimum. */ /* #define SQLITE_OMIT_AUTHORIZATION 1 */ /* #define SQLITE_OMIT_INMEMORYDB 1 */ /* #define SQLITE_OMIT_VACUUM 1 */ /* #define SQLITE_OMIT_DATETIME_FUNCS 1 */ /* #define SQLITE_OMIT_PROGRESS_CALLBACK 1 */ I now look in the implementation. auth.c for example reads: #ifndef SQLITE_OMIT_AUTHORIZATION date.c reads: #ifndef SQLITE_OMIT_DATETIME_FUNCS vacuum.c reads: #if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM That according to my understanding means: I define SQLITE_OMIT_AUTHORIZATION and the auth feature is omitted. I define SQLITE_OMIT_DATETIME_FUNCS and date and time functions are omitted. But what when I define SQLITE_OMIT_VACUUM? If OMIT is _not_ defined the vacuum code is compiled. Of course! But why is there an additional test for SQLITE_OMIT_VACUUM? This is true - so far I understand - if SQLITE_OMIT_VACUUM is defined and it's value is one. The value one traditionally means TRUE. That means I define OMIT TRUE and it's not omitted? That's strange! Please explain your thoughts and understanding of this code. Best, Bernhard
[sqlite] Re: *** Please help ***
I found out why Views were returning all NULLs. I have made a modified version of sqlite3_exec. Mine checks for NULL returned from sqlite3_column_decltype, for which Sqlite3 now returns NULL. Am I right that this is so? My COM wrapper depends on having the column data types returned for Views. Can you please make it work again, as this is vital. thanks -brett EzTools Support wrote: Hello. Can someone please help with this issue. It may turn out to be a bug in Sqlite 3. I have the following fairly basic SQL View that works fine with Sqlite 2.x: CREATE VIEW Invoices AS SELECT Customers.CustomerID, Customers.CompanyName,Orders.OrderID, OrderDetails.ProductID, OrderDetails.Quantity FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID When I create and run this view in 3.x, I get all of the rows back, but all values are NULL! If I run the SELECT statement apart from the view, it I get the data back fine. But if I select * FROM Invoices, I get all of the rows back, but all values are NULL. Another interesting thing is that when selecting from the View, the column names are without the table prefix. That is, CustomerID, CompanyName, OrderID, et. But if I select from the tables directly, the column names have the table prefix, Customers.CustomerID, Customers.CompanyName, Orders.OrderID I have put the database up on my website here (Zip file): www.eztools-software.com/downloads/northwind.zip This is happening in all version 3.0.5-3.0.7. Can someone please download this DB and investigate. If you want to test the same view and data with a v2.x database, you can get the SqlitePlus28 download from here: www.eztools-software.com/downloads/sqliteplus.exe TIA -Brett Goodman
[sqlite] sqlite3 DEF file
this file is incomplete, and has been for some time. here is the correct version. would somebody check it in, since I dont have CVS access handy: EXPORTS sqlite3_aggregate_context sqlite3_aggregate_count sqlite3_bind_blob sqlite3_bind_double sqlite3_bind_int sqlite3_bind_int64 sqlite3_bind_null sqlite3_bind_parameter_count sqlite3_bind_parameter_index sqlite3_bind_parameter_name sqlite3_bind_text sqlite3_bind_text16 sqlite3_busy_handler sqlite3_busy_timeout sqlite3_changes sqlite3_close sqlite3_collation_needed sqlite3_collation_needed16 sqlite3_column_blob sqlite3_column_bytes sqlite3_column_bytes16 sqlite3_column_count sqlite3_column_decltype sqlite3_column_decltype16 sqlite3_column_double sqlite3_column_int sqlite3_column_int64 sqlite3_column_name sqlite3_column_name16 sqlite3_column_text sqlite3_column_text16 sqlite3_column_type sqlite3_commit_hook sqlite3_complete sqlite3_complete16 sqlite3_create_collation sqlite3_create_collation16 sqlite3_create_function sqlite3_create_function16 sqlite3_data_count sqlite3_errcode sqlite3_errmsg sqlite3_errmsg16 sqlite3_exec sqlite3_finalize sqlite3_free sqlite3_free_table sqlite3_get_auxdata sqlite3_get_table sqlite3_interrupt sqlite3_last_insert_rowid sqlite3_libversion sqlite3_mprintf sqlite3_open sqlite3_open16 sqlite3_prepare sqlite3_prepare16 sqlite3_progress_handler sqlite3_reset sqlite3_result_blob sqlite3_result_double sqlite3_result_error sqlite3_result_error16 sqlite3_result_int sqlite3_result_int64 sqlite3_result_null sqlite3_result_text sqlite3_result_text16 sqlite3_result_text16be sqlite3_result_text16le sqlite3_result_value sqlite3_set_authorizer sqlite3_set_auxdata sqlite3_snprintf sqlite3_step sqlite3_total_changes sqlite3_trace sqlite3_user_data sqlite3_value_blob sqlite3_value_bytes sqlite3_value_bytes16 sqlite3_value_double sqlite3_value_int sqlite3_value_int64 sqlite3_value_text sqlite3_value_text16 sqlite3_value_text16be sqlite3_value_text16le sqlite3_value_type sqlite3_vmprintf sqlite3_version
Re: [sqlite] problem with sqlite velocity
alessandro bonvicini wrote: Hi all, I ask you an help because I don't understand what I am doing wrong with sqlite. First of all,anyway, I would like to thanks all of you for this software. Now I have some problem but I only need to understand where are my errors and I am sure that sqlite will be as fast as everyone says. I have two tables: parole (6 million rows, 10 field (id,word ..etc , all varchar 255, except id that is a number)) and mega (200.000 rows and two field : id,word, id is number and word varchar), So I create some index: two on parole: id_paroleidx and word_paroleidx and two on mega, id_megaidx and word_megaidx. Then I do the the following query : select parole.id, mega.id from parole,mega where mega.word=parole.word; This query take 7 minutes to get all output. The output is redirected to file to avoid terminal, PRAGMAS ,default_cache_size 30 and default_temp_store MEMORY, and synchronization is off. The same table in mysql with the same index take 20 seconds to output all results. This behaviour is the near the same on windows and on linux. With default PRAGMAS parameter sqlite performance are more slow. Any ideas ? Thank a lot in advance Alex Try: select parole.id, mega.id from parole JOIN mega ON mega.word=parole.word; Please, post the table schema and some sample data (so we are able to make tests quickly). Paolo
[sqlite] problem with sqlite velocity
Hi all, I ask you an help because I don't understand what I am doing wrong with sqlite. First of all,anyway, I would like to thanks all of you for this software. Now I have some problem but I only need to understand where are my errors and I am sure that sqlite will be as fast as everyone says. I have two tables: parole (6 million rows, 10 field (id,word ..etc , all varchar 255, except id that is a number)) and mega (200.000 rows and two field : id,word, id is number and word varchar), So I create some index: two on parole: id_paroleidx and word_paroleidx and two on mega, id_megaidx and word_megaidx. Then I do the the following query : select parole.id, mega.id from parole,mega where mega.word=parole.word; This query take 7 minutes to get all output. The output is redirected to file to avoid terminal, PRAGMAS ,default_cache_size 30 and default_temp_store MEMORY, and synchronization is off. The same table in mysql with the same index take 20 seconds to output all results. This behaviour is the near the same on windows and on linux. With default PRAGMAS parameter sqlite performance are more slow. Any ideas ? Thank a lot in advance Alex
