30 Dec, 2012, Kastion wrote in the 21st comment:
Votes: 0
quixadhal said:
That's why I suggested changing it from doing an update to doing an insert for each chunk of new data. That way you can get the most recent stats by the largest timestamp, and can see earlier snapshots by selecting all with the same userid.

Bojack said:
What quix suggested (inserting new records rather than updating) can create bloat very quickly if not done right. For one, it's possible that it will add a new record for each player for each zone every 5 min. And then you'll be keeping potentially very old records in a table that will most likely be queried for recent periods only (unless you add a smart procedure to "summarize" old records).


I would go the route quix described however I would create a duplicate table. Don't have your commands work off the live data that is updated every 5 minutes. Create a duplicate table and create a job to insert a new record by zone at midnight every night then use the timestamps in this new table to grab the recordsets you're looking for. If yearly is your largest denominator then create another duplicate table for the purpose of archiving old data and another job that runs in the middle of the night to move records with timestamps greater than a year to this table. That way you're working off a table that never contains more than a years worth of data with your commands and you're only inserting "Zone * Players" records once a day at the end of every day. If you want players to be able to look at previous years and not just the current year then you can modify your yearly command to work off the archive table when needed.

I use this method when dealing with large amounts of data in tables that will continue to grow larger and larger but for the current purpose you might just want to make the single table that gets inserted into once a night.
30 Dec, 2012, quixadhal wrote in the 22nd comment:
Votes: 0
Another thing you can do, regardless of keeping things in one big table and trusting the RDBMS to use indexes properly, or breaking it into smaller chunks, is to create views to access data by commonly used metrics.

I track intermud traffic and log all messages to a single table. You can easily query it however you like, but for a few common things I have views such as:

CREATE VIEW today AS
SELECT to_char(chanlogs.msg_date, 'MM/DD HH24:MI'::text) AS "time", chanlogs.channel, (chanlogs.speaker || '@'::text) || chanlogs.mud AS speaker, chanlogs.message
FROM chanlogs
WHERE chanlogs.msg_date >= (now() - '1 day'::interval)
ORDER BY chanlogs.msg_date;


which gives me a nice pre-formatted view of the last 24 hours worth of log data, which can further be filtered by where clauses in the select statement you use for the view.

select * from today where channel = 'intergossip';

Another fun one:

CREATE VIEW words AS
SELECT foo.speaker, sum(foo.wordcount) AS words
FROM ( SELECT chanlogs.speaker, fn_wordcount(chanlogs.message) AS wordcount
FROM chanlogs
WHERE NOT chanlogs.is_bot AND chanlogs.msg_date >= (now() - '7 days'::interval)
GROUP BY chanlogs.speaker, chanlogs.message) foo
GROUP BY foo.speaker
ORDER BY sum(foo.wordcount) DESC;


Which gives me a list of who has spoken the most in the last week, where "most" means the most words…. as defined by the stored procedture fn_wordcount:

my $text = $_[0];
return undef if !defined $text;
my @words = split /\s+/, $text;
return undef if !defined @words;
return scalar(@words);
[code]

Which in this case is simple whitespace delimited things in the message body… but you could be more fancy if you liked.

The idea is, you can let the database do some of the work for you, and provide a seamless view that makes it look to the code like it's one big table, even if it's actually joining multiple things together.
31 Dec, 2012, Bojack wrote in the 23rd comment:
Votes: 0
Thanks guys for your help, because of your idea's it helped me write this:
case CS_PlayerStatsRequest<Zone>.ChartType.ScoreDaily:
{
//Get the top100 stats sorted by points
//Note: This is if a player doesnt use args aka current day stats
var daily = (from dt in db.statsDailies
where dt.zone1 == zone._zone
orderby dt.assistPoints + dt.bonusPoints + dt.killPoints descending
select dt).Take(100);

//Are they requesting a specific date?
DateTime now = DateTime.Now;
if (pkt.options != "")
{
//Player wants to see yesterday's date
if (pkt.options.Equals("-1"))
{
now = now.AddDays(-1);
daily = (from dt in db.statsDailies
where dt.zone1 == zone._zone && dt.date >= now
orderby dt.assistPoints + dt.bonusPoints + dt.killPoints descending
select dt).Take(100);
}
else //Specific date
{
string[] args = pkt.options.Split('-');
string final = string.Join("/", args);
try
{
now = DateTime.Parse(final, System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat);
}
catch (FormatException)
{
//Wrong format, use yesterday as default
now = (now.AddDays(-1));
}
DateTime add = now.AddDays(1);

daily = (from dt in db.statsDailies
where dt.zone1 == zone._zone && dt.date >= now && dt.date < add
orderby dt.assistPoints + dt.bonusPoints + dt.killPoints descending
select dt).Take(100);
}
}

MemoryStream stream = new MemoryStream();
try
{
foreach (Data.DB.statsDaily day in daily)
{
BinaryWriter bw = new BinaryWriter(stream);
bw.Write(day.players[0].alias1.name.ToCharArray());
bw.Write((byte)0);

Data.DB.squad squad = day.players[0].squad1;
string squadname = "";
if (squad != null)
squadname = squad.name;

bw.Write(squadname.ToCharArray());
bw.Write((byte)0);

bw.Write((short)2);
bw.Write(day.vehicleDeaths);
bw.Write(day.vehicleKills);
bw.Write(day.killPoints);
bw.Write(day.deathPoints);
bw.Write(day.assistPoints);
bw.Write(day.bonusPoints);
bw.Write(day.kills);
bw.Write(day.deaths);
bw.Write((int)0);
bw.Write(day.playSeconds);
}
}
catch (Exception e)
{
Log.write(TLog.Warning, "WriteElementDaily " + e);
}

SC_PlayerStatsResponse<Zone> response = new SC_PlayerStatsResponse<Zone>();

response.player = pkt.player;
response.type = CS_PlayerStatsRequest<Zone>.ChartType.ScoreDaily;
response.columns = "Top100 Daily Score,Name,Squad";
response.data = stream.ToArray();

zone._client.sendReliable(response, 1);
}
break;

The rest is rinse, lather, repeat.
20.0/23