8 TABLE Join - 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 2005 Forums

Transact-SQL (2005)

8 TABLE Join

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-28 : 08:48:33

Hi all,
i have stored procedure which has about 8 table joins
CREATE PROCEDURE [dbo].[GetWidgetFeatureByUser]
@userId int
AS
BEGIN

SET NOCOUNT ON;

SELECT
Users.UserId,
Widget.WidgetId,
Users.FirstName,
UserCategory.CategoryName,
Provider.ProviderName,
Practice.PracticeName,
[Profile].ProfileName,
[Profile].ProfileId,
Widget.WidgetName,
Feature.FeatureName,
Controls.ControlName,
ControlType.ControlTypeName,
ProfileWidget.IsEnabled,
ProfileWidget.Visibility

FROM Users
INNER JOIN UserCategory
ON Users.CategoryId = UserCategory.CategoryId
INNER JOIN Provider
ON Users.UserId = Provider.UserId
INNER JOIN PracticeProvider
ON Provider.ProviderId = PracticeProvider.ProviderId
INNER JOIN Practice
ON PracticeProvider.PracticeId = Practice.PracticeId
INNER JOIN PracticeProviderProfile
ON PracticeProvider.PracticeProviderId = PracticeProviderProfile.PracticeProviderId
INNER JOIN [Profile]
ON PracticeProviderProfile.ProfileId = [Profile].ProfileId
INNER JOIN [ProfileWidget]
ON [Profile].ProfileId = [ProfileWidget].ProfileId
INNER JOIN [WidgetFeature]
ON [ProfileWidget].WidgetFeatureId = [WidgetFeature].WidgetFeatureId
INNER JOIN [Widget]
ON [WidgetFeature].WidgetId = Widget.WidgetId
INNER JOIN [Feature]
ON [WidgetFeature].FeatureId = Feature.FeatureId
INNER JOIN Controls
ON Feature.FeatureId = Controls.FeatureId
INNER JOIN ControlType
ON Controls.ControlTypeId = ControlType.ControlTypeId
WHERE
Users.UserId = @userId
END

I know this is not correct format for performance enhancement as maximum of 4-5 table joins r best for neat performance.
so how could i overcome this join. shall i split it into two temp tables and join them finally. Pls provide me solution

Iam a slow walker but i never walk back

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 09:44:31

You can rewrite the query and make the FROM and INNER JOIN tables in same order as number of records in them.
And add OPTION (FORCE ORDER) to the end of query.
It can make a huge difference.

E 12°55'05.63"
N 56°04'39.26"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-28 : 09:47:33

Thanks for Reply,

i could not get the first line "You can rewrite the query and make the FROM and INNER JOIN tables in same order as number of records in them."

could u pls provide me a sample query with 8 tables(table1...table8)

Iam a slow walker but i never walk back

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 09:58:00

I can't. I don't have access to your data.

E 12°55'05.63"
N 56°04'39.26"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-28 : 11:42:31

quote:
Originally posted by dineshrajan_it

I know this is not correct format for performance enhancement as maximum of 4-5 table joins r best for neat performance.

Where did you hear that? It is very common in a well normalized database to have to join more than 5 tables.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-28 : 12:25:10

quote:
Originally posted by Peso

You can rewrite the query and make the FROM and INNER JOIN tables in same order as number of records in them.
And add OPTION (FORCE ORDER) to the end of query.
It can make a huge difference.

E 12°55'05.63"
N 56°04'39.26"

Hi Peso -- Can I ask how often (and in what situations) you find that this actually makes a difference. I'm correct in thinking that your explicitly telling the engine which table to use as the outer and which as the inner?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources