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]
),结果是一样的。
1条答案
按热度按时间oxf4rvwz1#
解决方案:问题不在于SQL Server的NestJS配置,而恰恰在于Azure中分配给数据库的资源(DTU或vCores)
接下来我总结一下我们采取的一些行动,但最终,这是一个愚蠢的错误。然而,猜测是有用的,以保持职位。