Views or table functions or something else.

Question

I designed 5 stored procedures which almost use same join condition but parameters or values in where clause change for each on different runs. Is it best solution to create a view with all join conditions without where clause and then query from view or work on view? Can views auto update itself if i create view? Can i do sub-queries or query similar to (i think i read somewhere views do not support sub queries but not 100% sure)

select count(x1) as x1cnt, count(x2) as x2count
from (
        select x1,x2, 
        (
        case when x1 is 'y' then 1 else 0 end +
        case when x2 is 'y' then 1 else 0 end
        ) per
        from vw_viewname) v1
where v1.per = 1

Updated below:

In my queries i use joins similar to this also

select c1,c2,c3
FROM [[join conditions - 5 tables]]
Inner join
(
select x1,x2,x3, some case statements
FROM [[join conditions - 5 tables]]
where t1.s1 = val1 and t2.s2 = v2 etc
) s
on s.id = id

so i'm using join twice so i thought can i reduce it using some views

Answer 1

You can have subqueries in a view, and that approach is perfectly acceptable.

Answer 2

SQL Server views do support sub-queries. And, in a sense, views to auto update themselves because a view is not a persisted object (unless you use an Indexed View). With a non Indexed View, each time you query the view, it is using the underlying tables. So, your view will be as up to date as the tables they are based upon.

It sounds to me like a view would be a good choice here.

Answer 3

It's 5 different queries so leave it like that.

It's seductive to encapsulate similar JOINs in a view, but before you know it you have views on top of views and awful performance. I've seen it many times.

The "subquery in a view" thing probably refers to indexed views which have limitations.

Answer 4

It's fine to create a view, even if it contains a subselect. You can remove the where for the view.

Are you sure you want to use COUNT like that without a group by? It counts the number of rows which contain non-null values or the parameter.

Answer 5

Unless your talking about an indexed view, the view will actually run the script to generate the view on demand. In that regard, it would be the same as using a subquery.

If I were you, I would leave it as it is. It may seem like you should compact your code (each of the 5 scripts have almost the same code), but its what is different that is important here.

Answer 6

Leaving out there where clause could make the query run more slowly or just give more results than a specific query would. But you will have to determine if that is advantageous based on your system

You will get the common view results table to work with. View basically run the query when you use them so you will get results as if you did the query yourself by some other mechanism. You can do sub queries on a view just as if it were another table. That should not be a problem. But if you have 5 different queries doing 5 specific things then it is probably beneficial to leave it as so. One or two of those may be called more and you would be trading off their performance with a general view table and gain nothing really for doing so other than view reuse.

I would only construct the view if you have some specific benefit from doing so.

Also I found this post that may be similar Dunno if you will find it helpful or not.

EDIT: well I think it would just make it worse really. You would just be calling the view twice and if its a generic view it means each of those calls is going to get a lot of generic results to deal with.

I would say just focus on optimizing those queries to give you exactly what you need. Thats really what you have 5 different procedure for anyway right? :)

Answer 7

I've done a lot of presentations recently on the simplification offered by the Query Optimiser. Essentially if you have planned your joins well enough, the system can see that they're redundant and ignore them completely.

http://msmvps.com/blogs/robfarley/archive/2008/11/09/join-simplification-in-sql-server.aspx

Stored procedures will do the same work each time (parameters having some effect), but a view (or inline TVF) will be expanded into the outer query and simplified out.