Palyne, I am curious. How big are your tables?
Matthew >From: "Jacob Cameron" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: <[EMAIL PROTECTED]> >Subject: RE: Dynamically Creating Views >Date: Thu, 6 Jun 2002 15:02:43 -0500 > > 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 _________________________________________________________________ Join the world�s largest e-mail service with MSN Hotmail. http://www.hotmail.com ------------------------------------------------------------------------- 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
