Have Geek Brain

Is writing a request using Views a good strategy?

I just want to know if it is correct to write the query by first creating it in the view.

Any suggestions?

+1


a source to share


5 answers


The view is a macro and expanded .



The views at the top of the views simply mean more work for the optimizer to figure out the complexity at the bottom.

+3


a source


The lookup request is generally ok as the sql segment optimizer is not easy to trick. But don't use scalar functions in your view or you will get performance problems.



0


a source


It depends on what you are doing in the views and what you plan to do with them.

Creating views and then spreading them on top of each other (by referencing a view within another view) is generally not a good idea. Using ORDER BY on a view is also a generic no-no, because if you join the view, the order is done, but not needed.

0


a source


At the end of the day, the submission is only a sub-request, so it really isn't there.

SELECT *
FROM (
    SELECT *
    FROM myTable
    WHERE a = 2
) a

no different from

SELECT *
FROM myView

The plan is cached so you are not really affected by performance and you have the flexibility to reuse your code.

0


a source


I know there is a school of thought that says you should have an idea at the table and develop your code against your views instead of your tables. In this case, you can use evolutionary development techniques and, like your table changes, develop new views for your new code without breaking the views on which the old code relies. I've never had a project that I thought would be complex enough that I couldn't just update the code as the tables were changed, so I never used this method.

As far as writing queries in views, there is one scenario under which I do this. However, they may be specific to my LINQ use, so take that with a grain of salt. If I have a query that requires some complex processing and returns a composite object based on more than one table, I will usually write a stored procedure or table function to execute the query. I found it easier to work with the LINQ designer if I also create a view that I can drag and drop onto the design surface to create a class for the objects that the sp / function returns, instead of automatically generating the class. This gives me more control over the class name later if I choose.

0


a source






More articles:


All Articles