sql-optimization

Name: sql-optimization
Author: totvs

108stars50forks

How to install sql-optimization?

totvs/engpro-advpl-tlpp-skills/sql-optimization

$npx -y skills add totvs/engpro-advpl-tlpp-skills --skill sql-optimization

Installs into the current project.

Summary

Universal SQL performance optimization assistant for comprehensive query tuning, indexing strategies, and database performance analysis across SQL databases (PostgreSQL, SQL Server, Oracle). Provides execution plan analysis, pagination optimization, batch operations, and performa

Files · 1

View on GitHub

SKILL.md

1	# SQL Performance Optimization Assistant
2
3	Expert SQL performance optimization for ${selection} (or entire project if no selection). Focus on universal SQL optimization techniques that work across PostgreSQL, SQL Server, Oracle, and other SQL databases.
4
5	## Core Optimization Areas
6
7	This skill covers query performance analysis, index strategy, subquery optimization, JOIN optimization, pagination, aggregation, query anti-patterns, batch operations, temporary tables, index management, and performance monitoring.
8
9	For all universal SQL optimization patterns with BAD/GOOD code examples, see [sql-optimization-patterns.md](references/sql-optimization-patterns.md).
10
11	## Universal Optimization Checklist
12
13	### Query Structure
14	- [ ] Avoiding SELECT * in production queries
15	- [ ] Using appropriate JOIN types (INNER vs LEFT/RIGHT)
16	- [ ] Filtering early in WHERE clauses
17	- [ ] Using EXISTS instead of IN for subqueries when appropriate
18	- [ ] Avoiding functions in WHERE clauses that prevent index usage
19
20	### Index Strategy
21	- [ ] Creating indexes on frequently queried columns
22	- [ ] Using composite indexes in the right column order
23	- [ ] Avoiding over-indexing (impacts INSERT/UPDATE performance)
24	- [ ] Using covering indexes where beneficial
25	- [ ] Creating partial indexes for specific query patterns
26
27	### Data Types and Schema
28	- [ ] Using appropriate data types for storage efficiency
29	- [ ] Normalizing appropriately (3NF for OLTP, denormalized for OLAP)
30	- [ ] Using constraints to help query optimizer
31	- [ ] Partitioning large tables when appropriate
32
33	### Query Patterns
34	- [ ] Using LIMIT/TOP for result set control
35	- [ ] Implementing efficient pagination strategies
36	- [ ] Using batch operations for bulk data changes
37	- [ ] Avoiding N+1 query problems
38	- [ ] Using prepared statements for repeated queries
39
40	### Performance Testing
41	- [ ] Testing queries with realistic data volumes
42	- [ ] Analyzing query execution plans
43	- [ ] Monitoring query performance over time
44	- [ ] Setting up alerts for slow queries
45	- [ ] Regular index usage analysis
46
47	## 📝 Optimization Methodology
48
49	1. Identify: Use database-specific tools to find slow queries
50	2. Analyze: Examine execution plans and identify bottlenecks
51	3. Optimize: Apply appropriate optimization techniques
52	4. Test: Verify performance improvements
53	5. Monitor: Continuously track performance metrics
54	6. Iterate: Regular performance review and optimization
55
56	Focus on measurable performance improvements and always test optimizations with realistic data volumes and query patterns.
57
58	---
59
60	## Protheus SQL Optimization
61
62	The TOTVS Protheus ERP has specific database access patterns and constraints that require targeted optimization strategies. This covers cross-database compatibility, high-volume table optimization, SIX index alignment, NOLOCK hints, `FWExecStatement`/`TCSqlExec` performance (preferred over legacy `TCQuery`), and Workarea vs. Embedded SQL decision guidance.
63
64	For complete Protheus-specific optimization patterns, code examples, and the high-volume table reference, see [sql-optimization.md](references/sql-optimization.md).
65
66	### Protheus-Specific Optimization Checklist
67
68	- [ ] Queries on SD1/SD2/SE1/SE2/CT2 include branch filter and use indexed columns
69	- [ ] `SELECT *` is not used — only necessary columns are selected
70	- [ ] `D_E_L_E_T_ = ' '` is present on every Protheus table query
71	- [ ] Read-only queries use `%nolock%` hint (cross-DB safe — translates on MSSQL, ignored on PostgreSQL/Oracle)
72	- [ ] No `FWExecStatement`, `TCQuery` or `TCSqlExec` calls inside loops — batch with `:SetIn()` or a single statement
73	- [ ] Temporary aliases opened by `FWExecStatement:OpenAlias()` (or legacy `TCQuery ... New Alias`) are closed after use and `:Destroy()` is called
74	- [ ] `RetSqlName()` is used instead of hardcoded table names (e.g., `SA1010`)
75	- [ ] Complex reports use SQL with proper JOINs instead of nested workarea loops
76	- [ ] `FWExecStatement` is used when user input is part of the query
77	- [ ] Index usage verified against SIX dictionary entries
78	- [ ] No `IIF()` in SQL construction — use `CASE WHEN` or `If/Else/EndIf` in AdvPL logic
79
80	> Refer to [references/sonarqube-rules-reference.md](../references/sonarqube-rules-reference.md) for the complete SonarQube rules reference.

People who liked this also viewed

6 picks

Type

sql-optimization

108stars50forks

View on GitHub

How to install sql-optimization?

totvs/engpro-advpl-tlpp-skills/sql-optimization

$npx -y skills add totvs/engpro-advpl-tlpp-skills --skill sql-optimization

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# SQL Performance Optimization Assistant
2
3	Expert SQL performance optimization for ${selection} (or entire project if no selection). Focus on universal SQL optimization techniques that work across PostgreSQL, SQL Server, Oracle, and other SQL databases.
4
5	## Core Optimization Areas
6
7	This skill covers query performance analysis, index strategy, subquery optimization, JOIN optimization, pagination, aggregation, query anti-patterns, batch operations, temporary tables, index management, and performance monitoring.
8
9	For all universal SQL optimization patterns with BAD/GOOD code examples, see [sql-optimization-patterns.md](references/sql-optimization-patterns.md).
10
11	## Universal Optimization Checklist
12
13	### Query Structure
14	- [ ] Avoiding SELECT * in production queries
15	- [ ] Using appropriate JOIN types (INNER vs LEFT/RIGHT)
16	- [ ] Filtering early in WHERE clauses
17	- [ ] Using EXISTS instead of IN for subqueries when appropriate
18	- [ ] Avoiding functions in WHERE clauses that prevent index usage
19
20	### Index Strategy
21	- [ ] Creating indexes on frequently queried columns
22	- [ ] Using composite indexes in the right column order
23	- [ ] Avoiding over-indexing (impacts INSERT/UPDATE performance)
24	- [ ] Using covering indexes where beneficial
25	- [ ] Creating partial indexes for specific query patterns
26
27	### Data Types and Schema
28	- [ ] Using appropriate data types for storage efficiency
29	- [ ] Normalizing appropriately (3NF for OLTP, denormalized for OLAP)
30	- [ ] Using constraints to help query optimizer
31	- [ ] Partitioning large tables when appropriate
32
33	### Query Patterns
34	- [ ] Using LIMIT/TOP for result set control
35	- [ ] Implementing efficient pagination strategies
36	- [ ] Using batch operations for bulk data changes
37	- [ ] Avoiding N+1 query problems
38	- [ ] Using prepared statements for repeated queries
39
40	### Performance Testing
41	- [ ] Testing queries with realistic data volumes
42	- [ ] Analyzing query execution plans
43	- [ ] Monitoring query performance over time
44	- [ ] Setting up alerts for slow queries
45	- [ ] Regular index usage analysis
46
47	## 📝 Optimization Methodology
48
49	1. Identify: Use database-specific tools to find slow queries
50	2. Analyze: Examine execution plans and identify bottlenecks
51	3. Optimize: Apply appropriate optimization techniques
52	4. Test: Verify performance improvements
53	5. Monitor: Continuously track performance metrics
54	6. Iterate: Regular performance review and optimization
55
56	Focus on measurable performance improvements and always test optimizations with realistic data volumes and query patterns.
57
58	---
59
60	## Protheus SQL Optimization
61
62	The TOTVS Protheus ERP has specific database access patterns and constraints that require targeted optimization strategies. This covers cross-database compatibility, high-volume table optimization, SIX index alignment, NOLOCK hints, `FWExecStatement`/`TCSqlExec` performance (preferred over legacy `TCQuery`), and Workarea vs. Embedded SQL decision guidance.
63
64	For complete Protheus-specific optimization patterns, code examples, and the high-volume table reference, see [sql-optimization.md](references/sql-optimization.md).
65
66	### Protheus-Specific Optimization Checklist
67
68	- [ ] Queries on SD1/SD2/SE1/SE2/CT2 include branch filter and use indexed columns
69	- [ ] `SELECT *` is not used — only necessary columns are selected
70	- [ ] `D_E_L_E_T_ = ' '` is present on every Protheus table query
71	- [ ] Read-only queries use `%nolock%` hint (cross-DB safe — translates on MSSQL, ignored on PostgreSQL/Oracle)
72	- [ ] No `FWExecStatement`, `TCQuery` or `TCSqlExec` calls inside loops — batch with `:SetIn()` or a single statement
73	- [ ] Temporary aliases opened by `FWExecStatement:OpenAlias()` (or legacy `TCQuery ... New Alias`) are closed after use and `:Destroy()` is called
74	- [ ] `RetSqlName()` is used instead of hardcoded table names (e.g., `SA1010`)
75	- [ ] Complex reports use SQL with proper JOINs instead of nested workarea loops
76	- [ ] `FWExecStatement` is used when user input is part of the query
77	- [ ] Index usage verified against SIX dictionary entries
78	- [ ] No `IIF()` in SQL construction — use `CASE WHEN` or `If/Else/EndIf` in AdvPL logic
79
80	> Refer to [references/sonarqube-rules-reference.md](../references/sonarqube-rules-reference.md) for the complete SonarQube rules reference.

People who liked this also viewed

6 picks

Type