Tough Question, I would say that it would require testing no matter which
way you want to go.
It really depends on your situation. I referenced my 'SQL for Smarties'
(p.283) book for a second, and saw that Joe actually discusses views vs.
temp tables. I read it as: views are for restricting data by permissions in
ANSI SQL-92 standards and temp tables should be used in your scenario.
However, his book is based on ANSI SQL standards not necessarily how MS,
Oracle, or IBM implemented it. So the answer in the strict DB admin
perspective would be use a temp table. I personally would say that this
depends on the accuracy requirements.
Questions to consider:
Do you have a dedicated web server and DB server?
How is your memory on one (or both) machines?
What is your average CPU usage on both?
What part of the report is dynamic? Are the columns that are returned
dynamic or the filter of the data?
How accurate must the data be? Does it need to be refreshed once a day,
once a minute, up to the ms?
Do the reports require locking for accuracy?
What is going to hurt your DB server the most?
What is going to hurt your webserver the most?
What RDBMS are you on? Does it have any functions that will help (rollup
for instance on SQL Server)
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of David Grant
Sent: Thursday, June 06, 2002 9:28 AM
To: [EMAIL PROTECTED]
Subject: RE: Dynamically Creating Views
I might not be fully understanding you, but perhaps a stored procedure
would be best.
Just pass your parameter in, and you can use it in your WHERE statement.
WHERE I_AM = @out
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On
Behalf Of Palyne Gaenir
Sent: Thursday, June 06, 2002 7:06 AM
To: [EMAIL PROTECTED]
Subject: Dynamically Creating Views
I have a big table I need to combine with others for reports.
I would like to create a view to use for some reports, but the thing is,
the
table's already big, the view would be huge. So I'd like to create the
view
ONLY for the records that I actually need in it.
I thought maybe I could script it so the View is created based on a
dynamic
parameter (e.g., all student answers for course #123, mixed with the
actual
Q&A and student info in each record), then the report run on the view
(counting and grouping...), then the view would be DROPPED at the end of
the script, after the query run and display was output.
Would doing this be the wrong approach to my database? I
haven't heard about dynamically creating/dropping views so I'm
thinking, maybe there's a good reason for not doing it.
I understand that a view is essentially just a query run to create the
table
when called. But the WHERE statement on that query would have to be
different for every user or the resulting view-table would be so massive
it'd be
way too slow. So would creating/dropping views all over the place be
'taxing'
in some way on a database?
Any thoughts are appreciated.
Palyne
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Palyne Gaenir
Science Horizon Web Media
www.sciencehorizon.com
[EMAIL PROTECTED]
cel 918.533.3765 tf 877.316.0763
------------------------------------------------------------------------
-
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info:
www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
