database - What is the relative cost of reading rows vs. columns in SQLite? -

I am trying to create a cover around the SQLite database, which is the shortest access. Will allow from NET applications. It is an idea that each unit has only one collection or attribute value as joints.

Priority is easy to use for developers rather than simplicity and performance, but I do not want to completely ignore memory usage and disc read times.

The architecture of my cover will mean that the primary primary key will be read in the first query (and then cached), although other features can be read later.

The question is, is it better to create a column of each property name (at run time), and by dragging columns corresponding to the requested properties, or requesting to record and attribute per attribute In the records of properties.

With the very nature of the application, I would not know in advance what order features will be read and whether all the features will be read or not.

Do not reissue the wheel, especially when you try to change it already Get round

So let's see how it is to find the current wheel, how it compares.

1) Existing wheel (table with columns) we have a table unit in which there is a structure like this:

  id integer Primary key attr1 INTEGER attr2 real - it holds a date attr3 ATT4 text   
 2) re-initialized wheel, the attributes table says we create a table  EntityAttributes  for it We will keep all the features from  Entity  to 1), but the question is, what does this look like? Can we use all the code values ​​as a type of  TEXT  and handle the previous conversion in the application? Then the table can look like this:  
  entity_id INTEGER attr_name text atrulip text primary key (entity_id, atri_name)   
 This is not really such a big drama Because SQLite internally stores everyone as text, but when it comes to any normal RDBMS it is a big loss.  
 The option is to go to a complex feature table that supports several types. It may look like this:  
  entity_id INTEGER attr_name text atty_ int iNTEGER attr_real real attr_text text primary key (entity_id, attr_name)   
 to use another It is possible that a master table for each data type (which is in the form of a POK surrogate key from the main attribute table) for a surrogate key and the pair with the table (unit_ID, antitaide), but this is too much for this discussion Used to be.  
 Most importantly, right from the beginning, there are problems when we choose our wheel.  
 Now we go ahead and talk about performance. All prices from one row are usually stored next to (or off) each other, so the IO costs are generally lower. It can not be said about many rows, especially if they are not put one after the other, you can always modify the database file to optimize access, but the rows are not created one after the other If so, it will not help much. Another disadvantage to your solution is that more storage is needed than actually a natural solution (not much, but still more).  
 So far the good? How about it: You have the following simple SQL query:  
  SELECT id, attr1, attr2 unit by ORDER attr1;   
 Try doing this by using attribute per line view See how easy it is and it's just an example, many others come to mind.  
 Characteristics compared to conventional RDBMS table solutions look bad only in your case (for once because they work better in maintaining column values, line together and for others because they are called ' Contrary to various types of support) when the key-value data store is to be used, such as radis is a separate matter.  
 In everything, there is a lot to say a lot about per line approach and there really is no use if you have a table of certain column features useful, when you really need it There are some unknown numbers (dynamic) qualities to be kept in mind, but in your case I want to be with natural solutions.