How to optimize TypeORM performance? Including query optimization, index usage, and batch operations

Performance optimization is a critical aspect of database operations. TypeORM provides multiple optimization strategies and techniques.

Query Optimization

1. Selective Field Loading

typescript
// ❌ Bad: Load all fields
const users = await userRepository.find();

// ✅ Good: Load only needed fields
const users = await userRepository.find({
  select: ['id', 'name', 'email'],
});

// Use QueryBuilder to select fields
const users = await userRepository
  .createQueryBuilder('user')
  .select(['user.id', 'user.name', 'user.email'])
  .getMany();

// Exclude specific fields
const users = await userRepository.find({
  select: {
    id: true,
    name: true,
    email: true,
    password: false, // Exclude password field
  },
});

2. Using Indexes

typescript
import { Entity, PrimaryGeneratedColumn, Column, Index } from 'typeorm';

@Entity()
@Index(['email']) // Single column index
@Index(['name', 'email']) // Composite index
@Index(['createdAt']) // Regular index
@Index(['status'], { unique: true }) // Unique index
@Index(['name'], { fulltext: true }) // Full-text index
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  @Index() // Column-level index
  name: string;

  @Column()
  @Index({ unique: true })
  email: string;

  @Column()
  status: string;

  @Column({ type: 'timestamp' })
  createdAt: Date;

  @Column({ type: 'text' })
  bio: string;
}

// Use indexes in queries
const users = await userRepository.find({
  where: {
    email: 'john@example.com', // Use index
  },
});

// Use composite index
const users = await userRepository.find({
  where: {
    name: 'John',
    email: 'john@example.com',
  },
});

3. Pagination Optimization

typescript
// Use skip and take for pagination
const users = await userRepository.find({
  skip: 0,
  take: 10,
});

// Use QueryBuilder for pagination
const users = await userRepository
  .createQueryBuilder('user')
  .skip(0)
  .take(10)
  .getMany();

// Use cursor-based pagination (better performance)
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.id > :cursor', { cursor: lastId })
  .orderBy('user.id', 'ASC')
  .take(10)
  .getMany();

// Use LIMIT and OFFSET
const users = await userRepository
  .createQueryBuilder('user')
  .limit(10)
  .offset(0)
  .getMany();

Relation Optimization

1. Lazy Loading vs Eager Loading

typescript
// ❌ Bad: N+1 query problem
const users = await userRepository.find();
for (const user of users) {
  const posts = await user.posts; // Query database every time
}

// ✅ Good: Use eager loading
const users = await userRepository.find({
  relations: ['posts'],
});

// Use QueryBuilder for eager loading
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .getMany();

// Selective relation loading
const users = await userRepository.find({
  relations: {
    posts: true,
    profile: false, // Don't load profile
  },
});

// Use join to load relations
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoin('user.posts', 'post')
  .addSelect(['post.id', 'post.title'])
  .getMany();

2. Relation Query Optimization

typescript
// Use innerJoin instead of leftJoin (if sure there's related data)
const users = await userRepository
  .createQueryBuilder('user')
  .innerJoin('user.posts', 'post')
  .where('post.published = :published', { published: true })
  .getMany();

// Use join conditions for filtering
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoin('user.posts', 'post', 'post.published = :published', { published: true })
  .getMany();

// Use subqueries for optimization
const users = await userRepository
  .createQueryBuilder('user')
  .where((qb) => {
    const subQuery = qb
      .subQuery()
      .select('post.userId')
      .from(Post, 'post')
      .where('post.published = :published', { published: true })
      .getQuery();
    return 'user.id IN ' + subQuery;
  })
  .setParameter('published', true)
  .getMany();

Batch Operation Optimization

1. Batch Insert

typescript
// ❌ Bad: Loop insert
for (const userData of usersData) {
  const user = userRepository.create(userData);
  await userRepository.save(user);
}

// ✅ Good: Batch insert
const users = usersData.map(userData => userRepository.create(userData));
await userRepository.save(users);

// Use QueryBuilder for batch insert
await userRepository
  .createQueryBuilder()
  .insert()
  .into(User)
  .values(usersData)
  .execute();

// Use bulk insert
await userRepository.insert(usersData);

2. Batch Update

typescript
// ❌ Bad: Loop update
for (const user of users) {
  await userRepository.update(user.id, { status: 'active' });
}

// ✅ Good: Batch update
await userRepository.update(
  { status: 'inactive' },
  { status: 'active' }
);

// Use QueryBuilder for batch update
await userRepository
  .createQueryBuilder()
  .update(User)
  .set({ status: 'active' })
  .where('status = :status', { status: 'inactive' })
  .execute();

// Use CASE WHEN for batch update
await userRepository
  .createQueryBuilder()
  .update(User)
  .set({
    status: () => 'CASE WHEN age > 18 THEN "active" ELSE "inactive" END',
  })
  .execute();

3. Batch Delete

typescript
// ❌ Bad: Loop delete
for (const user of users) {
  await userRepository.delete(user.id);
}

// ✅ Good: Batch delete
await userRepository.delete({ status: 'deleted' });

// Use QueryBuilder for batch delete
await userRepository
  .createQueryBuilder()
  .delete()
  .from(User)
  .where('status = :status', { status: 'deleted' })
  .execute();

// Use soft delete for batch delete
await userRepository.softDelete({ status: 'deleted' });

Transaction Optimization

1. Transaction Scope Control

typescript
// ❌ Bad: Large transaction
await dataSource.transaction(async (manager) => {
  // Execute many operations
  for (let i = 0; i < 1000; i++) {
    await manager.save(User, { name: `User ${i}` });
  }
});

// ✅ Good: Batch processing
const batchSize = 100;
for (let i = 0; i < 1000; i += batchSize) {
  await dataSource.transaction(async (manager) => {
    const users = [];
    for (let j = i; j < i + batchSize && j < 1000; j++) {
      users.push({ name: `User ${j}` });
    }
    await manager.save(User, users);
  });
}

2. Using QueryRunner for Optimization

typescript
// Use QueryRunner for better performance control
const queryRunner = dataSource.createQueryRunner();

try {
  await queryRunner.connect();
  await queryRunner.startTransaction();

  // Execute multiple operations
  await queryRunner.manager.save(User, user1);
  await queryRunner.manager.save(User, user2);

  await queryRunner.commitTransaction();
} catch (error) {
  await queryRunner.rollbackTransaction();
  throw error;
} finally {
  await queryRunner.release();
}

Caching Optimization

1. Query Caching

typescript
// Enable query caching
const users = await userRepository.find({
  cache: true,
});

// Custom cache duration
const users = await userRepository.find({
  cache: {
    id: 'users_list',
    milliseconds: 60000, // 60 seconds
  },
});

// Use QueryBuilder for caching
const users = await userRepository
  .createQueryBuilder('user')
  .cache(true)
  .getMany();

// Clear cache
await dataSource.queryResultCache?.remove(['users_list']);

2. Application-Level Caching

typescript
// Use memory cache
const cache = new Map<string, any>();

async function getUserWithCache(userId: number) {
  const cacheKey = `user:${userId}`;
  
  // Try to get from cache
  if (cache.has(cacheKey)) {
    return cache.get(cacheKey);
  }
  
  // Get from database
  const user = await userRepository.findOne({
    where: { id: userId },
  });
  
  // Store in cache
  cache.set(cacheKey, user);
  
  return user;
}

Database Connection Optimization

1. Connection Pool Configuration

typescript
const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: true,
  
  // Connection pool configuration
  extra: {
    connectionLimit: 20, // Connection pool size
    acquireTimeout: 60000, // Connection acquisition timeout
    timeout: 60000, // Query timeout
    waitForConnections: true, // Wait for available connections
    queueLimit: 100, // Queue limit
  },
});

2. Read-Write Separation

