View or Temp Table?

[SQLBill]

using SQL Advanced Server 2000

I have some jobs that use views. The first step creates the view and the last step drops the view. I have been reading up on temporary tables (#mytable, ##mytable) and want to know:

If I want a temporary portion of a database, so that I can run queries against it, is it better to use a view and drop it when I'm done or should I create a temporary table.

Also, does a temporary table get dropped automatically or does it require the DROP TABLE command?

-SQLBill

 

[fluteplr]

Why do you create and then drop the view? A view is just code that creates a virtual table, leave the views out there. A view is almost always faster than a temporary table because it does not actually physically exist. It is in effect a sql select that is creating what looks like a table but is not really there.

Temp tables are dropped when the connction that created them is closed or the last user connection accesssing them closes, depending on if they are local or global.

That being said I always feel it is beeter to explicitly close them. Its the kindergarden rules thing, you know pick it up put it down, oepn it, close it. It makes for neater programming in the long run.

 

[SQLBill]

Thanks for the answers. I create and drop views because the view is created with just one day worth of information. Each day the job creates the view with data I need from yesterday, runs the query, then drops that view since I never need it again.

Bill

 

[tlbroadbent]

SQLBill,

It still seems inefficient to create a view each time you run the query. Can't you create a permanent view and then use daily criteria to select from the view? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[SQLBill]

I just finished the MS Programming a SQL Server 2000 database class and learned a lot. Now I'm going through all my programs and trying to make them more efficient. That's why I brought up this question. Since the answer is that views are more efficient but that I'm probably making my view unefficient...I'm going to look to see if I can make it better.

I currently have three different views that I use daily, each one has different information and includes the count() function on different columns. So, I'm going to play with them to see if I can combine them into one permanent view or if I have to keep three different views. But I am going to quit dropping the views.

Here is basically what I was doing:

create view my_t_list_view as
select distinct eventname, count(eventname) as 'Amount', priority as 'Priority'
from <table>
where eventdate >= convert(char(12), getdate()-1,1) + '05:00:00.000' --begin date/time
and eventdate < convert(char(12), getdate(),1) + '05:00:00.000' --end date/time
group by eventname, priority
go

select substring(eventname,1,40) as 'EventName', Amount, Priority
from my_t_list_view
group by EventName, Priority, Amount
order by Amount desc
go

-Bill

 

[computergeek]

Hi,

I am in a similar situation. I am not sure which would be more efficient and take less resources, and yet provide a quick response. A View, or using a temp table.

Currently our Web application (written in MS Interdev 6.0 and SQL Server 2000 runs a DB procedure (takes one parameter company_id to filter the data) to provide a navigational data grid on our home page. It has been requested that we provide some additional information... therefore we have to provide the current data and join to an additional table via a UNION to get the rest of the data. I have come up this a number of different approaches, all seem like they would work... but which is the most efficient use of resources under our circumstances? When is the View built? Can you define a VIEW as being a UNION of 2 tables?

Approach #1:
Create a VIEW which would basically do a UNION on two tables. Since a view is only resident on virtual memory and not physically stored I'm afraid this approach this might chew up alot of memory once our tables have grown. How much memory is enough? How does one know when you are reaching your limit? This approach would provide a quicker response than using a temp table though. I would access the view within the same procedure (passing the company_id as the filter).

Approach #2:
Use a temp table within the procedure to take data from both tables, once the temp table is created in the proc, return the result set. The main drawbacks here that I see is ensuring that Tempdb is large enough (how do you know how large to make Tempdb?), the next drawback is that I think the response time will be much slower than using a VIEW.

Approach #3:
Revise select within existing procedure to do a UNION to get the data from the new table. Foreseen drawback: Response time would probably be effected.

I am leaning towards Approach #1. Can someone please provide some expert advise on which method you would choose and why. Thanks in advance for the advise.

Computergeek

 

[tlbroadbent]

If you already have a procedure in place, I'd lean toward solution #3 if using SQL 2000. A union query can be efficient if you use UNION ALL and the tables are properly indexed. Regarding memory usage, SQL will use as much memory as available. The it will start using disk. This is true whether using views, ad-hoc queries, or temp tables.

Sometimes, inserting data into a temp table with two queries and selecting the final result set from the temp table can be more efficient. This was particularly true with SQL 7 and earlier.

Another option is to use table variables rather than temp tables in SQL 2000. They are less resource intensive and may provide better performance in some circumstances.

In reality, the only way to know which method will provide best performance is to test different methods. I'm often surprised to find performance results that are contrary to what I had thought they would be. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.