Posted by Hugo Kornelis on 07/18/07 22:06

On Sun, 15 Jul 2007 18:33:19 -0700, pbd22 wrote:

(snip)
>OK, I have done what you said and reduced the tables used in the
>search.
>After much messing around with the stored procedure, I have figured
>out that
>by commenting out the following code (at the end of the procedure), I
>can get
>results:
>
>WHERE
>
>tab1.gender = @gender
>AND tab1.country = @country
>AND tab1.bday_year BETWEEN @low AND @high
>AND tab2.photo_default = 1 + @photo--WHERE
>
>(and, the ORDERBY code is commented out as it depends on this code).
>
>I have also found that if I leave any one of the above lines the code
>again
>fails. So, for some reason, @gender, @country, @low, @high, and @photo
>are not getting passed appropriately.

Hi Peter,

I found the stored procedure code in an earlier message in this thread.
I don't know how much you changed, so the following might or might not
apply.

Your query uses a lot of left (outer) joins. Are you sure that these
can't be inner joins?

The tab1 and tab2 tables are among the tables that are outer joined. By
adding a criterium in the WHERE clause, you effectively convert them to
inner joins - so you should either modify the query to use inner join
(improved readability and maintainability and probably better
performance as well), or move the filters to the ON part of the
appropriate JOIN clauses.

Note that I did not do a complete review of your code; it's too long for
that. Try to trim down the problem to a more simplified case that's
short enough for us to invest our time in, yet similar enough to your
real problem that you can translate our soultions back to your original
situation. If you're not able to simplify the problem, you should
probably hire a SQL developer to aide you with this issue.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]