Flagship Bots Query Optimization
|Reported by:||WingZero||Owned by:||24|
|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.