I had this connection pool problem:
How to solve a connection pool problem between ASP.NET and SQL Server?
And I am now tracing using the SQL profiler, and I found some queries take about 400 duration to finish and return data.
Is that value too much?
Could it cause the previous connection pool problem?
400 means 400 ms, or 0.4 seconds.
Whether that is too long really depends. If this is your front page or other high traffic area, then that's too much unless this is an intranet site. You may need to rethink how that data can be obtained, or use caching to reduce the number of times you actually have to run the query.
If this is a report, or other more involved page, then 400 ms is not out of line. At best you might want to check if the query can be done a bit faster to improve the overall user experience.
You may also want to run the Tuning Advisor to determine if the queries are only running slow because you don't have the correct indexes.
It depends on your application. That's less than 1/2 a second. If you're dealing with tens of millions of rows of data then that might be pretty good. On the other hand, if you've got 1000s of users, your datasets are small, and this query is being run constantly, then maybe you need to look at the performance of the query.
That sort of depends on what the queries are being used for, how often they are running, and what the user expectation is for response time.
If the query is running slow over a small set of data, there's probably a problem with your SQL, your indexes, or your network connection between your web server and your database server. Optimization of the query is probably your best first task to address this.
Also, if the queries are running often, or in an area of the system where you need a fast response time, you may want to look at optimizing the query a little.
If the query NEEDS to run for that length of time and cannot be optimized further, but the data doesn't change very often, perhaps caching the result in the application would help improve response time.
If you have a small load of users who don't care about performance (ex. admins running a report) then you don't have to worry.