Performance question

Johnny J

If you have a SP where you (among other things) need to extract the number of a certain rowtype, and you do this: SELECT a, b, c, d, (SELECT COUNT(*) FROM table1 WHERE my_condition1=1) AS FooBarCount, e, f FROM table2 WHERE my_condition2=1; it seems to me that the performance ought to be remarkably slower than if you do: SELECT a, b, c, d, (SELECT COUNT(table1s_indexed_column) FROM table1 WHERE my_condition1=1) AS FooBarCount, e, f FROM table2 WHERE my_condition2=1; At least according to everything I've learnt until now. Can anybody confirm if I'm right or not?

Why can't I be applicable like John? - Me, April 2011
-----
Beidh ceol, caint agus craic againn - Seán Bán Breathnach
-----
Da mihi sis crustum Etruscum cum omnibus in eo!
-----
Everybody is ignorant, only on different subjects - Will Rogers, 1924

PIEBALDconsult

It depends. But in general, I wouldn't expect so. But that's some weird code, there must be a better way.

David Mujica

I can't answer your question with 100% certainty, but you should explore the SQL Profiler and run your queries through them to see what execution plan is choosen. Remember that just because an index exists, the optimizer may choose not to use it; resulting in a table scan. Experiment with SQL Profiler and you can learn alot. Good luck. :thumbsup:

dasblinkenlight

I read somewhere[^] that the difference between count(*) and count(1) or count(indexed_column) is not nearly as relevant now as it used to be in the past. Basically, once the writers of the database engines learned of the problem, they quietly fixed it. The habit of writing count(1), however, outlived the bug. In the late nineties, using count(indexed_column) or count(1) would make queries a lot faster on many commercial engines (e.g. Oracle-7). At this point, however, most modern DB engines with the "non-toy" status should prefer count(*) to count(1).

SilimSayo

I don't know what the performance would be like but your code seems a bit weired. Does table1 and table2 have any relationship(column[s]) in common? it seems to be you're going to end up with a cartesian product of some sort.