node.js - 繁琐的超时 PIVOT mssql
问题描述
我有一个令人作呕的类似于 PIVOT 的 SQL 查询（类似于运行 SQL Server 2000 的 PIVOT）。
它使用一个子句选择大约 3500 列（可能更多）WHERE ，其中值在 1000 个元素的数组中。
注意： 查询在 MS SQL Server Management Studio 中运行良好...大约需要 4 分钟才能返回完整结果
SELECT prodIdxTbl.ID AS ID,
prodIdxTbl.ProductID AS orProductCode,
COALESCE(prodIdxTbl.Manufacturer, '') + '-' + COALESCE(prodIdxTbl.Partno, '') AS primaryId,
prodIdxTbl.manufacturerid AS ManufacturerID1534,
prodIdxTbl.Model AS Model1551,
prodIdxTbl.Partno AS PartNo1581,
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='CacheMemory1206' THEN attrsTbl.Data END) AS [CacheMemory1206],
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='ISA1207' THEN attrsTbl.Data END) AS [ISA1207],
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='MonoTonerLife1208' THEN attrsTbl.Data END) AS [MonoTonerLife1208],
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='SpeedMonochrome1209' THEN attrsTbl.Data END) AS [SpeedMonochrome1209],
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='RibbonLife1210' THEN attrsTbl.Data END) AS [RibbonLife1210],
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='DataRate1211' THEN attrsTbl.Data END) AS [DataRate1211],
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='1792x1344GraphicMode1212' THEN attrsTbl.Data END) AS [1792x1344GraphicMode1212],
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='HorizontalFrequency1214' THEN attrsTbl.Data END) AS [HorizontalFrequency1214],
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='ModemSpeeds1216' THEN attrsTbl.Data END) AS [ModemSpeeds1216],
MIN(CASE WHEN (REPLACE(COALESCE(fieldNamesTbl.Text, '') + COALESCE(CONVERT(VARCHAR(10), fieldNamesTbl.ID), ''), ' ', ''))='ScannerResolutionTrueHorizontal1217' THEN attrsTbl.Data END) AS [ScannerResolutionTrueHorizontal1217],
...
MIN(CASE WHEN ('Feature' + (COALESCE(CONVERT(VARCHAR(10), featureIdxTbl.Sequence), '')))='Feature4' THEN featuresTbl.Name END) AS [Feature4],
MIN(CASE WHEN ('Feature' + (COALESCE(CONVERT(VARCHAR(10), featureIdxTbl.Sequence), '')))='Feature5' THEN featuresTbl.Name END) AS [Feature5],
MIN(CASE WHEN ('Feature' + (COALESCE(CONVERT(VARCHAR(10), featureIdxTbl.Sequence), '')))='Feature6' THEN featuresTbl.Name END) AS [Feature6],
MIN(CASE WHEN ('Feature' + (COALESCE(CONVERT(VARCHAR(10), featureIdxTbl.Sequence), '')))='Feature7' THEN featuresTbl.Name END) AS [Feature7],
MIN(CASE WHEN ('Feature' + (COALESCE(CONVERT(VARCHAR(10), featureIdxTbl.Sequence), '')))='Feature8' THEN featuresTbl.Name END) AS [Feature8],
MIN(CASE WHEN ('Feature' + (COALESCE(CONVERT(VARCHAR(10), featureIdxTbl.Sequence), '')))='Feature9' THEN featuresTbl.Name END) AS [Feature9] FROM [Data_Build].[dbo].[ProductIndex] AS prodIdxTbl
INNER JOIN [IT_Data].[dbo].[Attributes] AS attrsTbl
ON prodIdxTbl.ProductID = attrsTbl.ProductID
INNER JOIN [FieldConfig].[dbo].[systemFieldNames] AS fieldNamesTbl
ON attrsTbl.FieldNameID = fieldNamesTbl.ID
LEFT JOIN [IT_Data].[dbo].[IndexFeature] AS featureIdxTbl
ON prodIdxTbl.ProductID = featureIdxTbl.Productid
LEFT JOIN [IT_Data].[dbo].[Features] AS featuresTbl
ON featureIdxTbl.FeatureID = featuresTbl.ID
WHERE CONVERT(DATETIME, prodIdxTbl.lastupdated) BETWEEN '2019-04-01 00:00:00.000' AND GETDATE()
AND prodIdxTbl.ProductID IN ('8200','8202','8203','8205','8210','8211','8212','8218','8219','8255','8273','8276','8277','8278','8287','8299','8313','8327','8417','8484','8499','8595','8596','8597','8600','8601','8602','8604','8608','8611','8612','8621','8625','8627','8629','8632','8634','8635','8636','8637','8638','8639','8640','8671','8677','8678','8679','8681','8731','8732','8739','8780','8783','8788','8791','8796','8798','8801','8804','8809','8833','8837','8852','8853','8855','8859','8872','8879','8880','8886','8889','8963','8965','8966','8968','8974','8975','8989','8995','8997','9000','9001','9002','9003','9004','9005','9006','9007','9009','9018','9027','9028','9059','9060','9061','9062','9063','9064','9065','9066','9067','9068','9080','9081','9082','9084','9085','9086','9087','9088','9089','9107','9108','9181','9189','9190','9191','9208','9210','9211','9212','9213','9214','9215','9216','9217','9218','9222','9223','9224','9225','9226','9227','9229','9230','9238','9250','9255','9256','9259','9260','9261','9262','9263','9267','9268','9269','9270','9313','9338','9339','9341','9342','9353','9373','9389','9394','9494','9496','9497','9499','9501','9519','9520','9523','9525','9527','9590','9592','9593','9600','9622','9623','9624','9634','9635','9638','9639','9640','9665','9688','9689','9690','9694','9695','9696','9703','9704','9705','9706','9707','9708','9744','9745','9747','9749','9751','9752','9753','9754','9786','9787','9788','9789','9790','9791','9793','9799','9802','9813','9822','9836','9837','9847','9872','9922','9923','9924','9963','9972','10004','10065','10066','10067','10068','10069','10070','10071','10072','10073','10074','10075','10076','10078','10079','10080','10083','10085','10087','10089','10118','10120','10122','10142','10146','10161','10183','10208','10213','10214','10222','10228','10233','10239','10242','10244','10248','10379','10563','10595','10603','10604','10606','10641','10643','10644','10648','10650','10673','10693','10697','10712','10713','10719','10720','10723','10731','10735','10738','10741','10742','10748','10749','10757','10760','10895','10899','10903','10904','10928','10929','10930','10931','10932','10933','10934','10935','10936','10937','10938','10939','10940','10941','10942','10943','10944','10945','10946','10947','10948','10949','10950','10951','10952','10953','10954','10955','10956','10957','10958','10959','10960','10961','10962','10963','11043','11053','11054','11135','11183','11185','11188','11189','11190','11191','11193','11194','11195','11196','11197','11198','11199','11200','11201','11202','11203','11204','11205','11206','11207','11208','11209','11210','11211','11212','11213','11214','11215','11216','11217','11218','11219','11220','11221','11222','11223','11224','11225','11226','11227','11228','11229','11230','11231','11232','11233','11234','11235','11236','11237','11238','11239','11240','11241','11242','11243','11244','11245','11246','11255','11256','11257','11275','11334','11774','11793','11794','11795','11796','11797','11798','11799','11800','11801','11802','11803','11804','11805','11806','11807','11808','11809','11810','11811','11812','11813','11814','11815','11816','11817','11818','11819','11820','11821','11822','11823','11824','11825','11826','11885','11886','11887','11888','11889','11890','11891','11892','11893','11894','11895','11896','11897','11898','11931','11932','12001','12002','12003','12004','12005','12006','12007','12008','12043','12073','12074','12075','12076','12077','12078','12079','12080','12081','12082','12083','12084','12085','12086','12087','12088','12089','12090','12091','12092','12093','12094','12095','12096','12097','12098','12099','12100','12101','12102','12103','12104','12105','12106','12107','12108','12109','12110','12111','12112','12113','12114','12115','12116','12117','12118','12119','12120','12121','12122','12123','12124','12125','12126','12127','12128','12129','12130','12131','12132','12133','12134','12135','12136','12137','12138','12139','12140','12141','12142','12143','12144','12145','12146','12147','12148','12149','12150','12151','12152','12153','12154','12155','12156','12157','12158','12159','12160','12161','12162','12163','12164','12165','12166','12167','12168','12169','12170','12171','12172','12173','12174','12175','12176','12177','12178','12179','12180','12181','12182','12183','12184','12185','12186','12187','12188','12189','12190','12191','12192','12193','12194','12195','12196','12197','12198','12199','12200','12201','12202','12203','12204','12205','12206','12207','12208','12209','12210','12211','12212','12214','12215','12216','12217','12218','12219','12220','12221','12222','12223','12224','12225','12226','12227','12228','12229','12230','12231','12232','12233','12234','12235','12236','12237','12238','12239','12240','12241','12242','12243','12244','12245','12246','12247','12248','12249','12250','12251','12252','12253','12254','12255','12256','12257','12258','12259','12260','12261','12262','12263','12264','12265','12266','12267','12268','12269','12270','12271','12272','12274','12275','12276','12277','12278','12279','12280','12281','12282','12283','12284','12285','12286','12287','12288','12289','12290','12291','12292','12293','12294','12295','12296','12297','12298','12299','12300','12301','12302','12303','12304','12305','12306','12307','12308','12309','12310','12311','12312','12313','12314','12315','12316','12317','12318','12319','12320','12321','12322','12323','12324','12326','12327','12328','12329','12330','12331','12332','12333','12334','12347','12348','12349','12350','12351','12353','12354','12355','12356','12358','12359','12360','12361','12362','12363','12364','12365','12366','12367','12368','12369','12370','12371','12372','12373','12374','12375','12376','12377','12378','12379','12380','12381','12382','12383','12384','12385','12386','12388','12391','12392','12393','12394','12401','12462','12463','12464','12483','12489','12491','12492','12493','12494','12496','12497','12498','12499','12500','12501','12502','12523','12524','12526','12528','12529','12530','12531','12532','12533','12534','12535','12536','12537','12538','12539','12540','12541','12542','12543','12544','12545','12546','12547','12549','12550','12551','12552','12553','12554','12555','12556','12557','12558','12559','12560','12561','12564','12570','12577','12578','12579','12580','12647','12662','12665','12710','12770','12775','12885','12909','13110','13111','13124','13128','13133','13137','13138','13139','13140','13141','13142','13143','13144','13145','13147','13149','13156','13157','13158','13780','13781','13782','13799','13817','13818','13819','13820','13821','13822','13823','13827','13829','13831','13834','13835','13836','13839','13841','13844','13847','13849','13850','13851','13852','13853','13854','13855','13856','13858','13885','13888','13889','13890','13891','13892','13893','13894','13895','13896','13897','13898','13899','13900','13901','13902','13909','13910','13911','13912','13914','13924','13925','13936','13937','13938','13955','13956','13957','13958','13959','13960','13961','13962','13963','13964','13965','13966','13967','13968','14261','14262','14263','14264','14270','14272','14279','14280','14281','14329','14330','14331','14332','14333','14334','14335','14336','14337','14338','14339','14340','14341','14342','14343','14345','14346','14347','14348','14349','14350','14351','14352','14353','14354','14355','14356','14357','14358','14359','14360','14361','14362','14363','14364','14365','14366','14367','14368','14369','14370','14371','14372','14373','14374','14375','14376','14377','14378','14379','14380','14385','14386','14387','14388','14389','14884','14889','14939','15015','15030','15045','15062','15093','15096','15098','15103','15148','15172','15173','15174','15177','15178','15179','15189','15191','15231','15232','15244')
GROUP BY prodIdxTbl.ProductID,
prodIdxTbl.ID,
prodIdxTbl.manufacturerid,
COALESCE(prodIdxTbl.Manufacturer, '') + '-' + COALESCE(prodIdxTbl.Partno, ''),
prodIdxTbl.Model,
prodIdxTbl.Partno
可悲的是，我们坚持使用 SQL Server 2000，但如果有人有任何简化此查询的想法，请提及。在过去的几年里，我对 SQL 一直在 NoSQL 领域感到非常生疏。
这...只是它上面另外几千行类似的占位符。
index.js
const _ = require('lodash')
const fs = require('fs')
const sql = require('mssql')
const { getWantedAttributes,
getWantedFeatures,
runQuery
} = require('./lib')
require('dotenv').config()
const ids = fs.readFileSync('./src/data/ids.json', 'utf8')
const parsed = JSON.parse(ids)
const idChunks = _.chunk(parsed, 1000)
async function init() {
const pool = new sql.ConnectionPool({
user: process.env.MSSQL_USERNAME,
password: process.env.MSSQL_PASSWORD,
server: '<server_name>',
parseJSON: true,
requestTimeout: 300000, // 5 mins
pool: {
min: 1,
max: 100,
idleTimeoutMillis: 15000 // 15 secs
}
})
try {
const conn = await pool.connect()
const wantedAttrs = await getWantedAttributes(conn)
const wantedFeatures = await getWantedFeatures(conn)
// Num of elements in query limited to 4096
// Unsure how many Features there will be but 3000 is a safe num I think
const attrsChunks = _.chunk(wantedAttrs, 3000)
for await (const idChunk of idChunks) {
for await (const aChunk of attrsChunks) {
await runQuery({
pool,
chunks: {
attr: aChunk,
id: idChunk
},
wantedFeatures
})
}
}
} catch(err) {
throw err
}
}
lib.js
exports.runQuery = async function({ pool, chunks, wantedFeatures }) {
return new Promise((resolve, reject) => {
const request = pool.request({
stream: true
})
const sqlQuery = buildSqlQuery(chunks.attr, wantedFeatures)
const arr = JSON.stringify(chunks.id).replace(/"/g, "'").replace('[', '(').replace(']', ')')
const queryEnd = `\n\tAND prodIdxTbl.ProductID IN ${arr}
GROUP BY prodIdxTbl.ProductID,
prodIdxTbl.ID,
prodIdxTbl.manufacturerid,
COALESCE(prodIdxTbl.Manufacturer, '') + '-' + COALESCE(prodIdxTbl.Partno, ''),
prodIdxTbl.Model,
prodIdxTbl.Partno`
request.query(`${sqlQuery} ${queryEnd}`)
request.on('recordset', handleColumns)
request.on('row', handleRow)
request.on('error', reject)
request.on('done', resolve)
})
}
我正在使用请求中的属性，正如返回的大型数据集的文档stream所强调的那样。
如果我在 VSCode 中一直调试到查询本身，应用程序会挂起大约 20 秒（我假设正在运行查询）然后吐出这个错误：
C:\Program Files\nodejs\node.exe --inspect-brk=32621 index.js
Debugger listening on ws://127.0.0.1:32621/6c13f5a7-e858-43bc-ac87-aea8c616a1cb
For help, see: https://nodejs.org/en/docs/inspector
Debugger attached.
c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:94
readTextPointerNull(parser, type, textPointerNull => {
^
RangeError: Maximum call stack size exceeded
at textPointerNull (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:94:37)
at readTextPointerNull (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:42:5)
at valueParse (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:94:3)
at next (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\row-token-parser.js:17:5)
at value (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\row-token-parser.js:32:7)
at readNChars (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:412:12)
at dataLength (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:212:20)
at awaitData (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\stream-parser.js:147:7)
at Parser.awaitData (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\stream-parser.js:103:7)
at Parser.readUInt16LE (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\stream-parser.js:144:10)
Waiting for the debugger to discon
RangeError: Maximum call stack size exceeded
at textPointerNull (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:94:37)
at readTextPointerNull (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:42:5)
at valueParse (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:94:3)
at next (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\row-token-parser.js:17:5)
at value (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\row-token-parser.js:32:7)
at readNChars (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:412:12)
at dataLength (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\value-parser.js:212:20)
at awaitData (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\stream-parser.js:147:7)
at Parser.awaitData (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\stream-parser.js:103:7)
at Parser.readUInt16LE (c:\Users\me\Documents\ProductAttributeRefresh\node_modules\tedious\lib\token\stream-parser.js:144:10)
nect...
我最初的想法是，这与查询的绝对规模有关。特别是评估的CASE陈述，但我不确定。
解决这个问题的最佳方法是什么？有没有办法进一步配置 mssql/tedious 来解决这个问题，或者有没有更好的方法来编写这个可怕的 SQL 语句。如果是后者，怎么做？
解决方案
推荐阅读
- python - pip安装pyarrow后在jupyter notebook中导入pyarrow出错
- javascript - Shopify 脚本标签未呈现
- vaadin - Vaadin：再次：构建完全停滞
- javascript - Selenium：unhandledPromiseRejectionWarning：NoSuchElementError：无法定位元素：.alert
- sql - 如何使用 Holistic (SQL) 获得在 7 天内购买超过 3 次的客户
- mysql - 如何使用 docker setup 更改默认 MySQL 用户名和密码？
- python - 如果 client.event 在脚本中，Python Discord Bot，client.command 将停止工作
- asp.net-web-api - 是否有多个 Http Post 的最佳实践 - RPC 样式、自定义 MediaTypeFormatter 或其他
- spartacus-storefront - PDP 中不可更改的属性类别 | 斯巴达克斯电信
- html - 内容正文导致导航栏和标题 HTML/CSS 之间的空间