Separate table by often changed / not often changed data

optimizationperformance

I have a application which querys different gameservers, and stores for example if the server is online, and how many players are connected. But of course there is also data which is changed rarely.

Currently I have all this in one table.

So my question is: does it make a performance difference if I split this table up, so that often changed data is in one table, and things like the description in another?

(I have to add that I always only select the data I need, so not like Select *). But when I select the data I often have to select the often changed data + the rarely changed data at once.

Best Answer

Yes, it will make a difference. How big a difference will depend both on how many rows the table has and how many columns as well as how frequently the table is read from and updated. Depending on the database, the block size and how many rows are updated at the same time could also be factors.

Keep in mind the overhead you are introducing by splitting the table for how often the data will be queried separately vs how often it will need to be joined and the performance requirements of various reading and writing activities.

Related Solutions

Sql-server – Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view

I might be misunderstanding your question. Are you asking if given the following tables and views:

CREATE TABLE dbo.Foo
(
    [col1_abc] INT
    , [col2_def] INT
)
GO

CREATE TABLE dbo.Bar
(
    [col1_ghi] INT
    , [col2_jkl] INT
)
GO

CREATE VIEW dbo.vwFoo
AS
SELECT
    [col1_abc] AS col1
    , [col2_def] AS col2
FROM
    dbo.Foo 
GO

CREATE VIEW dbo.vwBar
AS
SELECT
    [col1_ghi] AS col1
    , [col2_jki] AS col2
FROM
    dbo.Bar 
GO

Will this:

SELECT  
    f.col1
    , f.col2 
    , b.col2
FROM
    dbo.vwFoo f
INNER JOIN
    dbo.vwBar b
ON  b.col1 = f.col1

perform comparably to:

SELECT
    f.[col1_abc]
    , f.[col2_def] 
    , b.[col2_jki]
FROM
    dbo.Foo b
INNER JOIN
    dbo.Bar b
ON  b.[col1_ghi] = f.[col1_abc]

If that's the question the answer is yes, the performance will be identical.

In SQL2000, the optimiser sometimes struggled with deeply nested complex views joining to each other but not with simple scenarios such as this. I've not encountered the problem so much in 2005+.

Should fields that aren’t needed often be kept in separate tables

I sympathize with this situation because of the SELECT * FROM statements from ORMs. General MySQL (and probably other RDBMs) index theory is to only select the columns that you need, preferably entirely from an index.

That being said, the answer depends on how much traffic you are expecting to get to see a noticeable degradation of performance. It also depends on how much usage you'll get out of the category descriptions.

I wouldn't think storing the descriptions in the same table would reduce performance enough to warrant 2x calls to the database when you DO need to grab the descriptions.

To sum it up, I think you've got a bigger performance problem in using an ORM than in storing the descriptions in the same tag table.

Best Answer

Related Solutions

Sql-server – Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view

Should fields that aren’t needed often be kept in separate tables

Related Question