Number of queries on a list page.

For general questions and discussions specific to the AbleCommerce GOLD ASP.Net shopping cart software.
Post Reply
matty
Ensign (ENS)
Ensign (ENS)
Posts: 10
Joined: Wed Jun 07, 2017 10:58 am

Number of queries on a list page.

Post by matty » Wed Jun 07, 2017 11:25 am

Hi... new here so I'll try to make sure to include as much info as I can.

We use Able Commerce Gold R10 (build 8302) and I am looking for table improvements as we are getting HUGE slowdown behavior on our list page. I am doing my best to profile our DB and figure out what is causing the hits and it's somewhat alarming what I am finding. On a standard category page I am seeing almost 500 queries to render the page.

Here is one of the top calls that is executed with a new id each time until the page paints:

Code: Select all

exec sp_executesql N' SELECT COUNT(*) FROM   (   SELECT CN.* FROM ac_CatalogNodes AS CN INNER JOIN ac_Categories AS C   ON CN.CatalogNodeId = C.CategoryId AND CN.CatalogNodeTypeId = 0  WHERE CN.CategoryId = @p0 AND C.VisibilityId = @p1  UNION ALL  SELECT CN.* FROM ac_CatalogNodes AS CN INNER JOIN ac_Products AS P  ON CN.CatalogNodeId = P.ProductId AND CN.CatalogNodeTypeId = 1  WHERE CN.CategoryId = @p0  AND P.VisibilityId = @p1  UNION ALL  SELECT CN.* FROM ac_CatalogNodes AS CN INNER JOIN ac_Webpages AS W  ON CN.CatalogNodeId = W.WebpageId AND CN.CatalogNodeTypeId = 2   WHERE CN.CategoryId = @p0  AND W.VisibilityId = @p1  UNION ALL  SELECT CN.* FROM ac_CatalogNodes AS CN INNER JOIN ac_Links AS L   ON CN.CatalogNodeId = L.LinkId AND CN.CatalogNodeTypeId = 3  WHERE CN.CategoryId = @p0 AND L.VisibilityId = @p1  ) AS CN1 ',N'@p0 int,@p1 tinyint',@p0=194,@p1=0


I am also seeing some automation SQL queries running as well. Where in code are these being scheduled? Over the course of 60 seconds I am seeing 1200 queries hit the DB. Some of these are doing over 42k in reads. For example: this one that cleans up the table for page view activity which is doing a batch delete to the best of my knowledge. This is an odd execution that required a lot of back and forth from the web server to the DB server for data:

Code: Select all

exec sp_executesql N'SELECT TOP (@p0)  this_.PageViewId as y0_ FROM ac_PageViews this_ WHERE this_.StoreId = @p1 and this_.ActivityDate < @p2 ORDER BY this_.ActivityDate asc',N'@p0 int,@p1 int,@p2 datetime',@p0=10000,@p1=1,@p2='2017-05-31 07:00:00'
Here's the delete that is executed:

Code: Select all

exec sp_executesql N'delete from ac_PageViews where PageViewId in (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100, @p101, @p102, @p103, @p104, @p105, @p106, @p107, @p108, @p109, @p110, @p111, @p112, @p113, @p114, @p115, @p116, @p117, @p118, @p119, @p120, @p121, @p122, @p123, @p124, @p125, @p126, @p127, @p128, @p129, @p130, @p131, @p132, @p133, @p134, @p135, @p136, @p137, @p138, @p139, @p140, @p141, @p142, @p143, @p144, @p145, @p146, @p147, @p148, @p149, @p150, @p151, @p152, @p153, @p154, @p155, @p156, @p157, @p158, @p159, @p160, @p161, @p162, @p163, @p164, @p165, @p166, @p167, @p168, @p169, @p170, @p171, @p172, @p173, @p174, @p175, @p176, @p177, @p178, @p179, @p180, @p181, @p182, @p183, @p184, @p185, @p186, @p187, @p188, @p189, @p190, @p191, @p192, @p193, @p194, @p195, @p196, @p197, @p198, @p199, @p200, @p201, @p202, @p203, @p204, @p205, @p206, @p207, @p208, @p209, @p210, @p211, @p212, @p213, @p214, @p215, @p216, @p217, @p218, @p219, @p220, @p221, @p222, @p223, @p224, @p225, @p226, @p227, @p228, @p229, @p230, @p231, @p232, @p233, @p234, @p235, @p236, @p237, @p238, @p239, @p240, @p241, @p242, @p243, @p244, @p245, @p246, @p247, @p248, @p249, @p250, @p251, @p252, @p253, @p254, @p255, @p256, @p257, @p258, @p259, @p260, @p261, @p262, @p263, @p264, @p265, @p266, @p267, @p268, @p269, @p270, @p271, @p272, @p273, @p274, @p275, @p276, @p277, @p278, @p279, @p280, @p281, @p282, @p283, @p284, @p285, @p286, @p287, @p288, @p289, @p290, @p291, @p292, @p293, @p294, @p295, @p296, @p297, @p298, @p299, @p300, @p301, @p302, @p303, @p304, @p305, @p306, @p307, @p308, @p309, @p310, @p311, @p312, @p313, @p314, @p315, @p316, @p317, @p318, @p319, @p320, @p321, @p322, @p323, @p324, @p325, @p326, @p327, @p328, @p329, @p330, @p331, @p332, @p333, @p334, @p335, @p336, @p337, @p338, @p339, @p340, @p341, @p342, @p343, @p344, @p345, @p346, @p347, @p348, @p349, @p350, @p351, @p352, @p353, @p354, @p355, @p356, @p357, @p358, @p359, @p360, @p361, @p362, @p363, @p364, @p365, @p366, @p367, @p368, @p369, @p370, @p371, @p372, @p373, @p374, @p375, @p376, @p377, @p378, @p379, @p380, @p381, @p382, @p383, @p384, @p385, @p386, @p387, @p388, @p389, @p390, @p391, @p392, @p393, @p394, @p395, @p396, @p397, @p398, @p399, @p400, @p401, @p402, @p403, @p404, @p405, @p406, @p407, @p408, @p409, @p410, @p411, @p412, @p413, @p414, @p415, @p416, @p417, @p418, @p419, @p420, @p421, @p422, @p423, @p424, @p425, @p426, @p427, @p428, @p429, @p430, @p431, @p432, @p433, @p434, @p435, @p436, @p437, @p438, @p439, @p440, @p441, @p442, @p443, @p444, @p445, @p446, @p447, @p448, @p449, @p450, @p451, @p452, @p453, @p454, @p455, @p456, @p457, @p458, @p459, @p460, @p461, @p462, @p463, @p464, @p465, @p466, @p467, @p468, @p469, @p470, @p471, @p472, @p473, @p474, @p475, @p476, @p477, @p478, @p479, @p480, @p481, @p482, @p483, @p484, @p485, @p486, @p487, @p488, @p489, @p490, @p491, @p492, @p493, @p494, @p495, @p496, @p497, @p498, @p499)',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 int,@p10 int,@p11 int,@p12 int,@p13 int,@p14 int,@p15 int,@p16 int,@p17 int,@p18 int,@p19 int,@p20 int,@p21 int,@p22 int,@p23 int,@p24 int,@p25 int,@p26 int,@p27 int,@p28 int,@p29 int,@p30 int,@p31 int,@p32 int,@p33 int,@p34 int,@p35 int,@p36 int,@p37 int,@p38 int,@p39 int,@p40 int,@p41 int,@p42 int,@p43 int,@p44 int,@p45 int,@p46 int,@p47 int,@p48 int,@p49 int,@p50 int,@p51 int,@p52 int,@p53 int,@p54 int,@p55 int,@p56 int,@p57 int,@p58 int,@p59 int,@p60 int,@p61 int,@p62 int,@p63 int,@p64 int,@p65 int,@p66 int,@p67 int,@p68 int,@p69 int,@p70 int,@p71 int,@p72 int,@p73 int,@p74 int,@p75 int,@p76 int,@p77 int,@p78 int,@p79 int,@p80 int,@p81 int,@p82 int,@p83 int,@p84 int,@p85 int,@p86 int,@p87 int,@p88 int,@p89 int,@p90 int,@p91 int,@p92 int,@p93 int,@p94 int,@p95 int,@p96 int,@p97 int,@p98 int,@p99 int,@p100 int,@p101 int,@p102 int,@p103 int,@p104 int,@p105 int,@p106 int,@p107 int,@p108 int,@p109 int,@p110 int,@p111 int,@p112 int,@p113 int,@p114 int,@p115 int,@p116 int,@p117 int,@p118 int,@p119 int,@p120 int,@p121 int,@p122 int,@p123 int,@p124 int,@p125 int,@p126 int,@p127 int,@p128 int,@p129 int,@p130 int,@p131 int,@p132 int,@p133 int,@p134 int,@p135 int,@p136 int,@p137 int,@p138 int,@p139 int,@p140 int,@p141 int,@p142 int,@p143 int,@p144 int,@p145 int,@p146 int,@p147 int,@p148 int,@p149 int,@p150 int,@p151 int,@p152 int,@p153 int,@p154 int,@p155 int,@p156 int,@p157 int,@p158 int,@p159 int,@p160 int,@p161 int,@p162 int,@p163 int,@p164 int,@p165 int,@p166 int,@p167 int,@p168 int,@p169 int,@p170 int,@p171 int,@p172 int,@p173 int,@p174 int,@p175 int,@p176 int,@p177 int,@p178 int,@p179 int,@p180 int,@p181 int,@p182 int,@p183 int,@p184 int,@p185 int,@p186 int,@p187 int,@p188 int,@p189 int,@p190 int,@p191 int,@p192 int,@p193 int,@p194 int,@p195 int,@p196 int,@p197 int,@p198 int,@p199 int,@p200 int,@p201 int,@p202 int,@p203 int,@p204 int,@p205 int,@p206 int,@p207 int,@p208 int,@p209 int,@p210 int,@p211 int,@p212 int,@p213 int,@p214 int,@p215 int,@p216 int,@p217 int,@p218 int,@p219 int,@p220 int,@p221 int,@p222 int,@p223 int,@p224 int,@p225 int,@p226 int,@p227 int,@p228 int,@p229 int,@p230 int,@p231 int,@p232 int,@p233 int,@p234 int,@p235 int,@p236 int,@p237 int,@p238 int,@p239 int,@p240 int,@p241 int,@p242 int,@p243 int,@p244 int,@p245 int,@p246 int,@p247 int,@p248 int,@p249 int,@p250 int,@p251 int,@p252 int,@p253 int,@p254 int,@p255 int,@p256 int,@p257 int,@p258 int,@p259 int,@p260 int,@p261 int,@p262 int,@p263 int,@p264 int,@p265 int,@p266 int,@p267 int,@p268 int,@p269 int,@p270 int,@p271 int,@p272 int,@p273 int,@p274 int,@p275 int,@p276 int,@p277 int,@p278 int,@p279 int,@p280 int,@p281 int,@p282 int,@p283 int,@p284 int,@p285 int,@p286 int,@p287 int,@p288 int,@p289 int,@p290 int,@p291 int,@p292 int,@p293 int,@p294 int,@p295 int,@p296 int,@p297 int,@p298 int,@p299 int,@p300 int,@p301 int,@p302 int,@p303 int,@p304 int,@p305 int,@p306 int,@p307 int,@p308 int,@p309 int,@p310 int,@p311 int,@p312 int,@p313 int,@p314 int,@p315 int,@p316 int,@p317 int,@p318 int,@p319 int,@p320 int,@p321 int,@p322 int,@p323 int,@p324 int,@p325 int,@p326 int,@p327 int,@p328 int,@p329 int,@p330 int,@p331 int,@p332 int,@p333 int,@p334 int,@p335 int,@p336 int,@p337 int,@p338 int,@p339 int,@p340 int,@p341 int,@p342 int,@p343 int,@p344 int,@p345 int,@p346 int,@p347 int,@p348 int,@p349 int,@p350 int,@p351 int,@p352 int,@p353 int,@p354 int,@p355 int,@p356 int,@p357 int,@p358 int,@p359 int,@p360 int,@p361 int,@p362 int,@p363 int,@p364 int,@p365 int,@p366 int,@p367 int,@p368 int,@p369 int,@p370 int,@p371 int,@p372 int,@p373 int,@p374 int,@p375 int,@p376 int,@p377 int,@p378 int,@p379 int,@p380 int,@p381 int,@p382 int,@p383 int,@p384 int,@p385 int,@p386 int,@p387 int,@p388 int,@p389 int,@p390 int,@p391 int,@p392 int,@p393 int,@p394 int,@p395 int,@p396 int,@p397 int,@p398 int,@p399 int,@p400 int,@p401 int,@p402 int,@p403 int,@p404 int,@p405 int,@p406 int,@p407 int,@p408 int,@p409 int,@p410 int,@p411 int,@p412 int,@p413 int,@p414 int,@p415 int,@p416 int,@p417 int,@p418 int,@p419 int,@p420 int,@p421 int,@p422 int,@p423 int,@p424 int,@p425 int,@p426 int,@p427 int,@p428 int,@p429 int,@p430 int,@p431 int,@p432 int,@p433 int,@p434 int,@p435 int,@p436 int,@p437 int,@p438 int,@p439 int,@p440 int,@p441 int,@p442 int,@p443 int,@p444 int,@p445 
int,@p446 int,@p447 int,@p448 int,@p449 int,@p450 int,@p451 int,@p452 int,@p453 int,@p454 int,@p455 int,@p456 int,@p457 int,@p458 int,@p459 int,@p460 int,@p461 int,@p462 int,@p463 int,@p464 int,@p465 int,@p466 
int,@p467 int,@p468 int,@p469 int,@p470 int,@p471 int,@p472 int,@p473 int,@p474 int,@p475 int,@p476 int,@p477 int,@p478 int,@p479 int,@p480 int,@p481 int,@p482 int,@p483 int,@p484 int,@p485 int,@p486 int,@p487 
int,@p488 int,@p489 int,@p490 int,@p491 int,@p492 int,@p493 int,@p494 int,@p495 int,@p496 int,@p497 int,@p498 int,@p499 
int',@p0=35519975,@p1=35519976,@p2=35519977,@p3=35519978,@p4=35519979,@p5=35519980,@p6=35519981,@p7=35519982,@p8=35519983,@p9=35519984,@p10=35519985,@p11=35519986,@p12=35519987,@p13=35519988,@p14=35519989,@p15=35519990,@p16=35519991,@p17=35519992,@p18=35519993,@p19=35519994,@p20=35519995,@p21=35519996,@p22=35519997,@p23=35519998,@p24=35519999,@p25=35520000,@p26=35520001,@p27=35520002,@p28=35520003,@p29=35520004,@p30=35520005,@p31=35520006,@p32=35520007,@p33=35520008,@p34=35520009,@p35=35520010,@p36=35520011,@p37=35520012,@p38=35520013,@p39=35520014,@p40=35520015,@p41=35520016,@p42=35520017,@p43=35520018,@p44=35520019,@p45=35520020,@p46=35520021,@p47=35520022,@p48=35520023,@p49=35520024,@p50=35520025,@p51=35520026,@p52=35520027,@p53=35520028,@p54=35520029,@p55=35520030,@p56=35520031,@p57=35520032,@p58=35520033,@p59=35520034,@p60=35520035,@p61=35520036,@p62=35520037,@p63=35520038,@p64=35520039,@p65=35520040,@p66=35520041,@p67=35520042,@p68=35520043,@p69=35520044,@p70=35520045,@p71=35520046,@p72=35520047,@p73=35520048,@p74=35520049,@p75=35520050,@p76=35520051,@p77=35520052,@p78=35520053,@p79=35520054,@p80=35520055,@p81=35520056,@p82=35520057,@p83=35520058,@p84=35520059,@p85=35520060,@p86=35520061,@p87=35520062,@p88=35520063,@p89=35520064,@p90=35520065,@p91=35520066,@p92=35520067,@p93=35520068,@p94=35520069,@p95=35520070,@p96=35520071,@p97=35520072,@p98=35520073,@p99=35520074,@p100=35520075,@p101=35520076,@p102=35520077,@p103=35520078,@p104=35520079,@p105=35520080,@p106=35520081,@p107=35520082,@p108=35520083,@p109=35520084,@p110=35520085,@p111=35520086,@p112=35520087,@p113=35520088,@p114=35520089,@p115=35520090,@p116=35520091,@p117=35520092,@p118=35520093,@p119=35520094,@p120=35520095,@p121=35520096,@p122=35520097,@p123=35520098,@p124=35520099,@p125=35520100,@p126=35520101,@p127=35520102,@p128=35520103,@p129=35520104,@p130=35520105,@p131=35520106,@p132=35520107,@p133=35520108,@p134=35520109,@p135=35520110,@p136=35520111,@p137=35520112,@p138=35520113,@p139=35520114,@p140=35520115,@p141=35520116,@p142=35520117,@p143=35520118,@p144=35520119,@p145=35520120,@p146=35520121,@p147=35520122,@p148=35520123,@p149=35520124,@p150=35520125,@p151=35520126,@p152=35520127,@p153=35520128,@p154=35520129,@p155=35520130,@p156=35520131,@p157=35520132,@p158=35520133,@p159=35520134,@p160=35520135,@p161=35520136,@p162=35520137,@p163=35520138,@p164=35520139,@p165=35520140,@p166=35520141,@p167=35520142,@p168=35520143,@p169=35520144,@p170=35520145,@p171=35520146,@p172=35520147,@p173=35520148,@p174=35520149,@p175=35520150,@p176=35520151,@p177=35520152,@p178=35520153,@p179=35520154,@p180=35520155,@p181=35520156,@p182=35520157,@p183=35520158,@p184=35520159,@p185=35520160,@p186=35520161,@p187=35520162,@p188=35520163,@p189=35520164,@p190=35520165,@p191=35520166,@p192=35520167,@p193=35520168,@p194=35520169,@p195=35520170,@p196=35520171,@p197=35520172,@p198=35520173,@p199=35520174,@p200=35520175,@p201=35520176,@p202=35520177,@p203=35520178,@p204=35520179,@p205=35520180,@p206=35520181,@p207=35520182,@p208=35520183,@p209=35520184,@p210=35520185,@p211=35520186,@p212=35520187,@p213=35520188,@p214=35520189,@p215=35520190,@p216=35520191,@p217=35520192,@p218=35520193,@p219=35520194,@p220=35520195,@p221=35520196,@p222=35520197,@p223=35520198,@p224=35520199,@p225=35520200,@p226=35520201,@p227=35520202,@p228=35520203,@p229=35520204,@p230=35520205,@p231=35520206,@p232=35520207,@p233=35520208,@p234=35520209,@p235=35520210,@p236=35520211,@p237=35520212,@p238=35520213,@p239=35520214,@p240=35520215,@p241=35520216,@p242=35520217,@p243=35520218,@p244=35520219,@p245=35520220,@p246=35520221,@p247=35520222,@p248=35520223,@p249=35520224,@p250=35520225,@p251=35520226,@p252=35520227,@p253=35520228,@p254=35520229,@p255=35520230,@p256=35520231,@p257=35520232,@p258=35520233,@p259=35520234,@p260=35520235,@p261=35520236,@p262=35520237,@p263=35520238,@p264=35520239,@p265=35520240,@p266=35520241,@p267=35520242,@p268=35520243,@p269=35520244,@p270=35520245,@p271=35520246,@p272=35520247,@p273=35520248,@p274=35520249,@p275=35520250,@p276=35520251,@p277=35520252,@p278=35520253,@p279=35520254,@p280=35520255,@p281=35520256,@p282=35520257,@p283=35520258,@p284=35520259,@p285=35520260,@p286=35520261,@p287=35520262,@p288=35520263,@p289=35520264,@p290=35520265,@p291=35520266,@p292=35520267,@p293=35520268,@p294=35520269,@p295=35520270,@p296=35520271,@p297=35520272,@p298=35520273,@p299=35520274,@p300=35520275,@p301=35520276,@p302=35520277,@p303=35520278,@p304=35520279,@p305=35520280,@p306=35520281,@p307=35520282,@p308=35520283,@p309=35520284,@p310=35520285,@p311=35520286,@p312=35520287,@p313=35520288,@p314=35520289,@p315=35520290,@p316=35520291,@p317=35520292,@p318=35520293,@p319=35520294,@p320=35520295,@p321=35520296,@p322=35520297,@p323=35520298,@p324=35520299,@p325=35520300,@p326=35520301,@p327=35520302,@p328=35520303,@p329=35520304,@p330=35520305,@p331=35520306,@p332=35520307,@p333=35520308,@p334=35520309,@p335=35520310,@p336=35520311,@p337=35520312,@p338=35520313,@p339=35520314,@p340=35520315,@p341=35520316,@p342=35520317,@p343=35520318,@p344=35520319,@p345=35520320,@p346=35520321,@p347=35520322,@p348=35520323,@p349=35520324,@p350=35520325,@p351=35520326,@p352=35520327,@p353=35520328,@p354=35520329,@p355=35520330,@p356=35520331,@p357=35520332,@p358=35520333,@p359=35520334,@p360=35520335,@p361=35520336,@p362=35520337,@p363=35520338,@p364=35520339,@p365=35520340,@p366=35520341,@p367=35520342,@p368=35520343,@p369=35520344,@p370=35520345,@p371=35520346,@p372=35520347,@p373=35520348,@p374=35520349,@p375=35520350,@p376=35520351,@p377=35520352,@p378=35520353,@p379=35520354,@p380=35520355,@p381=35520356,@p382=35520357,@p383=35520358,@p384=35520359,@p385=35520360,@p386=35520361,@p387=35520362,@p388=35520363,@p389=35520364,@p390=35520365,@p391=35520366,@p392=35520367,@p393=35520368,@p394=35520369,@p395=35520370,@p396=35520371,@p397=35520372,@p398=35520373,@p399=35520374,@p400=35520375,@p401=35520376,@p402=35520377,@p403=35520378,@p404=35520379,@p405=35520380,@p406=35520381,@p407=35520382,@p408=35520383,@p409=35520384,@p410=35520385,@p411=35520386,@p412=35520387,@p413=35520388,@p414=35520389,@p415=35520390,@p416=35520391,@p417=35520392,@p418=35520393,@p419=35520394,@p420=35520395,@p421=35520396,@p422=35520397,@p423=35520398,@p424=35520399,@p425=35520400,@p426=35520401,@p427=35520402,@p428=35520403,@p429=35520404,@p430=35520405,@p431=35520406,@p432=35520407,@p433=35520408,@p434=35520409,@p435=35520410,@p436=35520411,@p437=35520412,@p438=35520413,@p439=35520414,@p440=35520415,@p441=35520416,@p442=35520417,@p443=35520418,@p444=35520419,@p445=35520420,@p446=35520421,@p447=35520422,@p448=35520423,@p449=35520424,@p450=35520425,@p451=35520426,@p452=35520427,@p453=35520428,@p454=35520429,@p455=35520430,@p456=35520431,@p457=35520432,@p458=35520433,@p459=35520434,@p460=35520435,@p461=35520436,@p462=35520437,@p463=35520438,@p464=35520439,@p465=35520440,@p466=35520441,@p467=35520442,@p468=35520443,@p469=35520444,@p470=35520445,@p471=35520446,@p472=35520447,@p473=35520448,@p474=35520449,@p475=35520450,@p476=35520451,@p477=35520452,@p478=35520453,@p479=35520454,@p480=35520455,@p481=35520456,@p482=35520457,@p483=35520458,@p484=35520459,@p485=35520460,@p486=35520461,@p487=35520462,@p488=35520463,@p489=35520464,@p490=35520465,@p491=35520466,@p492=35520467,@p493=35520468,@p494=35520469,@p495=35520470,@p496=35520471,@p497=35520472,@p498=35520473,@p499=35520474
Another one that is executed every second with a new id:

Code: Select all

exec sp_executesql N'SELECT specials0_.ProductId as ProductId1_, specials0_.SpecialId as SpecialId1_, specials0_.SpecialId as SpecialId74_0_, specials0_.ProductId as ProductId74_0_, specials0_.Price as Price74_0_, specials0_.StartDate as StartDate74_0_, specials0_.EndDate as EndDate74_0_ FROM ac_Specials specials0_ WHERE specials0_.ProductId=@p0',N'@p0 int',@p0=46660
Followed by:

Code: Select all

exec sp_executesql N'SELECT distinct this_.CategoryId as y0_ FROM ac_CatalogNodes this_ WHERE this_.CatalogNodeId = @p0 and this_.CatalogNodeTypeId = @p1',N'@p0 int,@p1 tinyint',@p0=46674,@p1=1
Followed by:

Code: Select all

exec sp_executesql N'SELECT this_.Id as Id3_0_, this_.CategoryId as CategoryId3_0_, this_.ParentId as ParentId3_0_, this_.ParentLevel as ParentLe4_3_0_, this_.ParentNumber as ParentNu5_3_0_ FROM ac_CategoryParents this_ WHERE this_.CategoryId = @p0 and this_.ParentId > @p1 ORDER BY this_.ParentLevel asc',N'@p0 int,@p1 int',@p0=53,@p1=0
etc...

