May 11, 2010 at 9:40 am
Hi
I have a complex query.
Now this query can be written as
1. A view
2. A stored Procedure
Assume that the query do not accept any parameter .
Which one should be used for Optimal Performance and when should it be used?
Any pointers would be helpful.
May 11, 2010 at 10:16 am
I would code as a stored procedure, as it can cache the query plan.
It's a quite vague question, without any other requirement this is all I can suggest.
Hope this helps
Gianluca
-- Gianluca Sartori
May 11, 2010 at 10:48 am
This is one classic debate. I tend to lean towards stored procedures heavily. Could be that it creates more DBA work and I like DBA work. 🙂
But it also get SQL coding closer to where it should be done, by DBA's because generally speaking .net oop developers aren't that great at writing good enough performance SQL, anyone agrees?:-D
May 11, 2010 at 4:24 pm
Views are more flexible, because you can query them for subsets of rows and columns just like tables. On the other hand, I've seen disasters where developers used an existing view and then joined it to tables that the view was already touching. Performance cratered. Stored procedures protect you against that, as long as you don't let those same developers modify the stored procedures.
Gianluca, did I understand you to say that queries against views don't have their query plans cached? Surely not.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 11, 2010 at 4:46 pm
I think this is a classic it depends kind of answer. A lot will depend on requirements and your environment.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 11, 2010 at 8:41 pm
koustav_1982 (5/11/2010)
HiI have a complex query.
Now this query can be written as
1. A view
2. A stored Procedure
Assume that the query do not accept any parameter .
Which one should be used for Optimal Performance and when should it be used?
Any pointers would be helpful.
In most cases, I'd have to side with the others and say "It Depends".
[font="Arial Black"]HOWEVER[/font], you stated that it's for a "complex query" and that very quickly takes the air out of "It Depends" for this question. If the views are kept simple, then views can be good. For complex queries... views can be absolutely terrible because of just one thing... views require you to write the whole thing in a single query and for complex queries, that's usually just exactly the wrong thing to do. Yeah... CTE's and Derived tables can help in the "Divide'n'Conquer" area but they are by no means a panacea for making high performance code. Sometimes you can make code literally hundreds and sometimes thousands of times faster simply by dividing the code up and maybe storing an interim answer in a Temp table. You can't do that in a view.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2010 at 10:19 pm
Sometimes you can make code literally hundreds and sometimes thousands of times faster simply by dividing the code up and maybe storing an interim answer in a Temp table. You can't do that in a view.
I absolutely agree, Jeff.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 11, 2010 at 11:31 pm
Without seeing the actual query it is still an it depends scenario for me. The word complex is a subjective one. It depends on level of experience and tsql ability. If taken to the level of complexity for Jeff, then absolutely in procs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 12, 2010 at 1:03 am
The Dixie Flatline (5/11/2010)
Gianluca, did I understand you to say that queries against views don't have their query plans cached? Surely not.
Views do not have cached exec plans. Queries that use them do. So unlike a stored proc, a view doesn't have a single cached plan but, when it's used within a query, the resultant query's plan is cached. 3 different queries querying the view - 3 different execution plans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 1:19 am
GilaMonster (5/12/2010)
The Dixie Flatline (5/11/2010)
Gianluca, did I understand you to say that queries against views don't have their query plans cached? Surely not.Views do not have cached exec plans. Queries that use them do. So unlike a stored proc, a view doesn't have a single cached plan but, when it's used within a query, the resultant query's plan is cached. 3 different queries querying the view - 3 different execution plans.
Ditto. That's what I meant, even if poorly worded.
I don't like using views, because it's too tempting for developers to use views against views against views ... OPTION (MAXRECURSION 10000000) 🙂
In my opinion, it's asking for trouble.
-- Gianluca Sartori
May 12, 2010 at 3:37 am
Could you please describe the situation a bit more, to steer the (though interesting) discussion in your way?
If you can't provide real world examples (could be sensitive) but maybe a constructed example similar to the task, that could help us help you furter?
Maybe DDL involved, amount of rows, what indecies you have right now, what tables and what you need to do in the view to produce the output you need, i.e. what makes it complicated in your "point of view".
May 12, 2010 at 5:42 am
Well to tell u guys frankly , this was a question that was asked to me in an interview.Though the question is really vague but let us assume the below points:
Say the select statement joins 5 tables each with around 200,000 rows.
there are no parameters/functions.
May 12, 2010 at 5:47 am
It depends.
The two are not directly exchangeable and are used for different reasons. Just saying 5 tables of x rows is not enough to make a decision.
Is the query only ever going to be run exactly like that? If so, stored proc. Will there be cases where additional filters will be necessary or additional tables joined? If so, view.
p.s. A 5-table query is not exactly complex.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 6:56 am
Oh. It was for an interview. Well well. 🙂 Hypotethical questions can be interesting for the community as well as I see it. Forums are a great knowledge base of "self-practical-problem-solving" when documentation in books online isn't good enough (rare cases).
I agree though that it was not complex enough specified to make a clear decision. Still depends.
In that case I would default to an sp if I can't get more information. Not just because I like sp's more.
The less bytes travelling to the server at executiontime the better. Also easier to debug and work with the data during development in an sp (maybe not an objective opinon) and you can go in any which way in "next version" without affecting non-tsql code, at a later time, when the demands is more specified ,as long as the calling contract is not changed.
May 12, 2010 at 10:40 am
GilaMonster (5/12/2010)
It depends.p.s. A 5-table query is not exactly complex.
Agreed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply