NestJS：使用SQL Server执行原始SQL查询太慢,配置问题？

Nestjs中连接到SQL Server数据库的APP。所有查询都是在数据库端编写的，因此它们之间的连接是使用简单的原始SQL和mssql包。
事情是这样的：当我在SSMS中运行时，一个非常小的查询（假设返回〈20条记录）在毫秒内执行（甚至更大和更复杂的查询或存储过程也有很好的性能）。
当我使用本地数据库连接在应用程序中运行时，查询开始有一些延迟（假设同一个查询延迟1秒）。
但是当我开始使用Azure上的数据库时，同样的小查询需要3到5秒钟（对于20条记录）。
我读到一些原因可能与参数嗅探有关，但我不认为这是事实。
我猜，我的后端是重新启动数据库连接，每次一个新的查询到达。
以下是这款应用的逻辑：一个由控制器使用的集中式CRUD服务。
在main.ts中是连接：

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 SQL Server database');

  // 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();

在CRUD服务中，请求的查询

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 storedProcedure =
      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 =
      storedProcedure +
      ' ' +
      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 storedProcedure =
      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 =
      storedProcedure +
      ' ' +
      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 storedProcedure =
      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 =
      storedProcedure +
      ' ' +
      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 storedProcedure =
      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 =
      storedProcedure +
      ' ' +
      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 storedProcedure =
      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 =
      storedProcedure +
      ' ' +
      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 storedProcedure =
      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 =
      storedProcedure +
      ' ' +
      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,
      );
    }
  }
}

补充信息：我正在测试的查询（我称之为非常小的查询）是：

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])

实际上，它被存储为视图，并通过一个存储过程运行。但是我们测试了直接执行视图（Select * from [viewName]），结果是一样的。

NestJS：使用SQL Server执行原始SQL查询太慢,配置问题？

1条答案

相关问题

热门标签

最新问答