Results 1 to 6 of 6
  1. #1
    chemipot is offline Member
    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    Default HQL queries performance

    1
    down vote
    favorite
    I have a couple of HQL queryes that takes rather more time that they should. I am trying to improve the execution time by modifying the queries and I dont know how. Perhaps I am missing something. The first query:

    Java Code:
    SELECT linkResult.url AS url, linkResult.title AS title, 
           (SELECT MIN(r.linkQuery.queryDate.date)
               FROM LinkResult r
                 WHERE r.linkQuery.domain=linkQuery.domain
                    AND r.url=linkResult.url) AS indexedDate 
     FROM CompetitiveEntity entity, 
          IN(entity.domains) domain, 
          LinkQuery linkQuery, 
          IN(linkQuery.results) linkResult 
       LEFT JOIN FETCH linkResult.url.hostname 
         WHERE entity.id=2062
           AND (linkResult.url.hostname NOT MEMBER entity.domains) 
           AND (linkQuery.domain=domain) 
           AND (linkQuery.id=(SELECT MAX(lastQuery.id) 
                        FROM LinkQuery lastQuery 
                           WHERE lastQuery.domain=domain 
                        AND SIZE(lastQuery.results) > 0 ) ) 
    GROUP BY linkResult.url 
    ORDER BY linkResult.url
    The second one:
    Java Code:
    SELECT subscription.term.id, subscription, termQueryPrev.provider.name,
           MIN(termResultPrev.position) as prevPosition, MIN(termResultCurrent.position) as currentPosition, 
           MIN(cast(termResultCurrent.position as integer)) - MIN(cast(termResultPrev.position as integer)) as change
      FROM TermSubscription subscription,  
           TermQuery termQueryPrev, 
           IN(termQueryPrev.results) termResultPrev, 
           TermQuery termQueryCurrent,
           IN(termQueryCurrent.results) termResultCurrent 
      WHERE subscription.account.id= :accountId  
       AND termQueryPrev.term=subscription.term 
       AND termQueryCurrent.term=subscription.term 
       AND termQueryCurrent.provider.id=termQueryPrev.provider.id 
       AND termQueryPrev.queryDate.yearWeek = YEARWEEK(FROM_DAYS(TO_DAYS(current_date) - 7), 1) 
       AND termQueryCurrent.queryDate.yearWeek = YEARWEEK(:endDate,1) 
       AND termResultPrev.url.hostname   MEMBER subscription.account.domains 
       AND termResultCurrent.url.hostname   MEMBER subscription.account.domains  
    GROUP BY subscription.term.id, termQueryPrev.queryDate.yearWeek,termQueryPrev.provider.id 
    HAVING MIN(cast(termResultCurrent.position as integer)) != MIN(cast(termResultPrev.position as integer)) 
    ORDER BY ABS(MIN(cast(termResultCurrent.position as integer)) - MIN(cast(termResultPrev.position as integer))) DESC
    I stress the fact that I am looking for a better way to improve the current HQL query construction.
    Thank you.

  2. #2
    DarrylBurke's Avatar
    DarrylBurke is offline Forum Police
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,458
    Rep Power
    20

    Default Re: HQL queries performance

    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  3. #3
    chemipot is offline Member
    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    Default Re: HQL queries performance

    Quote Originally Posted by DarrylBurke View Post
    Hello DarryBurke,

    Is cross-post not allowed? I supossed that do it in different forums is ok. I didnt know but if I will delete if you tell me that cross-post is forbidden.

  4. #4
    DarrylBurke's Avatar
    DarrylBurke is offline Forum Police
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,458
    Rep Power
    20

    Default Re: HQL queries performance

    I didn't say that it is forbidden. Nor did I ask you to delete the post.

    What made you think that was implied?

    db
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default Re: HQL queries performance

    The reason is, if you look at the Stack Overflow thread, they've asked pretty much the same questions we would ask here.
    We would be duplicating effort.
    If you inform us where you have posted then we could avoid that.

    I will echo what's been said (sort of). You can't expect people to be able to analyse a query based solely on the query text.
    That's what query plans are for.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #6
    chemipot is offline Member
    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    Default Re: HQL queries performance

    ok, Thanks your comment on that.
    Would it be possible to delete this post, please ? I was looking for the option to delete my post but I didnt find it.
    Thanks.

Similar Threads

  1. how to run the two queries at the same time
    By baktha.thalapathy in forum JDBC
    Replies: 7
    Last Post: 06-04-2010, 04:54 AM
  2. help me need to understand queries
    By hossainsadd in forum JDBC
    Replies: 1
    Last Post: 05-26-2008, 01:02 AM
  3. queries regarding SWT
    By ravian in forum New To Java
    Replies: 0
    Last Post: 11-26-2007, 04:06 PM
  4. some queries
    By Feng in forum New To Java
    Replies: 7
    Last Post: 11-24-2007, 09:10 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •