SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 which one is better?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

iamsmahd
Yak Posting Veteran

Iran
55 Posts

Posted - 10/23/2005 :  07:35:58  Show Profile  Reply with Quote
Hi all,

which method has better performance?
for creating a report that uses a query

1-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 much

sonia

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/23/2005 :  07:59:20  Show Profile  Reply with Quote
Most often 2-1

Kristen
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 10/23/2005 :  07:59:48  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/23/2005 :  08:12:45  Show Profile  Reply with Quote
"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
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 10/23/2005 :  08:26:12  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 10/24/2005 :  01:46:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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 reply

Except 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 sp



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

iamsmahd
Yak Posting Veteran

Iran
55 Posts

Posted - 10/24/2005 :  05:36:00  Show Profile  Reply with Quote
ok friends, So which method should I use after these discussions?

Thank you

sonia
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 10/24/2005 :  05:48:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Kris's first Reply

Most often 2-1


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

steamngn
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/26/2005 :  20:50:11  Show Profile  Visit steamngn's Homepage  Reply with Quote
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.
Andy

There's never enough time to type code right,
but always enough time for a hotfix...
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/26/2005 :  22:01:32  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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 ...

Edited by - jsmith8858 on 10/26/2005 22:02:30
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/26/2005 :  23:51:05  Show Profile  Reply with Quote
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_1
AS
SELECT ColA, ColB, ...
FROM TableA
JOIN TableB ON B_Col1 = A_Col1 
GO

CREATE PROCEDURE MyProc1_2
    @MyColA varchar(10)
AS
SELECT ColA, ColB
FROM MyView1_1
WHERE ColA = @MyColA
GO

CREATE PROCEDURE MyProc2_1
    @MyColA varchar(10)
AS
SELECT ColA, ColB
FROM TableA
JOIN TableB ON B_Col1 = A_Col1
WHERE ColA = @MyColA
GO

There is no tangible difference between MyProc1_2 and MyProc2_1 that I know of.

But was that the question?

Kristen
Go to Top of Page

steamngn
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/27/2005 :  07:37:33  Show Profile  Visit steamngn's Homepage  Reply with Quote
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!
Andy


There's never enough time to type code right,
but always enough time for a hotfix...
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/27/2005 :  09:36:16  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Why would a view not have a cached execution plan?
Go to Top of Page

steamngn
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/27/2005 :  10:07:03  Show Profile  Visit steamngn's Homepage  Reply with Quote
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!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/27/2005 :  13:56:01  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/27/2005 :  14:31:36  Show Profile  Reply with Quote
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!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...




[thud]
Lots of if's there

Have 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 862

Anyway, the answer is

1. Create a View to retain all business logic and to isolate database changes to the developer/user

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)

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....



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/27/2005 :  14:39:37  Show Profile  Reply with Quote
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



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37161 Posts

Posted - 10/27/2005 :  15:07:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

steamngn
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/27/2005 :  16:30:51  Show Profile  Visit steamngn's Homepage  Reply with Quote
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%...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37161 Posts

Posted - 10/27/2005 :  16:36:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

steamngn
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/27/2005 :  16:38:16  Show Profile  Visit steamngn's Homepage  Reply with Quote
Hey Brett,
Hope I didn't hurt your head with those bone-crushing thuds!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000