Views and Performance
-
Hi,
Quick question: when querying a VIEW, must the data for the view be generated in the view first or the data is already in the view?
Thanks,
-
Not sure exactly what you mean, but as I understand your question the answer is a view is a stored query, it executes and returns the resultset when called, no data (unless you have an index on the view) is stored with relation to the view itself.
-
Thank you. We have oversea developers working on a separate project that when they plugged it into our existing dev system the SQL server is brought down to its knees with CPU pegging at 100% for hours. They blamed the server hardware but when I looked at their codes I can't believe my eyes. They created 40 plus views, having a job with a cursor looping through the views (thousands of rows, not indexed) then for each line in the cursor they have another cursor looping through our databases. Everytime the job runs we can't even RDP to the server. Beside the cursors killing the server I am fighting with them about performance of the views. They said the data is there already in the views so there is no need to worry about.
I don't know what to do. There is more about it, management, political, etc.
-
Let me give you some advise: never trust overseas developers, they usually have no idea what they are doing. This is usually Oracle habit to build everything on cursors, so apparently they must be Oracle developers but due to a shortage of SQL server developers your management gave them this project.
So probably in your case the only solution would be is to shutdown the server, through all their code away and take development in your hands.
-
Sad but true... they're a reason they say "If you want a job done right... do it yourself".
You might be interested to do some research on management articles related to off shore outsourcing, many IT projects will actually cost MORE to off shore the work than it would have just cost to hire the people locally to do it.
Granted not ALL projects, but it has been the majority of them historically speaking. So any savings management thought they would get by hiring someone to do it at a 1/4 of the cost of doing it localy is quickly eaten up by things like this, at which point you end up paying even more to fix it...
-
maybe they didn't performance test on a full size data cut?
-
I don't know what to do. There is more about it, management, political, etc.
Fire the Boss
* Noel -
The views alone are probably not the perfomance culprit. More than likely it is the nested cursors that are fed by the views. As for what to do about it, well there are management and political concerns at work. Well part and parcel of being a DBA entails the arts of 'management' and 'politics'. After all, just pointing out a problem is not going to solve anything in most situations unless you can provide alternative(s) to a proper solution. If you can take a few (2 or 3) of these processes that bring the server to its knees and rewrite them using set based logic thereby improving perfomance as an example, then these processes could be used moving forward as templates. If you can do this then you are well on your way to becoming more 'management' and 'political' savvy in addition to honing your SQL skills. However, if after going through these excercises no change occurs, then noeld's advice probably stands as the best alternative.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
can some one give me an answer about how views can affect the performance of the application. which is better ? to use stored procedures or to use Views
Kindest Regards,SQL Server Lover
-
Views and stored procedures aren't interchangeable. You can do a LOT more in SP's than you cannot do within a view, so the question is a "trick" one to answer (As Steve would say - "It depends..."). depending on the application, what it's written in and what optimizations that language has for eeach of the objects, you might get very different answers. In a generic setting - that's a NP problem (can't be answered as is).
Views tend to get maligned, because they've been misused in the past as a convenient way to create "permanent" relationships (i.e. Select * from tablea inner join tableb on a=b), and then using that saved view as a basis for writing other statements. The problem with doing that is that by doing that - the subsequent operations tend to run without being able to take advantage of indexes of the tables underlying the view...
On the other hand - if the view is saved and represents "the whole enchilada" (relations AND filtering) - views work very well. SP's are more powerful, so they have their own reasons to be used.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
In a somewhat related topic, I've used both views and sub-queries to pre-filter and aggregate data for report queries. Views tend to be much easier for me to de-bug, but I've always wondered if there is a performance difference between the to methods.
-
There shouldn't be. Views are inlined when SQL runs the query. But as I often tell my developes, try both ways and see if there's a difference
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply