Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2008
    Posts
    24

    Unanswered: Effective Way Of Combining Large Tables

    I need to combine (not merge) some tables into a single big table.Each table structure is identical.


    The method i am using is INSERT INTO newTable select * from table1 UNION ALL select * from table2 UNION ALL select * from table3.I can't use SELECT INTO as i need to retain the indexes of the table.

    Like to know is there and efficient way to do that.

    Thanks in advance
    Kiran

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Any reason you can't use CREATE TABLE & CREATE INDEX syntax and then INSERT INTO?

    Also, how come you have so many tables in your database of identical structure?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2008
    Posts
    24
    Hi,

    Thanks for replying.

    I have already created the table with indexes and using INSERT INTO and it is working perfectly.I just like to know is there anymore efficient way to do that operation.

    For easier access of the data in our application we will split the tables into small partitions and we will combine them while querying.

    Thanks
    Kiran

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is typically the most efficient way to create & populate a table - much more efficient than SELECT INTO.... You might experiment with creating indexes after the insert, but I have never found this to be beneficial.

    If you are using SQL 2005 Enterprise, you could look at the native table partitioning. If you are using SQL Server 2000 you might want to investigate PARTITIONED VIEWS before continuing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2008
    Posts
    24
    thanks for that !

    Is there any equivalent in Sql server like merge tables or memory tables like in Mysql.

    If we create a temporary tables it will be fast and everything is done in the memory.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "For easier access of the data in our application we will split the tables into small partitions and we will combine them while querying."

    i don't see this as easier at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't know mySQL so I don't know.

    I'm getting a bit nervy that you might not be doing the best thing here.

    When exactly do you insert into this one table? What triggers it and what purpose will it serve?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Dec 2008
    Posts
    24
    i mean like combining bunch of tables with huge data using join and with criteria is costly so we will dump all the data in a single table and query it.

    we will split the tables because to make sure that each table will contains less number of rows say 10,00,000 per table kind of thing.



    Kiran

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - are you building this table every time you query it, or is it something you do nightly and you are happy to query out of date data?

    Also:
    Are you doing this because you expect performance problems or because you have experienced performance problems?
    How many rows do you typically return for each query? Presumably these would be distributed amongst all the tables?
    Did you read up on partitioned views?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    for populating large tables, it's faster to use a bulk load of some sort (BCP, bulk insert, SSIS) than INSERT INTO. 10m rows is not that many though.

  11. #11
    Join Date
    Dec 2008
    Posts
    24
    i want to do it at the application level rather executing a exe file ..my temporary table structure may vary according to the selected criteria...so as pootl flump said i am trying out temporary tables or views..i am just reading what are advantages and disadvantages in both the things

  12. #12
    Join Date
    Dec 2008
    Posts
    24
    hi pootle flump,

    I am combining the data only when i query.

    I had some performance problems while i am working with mysql so i moved to this approach.

    The maximum number of rows is 10 million.

    i read the partitioned views it is very useful for me and it is giving the result quickly can i know there are any dis advantages dealing with views ? but i searched in the net it is said that getting the details from a view is costly ..how to improve the performance on this can i use indexed views ? as i need to query the table once it is been created.

    Thanks for your reply
    Kiran
    Last edited by kirandarisi; 12-22-08 at 17:02.

Posting Permissions

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