0

I have a scenario where i need to

  1. join with 3 tables to get the value for one field
  2. for second field join with 4 tables
  3. for third field join with 3 tables

all above joins are inner and if data exist the value of my field would be "Y" else "N"

to achieve this which method gives best performance
  1. create a tablevalued function with which i can join and check if null then "N" else "Y"
  2. create a view instead table valued function and do join with that
  3. write subquery and use join with subquery.

which will be faster... this is goin to executed on large data. hence performance is the major factor.

-Thanks

Radhi
  • 6,289
  • 15
  • 47
  • 68

1 Answers1

1

Functions are evil.

Difference between a view and a subquery, on the other hand, will be hardly noticeable - as long as queries are exactly the same, of course. Views will probably be less flexible, though.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • 1
    *‘Functions are evil’* is too much of a generalisation. In-line TVFs are quite optimiser-friendly and perfectly fine to use. – Andriy M Aug 26 '14 at 06:21
  • @AndriyM, inline is the only exception. But with regards to performance, as was the topic of this question, I doubt they will beat either views or subqueries. They have their uses, but performance-wise, they are the worst choice. – Roger Wolf Aug 26 '14 at 06:45