I have a scenario where i need to

  • join with 3 tables to get the value for one field
  • for second field join with 4 tables
  • 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.


  • 3
    Your question is very general, given that you haven't posted any DDL or DML, the amount of data in each table, indexes, whether or not you're running Standard or Enterprise Edition and/or using enterprise features like partitioning. This makes your question near-impossible to answer. Views and subqueries are equivalent from a query plan perspective. – Daniel Hutmacher Aug 22 '14 at 11:00

As Daniel says in his comment, without more information we can only guess.

This this sort of circumstance your best bet is to generate some realistic test data (or just grab a copy of the live data if it already exists and there are no compliance issues with using it for dev/test purposes) and try each method and benchmark them. There may of course be several ways to write each of the three options, so you might have more than three methods to benchmark. If you benchmark with realistic data you will know which is the better option (and by analysing the query plans generated you might understand why or even see a better way). If you test against current data, it is a good idea to also test against data bigger than the current set (i.e. even if you use real data, add in some realistic generated test data) so you know that things are not going to change for the worst as your data grows over at least the next year or few.

For a couple of general rules:

  • For many things views, table valued functions and sub-queries are equivalent: the query planner will unroll them to the same plan if they are in fact doing exactly the same thing.
  • This changes depending on how you use them in the query and if you need to use values from the other objects in the sub-query (making a correlated sub-query) or function call, and so forth (i.e. anything that means that portion of the query could not be run on its own and joined in later instead of relying on data from other objects in the query).

There is another option that you have not mentioned: you can use CTEs too. For this purpose these are equivalent to using views except they only exist for the time the query is running. Again (with some caveats on if you are doing something complex with them) you are not unlikely to find the query planned unrolls them in exactly the same way it woudl the view/function/subquery versions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.