Jautājums uz ribas – lēns selekts, ko nu?


Ir šāda datu bāze (pieraksts nosacītā sintaksē):

Abonents (AbonentsNr int, Vards varchar(20), Uzvards varchar(20)) primary key (AbonentsNr);
Izdevums (IzdevumsNr int, Nosaukums varvhar(255), Tematika varchar(50)) primary key (IzdevumsNr);
Abonements (AbonementsNr int, AbonentsNr int, IzdevumsNr int, DatumsNo date, DatumsLidz date) primary key (AbonementsNr), foreign key (IzdevumsNr) references Izdevums (IzdevumsNr), foreign key (AbonentsNr) references Abonents (AbonentsNr)

Abonents 100 000 ierakstu, Izdevums 100 ierakstu, Abonements 500 000 ierakstu.

Sistēma izpilda SQL pieprasījumu:

SELECT * FROM Abonents WHERE AbonentsNr in (SELECT AbonentsNr From Abonements, Izdevums WHERE Abonements.IzdevumsNr = Izdevums.IzdevumsNr AND Izdevums.Tematika=”Lauksaimniecība”)

Pieprasījums izpildās lēni. Ko nu?

P.S. šis raksts nav tāpēc, ka personīgi man būtu problēmas atrisināt šāda veida uzdevumus. Vienkārši – interesē, kādus risinājumus komentāros piedāvās lasītāji.

Un šeit ir manas solītās pārdomas (pievienotas 13.01.2012) – kāds būtu mans domu gājiens.

1) indeksi (sk. Jāņa komentāru zemāk). Iemesls, kāpēc kā pirmo lieku indeksus, nevis acīmredzami nepieciešamo selekta optimizēšanu, ir šāds: lai uztaisītu vienkāršu indeksu, nevajag ne daudz prāta, ne daudz laika, tāpēc šis ir risinājums – aizbāznis, kurš var atvieglot dzīvi ĀTRI.

2) selekta optimizēšana. Daži no komentāros minētajiem variantiem (Gatis, Kaspars, Jānis) ir tīri ok.

select distinct a.*
from Izdevums i
inner join Abonements ab on ab.IzdevumsNr = i.IzdevumsNr
inner join Abonents a on a.AbonentsNr = ab.AbonentsNr
where Izdevums.Tematika = ”Lauksaimniecība”

Un ātri izdarāmais:

… WHERE AbonentsNr in (SELECT AbonentsNr From … )” vietā rakstīt WHERE EXISTS (SELECT … ). Jo ar EXISTS tiek meklēts pirmais ieraksts, kas atbilst kritērijam, kamēr ar AbonentsNr in … vispirms ir jāatlasa visi ieraksti, kas atbilst tam.

Arī šis Jāņa man patika:

Uztaisi klasifikatoru “Tematika”, un Izdevums.Tematika aizvieto ar Izdevums.TematikaNr, kuru taisi par foreign.key. Rezultātā nemeklē pēc teksta ”Lauksaimniecība”, bet pēc iepriekš atlasīta Tematika.Nr.

Un Jāņa doma par materializēto skatu.

2 ar pusi) tabulas iespējamā pārslodze – varbūt tajā ir neaktuāli dati, kurus vienreiz vajag aizarhivēt prom, un varbūt vajag padomāt par informācijas dalīšanu pa vairākām tabulām, piemēram, līdzko pienācis DatumsLidz, tā ieraksts ar kāda procesa palīdzību tiek automātiski pārnests uz vēsturisko datu tabulu.

2 ar trim ceturtdaļām): pārliecināt klientu, ka viņam patiesībā šāda vaicājuma rezultātu nemaz nevajag :-) :-) :-) (reveranss Gintam)

3) servera jauda – atmiņa, ātrums, kāds ir, ko var uzlabot?

3 ar pusi) pārbaudīt, kāds ir datu pārsūtīšanas ātrums savienojumā + datu attēlošanas uz ekrāna ātrums. Varbūt pats selekts izpildās ātri, taču problēma ir tur, ka tehnika rada sajūtu, ka tas izpildās lēni?

3 ar trim ceturtdaļām) izvietot tabulas uz dažādiem diskiem, tādējādi paātrinot fizisko nolasīšanu. Jā, šāds risinājums vairāk ir apsverams lielākiem datu apjomiem, taču kā variantu pieminu.

 4) datubāzes iespējamā pārslodze – izvērtēt iespēju samazināt datu apjomu vai pieprasījumu apjomu citās tabulās.

