Same Query, Different Criteria Values = Wildly Different Speeds

[BlakeK]

I am completetly at a loss for what is going on here.
I have a query, which when given one set of values, executes in less than 1 second.
Give it another set of values, and it take more than 1 minute to complete!
Here is the query that executes fast:
SELECT tbl_bin_group_detail.content_id, tbl_server_install_library.file_runtime as fruntime, tbl_entertainment.entertainment_type_id, tbl_bin_group_detail.revision_number
FROM tbl_bin_group_detail, tbl_entertainment, tbl_server_install_library
WHERE tbl_bin_group_detail.content_id = tbl_entertainment.content_id
AND tbl_entertainment.content_id = tbl_server_install_library.content_id
AND tbl_server_install_library.server_install_id = 1419
AND tbl_bin_group_detail.bin_group_id = 36
AND tbl_bin_group_detail.library_id = 35
AND tbl_bin_group_detail.version_number = 10
AND tbl_bin_group_detail.revision_number =
(SELECT MAX(revision_number) FROM tbl_bin_group_versions
WHERE version_number = 10
AND approved <> 0
AND library_id = 35
AND bin_group_id = 36 )
AND tbl_bin_group_detail.schedule_day = 1
AND tbl_bin_group_detail.schedule_hour = 1
AND tbl_bin_group_detail.schedule_slot IN (0,1,2,3)
ORDER BY tbl_bin_group_detail.schedule_slot


Here is the query that executes slower than dirt!

SELECT tbl_bin_group_detail.content_id, tbl_server_install_library.file_runtime as fruntime, tbl_entertainment.entertainment_type_id, tbl_bin_group_detail.revision_number
FROM tbl_bin_group_detail, tbl_entertainment, tbl_server_install_library
WHERE tbl_bin_group_detail.content_id = tbl_entertainment.content_id
AND tbl_entertainment.content_id = tbl_server_install_library.content_id
AND tbl_server_install_library.server_install_id = 1491
AND tbl_bin_group_detail.bin_group_id = 34
AND tbl_bin_group_detail.library_id = 36
AND tbl_bin_group_detail.version_number = 5
AND tbl_bin_group_detail.revision_number =
(SELECT MAX(revision_number) FROM tbl_bin_group_versions
WHERE version_number = 5
AND approved <> 0
AND library_id = 36
AND bin_group_id = 34 )
AND tbl_bin_group_detail.schedule_day = 1
AND tbl_bin_group_detail.schedule_hour = 1
AND tbl_bin_group_detail.schedule_slot IN (0,1,2,3)
ORDER BY tbl_bin_group_detail.schedule_slot

As you can see, all the syntax is exactly the same, only the values changed. I've never seen a queries performance affected by the criteria values before. I'm really confused.

And here is another oddity with this query.
Notice the subquery to get the max revision number?

SELECT MAX(revision_number) FROM tbl_bin_group_versions
WHERE version_number = 10
AND approved <> 0
AND library_id = 35
AND bin_group_id = 36

If I run this query seperately for either set of values, the results are instantaneous.
Now, here's the odd part. If I remove the subquery, and set the value manually with what I know is returned in each case, then both main queries run instantly!!

SELECT tbl_bin_group_detail.content_id, tbl_server_install_library.file_runtime as fruntime, tbl_entertainment.entertainment_type_id, tbl_bin_group_detail.revision_number
FROM tbl_bin_group_detail, tbl_entertainment, tbl_server_install_library
WHERE tbl_bin_group_detail.content_id = tbl_entertainment.content_id
AND tbl_entertainment.content_id = tbl_server_install_library.content_id
AND tbl_server_install_library.server_install_id = 1419
AND tbl_bin_group_detail.bin_group_id = 36
AND tbl_bin_group_detail.library_id = 35
AND tbl_bin_group_detail.version_number = 10
AND tbl_bin_group_detail.revision_number = 99
AND tbl_bin_group_detail.schedule_day = 1
AND tbl_bin_group_detail.schedule_hour = 1
AND tbl_bin_group_detail.schedule_slot IN (0,1,2,3)
ORDER BY tbl_bin_group_detail.schedule_slot

SELECT tbl_bin_group_detail.content_id, tbl_server_install_library.file_runtime as fruntime, tbl_entertainment.entertainment_type_id, tbl_bin_group_detail.revision_number
FROM tbl_bin_group_detail, tbl_entertainment, tbl_server_install_library
WHERE tbl_bin_group_detail.content_id = tbl_entertainment.content_id
AND tbl_entertainment.content_id = tbl_server_install_library.content_id
AND tbl_server_install_library.server_install_id = 1491
AND tbl_bin_group_detail.bin_group_id = 34
AND tbl_bin_group_detail.library_id = 36
AND tbl_bin_group_detail.version_number = 5
AND tbl_bin_group_detail.revision_number = 86
AND tbl_bin_group_detail.schedule_day = 1
AND tbl_bin_group_detail.schedule_hour = 1
AND tbl_bin_group_detail.schedule_slot IN (0,1,2,3)
ORDER BY tbl_bin_group_detail.schedule_slot

Both of those queries are instant.

So, does anyone have any idea why the second query, when the subquery is in place, would take so long, while the first query which has the same structure bu different data, would be very fast?

Thanks

 

[SQLSister]

Subselects are not very efficent. Try a derived table instead. (Note that I also put in real joins to make the code more understandable. )
Try:

SELECT tbl_bin_group_detail.content_id, tbl_server_install_library.file_runtime as fruntime, 
tbl_entertainment.entertainment_type_id, tbl_bin_group_detail.revision_number
FROM tbl_bin_group_detail join tbl_entertainment
on tbl_bin_group_detail.content_id = tbl_entertainment.content_id
join tbl_server_install_library
on tbl_entertainment.content_id = tbl_server_install_library.content_id
Join (SELECT MAX(revision_number) as MaxRevNo FROM tbl_bin_group_versions
WHERE version_number =  5
AND approved <> 0
AND library_id =  36
AND bin_group_id =  34 ) a
on tbl_bin_group_detail.revision_number = a.MaxRevNo
WHERE tbl_server_install_library.server_install_id =  1491
AND tbl_bin_group_detail.bin_group_id =  34
AND tbl_bin_group_detail.library_id  =  36
AND tbl_bin_group_detail.version_number =  5
AND tbl_bin_group_detail.schedule_day =  1
AND tbl_bin_group_detail.schedule_hour =  1
AND tbl_bin_group_detail.schedule_slot IN (0,1,2,3)
ORDER BY tbl_bin_group_detail.schedule_slot

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[BlakeK]

SQLSister,
Thanks for the reply.
I tried the query you provided above, and it greatly increased the time of the seond set of values. But it slowed down the response on the first set.
Both queries now take about 2 seconds to run.
2 seconds is much better than the 1 minute plus the second query was taking! But for some reason the first query went from basically instant to also taking 2 seconds.
Strange. I still don't understand why different values are effecting the same query in such a radical way.
Although 2 seconds would be acceptable if this was for a report or something, unfortunately this is for a process that needs to run the query 2,688 times!
I will play around more with the derived tables, and check indexes.
If you have any other ideas on the disparity of the queries based on the data, let me know.

Thanks.

 

[SQLSister]

What are you doing that you have to run this query 2688 times? Maybe there is a better approach.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief