Hi Jose, thanks for your explanations. :)
AFAIR it is a good practice to separate OLTP and OLAP databases. In this case you do not impact interactivity and feel free to rotate/transform/reformat data as you wish. WBR, Denis -----Original Message----- From: jose simas [mailto:[EMAIL PROTECTED] Sent: Thursday, September 14, 2006 4:42 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Very wide tables and performance Hi Dennis, This is an OLAP product and indeed we don't know upfront how much of that data is relevant to the processing. I had a normalized group of tables for those sets but it took an awfull time to load and to process. In practice the wider tables are usually 25,000 columns per 50 to 100 rows and it would make sense to "rotate" the data before processing and store it in 50 to 100 columns per 25k rows. But the problem is that the same application has to cope with sets with say 5 columns and some 100,000 rows. Rotating sometimes and not in others would increase the complexity of the code. The application is very "interactive" and we try to keep the data load as low as possible so load time is key for us. SQLite performance is oustanding in comparison with what we used before (Access), our load times improved up to ten times and with Access we were limited to 250 columns and the normalized version took forever to load one million data points. Of course the schemas are generated when the user loads their data... and you are right, they are unreadable :-) Cheers Jose On 9/14/06, Denis Povshedny <[EMAIL PROTECTED]> wrote: > Hi Jose! > > It is really hard to believe that you do not have a sparse matrix. I > mean that for every single row: from 20000 colums only a several > columns are used and others are nil. This is a point to perform > so-called normalizations for the table. The single exception what I > remember is OLAP databases. > > Anyway, I (and probably not only me) am very excited to see definition > for this table ;) > > WBR, Denis > > -----Original Message----- > From: jose simas [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 13, 2006 11:05 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Very wide tables and performance > > > Thanks for your answers! I am very happy with SQLite as it is I was > just wondering if I could improve it for this case. > > I am using tables with this configuration for performance reasons. I > have to support an indeterminate number of columns (user data) and a > "normal" design is not as fast as this solution. I can't remember the > results of the tests right now but the differences in loading data > into the database and reading it to memory were very large. > > Thanks, > Jose > > On 9/13/06, Dennis Cote <[EMAIL PROTECTED]> wrote: > > jose simas wrote: > > > My application uses SQLite as its file format and up to two of the > > > tables can have several thousand columns (up to 20 or 30 thousand > > > at > > > > times). > > > > > > When I open a connection there's a noticeable pause (around one > > > second on a file with a table of 7,000 columns, for example). > > > There is also a noticeable delay in sorting them by the primary > > > key. > > > > > > Is there anything I can do to favour this kind of tables? > > > > > Jose, > > > > What can you possibly be doing with tables that have that many > > columns? Are you sure you don't mean 20K-30K rows? In SQL a row > > corresponds to a record, and a column corresponds to a field in a > > record. > > > > If you really mean columns, then your best approach is probably to > > redesign your tables to move much of the data into other related > > tables. Can you give us some idea of your table schema and how it is > > used? > > > > There will be little or no benefit to compiling sqlite yourself. > > > > Dennis Cote > > > > -------------------------------------------------------------------- > > -- > > ------- > > To unsubscribe, send email to [EMAIL PROTECTED] > > > ---------------------------------------------------------------------- > -- > ----- > > > > > > ---------------------------------------------------------------------- > -- > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > ------------------------------------------------------------------------ > ----- > > > ---------------------------------------------------------------------- > ------- > To unsubscribe, send email to [EMAIL PROTECTED] > ------------------------------------------------------------------------ ----- > > ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
