Posted by pbd22 on 07/19/07 23:56

On Jul 18, 3:06 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> 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


Thanks Hugo and Erland (again).

Erland - thank you for your suggestions. The revising of the SET
statement
to a SELECT statement is an obvious time-saver. I don't want you to
think
I haven't tried to test my code - I was trying but every time I tried
to run it to completion I was getting errors that prevented a clean
compile. I have since
isolated some of my problems and redesigned my procedure with more
success.
Per Hugo's suggestion, I have cut a lot of the BS out of the code and
left a single join block as the core of the procedure - much more
logical (to me) and easier on the eyes.

The below procedure seems to work except for one major error and a
minor one:

major: when I simply leave the edit_date column as is, I get the
error:

"ambiguous column name edit_date"

and, when I include the alias with the edit_date column, I get the
following:

"The multi-part identifier "tab3.edit_date" could not be bound."

The lesser problem is that when I added SELECT DISTINCT at the bottom
of
the procedure to avoid duplicates it seems to have thrown off the
paging. I have
designated 10 rows as a default page parameter. This worked well
before I
changed the bottom select statement to eliminate duplicates.

Hugo - I'll try to digest your idea behind changing the join block to
inner joins later tonight. By this, do you mean replacing all "LEFT
JOIN" statements with
"INNER JOIN"?

Otherwise, I hope the updated procedure makes more logical sense.

Thanks again for your tremendous help.
Peter

ALTER PROCEDURE [dbo].[tre_SavedSearch]
@searchname VARCHAR(50) = null, -- The Name Of The User-Defined
Search
@emailaddy VARCHAR(50) = null, -- The ID (email) of the User
@PageNum INT = 1, -- The Starting Page
@PageSize INT = 10, -- The Number of Rows Per Page
@debug INT = 0 -- Debug Value

AS
BEGIN

SET NOCOUNT ON

DECLARE
@saveddate VARCHAR(50),
@savedname VARCHAR(50),
@defaultsearch VARCHAR(50),
@gender VARCHAR(50),
@fromage VARCHAR(50),
@toage VARCHAR(50),
@country VARCHAR(50),
@miles VARCHAR(50),
@pictures VARCHAR(50),
@zipcode VARCHAR(50),
@whereSQL VARCHAR(1000),
@sortID INT -- 1 = registration
-- 2 = recent changes
-- 3 = recent login
-- 4 = distance order

SELECT @saveddate = saved_date, @savedname = saved_name, @gender =
saved_sex,
@fromage = saved_fage, @toage = saved_tage, @country =
saved_country , @miles = saved_miles,
@pictures = saved_pictures, @zipcode = saved_postal, @sortID =
saved_sort
FROM SavedSearches
WHERE saved_name=@searchname
AND email_address=@emailaddy

WITH SavedSearch AS
(

SELECT ROW_NUMBER() OVER ( ORDER BY CASE @sortID
WHEN 1 THEN registerDate
--WHEN 2 THEN tab3.edit_date
WHEN 3 THEN login_date
--WHEN 4 THEN up_order
END DESC
) AS RowNum
,tab1.registerDate
,tab3.edit_date
,tab4.login_date
,tab1.bday_day
,tab1.bday_month
,tab1.bday_year
,tab1.gender
,tab1.zipCode
,tab1.siteId
,tab1.userID
--,tab5.up_order
FROM
(select distinct emailAddress
from Users with(nolock) union select distinct user_name
from PersonalPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from LastLogin with(nolock)) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
--Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join PersonalPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LastLogin tab4 on (drv.emailAddress = tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

WHERE
(tab1.gender = @gender OR CASE WHEN COALESCE(@gender,'Show All') =
'Show All' THEN 1 ELSE 0 END = 1)
AND
(tab1.country = @country OR CASE WHEN COALESCE(@country,'Show All') =
'Show All' THEN 1 ELSE 0 END = 1)
AND
(tab1.bday_year BETWEEN @toage AND @fromage)
--AND tab2.photo_default = 1 + @photo
)

SELECT registerDate
--,tab3.edit_date
,login_date
,bday_day
,bday_month
,bday_year
,gender
--,up_order
,zipCode
,siteId
,userID
FROM (SELECT DISTINCT registerDate
--,tab3.edit_date
,login_date
,bday_day
,bday_month
,bday_year
,gender
--,up_order
,zipCode
,siteId
,userID
FROM SavedSearch
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize) V
ORDER BY CASE @sortID
WHEN 1 THEN registerDate
--WHEN 2 THEN tab3.edit_date
WHEN 3 THEN login_date
--WHEN 4 THEN up_order
END DESC;
END

[Back to original message]