Thomas, I just upgraded from the release on 9-26-08 to the version released on 1-24-09...
I'm not seeing the issues anymore... I found another similar post where you thought the delay was potentially a db cache issue - Do any significant performance/cache changes between those releases jump out at you which could have solved this issue? On Jan 28, 12:41 pm, edwardsk <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
