How to handle Large Tables in Sql server 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

How to handle Large Tables in Sql server 2008

Author

Topic

irs2k3
Starting Member

22 Posts

Posted - 2009-10-20 : 03:59:08

Hi guys,

I am creating a news portal with data entry over 1 million record each month in a single table.

By time, the table will become hard to manage.

I will not be able to use partioning or Compression because Sql server 2008 Enterprise hosting is very expensive.

What other solutions are available to manage large tables?

Thanks

irs2k3
Starting Member

22 Posts

Posted - 2009-10-20 : 04:31:33

btw, There is a new feature in Sql sevrer 2008 called filtered indexes.

Is this supported in Sql server 2008 Web Edition.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-20 : 06:18:09

It's not the volume of records per se that you need to be worried about, it's how well the databases is structured and how well you have it indexed and also how much (and often) it changes....daily periodic, daily ad-hoc, ETL process at EOD, etc.

You also need to consider what you do with "old" data...archive or delete, etc.

And why this "single table"? What are you planning to do with the incoming information?

MS SQLServer can handle tables with hundreds of millions of records.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-20 : 07:51:04

A mill row per month isn't all that much actually but it can still give you substantial performance problems if you don't handle it properly. What you can do though is to create a "custom partitioning scheme", it's not as good as the built-in enterprise version but it's definitely worth looking at. Check out the sample code I posted in this topic to see how it works: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=132726&whichpage=2

You should also consider archiving old data...removing data that isn't frequently queried can help speed things up considerably.

- Lumbago
http://xkcd.com/327/

irs2k3
Starting Member

22 Posts

Posted - 2009-10-20 : 12:31:35

Thanks for the feedback guys,

@AndrewMurphy,

I have several tables, but the main table will host the news Table. I read feeds from different resource and add them in the news table.

It has the following schema :

[ID]
,[CategoryID]
,[Title]
,[Description]
,[AuthorNewsURL]
,[NewsImageURL]
,[NewsDate]
,[AuthorURL]
,[Author]
,[InternalURL]
,[ImageSize]

I was thinking of using Filtered Index on NewsDate seeing that I will display on my site only the news that are one week old. But of course a user can search for older news.

What do u think?

@Lumbago,

I will check this custom partioning technique.

How archiving works? Data are transfered to other table with the same schema?

Thanks.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-21 : 03:20:08

>> How archiving works? Data are transfered to other table with the same schema?

Yes, what I've done is to create another table with the same schema and then create a union all view (sort of like the custom partitioning) in case I need to query both the active table and the archive:

CREATE VIEW news_all AS
SELECT * FROM news
UNION ALL
SELECT * FROM news_archive

Pretty simple stuff yet it helps a lot on querying the non-archived table.

- Lumbago
http://xkcd.com/327/

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-21 : 06:55:58

are you embedding images into your table?

could you leave the images as raw files and just point to them. far more efficient in terms of accessing. there is an overhead in terms of syncing the records with the images, but there are some benefits. search here for previous discussions on saving images within sqlserver.

irs2k3
Starting Member

22 Posts

Posted - 2009-10-21 : 11:44:27

@Lumbago,

The table will contain only the Http:// link to the image.

Thanks for your input.

@AndrewMurphy,

Kick ass. Thanks for the tip.

irs2k3
Starting Member

22 Posts

Posted - 2009-10-21 : 11:48:17

quote:
Originally posted by irs2k3

@Lumbago,

The table will contain only the Http:// link to the image.

Thanks for your input.

@AndrewMurphy,

Kick ass. Thanks for the tip.

Sorry, the answers should be reversed! I counldn't find the edit button.

irs2k3
Starting Member

22 Posts

Posted - 2009-10-22 : 12:38:36

btw guys,

I am planning on creating one database for each language ( English, French, Arabic, etc...)

Do you think this will be a good practice? The Tables in each DBs will be almost similar.

Thanks

irs2k3
Starting Member

22 Posts

Posted - 2010-01-14 : 08:48:32

^^^
Hi guys,

Creating a Table for each language is a good idea?

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-01-14 : 10:51:53

I'd vote no to that idea.

You can setup database tables to be multi-language....using unicode character sets and datatypes (nchar, nvarchar, etc).

how? - ask others here...especially any members from the scandavian/chinese/japanese countries who use odd character sets.

will you be presenting english data to french users?...ie looking for translations on the fly? otherwise just treat each story as "a story saved in it's own character set"

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 11:03:27

I was going to say that we just have an Attribute column for Language in our "Articles" table.

However, I don't have to deal with "odd" character sets (you're going to get yourself strung-up Andrew!), nor such huge data sets.

The moment you have two databases you have to deal with how you will back them up, and more importantly "restore them", to a consistent moment-in-time following a disaster, otherwise you will have botched referential-integrity to deal with ...

If a single table won't work then then horizontally partitioned tables (one per language) accessed through a VIEW so that the language is transparent I think. (Horizontal-partition view would work as well across multiple databases - or even multiple servers for that matter).

You could even have some tables hosting multiple languages until it becomes a problem, then hive-off a language to its own table and change the horizontal-partitioning view to indicate that its now in its own table - so quite scalable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources