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.
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: