Query optimization

import { QueryOptimizer } from 'ts-prorm-orm';

const optimizer = new QueryOptimizer(prorm.getDialectInstance(), {
  enableExplain: true,
  slowQueryOptions: { threshold: 250 },
});

import { QueryOptimizer } from 'ts-prorm-orm';

const optimizer = new QueryOptimizer(prorm.getDialectInstance(), { enableExplain: true });
const sql = 'SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC';

const { plan, recommendations } = await optimizer.analyzeQuery(sql);
console.log(plan.parsedPlan);
// [ { operation: 'SCAN', table: 'orders', details: { detail: 'SCAN orders' } },
//   { operation: 'OPERATION', details: { detail: 'USE TEMP B-TREE FOR ORDER BY' } } ]
for (const r of recommendations) console.log(r.severity, r.type, r.message, r.table);
// HIGH INDEX Full table scan detected. orders

const after = await optimizer.analyzeQuery(sql);
console.log(after.plan.plan.rows[0].detail);
// SEARCH orders USING INDEX idx_orders_customer_created (customer_id=?)
console.log(after.recommendations.length); // 0

const plan = await optimizer.explainQuery(
  'SELECT * FROM users WHERE email = \'a@example.com\'',
  { analyze: true },
);

const { plan: p, recommendations } = await optimizer.analyzeQuery(sql);
for (const r of recommendations) {
  console.log(r.severity, r.type, r.message); // HIGH INDEX  "Full table scan detected."
}

import { FORCE_INDEX } from 'ts-prorm-orm';

const sql = optimizer.applyHints('SELECT * FROM users WHERE email = ?', [
  FORCE_INDEX('idx_users_email', 'users'),
]);

import { QueryHintManager, FORCE_INDEX, JOIN_FIXED_ORDER, MERGE_HASH, HintType } from 'ts-prorm-orm';

const hints = new QueryHintManager(prorm.getDialectInstance());
const sql = hints.applyHints(
  'SELECT o.id, c.email FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = ?',
  [
    FORCE_INDEX('idx_orders_status', 'orders'),
    hints.maxExecutionTime(5),
    JOIN_FIXED_ORDER,
    { type: HintType.NESTED_LOOP },
    MERGE_HASH,
    { type: HintType.NO_SEQ_SCAN, tableName: 'orders' },
  ],
);

import { createSlowQueryLogger } from 'ts-prorm-orm';

const slow = createSlowQueryLogger({
  threshold: 500,          // ms; below this nothing is recorded
  logParams: true,
  maxEntries: 1000,        // ring buffer
  alertThreshold: 10,      // 10 slow queries…
  alertWindow: 60_000,     // …within a minute fires 'alert'
});

slow.on('slowQuery', (entry) => metrics.increment('db.slow', { sql: entry.sql }));
slow.on('alert', (entries) => pageOncall(entries));

const started = Date.now();
const rows = await prorm.query(sql);
slow.logQuery({ sql, duration: Date.now() - started, dialect: prorm.getDialect() });

import { optimizeBatchInsert } from 'ts-prorm-orm';

const result = await optimizeBatchInsert(dialect, 'users', records, {
  chunkSize: 1000,
  skipDuplicates: true,
  onProgress: ({ processed, total }) => console.log(`${processed}/${total}`),
});
result.success.length; result.failed.length; result.batches; result.duration;

import { BatchQueryOptimizer } from 'ts-prorm-orm';

const batch = new BatchQueryOptimizer(prorm.getDialectInstance());
await batch.optimizeBatchInsert('orders', [
  { customer_id: 7, total: 120 },
  { customer_id: 9, total: 45.5 },
]);
await batch.optimizeBatchDelete('orders', [3, 4, 5]);

import { FORCE_INDEX } from 'ts-prorm-orm';

const { result, fromCache, duration } = await optimizer.executeQuery(sql, {
  useCache: true,
  usePreparedStatement: true,
  hint: [FORCE_INDEX('idx_users_email', 'users')],
});

const first = await optimizer.executeQuery('SELECT COUNT(*) AS n FROM orders', { useCache: true });
const again = await optimizer.executeQuery('SELECT COUNT(*) AS n FROM orders', { useCache: true });
first.fromCache; // false
again.fromCache; // true
optimizer.invalidateTableCache('orders'); // 1 — the entry mentioning "orders"