spring mvc - ajax running slow +500ms for each call

We are working on a Spring mvc project and are having some performance issues with our ajax calls. Each call takes a minimum of 500ms :(. Has someone had this problem before? Is there a nice way to profile this issue?

We make use of:

• spring mvc
• spring security
• hibernate 3.6.1
• tomcat 7
• jquery 1.4.4

After using the spring insight profiler we found out that this is the problem:

    try {
        HibernateTemplate ht = new HibernateTemplate(sf);
        List<Route> r = ht.findByNamedParam("select r from Route r inner join r.carPoolers as carPooler where (( r.owner.id  = :userid ) or ( carPooler.user.id = :userid )) AND r.id =:routeID", new String[]{"userid", "routeID"} , new Object[]{ u.getId() , id});
        if (r.size() == 1) {
            return r.get(0);
        } else {
            return null;
        }
    } catch (DataAccessException ex) {
        LogFactory.getLog(RouteRepository.class).fatal(ex);
        return null;
    }

This query takes atleast 460ms ...

SELECT ROUTE0_.ID AS ID4_, ROUTE0_.ARRIVALTIME AS ARRIVALT2_4_, 
    ROUTE0_.CAR_ID AS CAR13_4_, ROUTE0_.DATE AS DATE4_, 
    ROUTE0_.DAYOFWEEK AS DAYOFWEEK4_, ROUTE0_.DEPARTURETIME AS 
    DEPARTUR5_4_, ROUTE0_.ENDDATE AS ENDDATE4_, ROUTE0_.MESSAGEID 
    AS MESSAGEID4_, ROUTE0_.OPENSEATS AS OPENSEATS4_, 
    ROUTE0_.OWNER_ID AS OWNER14_4_, ROUTE0_.ROUTECACHE_ID AS 
    ROUTECACHE11_4_, ROUTE0_.ROUTEOPTIMIZED AS ROUTEOPT9_4_, 
    ROUTE0_.START_ID AS START12_4_, ROUTE0_.STOP_ID AS STOP10_4_ 
FROM ROUTE ROUTE0_ INNER JOIN CARPOOLER CARPOOLERS1_ ON 
    ROUTE0_.ID=CARPOOLERS1_.ROUTEID 
WHERE (route0_.owner_id=? or carpoolers1_.user_id=?) and route0_.id=?

SELECT CAR0_.ID AS ID5_3_, CAR0_.BRAND_ID AS BRAND8_5_3_, CAR0_.CARNAME 
    AS CARNAME5_3_, CAR0_.CARTYPE AS CARTYPE5_3_, CAR0_.IMAGEURL AS 
    IMAGEURL5_3_, CAR0_.PRICEKM AS PRICEKM5_3_, CAR0_.SEATS AS 
    SEATS5_3_, CAR0_.USER_ID AS USER7_5_3_, BRAND1_.ID AS ID6_0_, 
    BRAND1_.BRANDNAME AS BRANDNAME6_0_, USER2_.ID AS ID0_1_, 
    USER2_.EMAIL AS EMAIL0_1_, USER2_.FACEBOOKID AS FACEBOOKID0_1_, 
    USER2_.FIRSTNAME AS FIRSTNAME0_1_, USER2_.GENDER AS GENDER0_1_, 
    USER2_.IMAGEURL AS IMAGEURL0_1_, USER2_.LANGUAGE_ID AS 
    LANGUAGE12_0_1_, USER2_.LASTNAME AS LASTNAME0_1_, 
    USER2_.MOBILEPHONE AS MOBILEPH8_0_1_, USER2_.PASSWORD AS 
    PASSWORD0_1_, USER2_.SMOKER AS SMOKER0_1_, USER2_.TELEPHONE AS 
    TELEPHONE0_1_, LANGUAGE3_.ID AS ID9_2_, LANGUAGE3_.LANGUAGE AS 
    LANGUAGE9_2_, LANGUAGE3_.LANGUAGECODE AS LANGUAGE3_9_2_ 
FROM CAR CAR0_ LEFT OUTER JOIN BRAND BRAND1_ ON 
    CAR0_.BRAND_ID=BRAND1_.ID LEFT OUTER JOIN USER USER2_ ON 
    CAR0_.USER_ID=USER2_.ID LEFT OUTER JOIN LANGUAGE LANGUAGE3_ ON 
    USER2_.LANGUAGE_ID=LANGUAGE3_.ID 
WHERE car0_.id=?

SELECT USER0_.ID AS ID0_1_, USER0_.EMAIL AS EMAIL0_1_, 
    USER0_.FACEBOOKID AS FACEBOOKID0_1_, USER0_.FIRSTNAME AS 
    FIRSTNAME0_1_, USER0_.GENDER AS GENDER0_1_, USER0_.IMAGEURL AS 
    IMAGEURL0_1_, USER0_.LANGUAGE_ID AS LANGUAGE12_0_1_, 
    USER0_.LASTNAME AS LASTNAME0_1_, USER0_.MOBILEPHONE AS 
    MOBILEPH8_0_1_, USER0_.PASSWORD AS PASSWORD0_1_, USER0_.SMOKER 
    AS SMOKER0_1_, USER0_.TELEPHONE AS TELEPHONE0_1_, LANGUAGE1_.ID 
    AS ID9_0_, LANGUAGE1_.LANGUAGE AS LANGUAGE9_0_, 
    LANGUAGE1_.LANGUAGECODE AS LANGUAGE3_9_0_ 
FROM USER USER0_ LEFT OUTER JOIN LANGUAGE LANGUAGE1_ ON 
    USER0_.LANGUAGE_ID=LANGUAGE1_.ID 
WHERE user0_.id=?

SELECT ROUTECACHE0_.ID AS ID7_2_, ROUTECACHE0_.AANTALM AS AANTALM7_2_, 
    ROUTECACHE0_.AANTALMIN AS AANTALMIN7_2_, ROUTECACHE0_.ACTIVE AS 
    ACTIVE7_2_, ROUTECACHE0_.JSON AS JSON7_2_, 
    ROUTECACHE0_.LOCATIONS AS LOCATIONS7_2_, 
    ROUTECACHE0_.LOCATIONSOPTIMIZED AS LOCATION7_7_2_, 
    ROU开发者_StackOverflow中文版TECACHE0_.ROUTEOPTIMIZED AS ROUTEOPT8_7_2_, 
    ROUTECACHE0_.START_ID AS START10_7_2_, ROUTECACHE0_.STOP_ID AS 
    STOP9_7_2_, LOCATION1_.ID AS ID2_0_, LOCATION1_.LANG AS 
    LANG2_0_, LOCATION1_.LAT AS LAT2_0_, LOCATION1_.NUMBER AS 
    NUMBER2_0_, LOCATION1_.STREET AS STREET2_0_, LOCATION1_.ZIPCODE 
    AS ZIPCODE2_0_, LOCATION2_.ID AS ID2_1_, LOCATION2_.LANG AS 
    LANG2_1_, LOCATION2_.LAT AS LAT2_1_, LOCATION2_.NUMBER AS 
    NUMBER2_1_, LOCATION2_.STREET AS STREET2_1_, LOCATION2_.ZIPCODE 
    AS ZIPCODE2_1_ 
FROM ROUTECACHE ROUTECACHE0_ LEFT OUTER JOIN LOCATION LOCATION1_ ON 
    ROUTECACHE0_.START_ID=LOCATION1_.ID LEFT OUTER JOIN LOCATION 
    LOCATION2_ ON ROUTECACHE0_.STOP_ID=LOCATION2_.ID 
WHERE routecache0_.id=?

 SELECT ROUTECACHE0_.ROUTECACHESPUNTENTUSSEN_ID AS ROUTECAC1_1_, 
    ROUTECACHE0_.ROUTECACHETUSSENPUNTEN_ID AS ROUTECAC2_1_, 
    LOCATION1_.ID AS ID2_0_, LOCATION1_.LANG AS LANG2_0_, 
    LOCATION1_.LAT AS LAT2_0_, LOCATION1_.NUMBER AS NUMBER2_0_, 
    LOCATION1_.STREET AS STREET2_0_, LOCATION1_.ZIPCODE AS 
    ZIPCODE2_0_ 
FROM ROUTECACHE_LOCATION_PUNTEN ROUTECACHE0_ LEFT OUTER JOIN LOCATION 
    LOCATION1_ ON 
    ROUTECACHE0_.ROUTECACHETUSSENPUNTEN_ID=LOCATION1_.ID 
WHERE routecache0_.routecachesPuntenTussen_id=?

 SELECT CARPOOLERS0_.ROUTEID AS ROUTEID5_, CARPOOLERS0_.ID AS ID5_, 
    CARPOOLERS0_.ID AS ID3_4_, CARPOOLERS0_.APPROVED AS 
    APPROVED3_4_, CARPOOLERS0_.ONETIME AS ONETIME3_4_, 
    CARPOOLERS0_.ROUTEID AS ROUTEID3_4_, CARPOOLERS0_.START_ID AS 
    START5_3_4_, CARPOOLERS0_.STOP_ID AS STOP7_3_4_, 
    CARPOOLERS0_.USER_ID AS USER6_3_4_, LOCATION1_.ID AS ID2_0_, 
    LOCATION1_.LANG AS LANG2_0_, LOCATION1_.LAT AS LAT2_0_, 
    LOCATION1_.NUMBER AS NUMBER2_0_, LOCATION1_.STREET AS 
    STREET2_0_, LOCATION1_.ZIPCODE AS ZIPCODE2_0_, LOCATION2_.ID AS 
    ID2_1_, LOCATION2_.LANG AS LANG2_1_, LOCATION2_.LAT AS LAT2_1_, 
    LOCATION2_.NUMBER AS NUMBER2_1_, LOCATION2_.STREET AS 
    STREET2_1_, LOCATION2_.ZIPCODE AS ZIPCODE2_1_, USER3_.ID AS 
    ID0_2_, USER3_.EMAIL AS EMAIL0_2_, USER3_.FACEBOOKID AS 
    FACEBOOKID0_2_, USER3_.FIRSTNAME AS FIRSTNAME0_2_, 
    USER3_.GENDER AS GENDER0_2_, USER3_.IMAGEURL AS IMAGEURL0_2_, 
    USER3_.LANGUAGE_ID AS LANGUAGE12_0_2_, USER3_.LASTNAME AS 
    LASTNAME0_2_, USER3_.MOBILEPHONE AS MOBILEPH8_0_2_, 
    USER3_.PASSWORD AS PASSWORD0_2_, USER3_.SMOKER AS SMOKER0_2_, 
    USER3_.TELEPHONE AS TELEPHONE0_2_, LANGUAGE4_.ID AS ID9_3_, 
    LANGUAGE4_.LANGUAGE AS LANGUAGE9_3_, LANGUAGE4_.LANGUAGECODE AS 
    LANGUAGE3_9_3_ 
FROM CARPOOLER CARPOOLERS0_ LEFT OUTER JOIN LOCATION LOCATION1_ ON 
    CARPOOLERS0_.START_ID=LOCATION1_.ID LEFT OUTER JOIN LOCATION 
    LOCATION2_ ON CARPOOLERS0_.STOP_ID=LOCATION2_.ID LEFT OUTER 
    JOIN USER USER3_ ON CARPOOLERS0_.USER_ID=USER3_.ID LEFT OUTER 
    JOIN LANGUAGE LANGUAGE4_ ON USER3_.LANGUAGE_ID=LANGUAGE4_.ID 
WHERE carpoolers0_.RouteId=?

, ty in advance

Give Spring Insight a go. Here's a demo:

http://www.youtube.com/watch?v=P_EskssNDU8

and here's some docco:

http://static.springsource.com/projects/tc-server/6.0/devedition/cinintro.html

If you're using STS it's fairly simple to hook it up to your webapp and it'll show you end to end performance and any bottlenecks you have.

There's a really good blog post with some screenshots here

Based on your posting, it sounds like the SQL query is the bottleneck (460ms out of 500ms). There are a few things I would recommend you try:

• Try changing your Hibernate session flush mode. In a nutshell, by default whenever you execute a query, Hibernate has to iterate through every object in the Session to determine if anything needs to be flushed to the database before executing the query. Depending on how much stuff is in your session, this process can take longer than the actual query to execute! Here are some links with more information about changing the flush mode.

https://forums.hibernate.org/viewtopic.php?p=2263250&sid=3c1db460d6d28155799cf95f76730606 http://docs.jboss.org/hibernate/core/3.5/api/org/hibernate/FlushMode.html

• Try placing the check against the route ID first. That may be faster, depending on what database you are using
• Run your SQL through a profiling tool. All the major databases have them. The profile tool will show you what part of your query is the most expensive, and may even show you where an index could cut the query time significantly.

Please use Fiddler2

http://www.fiddler2.com/fiddler2/

you will be able to see if the timestamps of request/response and to see weather the bottle neck is on server or client. you an do it by clicking right mouse button on the request in fiddler.

Hope it helps.