SQL Server Performance

Design Question, split tables or not

Discussion in 'Performance Tuning for DBAs' started by Stuck, Mar 5, 2003.

  1. Stuck New Member

    I have a kinda large site that contains a forum, stuff to send message to each others and things like that. We are going to make the site in two language and what i wounder is if you guys think its a good idea to have a table for each language (ex. swe_tForumTopics eng_tForumTopics) or to just put everything in the same table. <br /><br />The reason i wonder is that i suspect that it will grow very large if i have everything in one table and since its so much inserts and selects from the tables all the time i suspect it will be slow if it is to big. I experince some performance problems already and have to clean up the tables kinda often to make search and stuff like that possible.<br /><br />Is there any other way to speed up big tables with much text in them then to just make two tables and split the data up?<br /><br />Please give me some pointers <img src='/community/emoticons/emotion-1.gif' alt=':)' /> And ask if you want more info of my design.<br /><br />/Ola - www.whoa.nu
  2. Chappy New Member

    In my experience this sort of design change is often a decision that you live to regret. What happens when you want to extend into French too? New tables, new indices etc all add to maintenance steps required, instead of simply using existing code and passing a new language identifier. Also depending on how you access these tables, if you are planning to bulid dynamic sql based on language, this could also potentially reduce sql's ability to cache execution plans etc

    Remember some databases function quite happily with millions and millions of rows. The large size does not necessarily detract from performance if it is well indexed and designed.

    Full text search may help you, I dont know what sort of queries youre going to run against this database. However, you might be on the right lines by wanting to split the bulky text body away from the logic of the forum records, it would depend on their schema I think, and how the tables will be interrogated.

    So to summarise, possibly split the large text elements away, but I wouldnt split it purely based on the language. Id have a column to dictate what language each forum post is using, and index based upon that, plus whatever other fields you require. Just my personal opinion of course.
  3. bradmcgehee New Member

    I agree with Chappy, and you should avoid using more than a single table, as it will make your life more complicated. As he said, the number of rows is not all that important if indexing is good, queries are optimized, and the indexes are rebuilt periodically.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page