Materialized Views or Table Functions or any other way to optimise query

Posted on 2009-12-22
Medium Priority
Last Modified: 2012-05-08
SQL Server 2005:

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)

In my queries i use joins similar to code attached

 i'm using join twice and for each stored procedure difference is only in where clause ( i add new 'And' conditions) so i thought can i reduce it using some views
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

Open in new window

Question by:rahulsurya
1 Comment
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 26108388
yes, you could reduce the code by the views, but the performance might eventually not be the best.
you must check this by testing

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question