What is the tradeoff of building my tables dynamicly, I have a maximum tables which means that the maximum dynamic tables = the static tables DB.
Printable View
What is the tradeoff of building my tables dynamicly, I have a maximum tables which means that the maximum dynamic tables = the static tables DB.
By dynamic tables I assume you are referring to tables that are created by the queries on the fly. These are usually used as intermediate tables or worktables. Some queries also make temporary worktables for the various manipulations with the data to provide you with the resultset. Practically, work tables and temp tables are very much needed (prepared out of various joins etc.). And there is no real need of them once the query has finished since the information that is stored in them is intermediate and we can easily deduce them again when they are queried for (they could have longer life depending upon situations and needs and frequency of usage). So this is what exactly is the scenario where you might find the need of those.Quote:
Originally Posted by ManicQin
As for trade-off, I don't really understand what you are comparing it with? With static tables? They are not comparable to a lot extent because they both serve very different purposes. Static tables comprise the schema of your database but dynamic tables do not. Hope I did not confuse you and understood you well. Regards.
Hi,
You mean to say abt the Dynamic tables.Considering sql server 7.0 there we are using the temporary tables this will be saved in the tempdb.But in sql server 2000 there is a data type called table.This is more efficient than using the temp table.tyr this
Thank you both for your answers but apperntly I didnt explain my self right.
When I mean dynamic tables I'm talkin about tables that are created by code in RUN-TIME and not short term temp tables (who are self created by the DB).
the problem is that I know that i will need these tables and I know what tables i will be creating.
Think about creating a survey with one million Q's BUT they are all optional.
Every Table is representing a question (by assumption).
because the questions are optional I cant predict which Questions will be answered.
If from the start I will create ALL the tables i will have a hugh wasted DB.
if i create a table only when i need it (first time someone answered the Q) i will start with a slim DB.
two more things 1) from time to time i query a filled survey.
2) after a survey was queried i delete it from the tables and when a table is empty i delete A table!
By tradeoff i mean: Does a Dynamic Table (NOT A TEMP RUN-TIME created one) takes more space in the memory/H.D. and Is it slower. (Not optimized)
Rather than creating a table (or a few related tables) for a "question", can't u create a record for each "question" and related data [like answer(s), importance, ...]
then u can delete the records when u don't need those.
ever heared the word theoretical? ;)
offcourse that the solution for the problem is ridiculous it was just an example...
thanks.
and again the solution was only to explain why should i need dynamic tables. it is a bad bad bad bad solution and you can stop analyzing it couse it's only a exsample
Even your theoretical example is not a very good one. What you provided as an example is practically very very incorrect and when you are designing a database you need to think of the practical issues that you might face. If you are not considering them, you are going to land up with a mess almost always. Theory is not always going to provide you the best performance.Quote:
Originally Posted by ManicQin
As far as your query is considered. Yes, you can create database tables on the fly and not in the tempDB (the temp tables that you are thinking). There are hash-tables also, but they are also created in the tempdb and are there for the given session for a user. I think you don't need them. You would be simply calling another stored procs or something like a DDL quer with a "CREATE TABLE..." to create databases on the fly. They would remain there in the database until you do a "DROP TABLE....". There is no problem with that. You can go ahead with this. But seriously speaking, what you are thinking of is still a little bit vague in my mind. And if you are really trying to do something like you explained in your last post, you should follow what srinika suggested and that would be:
Create a table consisting of questions - and if the details of users taking the survey is not needed then you could simply put a numeric fields for all the answers to a question. And as and when a user selected an answer for a particular question what you could do is simply update the count with 1. Like below:Something like this. It depends upon a lot of things. You may not have simple options (A, B, C, D) as answer, you might be having text answers as well. In that case you will probably need an ANSWERS table and link it to the QUESTIONS TABLE using a foriegn key in QUESTION_ID (there are many possibilities). Once you are sure of what exactly you need, fire away your doubts so that you could get better help. Hope this helps. Regards.Code:QUESTION_ID QUESTION OPT_A OPT_B OPT_C OPT_D
1 "What is..." 100 131 23 45
2 "Where is." 22 436 22 78
thanks exterminator for your unproductive replay.
I'm really happy that you took your time to teach me DB 101.
that's not a good manner..we only try to help u hereQuote:
thanks exterminator for your unproductive replay.
for the tempTable..
Quote:
Does a Dynamic Table (NOT A TEMP RUN-TIME created one) takes more space in the memory/H.D. and Is it slower. (Not optimized)
as it said there that temporary table is dropeed imediately if user not explicitly drop the temporary table..so the performance should not decrease much since the memory is reallocated again after it droppedQuote:
Originally Posted by BOL
PS:just my opinion..
and if u mean dynamic tables is not like the quoted then the answer is YES for all ur question above
good luck