DEV Community

Rohith Kunnath
Rohith Kunnath

Posted on

2

Database Query Statistics

I am pretty sure most of us developers will face some database performance problem every day.
Ninety percent of the time this could be due to the missing indexes.

Otherwise, the query does not use the newly created index and instead scans the whole table.

Most of the time we will not notice this since the application works perfectly fine for the first few weeks/months and performance gets worse slowly every day as the table grows.

I myself would prefer something automated which will prompt or notify in-case queries take a long time than expected. This is when I thought of making use of the hibernate-statistics setting and turning on this log. But this is doing a lot of things in the background and making use of a lot of non-week references which also takes a fair amount of JVM memory.

I thought of implementing a small library that will help me to customize to enable what we need and not store any other information.

My motivation was

  1. Easy customizations using system properties.
  2. Injectable even on non-hibernate or non-spring projects by little tweaks.
  3. Statistics reporting will help the data to be sent to Prometheus or similar databases and create alerts to notify whether there are queries that need to be improved.
  4. Developer need logs for SQL queries with the time taken to execute.
  5. Developers can create alerts based on the Splunk (logging system) or Prometheus if there are queries taking more time or fetching rows more than expected.

Library link here

Image of AssemblyAI tool

Challenge Submission: SpeechCraft - AI-Powered Speech Analysis for Better Communication

SpeechCraft is an advanced real-time speech analytics platform that transforms spoken words into actionable insights. Using cutting-edge AI technology from AssemblyAI, it provides instant transcription while analyzing multiple dimensions of speech performance.

Read full post

Top comments (0)

πŸ‘‹ Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay