We have two tables.
We are inserting data (around 100 rows, every second) into the "2nd"
table... and inserting data less frequently into the "1st" table.
We are running "Analyze;" every 10 minutes to keep the indexes "up to
date".
We have a select query that runs on demand by a user... the system can
be running for hours without the select query being run, even though
data is continually being added.
This query pulls data out to populate all the points per line on a
line graph (If a graph has four lines - the query will be run four
times)
Our issue:
After inserting data for about 3 hrs (about 100 rows per second), the
four queries (1 chart with 4 lines) took about 9 seconds TOTAL from
start to finish
A few moments later we ran the exact same process again to rerun the
four queries (prepared statements with the same parameters) and it
took less than 1 second.
Please note that I am 100% sure that "Analyze;" was successfully
running every 10 minutes...
Our table structure is here:
//Table 1 (config updated less frequently)
CREATE TABLE IF NOT EXISTS PAST_SCOREBOARD_CHANNEL_CONFIG(
ID IDENTITY PRIMARY KEY,
AGENT_ID VARCHAR(50),
CHANNEL_ID VARCHAR(50),
LABEL VARCHAR,
TYPE VARCHAR,
UNITS VARCHAR);
//table 1 indexes
CREATE INDEX IF NOT EXISTS indxChannelConfig_AgentId ON
PAST_SCOREBOARD_CHANNEL_CONFIG(AGENT_ID);
CREATE INDEX IF NOT EXISTS indxChannelConfig_ChannelId ON
PAST_SCOREBOARD_CHANNEL_CONFIG(CHANNEL_ID);
//Table 2 - (Actual data table - apprx 100 rows added every second);
CREATE TABLE IF NOT EXISTS PAST_SCOREBOARD_CHANNEL_DATA (
ID IDENTITY PRIMARY KEY,
CHANNEL_CONFIG_ID VARCHAR,
VALUE DOUBLE,
ELAPSED_TIME_IN_SECS FLOAT);
//table 2 indexes
CREATE INDEX IF NOT EXISTS indxChannelData_ChannelConfigId ON
PAST_SCOREBOARD_CHANNEL_DATA(CHANNEL_CONFIG_ID);
CREATE INDEX IF NOT EXISTS indxChannelData_ELAPSED_TIME ON
PAST_SCOREBOARD_CHANNEL_DATA(ELAPSED_TIME_IN_SECS);
Query (java Prepared Statement):
select TYPE, UNITS, VALUE, ELAPSED_TIME_IN_SECS
FROM PAST_SCOREBOARD_CHANNEL_CONFIG tConfig
inner join PAST_SCOREBOARD_CHANNEL_DATA tData
on tConfig.id=tData.CHANNEL_CONFIG_ID
WHERE tConfig.AGENT_ID=? AND tConfig.CHANNEL_ID=?;
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
