Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-13 : 13:06:24
|
| Yujiang writes "There are 2 char columns in a table. Do we have to go by power of 2 for the length to improve performance (for an application to read data)?Will char(17) and char(65) cause slower performance than char(32) and char(128)? Or are they the same?Answer is desperately expected." |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-13 : 13:39:26
|
| Not sure quite what you are asking but if you take the simplistic view that your table has only 2 columns then when they are char(17) and char(65) you will be able to fit approx twice as many rows per page for the first example leading to half the IO required to read the same data as compared to the second.Thus char(17) and char(65) will roughly double your IO efficency in an ideal world. This is as far as SQL is concerned - from an application point of view this means less network traffic , less data returned to client and thus speed. I'm not sure what you are getting at with this power of 2 business ??HTHJasper Smith |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-13 : 13:45:06
|
| No, it would not improve performance (don't know WHERE you got that idea)It makes no sense to pad a column to the nearest power of 2; you're just wasting space if you're not using it, plus you have extra spaces to trim from a char column.You can also use varchar, it will only store the actual characters in the string. The overhead is minor, and the extra efficiency in storage space can easily make up for it. |
|
|
|
yujiang
Starting Member
1 Post |
Posted - 2002-08-14 : 09:06:27
|
I don't believe the power-of-2 theory either. I know SQL Server reads data by 8pgx8k/pg. The second table with larger row size will definitely cause slower performance. However, a senior person at my company insisted his theory (I couldn't win the argument – simply not good at arguing without firm written evidence on my hand). I could not find any written material to support this strange idea. That's why I turned to this forum.Thanks, all.quote:
No, it would not improve performance (don't know WHERE you got that idea)It makes no sense to pad a column to the nearest power of 2; you're just wasting space if you're not using it, plus you have extra spaces to trim from a char column.You can also use varchar, it will only store the actual characters in the string. The overhead is minor, and the extra efficiency in storage space can easily make up for it.
|
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-14 : 10:11:01
|
| I've never read that the smallest chunck written to the buffer cache is 64K. Is that true? If I need data off a single page, will it cache the entire extent, even if its a mixed extent? I didn't think so. Granted some disk subsystems would require it, but I didn't think it had to be at least an extent.Jay White{0} |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-14 : 10:27:31
|
| There is an entry in Books Online under "pages, reading" that describes the process in more detail. Based on a perusal, I don't think you can generalize how it reads them, because it depends on the nature of the query. A covering index migh not require any reads from the data pages at all, for example. |
|
|
|
|
|
|
|
|
char datatype