typescript
// Primary database (write operations)
const writeDataSource = new DataSource({
  type: 'mysql',
  name: 'write',
  host: 'write-db.example.com',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

// Secondary database (read operations)
const readDataSource = new DataSource({
  type: 'mysql',
  name: 'read',
  host: 'read-db.example.com',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: true,
});

// Use read-write separation
class UserRepository {
  async findById(id: number) {
    // Read from secondary database
    const repository = readDataSource.getRepository(User);
    return await repository.findOne({ where: { id } });
  }

  async save(user: User) {
    // Write to primary database
    const repository = writeDataSource.getRepository(User);
    return await repository.save(user);
  }
}

Monitoring and Tuning

1. Query Log Analysis

typescript
// Enable query logging
const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logging: ['query', 'error', 'slow'], // Log queries, errors, and slow queries
  maxQueryExecutionTime: 1000, // Slow query threshold (milliseconds)
});

// Custom logging
const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  synchronize: false,
  logger: new QueryLogger(),
});

class QueryLogger implements Logger {
  logQuery(query: string, parameters?: any[]) {
    console.log('Query:', query);
    console.log('Parameters:', parameters);
  }

  logQueryError(error: string, query: string, parameters?: any[]) {
    console.error('Query Error:', error);
    console.error('Query:', query);
  }

  logQuerySlow(time: number, query: string, parameters?: any[]) {
    console.warn('Slow Query:', query);
    console.warn('Execution Time:', time);
  }
}

2. Performance Monitoring

typescript
// Query performance monitoring
class QueryPerformanceMonitor {
  private queries: Map<string, number[]> = new Map();

  trackQuery(query: string, executionTime: number) {
    if (!this.queries.has(query)) {
      this.queries.set(query, []);
    }
    this.queries.get(query)!.push(executionTime);
  }

  getAverageExecutionTime(query: string): number {
    const times = this.queries.get(query);
    if (!times || times.length === 0) {
      return 0;
    }
    const sum = times.reduce((a, b) => a + b, 0);
    return sum / times.length;
  }

  getSlowQueries(threshold: number = 1000): string[] {
    const slowQueries: string[] = [];
    
    for (const [query, times] of this.queries.entries()) {
      const avgTime = this.getAverageExecutionTime(query);
      if (avgTime > threshold) {
        slowQueries.push(query);
      }
    }
    
    return slowQueries;
  }
}

// Use performance monitoring
const monitor = new QueryPerformanceMonitor();

const startTime = Date.now();
const users = await userRepository.find();
const executionTime = Date.now() - startTime;

monitor.trackQuery('SELECT * FROM user', executionTime);

Best Practices

1. Avoid N+1 Queries

typescript
// ❌ Bad: N+1 queries
const users = await userRepository.find();
for (const user of users) {
  const posts = await user.posts; // N+1 queries
}

// ✅ Good: Use eager loading
const users = await userRepository.find({
  relations: ['posts'],
});

// ✅ Good: Use join
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .getMany();

2. Use Appropriate Data Types

typescript
// ✅ Good: Use appropriate data types
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', length: 255 }) // Limit length
  name: string;

  @Column({ type: 'int' }) // Use integer
  age: number;

  @Column({ type: 'decimal', precision: 10, scale: 2 }) // Use precise decimal
  balance: number;

  @Column({ type: 'boolean' }) // Use boolean
  isActive: boolean;

  @Column({ type: 'enum', enum: ['active', 'inactive'] }) // Use enum
  status: string;

  @Column({ type: 'json' }) // Use JSON
  metadata: any;
}

3. Regular Maintenance

typescript
// Regularly clean up data
async function cleanupOldData() {
  const thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

  await userRepository.delete({
    createdAt: LessThan(thirtyDaysAgo),
    status: 'deleted',
  });
}

// Regularly rebuild indexes
async function rebuildIndexes() {
  await dataSource.query('ANALYZE TABLE user');
  await dataSource.query('OPTIMIZE TABLE user');
}

// Regularly update statistics
async function updateStatistics() {
  await dataSource.query('ANALYZE TABLE user');
}

TypeORM performance optimization requires comprehensive consideration of queries, relations, batch operations, transactions, and other aspects. Proper use of optimization techniques can significantly improve application performance.