SQL: long varchar and impact on peformance

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开发者_如何学C 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.

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 WHERE clause. 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.

The length of the varchar fields 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:

28245 as a smallint takes 2 bytes

'25245' as a varchar(255) takes (5 bytes for data) + (2 bytes to store length) = 7 bytes

You also will have weird issues with sorting since strings sort differently than numerics.

That being said, there's no performance difference between varchar(20) and varchar(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?