June 22, 2008 at 9:57 pm
From the sql server performance point,is it better to create a 2-3 views for an output or create 2-3 #temp tables in a stored procedure.
can somebody clarify this to me please.
thanks.
June 22, 2008 at 11:53 pm
You'll have to test each way and see what's better. Without a lot more info, there's no way to say which will be faster in your case.
There is a massive difference between a view and a temp table. A view is just a saved select statement. When you run a query that refers to a view, SQL inlines the entire view definition into the query.
Temp tables on the other hand are tables and do store data.
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
June 23, 2008 at 12:55 am
I am considering whether to create 2-3 #temp 's in a procedure and generate a report or to create a 2-3 view and call in the report.
considering all other thing are common for both cases,which do you think will be faster.
June 23, 2008 at 4:56 am
mathewspsimon (6/23/2008)
considering all other thing are common for both cases,which do you think will be faster.
How long is a piece of string?
That's not a question that can be decisivly answered in the general case. Test both and see which is better.
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
June 23, 2008 at 8:07 am
Gail's 100% on the money here.
If you just look at the differences you can see why. A view is simply a select statement kept around in a way that sort of masks it as a table. It's not physically storing the data (except for materialized views, which we're not talking about). Temp tables are physical, if temporary, storage of the data. So, as she's trying to point out, in some cases, simply running a query, your views, is all you need and it will perform best. In some cases, moving the data into a different form, using up I/O resources, actually is the right way to go and it will perform best. Without more parameters, since we're comparing apples to lug nuts, there's no way to say which one is better. I'll eat the apple, but I won't use it to hold the tires on the car. I'll attach the tires with the lug nut, but I've no interest in eating it. Each is best for its purpose. What purpose are you trying to achieve?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 24, 2008 at 3:19 pm
Temp tables are faster in some cases, views are faster in others. It depends on a lot of factors.
Give us a sample of each, and some of the tables you're pulling data from, and we can probably help you decided which will be best in your particular case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 24, 2008 at 4:43 pm
If you try to join 32 tables with multiple aggregates and functions on columns in the ON or WHERE clauses, it'll likely be better to use "Divide'n'Conquer" methods in a proc with temp tables. Otherwise, the rest of the folks are pretty much spot on... it's impossible to know until you test the actual query...
--Jeff Moden
June 25, 2008 at 5:28 am
It is much more convenient to use temp tables while using stored procedure if the views are not created in the first place. If you think that you will be referring to views in future and want to reuse that definition, then view would be convenient. But, creating temp tables on the fly in a stored proc and then dropping them in the stored procedure itself shouldnt be a problem. Dropping this way would also clean up the occupied resources, which are stored in the TEMP database.
July 1, 2008 at 5:51 am
July 1, 2008 at 6:15 am
SrikanthSv (7/1/2008)
use views when the amount of data is lessIf the amount of data which the stored procedure hits is huge then use the temp tables. Using temp tables will recompile the procedure before execution.
Actually, as always, depending on the circumstances, I strongly suggest you take exactly the opposite tack on this. If you move "huge" data sets into temp tables you're adding a lot of I/O to your process and in all likelihood killing the performance of the query, not to mention what you're doing to your tempdb.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 1, 2008 at 6:37 am
Heck... even that "depends". The bottom line is that you don't really know for any particular circumstance until you write some POP code and test it.
--Jeff Moden
July 1, 2008 at 6:40 am
Yeah, yeah, I used the "depends" right at the beginning and added weasel words "in all likelihood"
I covered my behind.
😛
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 1, 2008 at 7:47 am
Heh... crack was showin'... :hehe:
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply