Dynamic Join BAD solution... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Join BAD solution...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-09-07 : 09:59:54

Ken kerns writes "I think this is very interesting to know...

I assumed the problem is not that I can't use a case in a join, but that the values I was tested were not loaded yet since the join had yet to happen.. Sounds strange but true, since it worked for some values but not others...

So I did the INNER JOIN on the table,and the JOINed again to the table after the values would be "loaded" and it worked like a charm.. with a problem, is that I recieved multiple duplicate records!!!

As a rig job I just did a group by on all fields and it works perfectly... But two problems:
1. This goes against everything I know about sql programming.
2. This procedure is going to be hit 10 million times a day, and must reply in .5 seconds.. Which it does now, but underload I'm very weary...

Ideas?

Heres the new code

ALTER PROCEDURE spPullUrls(@SiteProfileID int,@testcc smallint, @testbw tinyint,@testpv tinyint) AS
SELECT A.Url, A.Redirect, A.PassData, A.Width, A.height, A.[Top], A.[Left], A.GroupOrder
FROM tblFilters
INNER JOIN tblClientSitesProfiles
ON tblFilters.SiteProfileID = tblClientSitesProfiles.SiteProfileID
INNER JOIN tblUrls
ON tblUrls.UrlSchemeID = tblFilters.UrlSchemeID
INNER JOIN
tblUrls A
ON A.UrlSchemeID = (CASE WHEN tblUrls.CurrentGateID = 3 THEN tblClientSitesProfiles.PercentSchemeID ELSE tblFilters.UrlSchemeID END)
WHERE (testCC = @testcc OR testcc IS NULL) AND
(testBW = @testbw OR testbw IS NULL) AND
(testPV = @testpv OR testPV IS NULL) AND
(tblFilters.SiteProfileID = @SiteProfileID) AND
(tblUrls.CurrentGateID IN(1,3))
AND ((Case WHEN testCC is null then 0 else testCCRelevance END) +
(Case WHEN testBW is null then 0 else testBWRelevance END) +
(Case WHEN testPV is null then 0 else testPVRelevance END)) IN
(SELECT Max((Case WHEN testCC is null then 0 else testCCRelevance END) +
(Case WHEN testBW is null then 0 else testBWRelevance END) +
(Case WHEN testPV is null then 0 else testPVRelevance END)) AS Relevance
FROM tblFilters INNER JOIN tblClientSitesProfiles ON tblFilters.SiteProfileID = tblClientSitesProfiles.SiteProfileID
WHERE (testCC = @testcc OR testcc IS NULL) AND
(testBW = @testbw OR testbw IS NULL) AND
(testPV = @testpv OR testPV IS NULL)AND tblFilters.SiteProfileID = @SiteProfileID)
GROUP BY A.Url, A.Redirect, A.PassData, A.Width, A.height, A.[Top], A.[Left],A.GroupOrder
ORDER BY A.GroupOrder"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources