April 15, 2009 at 2:15 am
Hi Everyone,
Can someone tell me how does a view and procedure work internally?
I created an SP with the required parameters and Query to fecth data from multiple table. The Select list consists of arnd 150 columns. Procedure took 45 seconds to execute.
Later i created a view to fetch the data and in the same proc with same parameters i just wrote select * from viewname and where clause. When i excecuted this proc, it returned the same data in 8 sec. Can sm1 elxplain me how did the view called in SP speed up datd retrival?
Thanks in advance.
April 15, 2009 at 2:22 am
It may not be just view vs procedure. It may be that the second query the data was in cache and hence didn't have to be fetched from disk. To do proper time tests, run each one at least twice and discard the first result.
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
April 15, 2009 at 3:55 am
Hi,
I see that its written there as newbibe, Ok
1) Views are a way to represent data from mutiple tables. the easy way to see that is you are having few tables like products and categories. and you want to mix product and categories in a single resultset you write a Tsql and then execute it, works fine but how would it be if want to make it into a known object structure and i am going to use this Tql many times. so you create a view.
2) Procs have got additional functionality, you can do looping though a cursor, you can create if and else conditions branching and write programs. so procs are different.
Procs have additional benifit of caching and other things.
Best thing is to pick up a small sql book and start there it gives confidence that you have completed the sql book and then dive into details of each one.
Thats my personal experience of how i learn.
select * is very simple but also very not nice in sql world one you get your grips on sql you come to know why and when to use it.
Regards
Vinay
Regards
Vinay
April 15, 2009 at 4:20 am
I understand that its cached hence i'd re-executed the queries a couple of time but still the the SP in which i called the view was consistently fetching records in 5-8 sec.
April 15, 2009 at 4:33 am
Well m not a newbie..wht i was wanting to know is how does SQL Sever processes a View and a Stored Procedure is it just processed and complied once and remains as it or with every execution; it re-compiles it and what are the performance benefits. Thanks for your suggestion though
April 15, 2009 at 5:02 am
Ok,
Thanks for you honest feedback, Well then do the following to test the theory dont do it on production database.
dbcc freeproccache
go
dbcc dropcleanbuffers
go
set statistics profile on
go
set statistics time on
go
[exec .... / select view] your code here. each at a time and record the timings and see
go
set statistics time off
go
set statistics profile off
this will give you a actual time of execution and helps in putting the performence statistics for each one of them.
Regards
Vinay
April 15, 2009 at 5:15 am
results with the proc that calls a view inside are better than writing the query inside the view? can you tell me the reason behind this?
April 15, 2009 at 5:51 am
could yo post some example code please?
also if you time perhaps the two different execution plans 🙂
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 5:51 am
you should have had a similar output. in both select statements. could you paste your code so that we can see the results of the execution.
Regards
Vinay
April 15, 2009 at 5:53 am
If the two queries are basically identical, then I suspect it's parameter sniffing (you can look that up). First, make sure that the queries are identical (I know you said they are, but we all make little typo's on occasion). Next, get the execution plans and see if they're different. They probably will be, but you'll see that the difference is probably in the application of one of the parameters, the stored procedure is doing a scan where the view is doing a seek.
At this point it's just speculation. If you want more detailed answers, post some sample code, sample structures, sample data and the actual execution plans from your queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply