pirmdiena, 2012. gada 6. augusts

Vai redzi problēmu? (ar atbildi)

Ir divas tabulas:
Create Table dbo.T1
(
    T1ID int primary key identity,
    Value varchar(50)
)
Go
Create Table dbo.T2
(
    T2ID int primary key identity,
    T1ID int foreign key references dbo.T1 (T1ID),
    Value char(5000)
)
Ar datiem (T1 = 3 ieraksti, T2 = 10000 ieraksti):
-- SQL 2008 sintakse
set NoCount On;
Insert Into T1 (Value) Values ('jo'), ('jo 2'), ('jojo 2');
declare @i int = 0;
while @i < 10000
Begin
    Insert into dbo.T2 Values(1, @i);
    Set @i += 1;
End
Izpildu šādu komandu komplektu:
Insert Into T1 Values ('Jauna vērtība');
Delete T1 Where Value = 'Jauna vērtība';
Vai redzi problēmu??




Atbilde (publicēta 2012-08-06)
Patiesībā var saskatīt pat vairākas problēmas.
  • Galvenā problēma: dzēšot T1 tabulas rindu (1 no 4 ierakstiem), tiek pilnībā pārlasīta visa tabula T2 (10 000 ieraksti, gandrīz 80 MB datu). Tabulu T2 speciāli veidoju tā, lai tā būtu relatīvi liela.

    Ārējās atslēgas esamība nozīmē datu integritāti, nevis indeksa izveidi, par to plašāk var lasīt
    Ierobežojumi (Constraints). Problēmu ir viegli konstatēt izmantojot Vaicājuma IO statistiku.

    Laižot piemēru visdrīzāk problēmu nejūt, jo dati atrodas operatīvajā atmiņā. Lai nosimulētu situāciju kad dati tiek nolasīti no diska, pēc insert komandām var izmantot komandas:
    CHECKPOINT; -- saglabā datus fiziski uz diska
    DBCC DROPCLEANBUFFERS; -- iztīra buferi! Nav labi darīt uz produkcijas servera
    Problēmu varētu atrisināt izveidojot indeksu:
    create index ix_t1id on dbo.t2 (t1id asc)
  • Citas lietas, ko varēja pamanīt:
    • Tabulā T2 kolonna "Value" datu tips ir char(5000), bet reāli glabā int vērtību (šeit, protams, tas ir speciāli, lai tabulas izmērs būtu lielāks);
    • Komentāros bija piezīme, ka daudz insert komandas. Taisnība, ātrāka būtu datu ielāde, ja tiktu uzreiz ielādēta "kaudze" ar rindām, piemēram (arī šo iespējams paātrināt):
      Insert into dbo.T2
          Select Top (10000) 1,
              ROW_NUMBER() Over (Order By so1.object_id)
          From sys.all_objects so1
              cross join sys.all_objects so2
    • Labā prakse ir norādīt kolonnas aiz Insert Into dbo.T2; Labāk būtu:
      Insert into dbo.T2 (T1ID, Value) Values(1, @i);
    • Ja kaut kas ir vēl darāms ar ievietoto rindu, tad labāk iegūt tās identifikatoru- Kāda vērtība tika ielikta Identity laukā? (nevis atlasīt rindu pēc ievietotās vērtības, kas šajā gadījumā izsauc pilnu tabulas T1 pārlasīšanu):
      Insert Into T1 Values ('Jauna vērtība');
      Delete T1 Where T1ID = SCOPE_IDENTITY();
Galvenas ko gribēju teikt- ārējā atslēga (foreign key) nav indekss. 

Un paldies tiem, kas padomāja un īpaši tiem, kas arī atbildēja :).

6 komentāri:

  1. es neredzu. pat nepaslinkoju izpildīt visus skriptus. bez pārsteigumiem, izpildījās, neko neparastu nepamanīju.

    AtbildētDzēst
    Atbildes
    1. Vēl kādu brīsniņu pagaidīšu līdz atbildēšanai, varbūt tomēr kāds variants izskanēs.

      Dzēst
  2. Es ne par tēmu - par SQL server neko daudz nezinu, bet tik un tā - 10 000 vērtību pievienošanai jāizmanto 10 000 query!? Iekšēji apraudājos.

    AtbildētDzēst
    Atbildes
    1. Šajā gadījumā tā ir testa datu ielāde ilustrācijai, tas šķiet pieņemami.

      Bet tās 10 000 rindas ir virziens kurā var domāt, mēģinot ieraudzīt problēmu.

      Dzēst
  3. emm, pareizā atbilde būs?

    AtbildētDzēst
    Atbildes
    1. Dzēšot T1 tabulas rindu (1 no 4 ierakstiem), tiek pilnībā pārlasīta visa tabula T2 (10 000 ieraksti, gandrīz 80 MB datu).

      Tā arī ir atbilde, ko uzskatu par "pareizo", lai arī īpašos gadījumos tā var arī nebūt "pareizā" atbilde.

      Dzēst