Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2009
    Posts
    24

    Help with a very slow SQL-Statement

    Very slow SQL-Statement


    Hi,

    the following SQL statement (generated by Hibernate) is very slow. I aborted the execution after 15 minutes without a result.

    Code:
    select
            customer2_.id as col_0_0_,
            customerse4_.id as col_1_0_,
            campaign1_.id as col_2_0_,
            (select
                count(campaignde6_.id) 
            from
                CAMPAIGNDELIVERY campaignde6_ 
            where
                campaignde6_.campaign_id=campaign1_.id 
                and campaignde6_.customer_id=customer2_.id) as col_3_0_ 
        from
            CAMPAIGNDELIVERY campaignde0_ 
        inner join
            CAMPAIGN campaign1_ 
                on campaignde0_.campaign_id=campaign1_.id 
        left outer join
            CAMPAIGN_CUSTOMERSELECTION customerse3_ 
                on campaign1_.id=customerse3_.campaign_id 
        left outer join
            CUSTOMERSELECTION customerse4_ 
                on customerse3_.customerSelection_id=customerse4_.id 
        left outer join
            CUSTOMERSELECTIONASSIGNMENT customerse5_ 
                on customerse4_.id=customerse5_.customerSelection_id 
        inner join
            CUSTOMER customer2_ 
                on campaignde0_.customer_id=customer2_.id 
        where
            (
                customer2_.id=customerse5_.customer_id 
                or customerse5_.customer_id is null
            ) 
            and (
                campaign1_.id in (
                    775
                )
            ) 
            and (
                campaignde0_.feedback<>0 
                or campaign1_.frequencyCapping>0 
                and campaign1_.frequencyCapping<=(
                    select
                        count(campaignde7_.id) 
                    from
                        CAMPAIGNDELIVERY campaignde7_ 
                    where
                        campaignde7_.campaign_id=campaign1_.id 
                        and campaignde7_.customer_id=customer2_.id
                )
            ) 
            and campaignde0_.mandator_id=368 
        group by
            customerse4_.id ,
            customer2_.id ,
            campaign1_.id
    All tables contain just a few records (<50) except the table CAMPAIGNDELIVERY which has about 100.000 records.

    I have analyzed the query and found out that the first subquery (select count(campaignde6_.id)...) is the one causing the long execution time. If you remove that subquery the query executes in 0.2 seconds.

    The obvious question now is how can we change the query so that it executes faster? What is wrong with the subquery?

    Any hints would be greatly appreciated!

    Please find attached the explain plan of the query and the database schema.

    Thanks in advance,
    Ole
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2003
    Posts
    2,815
    and found out that the first subquery (select count(campaignde6_.id)...) is the one causing the long execution time
    Of course it slows down the statement, because it will be executed for every row that is returned from the main query.

    Why do you have a GROUP BY at the end of the statement, if the statement itself does not use any aggregate function? Is that used to get a DISTINCT result?

    I think (I might be mistaken!) you can replace the sub-select in the SELECT list with an analytical function. As the same count is again used in the WHERE part, I think you can get rid of the second sub-select as well by putting the whole thing into a derived table:
    Code:
    SELECT col_0_0_, 
        col_1_0_,
        col_2_0_, 
        campaign_count
    FROM (
    select
            customer2_.id as col_0_0_,
            customerse4_.id as col_1_0_,
            campaign1_.id as col_2_0_,
            count(campaign1_.id) over (partition by campaign1_.id, customer2_.id) as campaign_count, 
            campaign1_.frequencyCapping
        from
            CAMPAIGNDELIVERY campaignde0_ 
        inner join
            CAMPAIGN campaign1_ 
                on campaignde0_.campaign_id=campaign1_.id 
        left outer join
            CAMPAIGN_CUSTOMERSELECTION customerse3_ 
                on campaign1_.id=customerse3_.campaign_id 
        left outer join
            CUSTOMERSELECTION customerse4_ 
                on customerse3_.customerSelection_id=customerse4_.id 
        left outer join
            CUSTOMERSELECTIONASSIGNMENT customerse5_ 
                on customerse4_.id=customerse5_.customerSelection_id 
        inner join
            CUSTOMER customer2_ 
                on campaignde0_.customer_id=customer2_.id 
        where
            (
                customer2_.id=customerse5_.customer_id 
                or customerse5_.customer_id is null
            ) 
            and (campaign1_.id in (775)) 
            and (campaignde0_.feedback<>0 or campaign1_.frequencyCapping > 0) 
           campaignde0_.mandator_id=368 
    ) t 
    where frequencyCapping <= campaign_count
    Not sure if I got the syntax 100&#37; correct, but you might get what I'm aiming at.
    I'm also not sure if I understood the query 100% correct, so there might be a logical problem in there as well. Especially the analytical function that does the counting, might count the wrong rows.

    The statement also looks like it's traversing a hierarchical table.

    If that is the case, might also consider using CONNECT BY instead of mutliple outer joins. For one thing it makes the satement a lot easier to read and it might also improve performance. But as you said that the "base" statement is fast enough, that might not be necessary for performance reasons.

    Edit: just realized this is not the Oracle forum So forget the CONNECT BY clause (which is Oracle specific).
    The windowing function is ANSI standard though.
    Last edited by shammat; 11-27-09 at 09:47.

  3. #3
    Join Date
    Oct 2009
    Posts
    24
    Hi shammat,

    at first, I'd like to say thank you for your very quick answer and to apologize for not giving a response.
    Because we had some other things to fix first we haven't had time for this problem up to now, but the next days we will have a deeper lock at it and will ask another question maybe.

    Thanks so far,
    Ole

  4. #4
    Join Date
    Oct 2009
    Posts
    24
    I tried your statement today, but it looks like the "count(...) over" function is oracle specific as well. At least with MySql I get a syntax error.

    Is it possible to transform the query to a standard ANSI query?

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    "COUNT(...) OVER" is ISO SQL standard (aka ANSI SQL) syntax. It is defined in subclause 6.10, "<window functions>" at least since SQL-2003.

    So the answer here is that MySQL doesn't follow the standard syntax is this respect.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Oct 2009
    Posts
    24
    Unfortunately we are using Hibernate that creates those SQL statements for us which means

    1. we want to be absolutely indepent from the dbms
    2. we have to write a HQL (Hibernate Query Language) statement that represents the SQL above.

    Do someone possibly know a simpler SQL query that solves our problem as well? I have asked in the hibernate forum as well, but I am not sure if someone can help me there without knowing the SQL I would like to achieve.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,815
    Quote Originally Posted by olel View Post
    I tried your statement today, but it looks like the "count(...) over" function is oracle specific as well. At least with MySql I get a syntax error.

    Is it possible to transform the query to a standard ANSI query?
    As stolze has pointed out this is ANSI syntax
    MySQL is the only main-stream database that does not implement this.

    If you want MySQL specific answers you have to post your questions in the MySQL forum.

  8. #8
    Join Date
    Oct 2009
    Posts
    24
    Yes and I did understand him. I am not looking for a MySql specific solution, but for one that I am able to transform into a Hibernate HQL statement.

    But I cannot use constructs like 'count(...) over' in HQL. Therefore I asked for a simpler SQL. I don't know if it is possible for one of you to transform the query to a simpler one, but if so it would be great!

    Thanks!

  9. #9
    Join Date
    Nov 2003
    Posts
    2,815
    Quote Originally Posted by olel View Post
    But I cannot use constructs like 'count(...) over' in HQL.
    Therefore I asked for a simpler SQL. I don't know if it is possible for one of you to transform the query to a simpler one, but if so it would be great!
    Ah, the "joys" of ORM tools: they simply won't let you write efficient SQL (sorry I didn't see the HQL in your answer)

    My recommendation: do not use Hibernate for this, just run the SQL directly. As far as I know, Hibernate can run SQL directly without the need of its own SQL dialect.

  10. #10
    Join Date
    Oct 2009
    Posts
    24
    Maybe we will have to run the SQL directly indeed. But then we do have the problem that the SQL isn't working with MySql and we have to make sure that we are independend of the dbms used. So if we decide to run it directly we need another SQL nevertheless - one for all dbms. Some help? ;-)

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by olel View Post
    we have to make sure that we are independend of the dbms used.
    this is going to end in tears and frustration

    any application that has "dumbed down" the SQL so that it runs on all database systems is going to be pretty crappy

    besides, one SQL for all database systems is probably not even possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2003
    Posts
    2,815
    Quote Originally Posted by olel View Post
    we have to make sure that we are independend of the dbms used.
    If you make it independent it will be slow on all DBMS (some DBMS have better optimizers than others, so it might be that one DBMS runs this statement fast, while others do not).
    That's the price you pay for independency

    An alternative DBMS independent solution is to maintain an aggregration table that is updated through triggers and stores the counts that you need.
    In that case your application/Java code would be somewhat DBMS independent, but the triggers will not. You could also maintain the summary/aggregation table from within your application, but that would not ensure that an external access to the database would leave those tables in a consistent state.

    If you really want to limit yourself to the MySQL features (which is more or less the least common denominator when it comes to SQL features and ANSI compliance) then probably your only option is to read the necessary information into memory, and do the processing (filtering, sorting, ...) there.

    Most probably you'll need more than one SQL for that (which might or might not be faster depending on the DBMS).

    In any case, you will be implementing some kind of DBMS abstraction layer, so you could just as well implement a layer that will generate different SQL statements for different DBMS. But you should still verify that my solution is indeed faster when using windowing functions!

Posting Permissions

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