Help with a very slow SQL-Statement

olel · #1 (**permalink**) 11-27-09, 07:09

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

shammat · #2 (**permalink**) 11-27-09, 07:36

Quote:

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% 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.

olel · #3 (**permalink**) 12-01-09, 10:05

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

olel · #4 (**permalink**) 12-03-09, 03:42

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?

stolze · #5 (**permalink**) 12-03-09, 05:00

"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.

olel · #6 (**permalink**) 12-03-09, 08:09

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.

shammat · #7 (**permalink**) 12-03-09, 08:12

Quote:

Originally Posted by olel

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.

olel · #8 (**permalink**) 12-04-09, 02:29

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!

shammat · #9 (**permalink**) 12-04-09, 03:09

Quote:

Originally Posted by olel

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.

olel · #10 (**permalink**) 12-04-09, 06:40

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? ;-)

r937 · #11 (**permalink**) 12-04-09, 06:50

Quote:

Originally Posted by olel

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

shammat · #12 (**permalink**) 12-04-09, 07:32

Quote:

Originally Posted by olel

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech