Yes, there will be a performance hit 'cause when you access a column SQLite will loop through the columns in the row to find the target column. If the data is that sparse then I would suggest a different format. One that I've used a lot and have been pleased with is the following:
Results - ResultID, ResultName (or whatever primary identifiers there are) ResultDetails - ResultDetailID, ResultID, FieldID, Value Fields - FieldID, FieldRef so for each "record" in your original you create a Results record and then for each "column" in the original you get a ResultDetails row. The data is quickly retrieved and the advantage is the format is very flexible and can store pretty much anything. If your data is that sparse and I would assume flexible in that you may be adding columns as time goes on, then the above format should work well for you. Best regards, Sam ------------------------------------------- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -----Original Message----- From: Carl Jacobs [mailto:[EMAIL PROTECTED] Sent: Friday, June 29, 2007 2:33 AM To: sqlite-users@sqlite.org Subject: [sqlite] Sparse Data Sets I'm planning to use SQLite in an application that has a lot of rows (1M+) with a moderate number of columns (about 50) with the data being somewhat sparsely populated - ie each row may only have data in a primary column and 5 other columns. Will I get a performance hit - in terms of the size of the file - for all the unused columns? The other option would be to have a table per column, but then I need an extra column (primary) per table to tie the data together. I want to store (about 50) pieces of information each of which may or may not be logged at a reasonably high rate. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
