Speeding a database is a big subject, also, you need to tell us whether you have problems in querying the database, updating or deleting data. However, there are known facts that you can check:
0 - Make sure you are using the appropriate driver to connect to the database and that you are using the correct way to connect (for example using ODBC may be slower than a native driver). Use connection pools.
1 - Have a correct design.
2 - Have PK and FK defined with the same data type to speed joins.
3 - Create indexes on PK and FK for non-trivially sized tables
4 - Choose the right type of indexes
5 - Optimize your selects. Avoid "SELECT *" and don't join to tables unless you need to
6 - Qualify your selects properly so that the number of data rows returned is just enough to do the business function at hand. Don't return all the data all the time unless you need to.
7 - Avoid using large binary objects in queries. Consider removing photos from database to file system storage if possible.
8 - Use aggregate functions and ORDER BY wisely. Choose your clustering index so that you could avoid sorts if possible.
8a - Avoid using Not in WHERE and attempt to avoid complex transform operations.
8b - Make sure indexes are used in your queries otherwise tune the queries to utilize indexes or create the necessary indexes.
9 - Use a single column to build the key rather than multiple columns when possible.
10 - Check your physical design of tables and indexes. See how your space is allocated
11 - Consider index rebuilding and file system defragmanation
12 - Check strategies for fine tuning full-text-search (if you are using it) - See: FTS
13 - Determine if your network speed is good enough.
14 - Compare the transaction time in your ASP.NET application vs. the same query or transaction when performed on a console. The difference should be close. If you find a big variation then the problem may be with how you connect, the network or some other problem.