enterprise application performance RRS feed

  • Question

  • I'm curently trying to improve the performance of a multi tenancy database application. 


    There are multiple web servers and database servers for load balancing.  The application is accessed via internet.  Some of the components are in COM+.


    The application architecture is N-tier.


    When running the application I noticed lots of queries to the database.  Investigating this I found the middle tier sent lots of individual queries to the database and some of these were in loops. 


    We assumed the performance issues are related to too many round trips to the database so we have removed the queries from loops and batched up most of the queries to send to the database in one query.


    I have two questions; 

    1. How can we estimate the cost of a round trip to the database given that we have no access to the production environment?

    2. What other things should we consider to improve performance?




    Tuesday, October 14, 2008 12:26 PM

All replies

  • It all dependes, isn't that a loverly answer Stick out tongue

    Well it really does.

    1) You really can't estimate, you have to test it in an environment that looks like the production one, with the same kind of hardware, same kind of database engine, same databases and the same kind of workload you anticipate that you will have in production. So what you need is a Stage environment where you can perform and measure your applications performance.

    All performance work always starts with setting a baseline, e.g defining what the normal perf with a normal load is, and then start tweaking, measure, tweak, measure, until you got the performance that you require. Require is the keyword here, you should only optimize performance where it is required not because there "might" be a problem. There is an old saying that applies here: "premature optimization is the root to all evil".

    2) If you do measure and find a performance issue, I would start to look here: to get a good starting point, as you can see there is multiple guidelines for different parts of your enterprise system and you need to apply them wisely for your scenario.
    Friday, October 17, 2008 9:03 AM
  • Hi,


    I would recommend the following:


    Instead of creating bunch of queries and executing them, try to create Stored Procedures (unless it is explicitly probibited by your organization policies) and execute them. SPs will run faster than individual queries since they are pre compiled.


    Use Database Connection Blocks so that you can re use the connections.


    Check the indexes of the database.


    Some of the flip sides are, while creating SPs be careful not to put business rules in the procedures. The SPs should normally execute only the CRUD operations and not more than that.  Too many indexes may affect the performance too.

    Friday, October 17, 2008 9:17 AM
  • "SPs will run faster than individual queries since they are pre compile"

    If it's a SQL Server 7+ this statement is actually not true. If you use parameterized queries from the application layer they will get compiled as well and hence get the same performance gain as a pre-compiled proc.

    You can read more on this misconception here:

    What is true though is that if you do have queries that query a lot of data and you in the application layer filter that out or do some other kind of reduction of the query result, you might gain in perf by using SP's, but the perf you affect is the network since the network will get less load, if you process the same data inside the sp as you would in your app; SQL server itself will take a perf hit and thus you might end up in a situation where the SQL Server chokes much earlier then it should otherwise, which will hurt scalability instead.

    So in short, it all depends, SPs are not the ultimate performance solution and you really need to look at the full scenario to choose the most appropriate query strategy.

    Friday, October 17, 2008 9:26 AM
  • Putting sql statements in stored proedures only give us a net gain of 10% on the overall process.  We saw a higher gain by increasing the cachesize of our recordsets!  The performance issues are due to round trips to the database and too many connections. 


    Any other area's to investigate?


    However, we do have some connection issues.  what are database connection blocks? Is this a Sql server configuration?  how do i check them?


    Friday, October 17, 2008 1:49 PM
  • Is it possible to create a Test envoriment to estimate against?

    Tuesday, November 4, 2008 4:47 AM