Performance of Joins Vs Functions

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)

Performance of Joins Vs Functions

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-09-18 : 08:40:39

I have 1 million row table which has look up values in 7 columns, I will get the description for all the 7 columns from the corresponding look up tables.

In this case is it better to join the core table with all the look up tables or is it better to create a function for each of the lookup value which will in turn spit out the description for these lookup values.

Lookup value tables will not have more than 100 rows at any given point.

Any inputs/suggestions would help.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 08:45:44

I assume you already have tried both scenarios?
Because we know nothing about your tables, indexes, hardware and so on, nor what calculations to be made, all we can do is guessing.

Post back timing for both scenarios and if you then want some help with a specific problem, we will be happy to help.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 09:06:31

"is it better to create a function"

Which of these do you mean?


SELECT Col1, Col2, dbo.My_FN_Col3Lookup(Col3)
FROM MyTable


SELECT Col1, Col2, LU.Description
FROM MyTable AS T
     JOIN dbo.My_FN_Col3Lookup() AS LU
         ON LU.ID = T.Col3

or the "un-function" method:


SELECT Col1, Col2, LU.Description
FROM MyTable AS T
     JOIN MyLookupTable AS LU
         ON LU.ID = T.Col3

Assuming good indexes etc. I would expect (3) to be fastest, although for very few results (1) may be faster.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 09:12:10

Reading the question again, makes me think Kristen's third approach is what you want

SELECT		mt.Col1,
		mt.Col2,
		lt1.Description,
		lt2.Description,
		lt3.Description,
		lt4.Description,
		lt5.Description,
		lt6.Description,
		lt7.Description
FROM		MainTable AS mt
LEFT JOIN	LookupTable1 AS lt1 ON lt1.ID = mt.Col3
LEFT JOIN	LookupTable2 AS lt2 ON lt2.ID = mt.Col4
LEFT JOIN	LookupTable3 AS lt3 ON lt3.ID = mt.Col5
LEFT JOIN	LookupTable4 AS lt4 ON lt4.ID = mt.Col6
LEFT JOIN	LookupTable5 AS lt5 ON lt5.ID = mt.Col7
LEFT JOIN	LookupTable6 AS lt6 ON lt6.ID = mt.Col8
LEFT JOIN	LookupTable7 AS lt7 ON lt7.ID = mt.Col9

E 12°55'05.25"
N 56°04'39.16"

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-09-18 : 10:39:09

Thanks guys that helps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources