Is there any reason to use for two sizes of fields in my database?

Question

Once upon a time, when I was a young lat, I was engaged in assembly and optimization programming. Today, I mainly focus on creating web applications (this is also good ...). However, whenever I create fields for database tables, I find myself using values ​​like 16, 32, and 128 for text fields, and I try to combine boolean values ​​into SET data fields.

Is a text field of length 9 to make my database slower in the long run, and will I really help it by specifying a field length that is more convenient for memory alignment?

Answer 1

Database optimization is not at all like machine code optimization. With databases, most of the time when you want to reduce disk I / O and wastefully trying to align the fields, it will make fewer records in the disk block / page. In addition, if any kind of alignment is beneficial, the database engine will do this automatically for you.

The most important thing is indexes and how well you use them. Trying the trick to gather more information in a smaller space can ultimately make it difficult to get good indexes. (Do not overdo it, however, not only indexes slow down INSERT and UPDATE for indexed columns, but also mean more work for the scheduler, which should take all the possibilities into account.)

Most databases have an EXPLAIN command; try to use it when selecting (in particular, with multiple tables) to understand how the database engine works.

Answer 2

The size of the field itself may be important, but usually for text, if you use nvarchar or varchar, it doesn't really matter. Since the DB will use what you use. the following will have a greater effect on the speed of your SQL:

no more columns needed. A large table in terms of columns means that the database is less likely to find results for your queries on the same disk page. Note that this is true even if you only ask for 2 out of 10 columns in your selection ... (there is one way to combat clustered indexes, but this can only apply to one limited scenario).

You should give more details on the types of design problems / alternatives you plan to get in order to get additional advice.

Answer 3

Something that is implied above, but which may become apparent. You have no way of knowing what the computer is actually doing. This is not like the old days when you could look at the assembler and know well what steps the program would take. A value that "looks" like it in the CPU register may need to be extracted from the cache on the chip or even from the disk. If you are not writing assembler, but using an optimizing compiler or, even more confidently, bytecode in the runtime (Java, C #), give up hope. Or give up anxiety, which is a better idea.

It will probably take thousands, or perhaps tens of thousands of machine cycles, to write or obtain this database value. Do not worry about 10 additional cycles due to the complete alignment of words.