Opened 7 years ago

Last modified 7 years ago

#877 accepted task

Flagship Bots Query Optimization

Reported by: WingZero Owned by: 24
Priority: normal Milestone: milestone:
Component: Database Version: Latest version from repository
Severity: minor Keywords: database, bot queries, queries, jdbc

Description (last modified by WingZero)

We're seeing a lot of slow query reports (on top of ongoing database issues) and I think this might be solved by optimizing the queries the bots use the most.

The bot files I have in mind are: pubhubalias, staffbotbanc, elim, robohelp, twdop, twdbot.

Matchbot's need a lot more than just query optimization so I won't force anyone to even try to piece through that code.

Basically, look for bad query design that can be optimized individually quite easily. Also, make a note of any bots that are poorly organized and describe how they can decrease the number of queries they do or increase the queries with increased efficiency. For the bots needing code rewrites, I'll handle that but it would help to have a recommendation on what to change.

Short summary of BotAction db methods: basic jdbc are SQLQuery, SQLBackgroundQuery, SQLInsertInto, SQLQueryAndClose. Three are self-explanatory but a BackgroundQuery doesn't return a ResultSet instantaneously; instead it returns it as a handleEvent(SQLResultEvent) where it is handled instead. BGQueries are used when info doesn't need to be acted on right away.
PreparedStatements are great for efficiency but they each require they're own db connection slot. They're best used for queries that happen insanely often.

Change History (3)

comment:1 Changed 7 years ago by WingZero

  • Description modified (diff)

comment:2 Changed 7 years ago by 24

  • Status changed from new to accepted

comment:3 Changed 7 years ago by qan

Thoughts on this:

  • There are a lot of places where a foreground query could be changed to a background query. Requires a little bit of overhauling to work with the data not coming in instantly, but the trade-off -- not being process-locked by a slow DB connection -- means a lot greater functionality.
  • Shifting frequent foreground queries to PreparedStatements.
  • There was one bot that was using something like 10 different PS's. If they each take their own dedicated DB slot, that might not have been the best allocation of PSs. Sounds like 1 or 2 of those PSs actually need to exist, and the rest could be turned to FG or even BG queries.
Note: See TracTickets for help on using tickets.