Geek Answers Handbook

What is the recommended way to save matrix M by N in sql table

If I have a matrix M by N filled with data, what is the best way to store it in sql table for quick query? I can save 3 rows of the table (X, Y, Z) of the whole MN or just M rows with N columns. N is in the order of hundreds to thousands, M can be much larger. I guess the second way is faster for the query, since the query can be based on the names of known columns. But was it recommended to have as many columns in a table? thank,

+5
source share
2 answers

The second option (N columns) makes it difficult to select values ​​from an unknown column at compile time (you will have to dynamically generate selections). So I will go over with the table (X, Y, value) and make (x, y) the primary key.

+7
source

You will have a limit on the maximum number of columns a table can have on any RDBMS. For example, it SQL Server 2008has a maximum of 1,024 columns for a non-network table and 30,000 columns for a wide table, Oraclehas a maximum of 1,000 columns (although I'm not sure if this restriction has been changed). Therefore, I recommend your first option, in fact, I would choose this even without column restrictions.

+2
source

More articles:


All Articles