#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0

    Sql speed question


    I have two queries that i run separately because when i combine them into one it takes 20x as long to run. I must be doing something wrong when i combine the queries. I will show the simple example. I dont know much about sql optimization so i am probably doing something silly.

    //query 1: returns a bunch of keys. This returns ~5k items in 15 seconds. The returned keys are used in the IN clause in the query 2 below.

    select d.c_fk from d
    where created_time between '2012-08-01' and '2012-08-02'
    group by d.c_fk having count(d.c_fk) = 1 and every(file_status = 0);



    //query 2: uses the keys returned from query 1 above to return different ids. All the keys are in my IN clause below. This returns in about 2 second for all the 5k items.


    select id from a, b, c
    where c.pk IN
    ('46198054',
    '46198055',
    )
    and c.b_fk = b.pk
    and b.a_fk = a.pk;



    //query 3: Combine query1 and query2 to have one use the result of the other so as to not have two queries. I would hope this would take about 17 seconds(the sum of the two above). This takes 350 seconds!

    select id from a, b, c
    where c.pk IN
    (select d.c_fk from d
    where created_time between '2012-08-01' and '2012-08-02'
    group by d.c_fk having count(d.c_fk) = 1 and every(file_status = 0))
    and c.b_fk = b.pk
    and b.a_fk = a.pk;
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0

    found one solution


    Originally Posted by argo4242
    I have two queries that i run separately because when i combine them into one it takes 20x as long to run. I must be doing something wrong when i combine the queries. I will show the simple example. I dont know much about sql optimization so i am probably doing something silly.

    //query 1: returns a bunch of keys. This returns ~5k items in 15 seconds. The returned keys are used in the IN clause in the query 2 below.

    select d.c_fk from d
    where created_time between '2012-08-01' and '2012-08-02'
    group by d.c_fk having count(d.c_fk) = 1 and every(file_status = 0);



    //query 2: uses the keys returned from query 1 above to return different ids. All the keys are in my IN clause below. This returns in about 2 second for all the 5k items.


    select id from a, b, c
    where c.pk IN
    ('46198054',
    '46198055',
    )
    and c.b_fk = b.pk
    and b.a_fk = a.pk;



    //query 3: Combine query1 and query2 to have one use the result of the other so as to not have two queries. I would hope this would take about 17 seconds(the sum of the two above). This takes 350 seconds!

    select id from a, b, c
    where c.pk IN
    (select d.c_fk from d
    where created_time between '2012-08-01' and '2012-08-02'
    group by d.c_fk having count(d.c_fk) = 1 and every(file_status = 0))
    and c.b_fk = b.pk
    and b.a_fk = a.pk;

    I did find one solution which was to run one query, put it in a temporary table, and then run the other query on the temporary table. It is all one sql script so it behaves like 'one' command and it performs as if it is just each separate query running. For now i am going with that.

IMN logo majestic logo threadwatch logo seochat tools logo