With a profiler on the DB, this continues non-stop with zero site traffic. Wondering if there is a place I can read up on this? It's super difficult to troubleshoot and find performance tweaks especially when these queries make little to no sense unless they are populating a file of some sort?

I'll hold off with other questions for now. Would love some help / feedback on this.

Thanks,
Matt

User avatar
jmestep
AbleCommerce Angel
Posts: 8164
Joined: Sun Feb 29, 2004 8:04 pm
Location: Dayton, OH
Contact:

Re: Number of queries on a list page.

Post by jmestep » Wed Jun 07, 2017 11:16 pm

Code: Select all

exec sp_executesql N' SELECT COUNT(*) FROM   (   SELECT CN.* FROM ac_CatalogNodes AS CN INNER JOIN ac_Categories AS C   ON CN.CatalogNodeId = C.CategoryId AND CN.CatalogNodeTypeId = 0  WHERE CN.CategoryId = @p0 AND C.VisibilityId = @p1  UNION ALL  SELECT CN.* FROM ac_CatalogNodes AS CN INNER JOIN ac_Products AS P  ON CN.CatalogNodeId = P.ProductId AND CN.CatalogNodeTypeId = 1  WHERE CN.CategoryId = @p0  AND P.VisibilityId = @p1  UNION ALL  SELECT CN.* FROM ac_CatalogNodes AS CN INNER JOIN ac_Webpages AS W  ON CN.CatalogNodeId = W.WebpageId AND CN.CatalogNodeTypeId = 2   WHERE CN.CategoryId = @p0  AND W.VisibilityId = @p1  UNION ALL  SELECT CN.* FROM ac_CatalogNodes AS CN INNER JOIN ac_Links AS L   ON CN.CatalogNodeId = L.LinkId AND CN.CatalogNodeTypeId = 3  WHERE CN.CategoryId = @p0 AND L.VisibilityId = @p1  ) AS CN1 ',N'@p0 int,@p1 tinyint',@p0=194,@p1=0
The above is probably running to populate the results count at the top of the category page grid.(Displaying x of xx).
The one for pageviews looks like it is the maintenance routine, which runs according to the setting in the App_Data/AbleCommerce.config
<item key="MaintenanceInterval" value="360" />

