sql - Optimering af sql query - Udvikleren.dk - Programmering, webdesign og grafik

Tags: mssql sql query

Bruger #13770 @ 07.09.11 12:36

Hej.

Jeg har lavet en søgning til en shop, og jeg er ved at have en meget lang sql query til at sortere varelisterne med, så jeg tænkte på om der var nogle kloge hoveder her inde som måske kunne hjælpe med at optimere den lidt.

Her er min query

SQL kode

 SELECT     pg.ID, pg.sort, pg.prodCatID, pg.name, pg.link, pg.title, pg.internalID, pg.parentID, p.ID AS Expr1, p.ProductID, p.ProductNumber, p.ProductName, p.Image, 
                       p.ShortDescription, p.LongDescription, p.InternalNumber, p.UnitPrice, p.isMaster, p.ProductGroup, p.ProductHidden, p.ProductMasterID, p.Stock, p.SpecialOffer
 FROM         productgrp AS pg CROSS JOIN
                       product AS p
 WHERE     (CHARINDEX(',' + CAST(pg.prodCatID AS varchar) + ',', p.ProductGroup) > 0) AND (pg.internalID = 858) AND (p.ProductID IN
                           (SELECT     productID
                             FROM          product_keyword
                             WHERE      (keywordID = 88))) AND (p.ProductID IN
                           (SELECT     productID
                             FROM          product_keyword AS product_keyword_3
                             WHERE      (keywordID = 68))) AND (p.isMaster = 1) AND EXISTS
                           (SELECT     Stock
                             FROM          product AS p2
                             WHERE      (ProductMasterID = p.ProductNumber) AND (Stock > 0) AND (ProductID IN
                                                        (SELECT     productID
                                                          FROM          product_keyword AS product_keyword_2
                                                          WHERE      (keywordID = 88))) OR (ProductID IN
                                                        (SELECT     productID
                                                          FROM          product_keyword AS product_keyword_1
                                                          WHERE      (keywordID = 68))))

Det som man kan er at man via nogle selectboxe kan sorte en vareliste, og så får man vidst de produkter der findes i den valgte farve, størrelse osv. samtidig tjekker den også om produktet er på lager i den størrelse man godt vil have, ellers skal det nemlig ikke vise.

Den virker i grunden fint nok, men det kunne jo være det kunne gøres på en nemmere måde og med mindre kode

6 svar postet i denne tråd vises herunder
2 indlæg har modtaget i alt 2 karma

Sorter efter stemmer Sorter efter dato

Bruger #2730 @ 07.09.11 15:41

1.626

Har du prøvet den i MSSQL performance profiler? Der er en knap i stedet for at eksekvere i enterprise manager, så man kan se en execution plan for din query, måske det kan lede på vej.

Du har for mange sub-selects, det er dem der dræber din forespørgsel. For hver række i din hoved-select skal den lave tre under selects. Og for hver af de rækker i den sidste sub-select skal den igen spørge i databasen. Det er rigtig meget overhead. So Benni siger, tror jeg du skal dele det op i forskellige forespørgsler - det er svært.

Der ville måske være noget at hente ved at lave et index på Product_Id og KEyword_Id - men måske ikke helt nok.

Du skal af med alle dine subselects, jeg tror de er ioverflødige og kan langt hen ad vejen løses med where statements (du selecter altid fra samme tabel)

Bruger #5620 @ 07.09.11 18:26

1.500

er det nødvendigt 4 gange at teste om productID er i product_keyword med keyword_id 68 og/eller 88

og det her virker bare underligt:
(CHARINDEX(',' + CAST(pg.prodCatID AS varchar) + ',', p.ProductGroup) > 0)

virker lidt som om at du har en fejl i dine nøgler hvis det der er nødvendigt.

Hvert produkt er vel i en gruppe og, hver gruppe har vel en række i productgrp. Så den primærenøgle fra productgrp burde være fremmed nøgle i produkt.

Bruger #10576 @ 07.09.11 15:13

287

Er den database normaliseret ..? Kunne lyde lidt som om du prøver at lave én stor sql, frem for at minimere den, alt efter hvilke valg man har lavet i drop down ? Er for at sige det mildt lidt uoverskueligt med så lang et stykke sql, har aldrig set noget ligende så tror bestemt der er potentiale for forbedringer :=)

Har du evt. et screenshot af gui så man har lidt at forholde sig til ?

Bruger #13770 @ 07.09.11 16:24

Tak for svarene begge to, jeg vil prøve at få fjernet mine sub-selects o kigge på min execution plan og se om jeg ikke kan få det optimeret.

Benni, hvad mener du med om min database er normaliseret?

Bruger #10576 @ 07.09.11 19:00

287

@Andreas jeg mener, at jeg mistænker at din database er mærkeligt bygget op uden at have taget væsentlige design overvejelser.. men er lidt svært at overskue ud fra din sql (men også derfor jeg mistænker det :=) ) .. går egentlig i hånd med Jens' post.

Bruger #13770 @ 07.09.11 21:38

@Benni den er bygget mærkeligt op, desværer har jeg ikke haft mulighed for at være med til at lave opbygningen af databasen

@Jens Grunden til at jeg bliver nød at bruge CHARINDEX er at dem der har lavet strukturen på databasen har lavet et felt alle i produkt tabellen hvor alle grupper som produktet er tilkoblet ligger seperaret med komma. så kunne ikke lige finde på en anden løsning.

Havde en fejl i et loop, der gjorde at den linje som tester for productID i product_keyword blev udskrevet for mange gange. har fået rette det nu

EDIT:
Nu har jeg efterhånden fået forkortet min query en del, så er nede på dette

SQL kode

 SELECT * FROM product_keyword WHERE 
 productID in (
     SELECT productID FROM product WHERE (
         ProductNumber in (
             SELECT ProductMasterID FROM productgrp AS pg 
             CROSS JOIN product_keyword 
             LEFT OUTER JOIN product ON 
             product.ProductID = product_keyword.productID WHERE 
             (product_keyword.keywordID = 64) AND 
             (product.isMaster = 0) AND 
             (product.Stock > 0) AND 
             (CHARINDEX(',' + CAST(pg.prodCatID AS varchar) + ',',product.ProductGroup) > 0) AND 
             (pg.internalID = 858)
         )
     )
 ) AND keywordID = 88

Har dog lidt svært ved at se om jeg kan få fjernet de sidste sub-select som jeg har, er der nogle der har et par gode øjne som kan hjælpe mig?

Indlæg senest redigeret d. 08.09.2011 19:20 af Bruger #13770

Optimering af sql query

Karma barometer (30 dage)

Modtaget

Givet

Favorit hos
Forum tråde
Artikler