I have a table with over 40m records, and i found the read from this table incredible slow. The table itself has around 70 columns, and most of them have normal data type, like nvarchar(20) int,bit, etc.. I have only a few with nvarchar(1000) and nvarchar(4000) like 3-5 of them.
If I perform a select top1 row i see my IO cost is over 2000
When i want to select everything from the table, that takes more than an hour without any transform. Is that normal? Is there any way to improve it?
if i could lower my Io cost, then maybe it could increase my performance.
1条答案
按热度按时间t3psigkw1#
Each nvarchar(4000) can use up to 8 000 bytes. 8kb is one page... So potentially with 5 columns of nvarchar(4000) can potentially read 5 pages... 70 columns with nvarchar(20) is 2 800 bytes. The result is that one row can use 6 pages...
40m rows (not record... that is a cobol term) will use a maximum of 40 000 000 x 6 pages, = 240 m pages...
Consider yourself happy that your query just cost 2816 in terms of IO just with an obese table....
Don't you think the design of your table respects all the normalization theory ?