SQL：long varchar 及其对性能的影响
我正在使用一个非常慢的数据库。我们使用 ASP.NET 来访问/填充数据库。我是数据库新手，我发现有些事情没有正确完成。我想获得有关这些的更专业的意见。
在我们的数据库中，varchar(255) 已用于几乎所有字符类型字段，包括邮政编码、文本 ID、姓名、地址和电话号码。一张表大约有60个，其中一些也用于比较。我的问题是这对性能有多大影响？我应该改变它还是保持原样。我特别是在寻找可以突出性能问题的答案。
编辑：所以我认为我需要处理连接而不是 varchar。有人可以发布我的问题的答案我可以将连接池与 SQLDatasource 一起使用吗？。登录大约需要 20 秒，之后就可以接受了，但仍然很慢。数据库和应用程序位于同一台机器上。
I am working with a database which is very slow. We use ASP.NET to access/populate the database. I am new to the database and I found that some of the thing are not properly done. I want to get a more knowledgeable opinion about these.
In our database varchar(255) has been used for almost all character type fields that includes zip_codes, text_ids, name ,address and phone numbers. There are about 60 of them in one table and some of them are used in comparison as well. My question is how much of a drag that is on performance? Should I change it or leave it as it is. I am esp looking for an answer that can highlight the performance issue.
Edit: So I think I need to work on connection rather than varchar. Can someone post answer to my question Can I use connection pooling with SQLDatasource?. It takes about 20 sec to login and after that it becomes acceptable but still slow. Database and Application are on same machine.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
保持原样。此定义应该没有问题（至少只要 MS SQL Server 上的总记录大小不超过 8KB）。您的问题很可能与您的查询有关，因此隐含地与索引定义和使用有关。
由于除了主键之外没有任何索引，因此您应该检查运行缓慢的查询并将索引添加到
WHERE子句中的过滤字段。您可能需要考虑以下有关 SQL Server 性能的提示汇编：SQL Server 性能提示和指南
Leave as it is. There should be no problems with this definition (at least as long as the total record size doesn't exceed 8KB on MS SQL Server). Your problem is very likely related to your queries and thus implicitly to index definition and usage.
As you don't have any indexes besides the primary key ones, you should check your slow running queries and add indexes to the filter fields in your
WHEREclause. You might want to consider the following compilation of tips regarding SQL Server performance:SQL Server Performance Tips and Guidelines
不用担心这个。首先，您需要查看索引。接下来，您需要考虑调整特定的长时间运行的查询。根据我的经验，糟糕的查询编写是性能不佳的最常见原因之一。但听起来你基本上没有索引，所以必须先行。
Do not worry about this. First you need to look at indexing. Next you need to look at tuning the particular long-running queries you have. Poor query writing is in my experience one of the most common causes of poor performance. But is sounds like you have basically no indexing so that must come first.
与使用邮政编码、电话号码等数字相比，
varchar字段的长度问题不大。数字占用的空间比字符串少很多。例如：
28245作为smallint需要 2 个字节'25245'作为varchar(255)需要（数据的 5 个字节）+（存储长度的 2 个字节）= 7 个字节由于字符串的排序方式与数字不同，您还会遇到排序方面的奇怪问题。
话虽如此，
varchar(20)和varchar(255)之间没有性能差异。 Varchar 是 varchar 是 varchar，唯一的区别是它们可以在字段中占用的空间 LIMIT。The length of the
varcharfields is less of an issue than using them for numerics like zip codes, phone numbers, etc.Numerics take up a LOT less space than a string. For instance:
28245as asmallinttakes 2 bytes'25245'as avarchar(255)takes (5 bytes for data) + (2 bytes to store length) = 7 bytesYou also will have weird issues with sorting since strings sort differently than numerics.
That being said, there's no performance difference between
varchar(20)andvarchar(255). Varchar is varchar is varchar and the only difference is the LIMIT of the space they can take in the field.除非该表中有大量数据，否则我认为字段类型不会影响性能。每当我被要求考虑提高数据库的性能时，我通常可以通过改进访问数据的查询并在现有数据上创建更好的索引来产生巨大的影响。
另外，数据库和网站是否在同一台服务器上运行，或者两者之间的连接速度是否缓慢而导致延迟？
Unless you have a HUGE amount of data in this table I wouldn't think the field type will be impacting performance. Whenever I'm asked to look at improving the performance of a database I can usually make a big impact by improving the queries that access the data and create better indexes on the data that is already there.
Also, is the database and the website running on the same server or could there be a slow connection in between that's causing a delay?