I have done a similar trick to get around Access limitations. However, I made a table that was permanent and then just dumped the queryied rows into this table. Use this table in your next query then after your done just delete the rows from this table. That trick might be less prone to error then creating and dropping tables.
T ======================================== Tim Dudek GIS/IT Specialist Carter & Burgess, Inc. http://www.c-b.com Phone � 817.735.6750 Fax � 817.735.6148 E�Mail � [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Carter Burgess Plaza 25th Floor 777 Main Street Fort Worth, TX 76102 ======================================== -----Original Message----- From: Ferguson, Ken [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 4:30 PM To: '[EMAIL PROTECTED]' Subject: RE: Dynamically Creating Views PJ, What type of data are in these tables that you are left with so many records? I only ask as I wonder if it might be possible to separate some of it. For instance, it seems you are dealing with test questions, answers and students. If one of these HUGE tables is a table of all students, couldn't you have separate tables for current and former students (or they might be separated by some other means)? Also, could you not have separate tables for questions and answers per test. If anything along these lines is at all a possibility, I think it would make your life one that is infinitely easier with which to deal! Ferg -----Original Message----- From: Palyne Gaenir [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 4:19 PM To: [EMAIL PROTECTED] Subject: RE: Dynamically Creating Views I am publicly hosted, and so I do need to keep things reasonably small, as I'm sharing the server with many other domains. It has about 400,000 rows. (I'm going to be able to take a lot of those out, I'm working on that - some in deletion and some in recoding. I took out about 100K the other day and it vastly improved performance. I honestly think with revising the way I go about things I can bring this down to an average of 100K or so.) Part of the problem is that the db design (one of my first back in early '00) is wonderfully modular, but it means I have to do lots of joins for certain reports. (In hindsight, I would add some redundant integer fields, so I could do easy joins, rather than having to join four tables together to parse the 4th one by a value in the 1st one!) This gets complicated when, to do the report without taking all day or looping 150 times (BIG student classes) over a fairly complex query and output, I need to use SQL functions like count and group by. Further complicating it is that 'answers' can be in one of two fields in this (answers) table, so distinct(answer) in SQL won't work, unless I somehow get two columns into one column (only for these reports - wouldn't work in normal operation), then I could do that. My life'd be so much easier if this were in one table, with all the answers in one column. (Some are integer multiple choice, has to compare with other integer values. The others can be up to three long paragraphs of essay question. That's why they're separate columns in the table.) Due to the size of the table already, adding in the other fields (which are redundant of course, such as the question, and the user's name) would just make it groan... already it grows to the point of timing out queries on reports. But I have to make these reports requested by an instructor, and I'm having a hard time figuring out HOW (within query time limits) unless I use some creative SQL functions... which are tough to do on my tables as-is. I can't take the system offline to change it around, though I could make some small changes in the db structure in mid-July I think. I thought if I created a view table that was the join, but heavily parsed so it was small (and I'd make this a stPr when done), then I could do much more elegant SQL/output, and then just drop it when I was done. But I don't know what effect creating/dropping views all over the place has on a database. I doubt my remote dba (who already thinks I am his bad karma :-)) would appreciate me doing anything that would really tax the server resources or cause need for a lot of admin on his part. So, I thought I'd ask first. Regards, Palyne On 6 Jun 2002 at 15:42, Ferguson, Ken wrote: > PJ, it seems you are often having to deal with these issues regarding the > size of your tables. Maybe there is something you could do concerning the DB > design to alleviate some of the stress caused by these big daddies. > -----Original Message----- > From: Matthew Todd [mailto:[EMAIL PROTECTED]] > Tough Question, I would say that it would require testing no > matter which way you want to go. ------------------------------------------------------------------------- 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
