is this a lot  
Author Message
noahart





PostPosted: Wed Nov 23 12:43:56 CST 2005 Top

SQL Server Developer >> is this a lot

hey all,

is a 5-table join a lot of tables? the reason why i ask because this one
table has so many code fields (that reference master files which contain
descriptions i need for a report). how do you get around that?

thanks,
rodchar

SQL Server42  
 
 
Adam





PostPosted: Wed Nov 23 12:43:56 CST 2005 Top

SQL Server Developer >> is this a lot No, that's not what I'd consider to be a lot. But it really depends on the
system and what you're doing. Can you share DDL, sample data, and explain
what kind of problem you're having?


--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--




> hey all,
>
> is a 5-table join a lot of tables? the reason why i ask because this one
> table has so many code fields (that reference master files which contain
> descriptions i need for a report). how do you get around that?
>
> thanks,
> rodchar


 
 
JT





PostPosted: Wed Nov 23 12:50:20 CST 2005 Top

SQL Server Developer >> is this a lot Five joins is typical for a reporting query, especially if you are querying
directly from an OLTP database. I routinely write queries that join a dozen
tables or more. Redesigning a database model to eliminate the number of
required joins is called "denormalization" and is used only useful for
situations like offline data warehouse system where the goal is to optimize
the performance of reports.



> hey all,
>
> is a 5-table join a lot of tables? the reason why i ask because this one
> table has so many code fields (that reference master files which contain
> descriptions i need for a report). how do you get around that?
>
> thanks,
> rodchar


 
 
Mike





PostPosted: Wed Nov 23 14:31:54 CST 2005 Top

SQL Server Developer >> is this a lot > table has so many code fields (that reference master files which contain
> descriptions i need for a report). how do you get around that?

We have a similar thing here. We have a SoftwareProduct table where each of
its 10 or so columns is a foreign key integer to a related lookup table that
contains all the descriptive text.

Your best bet is probably just to make a view that joins the appropriate
tables and selects the columns of interest. You then simply select from the
view.

> is a 5-table join a lot of tables? the reason why i ask because this one

No. THIS is a lot :) The old version of this monster view faithfully
crashed TempDB every time. ehehe


FROM SurveyContact
INNER JOIN Survey ON Survey.SurveyKey = SurveyContact.SurveyKey
LEFT OUTER JOIN SurveyWave ON SurveyWave.SurveyKey = Survey.SurveyKey
LEFT OUTER JOIN Contact ON Contact.ContactKey = SurveyContact.ContactKey
INNER JOIN CompanyLocation ON CompanyLocation.CompanyLocationKey =
SurveyContact.CompanyLocationKey
INNER JOIN Country ON Country.CountryKey = CompanyLocation.CountryKey
LEFT OUTER JOIN City ON City.CityKey = CompanyLocation.CityKey
LEFT OUTER JOIN StateProvince ON StateProvince.StateProvinceKey =
CompanyLocation.StateProvinceKey
LEFT OUTER JOIN Language ON Language.LanguageKey =
CompanyLocation.LanguageKey
LEFT OUTER JOIN CompanyLocationSampleSource ON
CompanyLocationSampleSource.CompanyLocationKey =
CompanyLocation.CompanyLocationKey
LEFT OUTER JOIN SampleSource ON SampleSource.SampleSourceKey =
CompanyLocationSampleSource.SampleSourceKey
LEFT OUTER JOIN SampleSourceType ON SampleSourceType.SampleSourceTypeKey =
SampleSource.SampleSourceTypeKey
LEFT OUTER JOIN JobTitle ON JobTitle.JobTitleKey = Contact.JobTitleKey
LEFT OUTER JOIN JobCategory ON JobCategory.JobCategoryKey =
JobTitle.JobCategoryKey
LEFT OUTER JOIN PhoneNumber ON PhoneNumber.PhoneNumberKey =
Contact.CurrentPhoneNumberKey
INNER JOIN CompanyLocationProduct ON
CompanyLocationProduct.CompanyLocationKey =
CompanyLocation.CompanyLocationKey
INNER JOIN Product ON Product.ProductKey = CompanyLocationProduct.ProductKey
LEFT OUTER JOIN HardwareProduct ON HardwareProduct.ProductKey =
Product.ProductKey
LEFT OUTER JOIN HardwareSeries ON HardwareSeries.HardwareSeriesKey =
HardwareProduct.HardwareSeriesKey
LEFT OUTER JOIN SoftwareProduct ON SoftwareProduct.ProductKey =
Product.ProductKey
INNER JOIN DiagnosticQuotaGroup ON DiagnosticQuotaGroup.SurveyKey =
Survey.SurveyKey
INNER JOIN DiagnosticQuota ON DiagnosticQuota.DiagnosticQuotaGroupKey =
DiagnosticQuotaGroup.DiagnosticQuotaGroupKey
LEFT OUTER JOIN ProductDiagnosticQuotaGroup ON
ProductDiagnosticQuotaGroup.DiagnosticQuotaGroupKey =
DiagnosticQuotaGroup.DiagnosticQuotaGroupKey
LEFT OUTER JOIN DiagnosticQuotaGroupLastResort ON
DiagnosticQuotaGroupLastResort.DiagnosticQuotaGroupKey =
DiagnosticQuotaGroup.DiagnosticQuotaGroupKey
LEFT OUTER JOIN MeasurementQuota ON MeasurementQuota.SurveyKey =
Survey.SurveyKey AND MeasurementQuota.CountryKey = Country.CountryKey


--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a ****er.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane


 
 
rodchar





PostPosted: Wed Nov 23 17:20:04 CST 2005 Top

SQL Server Developer >> is this a lot thanks everyone for the help.



> hey all,
>
> is a 5-table join a lot of tables? the reason why i ask because this one
> table has so many code fields (that reference master files which contain
> descriptions i need for a report). how do you get around that?
>
> thanks,
> rodchar