It should be noted that we are also prepending SET MAX_MEMORY_ROWS 50000; to our initialize db statement which runs when the db is initialized.
On Jan 28, 12:02 pm, edwardsk <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
