| Author |
Topic |
|
iamsmahd
Yak Posting Veteran
55 Posts |
 Posted - 2005-10-23 : 07:35:58
|
| Hi all,which method has better performance?for creating a report that uses a query1-1: make the query as a view (v1) and save it.1-2: make a stored procedure and use v1 within it.2-1: make a stored procedure and build the query within it.Thank you very muchsonia |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-23 : 07:59:20
|
| Most often 2-1Kristen |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-10-23 : 07:59:48
|
| well its depends on the requirement.. .. How you easily you can get the records .. if you can directly get the records usiing normal select query then its would be faster.. but if you have some complex requiremnt and you are using sp for the same then it will degrade your performance depending upon number of tables used .. number of columns.. join.. filter coditions.. etc.. Complicated things can be done by simple thinking |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-23 : 08:12:45
|
"you can directly get the records usiing normal select query then its would be faster"Except that the query plan is unlikely to be cached, whereas with an SProc it will be more likely to be cached.I don't see why more tables degrades performance of one over the other (other than the lack of caching of the query plan) - the "query" will run in the same time - and the lack of a cached query plan applies to complex and simple queries alike, although it is likely to take longer to create a query plan for a complex query.Other than that a Stored Procedure tightens the permissions (by not needing permissions on the table), and centralises the "business rules" logic (may be good or bad) - but given that a multiple choice answer was offered I didn't bother with a justification in my answer!  Kristen |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-10-23 : 08:26:12
|
| Man .. i was thinking that if work is happening by the single select query then why u need to write the sp for it.. well that was great explanation for your choice.. :-)..Complicated things can be done by simple thinking |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-24 : 01:46:55
|
| >>Man .. i was thinking that if work is happening by the single select query then why u need to write the sp for it.. Here is Kris's replyExcept that the query plan is unlikely to be cached, whereas with an SProc it will be more likely to be cached.I think thats why you need spMadhivananFailing to plan is Planning to fail |
|
|
|
iamsmahd
Yak Posting Veteran
55 Posts |
Posted - 2005-10-24 : 05:36:00
|
ok friends, So which method should I use after these discussions?Thank you  sonia |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-24 : 05:48:33
|
| Kris's first ReplyMost often 2-1MadhivananFailing to plan is Planning to fail |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-10-26 : 20:50:11
|
| 2-1:procedure queries will always be better performance-wise for the reason that Kristen stated. procs are the way to go for most presentation layer retrieves; you get the added benefit of being able to pass parameters to the proc before returning the set.AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-26 : 22:01:32
|
| Why would a stored proc that uses a View would not have a cached plan or be any less efficient than one with the entire SELECT embedded? Is this documented somewhere?There should be no performance difference between options 1 or 2, other than that option 1 may result in shorter code in your stored procedure. Also, if many stored procs need variations of the same SELECT (i.e., a common join between two tables) then using a view to reduce repetition in your codde and introduce a little bit of abstraction into your stored procedures is, in my opinion, a great idea.It still amazes me how many of databases out there have 100's of stored procs and 0 views ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-26 : 23:51:05
|
OK, so I've gone back and re-read the question more carefully, and its not the question that I answered - because I didn't read it properly CREATE VIEW MyView1_1ASSELECT ColA, ColB, ...FROM TableAJOIN TableB ON B_Col1 = A_Col1 GOCREATE PROCEDURE MyProc1_2 @MyColA varchar(10)ASSELECT ColA, ColBFROM MyView1_1WHERE ColA = @MyColAGOCREATE PROCEDURE MyProc2_1 @MyColA varchar(10)ASSELECT ColA, ColBFROM TableAJOIN TableB ON B_Col1 = A_Col1WHERE ColA = @MyColAGO There is no tangible difference between MyProc1_2 and MyProc2_1 that I know of.But was that the question?Kristen |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-10-27 : 07:37:33
|
Hey guys,the difference between creating a view in the DB and then referencing it with a proc vs. just doing a select in a proc is this:The view select is resident in the DB, and when executed may or may not get cached. Provided that the hardware is configured properly,The proc will always get cached.If the proc calls the view AND the view gets cached, then there is no difference at all in performance; if, however, the view is NOT cached and the proc is, then performance takes a hit. Really the performance side is minimal. We normally use full SELECTS in our procs rather than views, as we can have template proc code that can quickly be modified for a specific report or group, and it allows us some control over what the report creator sees (This can be done with views as well, but new employees seem to handle the proc deal better).As an aside, we've seen faster backup times during the day as we've moved over to procs vs. views like this; I guess that is a performance plus .Hey Madhivanan, great quiz thread!AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-27 : 09:36:16
|
| Why would a view not have a cached execution plan? |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-10-27 : 10:07:03
|
| It has to do more with MSSQL itself: views have cached execution plans, but it is not empirical. The cache must be available to the view for it to cache; if the cache must be dumped first, the view will not cache. A proc, on the other hand, will clear cache in order to run. Again, it is hit and miss with views; I read this somewhere on the MS IT support site, but I can't seem to find it now. Basically the deal is viws will execute wether caching is available or not, and procs will pull a waitstate at the OS level until cache is available. I wonder if this changes in SQL 2005?Time to go a-diggin!AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-27 : 13:56:01
|
| I'm amazed, as I wouldn't have thought caching of the view had anything to do with it's use within an Sproc - I had thought (probably wrongly) that a view , when used in a stored procedure, would be "resolved" by SQL to its underlying tables and the query plan cached for the Sproc would, thus, be based on the underlying tables.Otherwise if the view JOINs to a table which is redundant that will have to be ignored - and that's part of the job of the overall query plan, isn't it?But I expect I'm being thick!Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-27 : 14:31:36
|
quote:
Originally posted by steamngn
It has to do more with MSSQL itself: views have cached execution plans, but it is not empirical. The cache must be available to the view for it to cache; if the cache must be dumped first, the view will not cache. A proc, on the other hand, will clear cache in order to run. Again, it is hit and miss with views; I read this somewhere on the MS IT support site, but I can't seem to find it now. Basically the deal is viws will execute wether caching is available or not, and procs will pull a waitstate at the OS level until cache is available. I wonder if this changes in SQL 2005?Time to go a-diggin!AndyThere's never enough time to type code right, but always enough time for a hotfix...
[thud]Lots of if's thereHave you read Kalen's Inside SQL Server yet?[/thud]There's a great discussion about PROCCACHE and what goes on and why, and why certain plans fall out of cache...none of which yo uhave mentioned.Check out page 862Anyway, the answer is1. Create a View to retain all business logic and to isolate database changes to the developer/user2. Create a sproc the uses that view. This simplifies the developers job and again the sproc is now isolated from changes to the view (business requirements, structure changes, ect)3. Add logging to the stored procedure to record when it was used by whom, and some other stats about what happened (@@ERROR< @@ROWCOUNT) into a heap log table.The last one I've found very useful of late....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-27 : 14:39:37
|
quote:
Originally posted by steamngn
As an aside, we've seen faster backup times during the day as we've moved over to procs vs. views like this;
You contribute faster backups to this?WOWBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-27 : 15:07:53
|
quote:
Originally posted by X002548
quote:
Originally posted by steamngn
As an aside, we've seen faster backup times during the day as we've moved over to procs vs. views like this;
You contribute faster backups to this?WOW
I find that astonishing as well. I don't buy it.steamngn,Could you explain what you mean?Tara |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-10-27 : 16:30:51
|
Ok,after reading what I typed it does sound spastic! I meant that we had many views on this DB, and after rewriting most of the selects into procs and dropping the views, the backups ran quicker. Not ALOT quicker, but quicker. I guess that would have to do with the DB size, no? Anyway, I did read Kalens piece,very informative. I really didn't do a good job of typing what I meant on this one! quote:
1. Create a View to retain all business logic and to isolate database changes to the developer/user
Won't business logic and DB changes still be isolated without the view?quote:
2. Create a sproc the uses that view. This simplifies the developers job and again the sproc is now isolated from changes to the view (business requirements, structure changes, ect)
Can you explain how this makes development easier? I'm a big fan of making everyones life easier...quote:
3. Add logging to the stored procedure to record when it was used by whom, and some other stats about what happened (@@ERROR< @@ROWCOUNT) into a heap log table.The last one I've found very useful of late....
I agree 100%... AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-27 : 16:36:48
|
I don't believe that getting rid of your views and putting them into sprocs had anything to do with the faster backup time. If it was faster though due to it, it would have been my one nanosecond.  Tara |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-10-27 : 16:38:16
|
| Hey Brett,Hope I didn't hurt your head with those bone-crushing thuds!AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
Next Page
|
which one is better?