Code: Select all

exec sp_executesql N'SELECT specials0_.ProductId....
gets the special price for each product on the page
exec sp_executesql N'SELECT distinct this_.CategoryId as y0_ FROM ac_CatalogNodes gets the parent categoryId for an object on the page

Code: Select all

exec sp_executesql N'SELECT this_.Id as Id3_0_, this_.CategoryId as CategoryId3_0_, this_.ParentId as ParentId3_0_,.....
is probably for the category breadcrumb.
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx

matty
Ensign (ENS)
Ensign (ENS)
Posts: 10
Joined: Wed Jun 07, 2017 10:58 am

Re: Number of queries on a list page.

Post by matty » Thu Jun 08, 2017 1:06 am

Judy,

Thank you for the reply. My confusion on the above queries is why they are called dozens of times for a single page load. I understand the need to get the count as well as the breadcrumb comma but it doesn't make sense that these would be called so many times. I was handed this code so I'm not sure if there is something wrong in where the category count and breadcrumb might have gotten caught in a loop.

Any ideas as to why these would be called so many times?

Thanks again!

matty
Ensign (ENS)
Ensign (ENS)
Posts: 10
Joined: Wed Jun 07, 2017 10:58 am

Re: Number of queries on a list page.

Post by matty » Fri Jun 09, 2017 5:43 am

I have some more detail on this. Our product list page (category) has roughly 400 calls to the DB for each page load. This is caused by each product having a handful of queries on the page to render it.

Is this normal behavior for AC and why is the DB hit each time? Do AC's caching store these queries for a period of time?

Currently I am seeing this hit every time the page is loaded.

Post Reply