Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Creating tables dynamically

    Hi All ,
    I have a scenario in my application where i need to create multiple tables and query the dynamically created table.so i will dynamically create / drop table , columns . I m doing this in java.
    My doubt will it cause any performance issue or any other technical problem if i frequrently do this create and drop actions ??
    Any suggestions will be appreciated.
    ----------------------
    Thanks in advance

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    In most DBMSs this is not a very good idea because it makes database administration and storage management harder.

    The obvious question to ask yourself is why do you think you need to do this? Where does the requirement come from for these new "dynamically created" tables and why can't you service that requirement through normal change management processes?

  3. #3
    Join Date
    Dec 2009
    Posts
    2
    hi dportas ,
    thanks for your reply.
    I analysed the requirement and there is no other way other than to create things dynamically.
    so wanted to know will i get into any technical problems when i constantly modify the structure of the table.
    When i say technical problem i mean with repect to querying will i get any issues ??or internally db cannot handle too much of table structure modification ??.
    frankly i dont know what kind of technical problem i can face because of this approach .
    All i can see is this is not a usual way of doing it in DBMs so just wanted to know will i later struck somewhere because of this approach ??.

    thanks once again for ur prompt reply

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It would be worth stating your requirements here so we can suggest another, better alternative. I would be very surprised if this is the optimal approach.

    This will likely be an administrative nightmare. In terms of performance, assuming you are flinging tables around (as it sounds you are) then the optimiser will have very poor (if any) statistics and indexes to use to optimise queries. There will also be a very high overhead when these are required as they will essentially be built and collated at run time. There will also likely be no, or very limited, cached plans so you will also get compilation delays.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by ashok.ganesan View Post
    I analysed the requirement and there is no other way other than to create things dynamically.
    I am not convinced of that. Dynamic table creation is not a business requirement, it's a possible solution that you have chosen. Please explain the actual requirement and maybe someone can suggest some alternatives. For instance you could make a more flexible design which you won't have to change at runtime. Trivially, one exterme of flexibility is: Tbl {id,obj}, ie a BLOB store. That's a static schema but you can put pretty much anything in it!

    There are lots of potential pitfalls to modifying a schema at runtime. How they affect you may depend on the actual reuirements, which you still haven't told us about.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by ashok.ganesan View Post
    hi dportas ,
    thanks for your reply.
    I analysed the requirement and there is no other way other than to create things dynamically.
    Then you arrived at the wrong conclusion.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    The posts so far are probably right. But I've done stuff that was almost a database running on a database where it made sense to do this.

    Quote Originally Posted by ashok.ganesan View Post
    My doubt will it cause any performance issue or any other technical problem if i frequrently do this create and drop actions ??
    They're always concerned about performance. For whatever reason, it's just never an issue for me. I'm usually more concerned about design issues, such as whether it fits comfortably, but that's just me.

    The answer is "it depends on the DBMS."

    Having lots of tables shouldn't matter much. The system tables and views are tuned by people with a fair amount of experience. You can always test this: just time some regular queries, then construct a however many tables, and time those queries again. Do post results.

    You'll need to know how to quote symbols such as table names, column names, etc. (Or you can avoid using reserved words; making all your table names start with a prefix like FOO_ is one way. But quoting Just Works.) The ANSI SQL standard is, confusingly, almost identical to quoting strings. To quote a symbol, you wrap it in double-quotes, and escape any double-quote characters.

    The biggest problem, IMHO, is whether you can mix DDL (any CREATE, ALTER or DROP statement) and DML in a transaction. That is, the following code is guaranteed to run completely or not at all:

    Code:
    INSERT INTO myTableInfo (tableName) VALUES ('footable')
    CREATE TABLE footable (...)
    That's important, because it is virtually impossible to use dynamic tables without adding some information of your own. You can't modify the system tables, so you need to keep some data in user tables in synch with them.

    In most DBMSs, DDL will force a transaction to commit. (You can test this by executing the above statements followed by ROLLBACK. Then test two insert statements, just to make sure you're not automatically committing or something.) In the above example, the insert statement will happen in one transaction, the DBMS will see the CREATE statement and commit the INSERT. It begins the CREATE statement in a separate transaction. But if the CREATE statement fails for some reason, the INSERT has already been committed. Your database is now inconsistent. If it seems like a simple problem to work around, you don't understand transactions.

    The UI is a bear. You'll find that it's very hard to handle cases where the user wants to change the type of a column and doesn't understand why the system locked up for a few hours. Or, if someone has a lock on a table, any attempt to drop or alter that table will have to wait on that lock. All the DDL is designed assuming that a DBA is doing it, and can guess how long it will take (by checking the stats or just prior experience) and is able to kill sessions if it hangs. There's a reason they pay those guys.

    The logic for handling types and constraints is extremely tricky. Even if you're only dealing with the relational model on a blackboard it's hard enough, add in SQL and your vendor's own weirdness and it gets to be a bit of a nightmare. Most DBMS tools will handle a limited set of cases and simply throw an error if the user tries to do anything complicated. Further, it's possible to create impossible constraints like foreign keys that point to other foreign keys, or check constraints that are logically or practically impossible to fulfill. Detecting those is hard.

  8. #8
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Hi ashok,

    I think most users want to know - in general - whats the special case that needs to
    dynamically create / drop table , columns
    etc.

    Most times, you dynamically drop and create tables based on some pre-existing structure and rarely drop columns. This should be OK. If you need to drop columns, it is better to have one pre-existing structure for each type (eg. one with the orriginal column and one with the column dropped).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •