Documentation

    {{docApp.userSession.userId}}
    {{docApp.userSession.userName}}
    {{docApp.userSession.email}}
    Issuer : {{docApp.userSession.issuerInstance}}
    Expires : {{new Date(docApp.userSession.validUntil).toLocaleDateString()}}

{{docApp.title}}

{{docApp.description}}

How can we help?

{{docApp.searchError}}
{{product.name}}

Searching in {{docApp.searchFilterBySpecificBookTitle}}

{{docApp.searchResultFilteredItems.length}} results for: {{docApp.currentResultsSearchText}} in {{docApp.searchFilterBySpecificBookTitle}}
Search results have been limited. There are a total of {{docApp.searchResponse.totalResultsAvailable}} matches.

You have an odd number of " characters in your search terms - each one needs closing with a matching " character!

{{docApp.libraryHomeViewProduct.title || docApp.libraryHomeViewProduct.id}}

{{docApp.libraryHomeViewProduct.description}}

  1. {{book.title}}

{{group.title || group.id}}

{{group.description}}

  1. {{book.title}}

{{group.title}}

{{book.title}}
More...

Optimize SQL queries for Hornbill reports using AI

This guide provides strategies for Hornbill Administrators to generate and optimize SQL queries using generative AI tools like ChatGPT or Google Gemini. Unoptimized queries can lead to resource exhaustion, slow performance, and the imposition of system limits on your Hornbill instance.

Before you begin

  • SQL Proficiency is key: Custom SQL reporting is an advanced feature. You must have existing SQL knowledge to verify AI-generated output.

  • Consider Data Privacy: Do not share personally identifiable information (PII) with third-party AI tools. Ensure compliance with your organization’s data policies.

  • You may incur performance limits: Hornbill may limit maximum query execution time or resources if queries degrade instance performance. No query should exceed a three-second execution time. If your requirements exceed this, consider the Data Delivery Service (DDS).

Optimization principles for Hornbill SQL

Follow these rules to ensure queries remain efficient and do not cause full table scans.

1. Avoid functions on columns

Do not use functions on columns in a WHERE clause. This prevents the database from using indexes and forces a full table scan.

Incorrect

WHERE date(h_date_logged) > '2026-01-01'

Correct

WHERE h_date_logged > '2026-01-01'

2. Use correct data types

Ensure your query matches the data type of the column. Quoting integers forces the database to ignore indexes.

Incorrect

WHERE h_type = '0'

Correct

WHERE h_type = 0

3. Select only required columns

Only include necessary columns in your SELECT statement. Prioritize columns that are part of an index to reduce disk activity.

4. Replace subqueries with JOINs

Subqueries often execute once for every row in the outer table, creating a loop that degrades performance. Use a JOIN to handle multi-table data retrieval.

5. Use reporting-specific tables

Select tables designed for reporting. Avoid activity and process tables for general reporting.

Use

h_itsm_request_Assignment

Avoid

h_buz_activies
h_bpm_instances

6. Filter data early

Structure your query to limit the result set as early as possible. Join the smallest result sets first to minimize the memory footprint during execution.

7. Exercise caution with Common Table Expressions (CTEs)

While CTEs allow for complex data manipulation, they can significantly increase complexity and decrease performance if not structured correctly. Only use CTEs when a simple optimized query is insufficient.

Prompt engineering for SQL generation

To get accurate results from an AI, you must bypass its default conversational tone and force it to adhere to technical constraints.

Set clear guardrails

Point the AI to the Hornbill Service Manager schema documentation and this guide.

Include a “zero-addition” rule that tells it not to infer any missing detail including tables or columns that are not documented.

Example prompt

Your task is to generate a MySQL query for the Hornbill Service Manager schema, found under subpages from: https://docs.hornbill.com/servicemanager-external-db. 

Do not infer or add schema information that is not documented. 

Follow the guidance in https://docs.hornbill.com/hornbill-how-to-guides/optimize-sql-queries-for-hornbill-reports-using-ai.

Establish a professional persona

Set clear expectations for the AI behavior at the start of the session. This prevents the AI from prioritizing politeness over technical accuracy.

Example prompt

Treat me as a peer. Be concise and blunt. Label your confidence in your answer as high, medium, or low. 

Separate facts from interpretation and explicitly state your assumptions. 

If you are unsure of an answer, state that you do not know rather than guessing.

Use the mentor approach

Instruct the AI to act as a critic to identify potential flaws in your logic or the generated SQL.

Example prompt

Act as a technical mentor. Identify any inefficiencies in this SQL logic, specifically regarding SaaS database performance. 

Explain the flaws and provide an optimized version.

Verify the logic

Ask the AI to explain its output in plain language to ensure the logic matches your intent.

Example prompt

Provide a step-by-step breakdown of the logic used in this JOIN and the specific assumptions made about the data schema.

Example prompts for Hornbill reports

Use these templates to guide the AI toward Hornbill-compatible SQL.

Prompt for a Service Manager report

You are a Principal Software Engineer.

I want a report to show team reassignments for every open request including current and previous team. 

Your task is to generate a MySQL query for the Hornbill Service Manager schema, found under subpages from: https://docs.hornbill.com/servicemanager-external-db. 
Do not infer or add schema information that is not documented. 
Follow the guidance in https://docs.hornbill.com/hornbill-how-to-guides/optimize-sql-queries-for-hornbill-reports-using-ai. 

1. Join h_itsm_requests with h_itsm_request_Assignment.
2. Filter by h_status = 'status.open'.
3. Ensure no functions are used on the h_datelogged column.
4. Use raw integer values for any ID columns.
5. Only select the h_pk_reference and h_summary columns.

Next steps

  • Iterative Optimization: Do not use the first query an AI generates. Use the feedback loop to ask the AI if the query is optimized for a high-volume SaaS environment.

  • Community Review: Post your generated queries on the Hornbill Community forums for feedback from other administrators and Hornbill experts.

  • Learn more: Chapter 8 of the book High-Performance SQL provides further best practices and guidance on SQL query optimization.

  • Version {{docApp.book.version}}
  • Node {{docApp.node}} / {{docApp.build}}
In This Document