NestJS: Raw SQL query execution with mssql. Too Slow. Configuration problems?

user9a7d1 0
Viewed 51

APP in Nestjs that is connected to a SQL Server DB. All the queries are written on the DB side, so the connection between them is with simple raw SQL and the use of mssql package.

Here is the thing: When I run in SSMS, a very small query (let's say <20 records) is executed in milliseconds. (even larger and complex queries or SP have good performance) When I run in the app, with a local database connection, queries start having some delay (let's, say 1 second for the same query) But when I start using the DB on Azure, the same small query takes 3 to 5 seconds (for 20 records

I read some of causes could be related to parameter sniffing, but I don't think it is the case. What I guess, is that my backend is restarting the DB connection every time a new query arrives.

Here is the logic of the app: One centralized CRUD service to be used by the controllers.

At main.ts is the connection:

  async function bootstrap() {
  const app = await NestFactory.create(AppModule);
  const logger = new Logger('Bootstrap', { timestamp: true });
  const configService = app.get(ConfigService);

  
  //Database configuration
  const sqlConfig = {
    user:
      configService.get('DB_USELOCAL') === 'false'
        ? configService.get('DB_USERNAME')
        : configService.get('DB_USERNAME_LOCAL'),
    password:
      configService.get('DB_USELOCAL') === 'false'
        ? configService.get('DB_PASSWORD')
        : configService.get('DB_PASSWORD_LOCAL'),
    server:
      configService.get('DB_USELOCAL') === 'false'
        ? configService.get('DB_SERVER')
        : configService.get('DB_SERVER_LOCAL'),
    database:
      configService.get('DB_USELOCAL') === 'false'
        ? configService.get('DB_DATABASE')
        : configService.get('DB_DATABASE_LOCAL'),
    pool: {
      max: 10,
      min: 0,
      idleTimeoutMillis: 30000,
    },
    requestTimeout: 180000, //3 minutes to wait for a request to the database.
    options: {
      //encrypt: false, // for azure
      encrypt: configService.get('DB_USELOCAL') === 'false' ? true : false,
      trustServerCertificate: false, // change to true for local dev / self-signed certs
    },
  };
  sql.connect(sqlConfig);
  logger.log('App connected to database MSSQL');

  //CORS: Cross-origin resource sharing (CORS) is a mechanism that allows resources to be requested from another domain.
  app.enableCors();

  //App running
  await app.listen(configService.get('PORT') || 3000);
  logger.log(`App running on port ${configService.get('PORT') || 3000}`);
}
bootstrap();

At the CRUD Service the queries requested

import { HttpException, HttpStatus, Injectable, Logger } from '@nestjs/common';
import { fxSQLerrorMsg } from './function/SLQerrorMsg.fx';
import * as sql from 'mssql';
import { FxArrayObjectStr } from './function/arrayObjectStr.fx';
import { FxObjectStr } from './function/objectStr.fx';
import { FindBodyDTO } from './findBody.dto';

@Injectable()
export class CrudService {
  private logger = new Logger('Crud Service', { timestamp: true });

  async find(
    sp: string,
    DB: string,
    body?: FindBodyDTO | null,
    query?: Record<string, any> | null,
    email?: string,
    filter?: string,
  ): Promise<Record<string, any>[]> {
    const method = "'" + 'find' + "'";
    const storeProcedure =
      process.env.SPECIFYDB == 'true'
        ? 'EXECUTE [' + DB + '].[ml_sp].[' + sp + ']'
        : 'EXECUTE [ml_sp].[' + sp + ']';
    const bodyParam = body
      ? "'" + JSON.stringify(body).replace('%20', ' ') + "'"
      : null;
    const queryParam = FxObjectStr(query);
    const emailScript = email ? "'" + email + "'" : null;
    const filterScript = filter ? "'" + filter + "'" : null;
    const spScript =
      storeProcedure +
      ' ' +
      method +
      ', ' +
      bodyParam +
      ', ' +
      queryParam +
      ',' +
      emailScript +
      ',' +
      filterScript;
    this.logger.verbose(spScript);

    try {
      return (await sql.query<Record<string, any>[]>(spScript))
        .recordset as unknown as Record<string, any>[];
    } catch (error) {
      this.logger.error(error);
      throw new HttpException(
        fxSQLerrorMsg(error.message, 'Find'),
        HttpStatus.BAD_REQUEST,
      );
    }
  }

  async post(
    sp: string,
    DB: string,
    body: Record<string, any>[],
    email?: string,
    filter?: string,
  ): Promise<Record<string, string>> {
    const method = "'" + 'post' + "'";
    const storeProcedure =
      process.env.SPECIFYDB == 'true'
        ? 'EXECUTE [' + DB + '].[ml_sp].[' + sp + ']'
        : 'EXECUTE [ml_sp].[' + sp + ']';
    const bodyParam = FxArrayObjectStr(body);
    const queryParam = null;
    const emailScript = email ? "'" + email + "'" : null;
    const filterScript = filter ? "'" + filter + "'" : null;
    const spScript =
      storeProcedure +
      ' ' +
      method +
      ', ' +
      bodyParam +
      ', ' +
      queryParam +
      ', ' +
      emailScript +
      ',' +
      filterScript;
    this.logger.verbose(spScript);
    try {
      return (
        (await sql.query<string>(spScript)).recordset as any[]
      )[0] as Record<string, string>;
    } catch (error) {
      this.logger.error(error);
      throw new HttpException(
        fxSQLerrorMsg(error.message, 'Post'),
        HttpStatus.BAD_REQUEST,
      );
    }
  }

  async updateOne(
    sp: string,
    DB: string,
    body: Record<string, any>[],
    query?: Record<string, any>,
    email?: string,
    filter?: string,
  ): Promise<Record<string, string>> {
    const method = "'" + 'updateOne' + "'";
    const storeProcedure =
      process.env.SPECIFYDB == 'true'
        ? 'EXECUTE [' + DB + '].[ml_sp].[' + sp + ']'
        : 'EXECUTE [ml_sp].[' + sp + ']';
    const bodyParam = FxArrayObjectStr(body);
    const queryParam = FxObjectStr(query);
    const emailScript = email ? "'" + email + "'" : null;
    const filterScript = filter ? "'" + filter + "'" : null;
    const spScript =
      storeProcedure +
      ' ' +
      method +
      ', ' +
      bodyParam +
      ', ' +
      queryParam +
      ', ' +
      emailScript +
      ',' +
      filterScript;
    this.logger.verbose(spScript);
    try {
      return (
        (await sql.query<string>(spScript)).recordset as any[]
      )[0] as Record<string, string>;
    } catch (error) {
      this.logger.error(error);
      throw new HttpException(
        fxSQLerrorMsg(error.message, 'Update'),
        HttpStatus.BAD_REQUEST,
      );
    }
  }

  async updateMany(
    sp: string,
    DB: string,
    body: Record<string, any>[],
    query?: Record<string, any>,
    email?: string,
    filter?: string,
  ): Promise<Record<string, string>> {
    const method = "'" + 'updateMany' + "'";
    const storeProcedure =
      process.env.SPECIFYDB == 'true'
        ? 'EXECUTE [' + DB + '].[ml_sp].[' + sp + ']'
        : 'EXECUTE [ml_sp].[' + sp + ']';
    const bodyParam = FxArrayObjectStr(body);
    const queryParam = FxObjectStr(query);
    const emailScript = email ? "'" + email + "'" : null;
    const filterScript = filter ? "'" + filter + "'" : null;
    const spScript =
      storeProcedure +
      ' ' +
      method +
      ', ' +
      bodyParam +
      ', ' +
      queryParam +
      ', ' +
      emailScript +
      ',' +
      filterScript;
    this.logger.verbose(spScript);
    try {
      return (
        (await sql.query<string>(spScript)).recordset as any[]
      )[0] as Record<string, string>;
    } catch (error) {
      this.logger.error(error);
      throw new HttpException(
        fxSQLerrorMsg(error.message, 'Update'),
        HttpStatus.BAD_REQUEST,
      );
    }
  }

  async deleteOne(
    sp: string,
    DB: string,
    query?: Record<string, any>,
    email?: string,
    filter?: string,
  ): Promise<Record<string, string>> {
    const method = "'" + 'deleteOne' + "'";
    const storeProcedure =
      process.env.SPECIFYDB == 'true'
        ? 'EXECUTE [' + DB + '].[ml_sp].[' + sp + ']'
        : 'EXECUTE [ml_sp].[' + sp + ']';
    const bodyParam = null;
    const queryParam = FxObjectStr(query);
    const emailScript = email ? "'" + email + "'" : null;
    const filterScript = filter ? "'" + filter + "'" : null;
    const spScript =
      storeProcedure +
      ' ' +
      method +
      ', ' +
      bodyParam +
      ', ' +
      queryParam +
      ', ' +
      emailScript +
      ',' +
      filterScript;
    this.logger.verbose(spScript);
    try {
      return (
        (await sql.query<string>(spScript)).recordset as any[]
      )[0] as Record<string, string>;
    } catch (error) {
      this.logger.error(error);
      throw new HttpException(
        fxSQLerrorMsg(error.message, 'Delete'),
        HttpStatus.BAD_REQUEST,
      );
    }
  }

  async deleteMany(
    sp: string,
    DB: string,
    body: Record<string, any>[],
    query: Record<string, any>,
    email?: string,
    filter?: string,
  ): Promise<Record<string, string>> {
    const method = "'" + 'deleteMany' + "'";
    const storeProcedure =
      process.env.SPECIFYDB == 'true'
        ? 'EXECUTE [' + DB + '].[ml_sp].[' + sp + ']'
        : 'EXECUTE [ml_sp].[' + sp + ']';
    const bodyParam = FxArrayObjectStr(body);
    const queryParam = FxObjectStr(query);
    const emailScript = email ? "'" + email + "'" : null;
    const filterScript = filter ? "'" + filter + "'" : null;
    const spScript =
      storeProcedure +
      ' ' +
      method +
      ', ' +
      bodyParam +
      ', ' +
      queryParam +
      ', ' +
      emailScript +
      ',' +
      filterScript;
    this.logger.verbose(spScript);
    try {
      return (
        (await sql.query<string>(spScript)).recordset as any[]
      )[0] as Record<string, string>;
    } catch (error) {
      this.logger.error(error);
      throw new HttpException(
        fxSQLerrorMsg(error.message, 'Delete'),
        HttpStatus.BAD_REQUEST,
      );
    }
  }
}

Additional information: the query that I am testing (what I called a very small query) is:

ALTER VIEW [ml_view].[User2Role] AS 
(SELECT [ml_users].[User2Role].[id] as [id],
[User_user_Aux].[email] as [user],
[PortfolioRole_portfoliorole_Aux].[name] as [portfoliorole],
[ml_users].[User2Role].[editiondate] as [editiondate],
[User_editedbyuser_Aux].[email] as [editedbyuser] 
FROM [ml_users].[User2Role]
LEFT JOIN [ml_users].[User] as [User_user_Aux] ON [User_user_Aux].[id] = [ml_users].[User2Role].[userid] 
LEFT JOIN [ml_setup].[PortfolioRole] as [PortfolioRole_portfoliorole_Aux] ON [PortfolioRole_portfoliorole_Aux].[id] = [ml_users].[User2Role].[portfolioroleid] 
LEFT JOIN [ml_users].[User] as [User_editedbyuser_Aux] ON [User_editedbyuser_Aux].[id] = [ml_users].[User2Role].[editedbyuser])

Actually, it is stored as view, and run through an SP. But we tested executing the view directly (Select * from [viewName]), and the result is the same.

nestjs-config query-optimization sql-server nestjs
1 Answers

Solution: the problem was not in the NestJS configuration to the SQL Server, was precisely in the resources assigned in Azure to the database (DTU or vCores)

I summarize next some of the actions that we undertook, but in the end, it was a silly mistake. However, guess is useful to keep the post.

  • Data sniffing controlling.
  • Rebuild index.
  • Reset statistics.
  • Trying different SQL configurations in Nestjs (typeorm, tedious, etc.)
  • Assigned resources to the (DTU or Vcore model). See in Azure "Compute + Storage" section.
user9a7d10
Related