Optimizing performance of ASP.NET online store

Ecom
1

I’m working on an online store using ASP.NET 3.5 and C#. The app has a 3-tier setup (Data, Business, UI) and uses stored procedures in SQL Server 2005 for CRUD operations. But it’s running really slow and I can’t figure out why.

The site is on shared hosting abroad. The web and database servers are separate, and the database server hosts about 1000 databases.

I’ve looked at the transaction model but didn’t spot any issues. What’s the best way to find the bottleneck? Are there any tools or techniques I should try to pinpoint the problem?

Here’s a simplified example of how I’m calling a stored procedure:

public DataSet GetProducts()
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand('spGetAllProducts', conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
}

Any advice on improving this or other parts of the app would be great. Thanks!

2

hellu, yur shared hosting might be the issue. try running a profiler on ur database to identify slow queries, and think about caching data. async methds may also help performance.

3

Hey there! I’m pretty curious about your setup. Have you considered the impact of having 1000 databases on that shared server? That’s a lot! :open_mouth:

I wonder if you’ve tried any client-side optimizations? Maybe some AJAX to load parts of the page separately? Or even a bit of caching on the browser side?

Oh, and what about your images? Are they optimized? Sometimes big product photos can really slow things down.

Just throwing ideas out there - what do you think? Have you tried any of these yet?

4

It’s worth considering that your performance bottleneck might be network-related, given the separation of web and database servers. To diagnose this, I’d recommend using tools like Fiddler or browser developer tools to monitor network requests and response times. Additionally, implement logging in your application to track execution times of key operations. This can help pinpoint whether the slowdown is occurring in your code, database queries, or network latency.

Regarding your code example, consider using SqlDataReader instead of DataSet for better performance when dealing with large datasets. Also, look into connection pooling to optimize database connections. Finally, if feasible, explore moving to a dedicated hosting environment for more consistent performance.