4 ar pusi) Ja esošā DBVS, par spīti veiktajiem pasākumiem ir par vāju, domāt par iespēju apstrādāt datus ar jaudīgāku DBVS.

Par iesūtītajiem komentāriem – sirsnīgs paldies visiem, kuri atsaucāties. Godīgi – mani sajūsmināja doma, ka par tabulas primāro atslēgu miera vējos tiek pieļauta iespēja, ka tā varētu būt telefona numurs :-). Un arī otrādi – ka lauku AbonentsNr int primarykey – var izlasīt kā telefona numuru ne brīdi nenošauboties :-) Mācība priekš manis: citreiz piemēros saukt par Id, nevis Nr. Izlasīju un pasmaidīju, ka dažreiz sistēmanalītiķis не читатель, sistēmanalītiķis писатель. Viens otrs komentārs atgādināja pasaki man savu problēmu  un es pateikšu, kāpēc tā nav atrisināma.

Lielākā daļa komentāru bija par paša selekta optimizēšanu vien, nepiesaucot iespējamos ārējos apstākļus. Ja šis būtu eksāmens, es teiktu – draugi, izkāpsim ārpus kastītes! Bet šis nebija eksāmens. Ceru, ka arī jums bija interesanti. Oficiālāku literatūru par dažādām optimizēšanām gūglējiet paši – papilnam.

Advertisements

20 responses to this post.

  1. Posted by Gatis on 20/02/2012 at 06:26

    Hm, hm. Par tematu, protams, neko piebilst nespeju, bet interesanti, vai konkretais gadijums ir tikai personiskaja pieredze bazeta akademiska interese vai ari tirgu tiesam ir paradijusies vajadziba pec jaunas preses abonesanas sistemas? :)

    Atbildēt

  2. Lielākā daļa komentāru bija par paša selekta optimizēšanu vien, nepiesaucot iespējamos ārējos apstākļus. Ja šis būtu eksāmens, es teiktu – draugi, izkāpsim ārpus kastītes!
    ===
    Nepiekrītu. Nekāpsim laukā no kastītes! :)

    Tu rakstīji: “Pieprasījums izpildās lēni. Ko nu?”. T.i., nevis biznesa funkciju nevar izpildīt nepietiekamas sistēmas ātrdarbības dēļ, bet tieši “pieprasījums izpildās lēni” (un šinī brīdī kļūst vienalga, vai lēni nozīmē 0,01 s vai 20 minūtes)

    :P

    Atbildēt

  3. Viens klasisks piegājiens, ko redzu uzreiz – “… WHERE AbonentsNr in (SELECT AbonentsNr From … )” vietā rakstīt WHERE EXISTS (SELECT … ). Jo ar EXISTS tiek meklēts pirmais ieraksts, kas atbilst kritērijam, kamēr ar AbonentsNr in … vispirms ir jāatlasa visi ieraksti, kas atbilst tam.

    Otrkārt, lai analizētu sīkāk, būtu nepieciešams explain plans, un atkarībā no tā tad arī risinājumi. Tie var būt atkarīgi arī no DBVS. Variants par materiālajiem skatiem vai savā ziņā līdzīgs – iet uz kaut kādu denormalizāciju un glabāt biežāk lietotos klienta datus pie abonementa. Var mēģināt risināt problēmu datubāzes līmenī – piemēram, Oracle tas varētu būt – izmantot tabulas uz dažādiem diskiem (bija minēts), particionēšanu vai ko tādu. Man gan liekas, ka 100 000 klienti nav daudz, diez vai tāda veida izvirtības ir vajadzīgas.

    Treškārt, un tas nav šis gadījums. Man ir pietiekoši bieži sanācis sastapties ar situāciju, kad cilvēki grib ierakstīt visu vienā pieprasījumā, piedevām, apgalvodami, ka serveris tā spēs izpildīt pieprasījumu ātrāk. Rezultātā parasti ir 3 A4 formāta lapu garuma SQL pieprasījums, kas strādā lēni, un kuru visi baidās mainīt, jo varbūtība salauzt funkcionalitāti ir 99,9%. Tāpēc, es cenšos garus selektus nerakstīt, rakstīt tos kodā kā kursorus, un vispār kopā vākšanas lietas izdarīt kodā. Jā, teorētiski, tas nav optimāli, bet kursori parasti strādā atbilstoši indeksiem un pietiekoši ātri, un tur ir lielākas iespējas ieviest izmaiņas. Praksē neesmu šo pieeju nožēlojis, toties esmu šādi būtiski optimizējis ne vienu vien 3 A4 lpp garus SQLu.

    Atbildēt

    • papildinot pats sevi (narcistiski protams) –

      “Rezultātā parasti ir 3 A4 formāta lapu garuma SQL pieprasījums, kas strādā lēni, un kuru visi baidās mainīt, jo varbūtība salauzt funkcionalitāti ir 99,9%” — turklāt, šis pieprasījums nav debugojams

      “kursori parasti strādā atbilstoši indeksiem” — jo ir viegli uzrakstīt mazus selectus, kas strādā pēc indeksiem. Ar lieliem selektiem čakaris ir daudz lielāks

      Atbildēt

  4. Par “Uztaisi klasifikatoru “Tematika”, un Izdevums.Tematika aizvieto ar Izdevums.TematikaNr” (tas bija e-remit komentārs)- šajā gadījumā, kad ir 100 rindiņas- šim manuprāt nebūs nekāda iespaida uz ātrdarbību..

    Par “2 ar pusi)”- tad jau vēl ir tabulu dalīšana partīcijās, tādējādi potenciāli samazinot pārmeklējamo tabulas daļu (dārgāko versiju iespēja).

    Atbildēt

  5. Papildināju ar savu domu konspektu.

    Atbildēt

  6. Posted by Jānis E on 05/01/2012 at 19:48

    Sāksim ar to, ka pie mūsdienu serveru jaudām, var atļauties rakstīt līkus selektus. Šis konkrētais uz MS SQL pat strādās, uz MySQL gan būs rezultāts ilgi jāgaida. Bet nu fleimu var sakurināt par šo tēmu varenu – subselekti slikti, joini labi. Kursori slikti, tīrs SQL labi. Temp tabulas ar # slikti, bez # – arī slikti, bet ja ļoti vajag tad nu labi. Pie kam visi varianti dara to, kas prasīts specifikācijā.

    Atbildēt

    • Jā, noteikti rakstīsim sliktus selektus un pēc tam klients dumš palikdams varēs ņemties ar PZ utt. Apbrīnoju šo ‘pofik ka tik strādā’ attieksmi…

      Atbildēt

  7. Posted by Gatis on 05/01/2012 at 12:27

    select a.*
    from Izdevums i
    inner join Abonements ab on ab.IzdevumsNr = i.IzdevumsNr
    inner join Abonents a on a.AbonentsNr = ab.AbonentsNr
    where Izdevums.Tematika = ”Lauksaimniecība”

    Atbildēt

  8. Posted by e-remit on 05/01/2012 at 10:02

    Spriežot pēc tā, ka meklē pēc Izdevums.Tematika=”Lauksaimniecība”, tas ir iepriekš atlasīts vārds, piemēram, no izvēlnes, nevis meklēšanas formā ievadīts teksts.
    Uztaisi klasifikatoru “Tematika”, un Izdevums.Tematika aizvieto ar Izdevums.TematikaNr, kuru taisi par foreign.key.
    Rezultātā nemeklē pēc teksta ”Lauksaimniecība”, bet pēc iepriekš atlasīta Tematika.Nr.

    Atbildēt

  9. Ja tīri no datubāzes viedokļa, tad problēmu iespējas ir ļoti daudz, tikai dažas, kā piemērs:
    – nav indeksu, lauksaimniecības telefonu nav pārāk daudz, viss iet fullskanā, tāpēc viss slikti
    – ir indeksi, lauksaimniecības telefoni patiesībā ir visi iespējamie, selekts iet pēc indeksiem, tāpēc viss slikti
    – ir indeksi, lauksaimniecības telefoni patiesībā ir visi iespējamie, selekts iet pa maksimāli labāko iespējamo izpildes plānu, bet vienkārši DB ir pārslogota un šī ir objektīvā īstenība.
    No tā izdaram secinājumu, ka par pieteikto PZ neko vairāk kā tikai minējumus izdarīt nevar, tāpēc ir pilnīgi skaidrs, ka info ir par maz. Kas tā par DBVS, tabulu struktūra, esošie indeksi, DBVS izvēlētais vaicājuma izpildes plāns un atgriežamo ierakstu skaits būtu minimālā info, kas nepieciešama, lai te kaut ko puslīdz droši varētu pateikt.

    Ja rokam dziļāk, tad atvainojiet, kuram saprātīgam cilvēkam vajag visus telefonus jādomā Latvijā, kam ir kaut kāda saistība ar lauksaimniecību? Vienīgais, ko reāli varu iedomāties, tas ir vai nu spameriem jebšu kaut kādiem tur viedokļu pētītājiem, kas izvēlas nejauši 1000 numurus, kuriem uzdot savus dīvainos jautājumus un tad tas ir pārskats/atskaite un tur ir mazāk svarīgi, cik ilgi tas iet.
    Visiem citiem saprātīgiem pielietojuma veidiem kritēriji ir acīmredzami par maz un viņiem vai nu ir jāprasa papildināt kritērijus, vai jāiedod jebkuri N (-epārāk daudz) telefoni un tālāk jāsaka, lai precizē kritērijus.
    Tāds nu ir mans ļaunais viedoklis, kas parasti apgriež visiem lidotājiem spārnus :)

    Atbildēt

    • Posted by Ģirts on 05/01/2012 at 17:24

      Kā šeit varēja saskatīt telefonus, ja uzreiz redzams, ka runa ir par abonentu unikālajiem ID. To vajag prast

      Atbildēt

      • Khe, khe, iespējams tajā brīdī risināju sava telefona problēmas un man iesēdās galvā nez kāpēc telefona abonenti :)
        Nu whatever, lai būtu pieņemsim visu avīžu un žurnālu lasītāji, kas pasūta jebkādu lauksaimniecības izdevumu, tas principā nemaina manu viedokli ne par vienu no augstāk minētajām 2 lietām, ka priekš tīra selekta problēmas info ir par maz un savukārt no prasību viedokļa tas labākajā gadījumā ir pārskats potenciāliem spameriem.

        Atbildēt

        • Ja klients ir pasūtījis to kā funkcionalitāti un tas ir līguma pantā 37.b”, tad rodas jautājums – vai analītiķim ir jāfilozofē par tēmu “a klientam to patiesībā nevajag”, vai analītiķim ir jānostājas miera stājā, jāsaka “jes, ser!”un jānodrošina, lai tas strādā?

          Atbildēt

          • Pirmām kārtām piedāvājumu rakstot nekas nekavē pierakstīt klāt, ka šaubīgas funkcionalitātes piedāvājam risināt mazāk šaubīgā veidā.
            Otrām kārtām, ja esam nonākuši līdz analīzei un uzdevumu izpildei, tad savu mīļāko pieeju reiz es prezentēju šeit:
            http://datubazes.files.wordpress.com/2011/05/g-plivna-sistemanalize.pdf
            Īsumā:
            – Informējam klientu, ka tas nav labi, saprotami izskaidrojot sekas un piedāvājot alternatīvas
            – Dokumentējam informēšanu
            – Ja klients ietiepjas, tad iestājas iepriekš dokumentētās “or face the consequences” sekas, par kurām viņš jau tika brīdināts

  10. 1. No sākuma uzrakstīt bez “In” daļas, apmēram (nepārbaudīju):

    SELECT AbonentsNr, Vards, Uzvards
    FROM Abonents
    Inner Join Abonements On Abonents.AbonentsNr = Abonements.AbonentsNr
    Inner Join Izdevums On Abonements.IzdevumsNr = Izdevums.IzdevumsNr
    Where Izdevums.Tematika = “Lauksaimniecība”
    Group By AbonentsNr, Vards, Uzvards

    2. Pēc tam pareizi indeksi (uz Abonements tabulas- AbonentsNr + IzdevumsNR tādā secībā un IzdevumsNr kā included kolonna, uz Abonents tabulas- AbonentsNr kolonnas). Ja iespējmas- var paskatīties uz filtŗēto indeksu pusi..

    3. Uztaisīt materializēto skatu un noindeksēt to- tātā gadījumā vaicājums būs tik ātri ka ātrāk vairs nevar :). nu updates un insert mazliet cietīs..

    Atbildēt

  11. Vai nu mainīgo tipu neatbilstība, kas liek datu bāzei konvertēt mainīgo tipus, vai kaut kas nepieciešams izpildes procesā tiek neveiksmīgi nolokots. Precīzi, protams, nezinu :)

    Atbildēt

  12. Posted by Dzintars on 05/01/2012 at 09:31

    Vai tik tur nevajag temp tabulu visam kverijam, kas iekavās?

    Atbildēt

  13. indexi (drīkst pievienot?), sagaidāmā insert/select attiecība (vismaz aptuvena)?

    Atbildēt

Mans viedoklis:

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Mainīt )

Twitter picture

You are commenting using your Twitter account. Log Out / Mainīt )

Facebook photo

You are commenting using your Facebook account. Log Out / Mainīt )

Google+ photo

You are commenting using your Google+ account. Log Out / Mainīt )

Connecting to %s

%d bloggers like this: