SQL - Kërkesa (query) shumë e ngadaltë

[Gepardi]

Pershendetje.
Po has nje problem prej disa ditesh ne nje projekt qe po punoj. Nje nga queriet me te rendesishme ka nje kohe ekzekutimi te patolerueshme (mbi 60s).
Database qe po perdor eshte mySQL.

Struktura e DB eshte e tille

klientet orders
===== =====
id id
emri klient_id
mbiemri totali
etj etj


qellimi eshte te kap klientet qe nuk kane asnje "order". Une veproj keshtu

SELECT t1.* FROM klientet as t1
LEFT JOIN orders as t2 ON t1.id=t2.klient_id
WHERE t2.klient_id IS NULL

query e plote kap informacion dhe nga dy tabela te tjera me INNER JOIN por ne disa prova qe kam bere rezultoi se vonesa shkaktohej per shkak te lidhjes me lart.

Do te mireprisja cdo lloj sugjerimi pasi ky eshte me te vertet nje problem i madh. Gjithashtu database e ketij programi eshte e gjere dhe rritet dita dites (aktualisht me rreth 100 kliente ne dite) dmth numri i regjistrimive eshte i madh.

Pra si mund te pershpejtohet kjo query pasi 60s jane me te vertete shume te patolerueshme.

Faleminderit.

[edspace]

Së pari sigurohu që struktura e DB është e saktë. Kolona id duhet të jetë primary key tek të dyja tabelat. Kolona klient_id gjithashtu duhet të jetë indeks.

Do ishte mirë të postoje kërkesën (query) e plotë. Megjithatë, mendoj se gabimi është se kërkesa po tërheq më shumë informacion seç duhet.

Nëse kemi klientet 1-5 dhe porositë 1-15, atëherë MySql do krijojë më parë një listë të tillë:

t1.id-t2.klient_id
1-1
1-1
1-1
2-2
2-2
2-2
3-NULL
4-4
4-4
5-4
5-5
5-5
5-5
5-5
5-5
5-5

Për të gjetur klientët pa porosi, i duhet të kontrollojë që të gjithë rreshtat e porosive. Kjo mendoj se mund të bëhet më mirë duke grupuar më parë sipas ID së klientit dhe pastaj duke filtruar vetëm rreshtat ku numri i porosive është zero.

SELECT t1.*, count(*) as numriPorosive
FROM klientet as t1
LEFT JOIN orders as t2 ON t1.id=t2.klient_id
GROUP BY t1.id
HAVING numriPorosive = 0

Grupimi do formojë një listë të tillë:

id-nurmiPorosive
1-3
2-3
3-0
4-2
5-7

Tani MySql i duhet të kërkojë vetëm 5 rreshta, jo 16. Gjithësesi nuk besoj të jetë vetëm këtu problemi. Shiko nëse ke ndonjë problem tjeter me formulimin e kërkesave ose hidhe të gjithë kërkesën këtu.

[Gepardi]

Kisha harruar te shenoja klient_id si index. Sapo e bera kete te fundit problemi u zgjidh dhe gjithcka u kthye ne normalitet (pergjigje rreth 0.05s).
Pra mosvendosja e indexeve sillte nje pergjije mbi 60 s (vinte ne rritje, tani ne fund shkonte deri 86s).

Para se ta beja kete i kisha dhene nje zgjidhje jo shume te pershtatshme me 2 query sepse INNER JOIN me jepte pergjigje te shpejte.

1. Kap id e klienteve qe kane porosi
SELECT t1.id FROM klientet as t1 INNER JOIN porosite as t2 ON t1.id=t2.cust_id
2. Kap klientet qe nuk u ben pjese id ne ato me lart
SELECT * FROM klientet WHERE id NOT IN (lista_id-ve)

ne versionin 5 te mysql mund te perdoren dhe subquery dhe ajo me lart do te shprehej

SELECT * FROM klientet WHERE id NOT IN(SELECT t1.id FROM klientet as t1 INNER JOIN porosite as t2 ON t1.id=t2.cust_id)


Ideja jote per grupim mu duk interesante megjithese ndoshta dhe grupimi do nje fare kohe. Gjithsesi pasi e provova nuk me punoi pasi vura re qe gjate grupimit nuk perfshiheshin klientet pa porosi (pra numriPorosive=0) dhe query dilte gjithmone bosh.

[edspace]

Më bëhet qejfi që u zgjidh problemi.

Nëse do të përdorësh GROUP BY, count(*) ndryshoje në count(t2.klient_id).

Kërkesa më poshtë jep rezultatin e saktë:


SELECT t1.*, count(t2.klient_id) as numriPorosive
FROM klientet as t1
LEFT JOIN orders as t2 ON t1.id=t2.klient_id
GROUP BY t1.id
HAVING numriPorosive=0


Kjo mendoj se është mënyra më e lehtë për MySQL.

[qoska]

Do te keshilloja dicka me te mire se MySQL nese do kesh ngarkesa te medha pasi database si postgreSQL dhe MsSQL kane disa variante me te mira per te optimizuar keto lloj query-ish.

Me falni nese jam jashte teme por besoj se subquery do te ishte optimalja per kete lloj problemi dhe MySQL sapo ka ardhur ne kete fushe.

Gjithsesi ebdhe zgjidhja e edspace me group by nuk eshte e keqe por besoj se do pak me shume kohe(pa e testuar) pasi HAVING ekzekutohet mas krijimit te JOIN.

[edspace]

Nuk e di çfarë optimizimesh kryen MySQL përbrenda, por kërkesa me nënkërkesë (subquery) mendoj se do merrte më shumë kohë.

E zëmë se kemi N klientë, M porosi, dhe K klientë pa asnjë porosi. Tani le të bëjmë një analizë të thjeshtë të të dyja kërkesave.

Kërkesa me nënkërkesë (subquery)

SELECT * 
FROM klientet 
WHERE id NOT IN(
      SELECT t1.id 
      FROM klientet as t1 
      INNER JOIN porosite as t2 ON t1.id=t2.cust_id
)

Kërkesa e jashtme do kthejë një listë L1 me N rreshta, një për çdo klient. Kërkesa e brendshme do kthejë një listë L2 me M rreshta, një për çdo porosi. Çdo element të L1 do na duhet ta krahasojmë me elementët e L2 deri sa të gjenden dy njësoj.

T(kërkesës) = T(kërkesës së jashtme)     + T(kërkesës së brendshme)
T(kërkesës) = T(SELECT) + T(WHERE)       + T(SELECT) + T(INNER JOIN)
T(kërkesës) =    N      + T(N-K) + T(K)  +    N      +      M
T(kërkesës) =    N      + N*M/2 + K*M    +    N      +      M
T(kërkesës) = O(N*M/2 + K*M)

Kërkesa me grupim (group by)

SELECT t1.*, count(t2.klient_id) as numriPorosive
FROM klientet as t1
LEFT JOIN orders as t2 ON t1.id=t2.klient_id
GROUP BY t1.id
HAVING numriPorosive=0

Join kthen M + K rreshta të cilët grupohen në M kohë dhe filtrohen në N kohë.

T(kërkesës) = T(SELECT) + T(JOIN)  + T(GROUP BY) + T(HAVING)
T(kërkesës) =    N      +  M + K   +     M       +    N
T(kërkesës) = O(M)

Rezultati

Nga analiza e mësipërme duket qartë së kërkesa me grupim është më e shpejtë me një faktor Maks(N/2 , K). Në kërkesën e parë faktori mbizotërues është filtrimi i WHERE me NOT IN, ndërsa në kërkesën e dytë faktori mbizotërues është LEFT JOIN.

[qoska]

Korrekte me supozimet qe ke bere ti, por nje database nje SELECT mund ta ekzekutoje ne nje shpejtesi marramendese(pasi strukturat e te dhenave te perdorura vetem filtrojne nivele ) + optimizimet jane me te kollajta per nje query me sub-SELECT per shkak te asaj qe thashe me siper.

Gjithsesi sub-SELECT ngelet ne optimizim te databases qqe gjithsesi SELECT e ekzekuton me shpejt ne cdo lloj rasti. Kurse ne query-in tend ti nuk i lejon keto optimizime pasi percakton vete radhen e veprimeve.

P.S. besoj se nuk ka nevoje te shpjegohen strukturat e perdorura ne nje database dhe pse nje SELECT eshte shume i shpejt! Per te interesuarit dokumente dhe libra ka me shumice.