Arī mani ir sasniegusi sērga par 7 lietām, kas neesot par mani zināmas. Bet nu nekā nebija, es šeit netaisos pagaidām apspriest savas privātās lietas vai kaut ko tādu, kas pārāk tālu attālinās no datubāzēm un ar to saistīto. Ja vien mani nenovedīs līdz tam briestošo priekšvēlēšanu laikā 😉 Bet tagad nē. Tagad Jums būs 7 lietas saistītas ar datubāzes ātrdarbību (faktiski lēndarbību), ar kurām visām es pašlaik cīnos vai man ir nācies cīnīties un par kurām varu izteikties tikai [cenzēts], [cenzēts], [cenzēts].
- Nelietojiet SQL vaicājumos lietotāja definētas (user-defined) funkcijas. Nelietojiet tās lieliem datu apjomiem. Vēl vairāk nelietojiet tās, ja tajās atkal kaut kas tiek lasīts no datubāzes. Un tas attiecas tikpat labi uz Oracle, kā uz SQL serveri. Izbaudu to uz savas ādas gandrīz katru reizi, kad nākas cīnīties ar bremzīgiem Select vaicājumiem. Kāpēc? Oracle tas ir tāpēc, ka vaicājums tiek nosacīti izpildīts zem SQL dziņa (SQL engine), savukārt jebkurš lietotāja definēts funkcijas izsaukums prasa PL/SQL dzini (PL/SQL engine) un mētāšanās no viena uz otru ir relatīvi dārgs process. To pašu esmu novērojis SQL Server, nezinu precīzu tehnisku skaidrojumu, bet tā vien izskatās, ka tur ir kaut kas ļoti līdzīgs.
- Nelasiet no datubāzes liekus datus. Nerakstiet SELECT * no 50 tabulām (jā 50!), rakstiet tikai tās kolonas no tām tabulām, ko Jums vajag. Tas ir pilnīgs ārprāts, ja Jūs atlasat 80 kolonas, jā 80! un pēc tam attēlojat 10. Un tas viss tikai tāpēc, ka šis vaicājums tagad derēs visās 17 vietās, kur Jums kaut ko no tā vajag attēlot. Autors par laimi (viņam) bija jau atlaists, citādi tas noteikti beigtos ar asinsizliešanu 😉
- Nekārtojiet liekus datus. Ja nu ir jākārto (ORDER BY) kādi dati, tad vienmēr raugieties, lai kārtojamo kolonu skaits nebūtu vairāk nekā nepieciešams. Nerakstiet SELECT * ORDER BY <1, 2, 3 kolonas>, ja izrādās, ka * nozīmē 20 kolonas no kurām tālāk Jūs lietojat tikai 10. Katra lieka kolona ir lieki dati, kas datubāzes serverim jātur atmiņā un jāvazā līdz kārtošanas laikā. Tā ir papildus atmiņa, papildus apstrādes laiks un bezjēdzīgi iztērēti resursi.
- Neatlasiet unikālās kolonas (DISTINCT jeb UNIQUE) tikai tāpat vien, drošības pēc. DBVS nezin, ka tas ir tikai tāpat, aiz nekā darīt. Ja tas patiesi ir pārāk bieži nepieciešams, tad diezgan droši tā ir kļūda datubāzes modelī. Kāpēc to nevajag? Tāpēc, ka katrs DISTINCT prasa unikālo ierakstu atlasi un jo vairāk kolonas tiks atlasītas, jo vairāk atmiņas un resursu tam būs nepieciešami. Ja tas ir izdarīts uz ierakstu kopu, kas jau tāpat ir ar unikāliem elementiem, tad tie ir burtiski zemē nomesti CPU cikli, atmiņas operācijas un iespējams pat diska apgriezieni.
- Nerakstiet dinamisko SQLu ar iešūtām mainīgo vērtībām. Datu bāzu vadības sistēmās (Oracle, MS SQL Server), kuru izstrādātāji ir ilgi pūlējušies, lai izveidotu kopīgu atmiņas apgabalu visiem SQL teikumiem (attiecīgi shared pool un procedure cache), maz kas var būt paradoksālāk kā nezinoši vai piedodiet par izteicienu stulbi izstrādātāji, kas katru savu SQL teikumu uzģenerē ar iešūtām mainīgo vērtībām, līdz ar to visus sākotnējos pūliņus izslaukot miskastē. Iešūtas mainīgo vērtības nozīmē praktiski nulles varbūtību, ka izveidotais SQL teikuma izpildes plāns būs lietojams arī nākošajam lietotājam, jo viņa SQL teikums būs gandrīz tāds pats, bet tikai gandrīz. Tajā atšķirsies iešūtie identifikatori vai arī kādi citi mainīgie un hopsā – tas vairs nav tas pats SQL teikums, kuram izpildes plāns jau bija zināms. Tā ir viena no tūkstoš hidras galvām, kam atkal jāpārbauda tiesības uz objektiem, jāģenerē savienojumu iespējamās kombinācijas un viss SQL teikuma izpildes plāns. Sīkums vienam lietotājam, kas reizi pusstundā palaiž SQL teikumu uz 15 minūtēm, bet nežēlīga sāpe kaut vai 10 lietotājiem, kuri katrs izpildītu desmitiem vai pat simtiem SQL teikumu sekundē, ja vien katram SQL teikumam nebūtu analīzes (parse) fāze, kas konstanti aizņem sekundi…
- Nelietojiet procedurālo valodu ciklus, lai apstrādātu ierakstus pa vienam, jo sevišķi, ja iterāciju skaits ir proporcionāls datu apjomam. Visizplatītākā lieta – kursori. Mans mazais skaistais kursoriņš, kurš izpildās zibenīgi uz dažiem ierakstiem, pārvēršas par milzīgu nekustīgu monstru, ja tas tikpat akli dodas caur reālajiem produkcijas miljons ierakstiem. Cikli, kuru iterāciju skaits ir tieši proporcionāls datu daudzumam nav savienojami ar vārdu ātrdarbība. Tie var būt tikai un vienīgi atbilstoši vārdam lēndarbība.
- Testējiet uz reālu datu apjomu, kādu Jūsu sistēma sasniegs pēc gada, diviem. Tas, ka Jūsu vaicājums izstrādes vidē uz 10 ierakstiem atgriež funkcionāli pareizu rezultātu zibenīgi ir nekas. Burtiski nekas. Sliktākajā gadījumā saģenerējiet datus pats, ja esošus reālus daudz nevar dabūt. Jo tad, kad Jūsu vaicājums sastapsies ar miljons ierakstiem, kam nāksies katram izsaukt lietotāja definētu funkciju, nolasīt visu ierakstu 50 kolonas, visu miljonu sakārtot dēļ liekā Distinct, un to darīs vismaz 10 lietotāji vienlaicīgi palaižot jūsu dinamiski ģenerēto SQL teikumu, lūk tad iestāsies brīdis, kad klients pacels cepuri un dosies pie Jūsu konkurentiem. Jo diemžēl lielāks dzelzis nederēs. Neviens dzelzis pie kaut cik ievērojama datu apjoma nespēj pārciest šeit uzskaitīto, tā lai lietotāji nesāktu dusmās vārīties un klusiņām lādēt izstrādātāju.
Man principā diez ko nepatīk visādas ķēdes un piramīdas, galu galā Medofs arī slikti beidza 🙂 , taču gribu pievērst lasītāju uzmanību vienam visnotaļ interesantam un (cerams, ka arī turpmāk) daudzsološam blogam, kurš raksta arī par lietām, kas saistās ar tīmekli un ātrdarbību.
Lasīt arī:
Datubāzes 
jā alx jau pamanīju 🙂
Lieliska pieeja.
Kas ir domāts ar “Nerakstiet dinamisko SQLu ar iešūtām mainīgo vērtībām”?
Katru reizi, kad nepieciešami konkrēti dati mēs tos atlasam. Respektīvi sql teikums var būt ļoti līdzīgs iepriekšējam tik ar citiem mainīgajiem.. Un neder man sql ar iepriekšējiem mainīgajiem, lai gan tas izpildītos ātrāk.. Vai arī es kaut ko ne tā sapratu? Vai arī domāts, lai izmanto procedūras?
Tātad mainīgajā “var” tiek ģenerēts SQL teikums:
var := ‘SELECT * FROM tabula WHERE tab_id = 17’.
Šis ir pavisam cits teikums nekā
var1 := ‘SELECT * FROM tabula WHERE tab_id = 95’.
Un Oraclē izmantojot EXECUTE IMMDIATE var un EXECUTE IMMEDIATE var1 būs 2 dažādi SQL teikumi un iestājas rakstā aprakstītā sliktā situācija.
Tas, ko vajag darīt, ir izmantot vietu priekš mainīgā (place holder), kurā izpiildot dinamisko sqlu izmantojot USING klauzu tiek ielikta konkrētā mainīgā vērtība.
Tātad jāraksta var1 := ‘SELECT * FROM tabula WHERE tab_id = :a’ un izpildot rakstam
EXECUTE IMMEDIATE var1 USING 17 vai
EXECUTE IMMEDIATE var1 USING 95.
Skat sīkāk šeit par Oracle dinamisko SQLu.
Ja nu lieto SQL Server, tad jāizmanto sp_executesql, kurai arī var noradīt parametrus, ko izmantot kā bind mainīgos, sīkāk skat šeit.
Un es nemaz vēl neesmu runājis par to, ka dinamiskais SQL ir fantastiska iespēja iegrūzt iekšā SQL injekciju.
Vai man šo atsauci būtu jāuztver par stafetes kociņu?:)
Par dinamiskajiem… Imho vairumā situāciju vajadzētu pietikt ar prepared sql, kas pie labvēlīgiem apstākļiem gan performē smuki.
@alx
kāpēc nē, uztver kā kociņu 😉
kas attiecas par dinamiskajiem, tad es šeit runāju arī no pl/sql un transact-sql viedokļa, kur tāpat var ģenerēt dinamiskus sql teikumus un dinamiski tos izpildīt 2 dažādos veidos – iešujot mainīgo vērtības, kas ir bad bad bad, un izmantojot mainīgos (bind variable), kas ir korektā pieeja.
[…] aļa kleita Mārcis Manofstyle iFuck Elnv aļa dzīvoklis vairs nepīpē Pbs Bozons Sid.id Bbeate Datu bāzes Dabbler Rendija Toms aļa pointless Herta&Berta Alx Kristaps aļa skujins Neatkarīgais […]
… Nelietojiet procedurālo valodu ciklus, lai apstrādātu ierakstus pa vienam, jo sevišķi, ja iterāciju skaits ir proporcionāls datu apjomam …
A ko tad darīt, ja katru rindiņu vajag aptaustīt, labi ir dažās vidēs ir bulk fetch iespējas (kas var dot grandiozu efektu), bet citeiz tādu iespēju nav
Te rodas jautājums kāpēc vajag aptaustīt un vai to aptaustīšanu var vertikalizēt. “Aptaustīt” parasti nozīmē:
Cikls pa visiem ierakstiemnolasa nākošo ierakstu X
izrēķina X kolonu A pēc izteiksme1
izrēķina X kolonu ...
izrēķina X kolonu N pēc izteiksmeN
koriģē ierakstu X datubāzē
Cikls beidzas
Tātad jāpārdomā vai šo te parasti bremzējošo kodu nevar pārtaisīt par 1 SQL teikumu:
UPDATE tabula
SET kolona A = izteiksme1,…,
kolona N = izteiksmeN
WHERE kursora atlases nosacījumi;
Vai par N SQL teikumiem:
UPDATE tabula
SET kolona A = izteiksme1
WHERE kursora atlases nosacījumi;
…
UPDATE tabula
SET kolona N = izteiksmeN
WHERE kursora atlases nosacījumi;
Tātad jācenšas rēķināt vērtības nevis katram konkrētam ierakstam atkal un atkal, bet vismaz visiem ierakstiem kopā. Atkarībā no aplikācijas loģikas, tas var prasīt kādas pagaidu datu struktūras, f-ju iekodēšanu SQLā utml lietas, bet kārtīgi pārdomājot vairumā gadījumu tā var izdarīt un tas strādās ātrāk. Esmu gatavs ticēt, ka ir gadījumi, kad to tiešām nevar/neatmaksājas darīt, jo:
1) datu apstrāde ir nenormāli sarežģīta un atkarīga no iepriekšējo ierakstu rezultātiem utt.
2) jāapstrādā maz dati
3) jāapstrādā daudz dati, bet tas notiek reti un/vai nevienu neinteresē ātrdarbība.
Tas izriet no SQL pamatpiegājiena – DOMĀT IERAKSTU KOPĀS, NEVIS PA ATSEVIŠĶIEM IERAKSTIEM. Domāt, kā apstrādāt (pievienot, koriģēt) veselu KOPU IERAKSTU REIZĒ, nevis to darīt pa atsevisķiem ierakstiem. Kā jau teicu, tas var prasīt kaut kādas pagaidu struktūras.
Par pēdējo jautājumu uzrakstīju atsevišķu rakstu Paradigmas maiņa – SQL ātrdarbības atslēga
Labs raksts. Vēl labāks būtu bijis, ja tas būtu bijis pozitīvs – respektīvi, ja būtu uzskaitītas lietas, ko vajag ievērot, nevis – ko nevajag. Informācija būtībā tā pati, tikai uzsvars cits.
Piemēram:
“2. Nelasiet no datubāzes liekus datus.”
vietā varētu rakstīt
“2. No datubāzes atlasiet tikai un vienīgi nepieciešamos datus.”
Pozitīvajai pieejai ir vismaz pāris priekšrocības:
1) informācija, kā kaut ko izdarīt pareizi ir daudz vērtīgāka! Pat, ja ir zināms kā kaut ko nevajag darīt, lai izdarītu pareizi, vēl ir jāuzzina arī kā tad to īsti vajag darīt. Savukārt, ja ir zināms kā kaut ko var paveikt pareizi, tad bez informācijas par aplamajām lietām var arī iztikt. 🙂
2) kaut ko izdarīt pareizi parasti var mazāk variantos nekā nepareizi – tātad mazāk lietas ko uzskaitīt
Bavarel, raksts radās spontānas reakcijas rezultātā, jo tajā brīdī es reāli cīnījos ar kodu, kurā bija praktiski visas šeit uzskaitītās problēmas. Mani tas pamatīgi izbesīja un tā radās šis raksts. Komentāru skaits pie tā un arīdzan lasījumu skaits liecina, ka cilvēkiem tas šķiet interesanti 😉 No otras puses tas arī liecina par to, ka cilvēki meklē kaut kādas vienkāršas dažas lietas kuras lietojot/nelietojot viss notiks. Diemžēl realitātē nav tik vienkārši, nevar uzrakstīt dažas fiksētas lietas, ko darīt/nedarīt, un tās kā burvju zāles palīdzēs visur. Bet nu, protams, var mēģināt saskatīt tipiskos gadījumus…
Atvainojos, tādā gadījumā mans komentārs ir nevietā ( tā man laikam tāda kā aroda slimība – esmu skolotājs). Un paldies par šo resursu, šeit ir vērtīga informācija un latviski – salikums, kas nav bieži sastopams IT jomā.
P.S.Īstenībā es nesaprotu, kur rodas visi tie “aplamā koda speciālisti”, vēl vairāk, kas tādiem vispār algu maksā?
Pirms iestājās tā saucamā Dižķibele (šis vārds kā minimums ir ņirdzīgs), bija izteikts IT darba spēka trūkums, par laimi ne tik briesmīgs kā celtniecībā, kur vakardienas pavārs prasīja 1K uz rokas un tas tikai iesākumam, bet trūkums bija. Otra lieta ir tā, ka pat ilgi strādājoši un it kā formāli pieredzējuši cilvēki, ieslīgst līmenī, kas ir sasniegts pirms N gadiem un tur arī sēž. Kaut kā jau tas veidotais kods parasti strādā un priekš pāris testpiemēriem funkcionāli pat it kā dod pareizu rezultātu.
Un vēl viena lieta – lai varētu/nevarētu algu maksāt, ir jābūt arī gana kompetentai priekšniecībai, kas spēj pamatot kāpēc algu nemaksā, vai arī spēj pamatoti prasīt atbilstošu rezultātu.