Sql Where Optional Nosacījumu Optimizācija

[Vilx-]

Probleema taada - ir projekts, kuraa tiek izmantota Microsoft SQL Server 2005 DB. Visi pieprasiijumi no aplikaacijas notiek caur storeetajaam proceduuraam - aatrdarbiibas un droshiibas deelj. Plus arii viss framework ir buuveets balstoties uz to, ka viss ir storeetajaas proceduuraas, taapeec to mainiit nevar. Storeetaas proceduuras meedz buut 3 veidu - Get, Save un Delete. Save un Delete ir OK. Probleemas ir ar GET. Tajaas meedz buut iekshaa katraa pa 1 SELECT teikumam, kursh tur atselektee taadus vai citaadus laukus no kaukaadaam tabulaam, un beigaas filtree ar WHERE nosaciijumu. Filtreeshana notiek parasti peec vienas vai vairaakaam kolonnaam. Filtreejamaas veertiibas tiek padotas ar parametriem. Ja parametra veertiiba ir NULL (defaultaa veertiiba), taatad parametrs nav noraadiits, un taatad peec shiis kolonnas (parametra) nefiltreejam. Ja parametrs nav NULL, tad filtreejam peec taa. Nu, piemeeram:

CREATE PROCEDURE MansSelekts
@id int = null,
@name nvarchar(250) = null,
@code nvarchar(250) = null
AS
BEGIN

SELECT
ID, NAME, CODE
FROM
MyClassifier
WHERE
(@id is null or ID=@id) and
(@name is null or NAME=@name) and
(@code is null or CODE=@code)

END

Viss jau buutu jauki, ja vien vinjsh shaadeejaadi arii vareetu izmantot indeksus. Piemeeram, augshupmineetajaa piemeeraa ID ir primaaraa atsleega. Bet pat tad, ja @id ir noraadiits, un @name un @code ir NULL, es tik un taa ieguustu pilnu tabulas paarlasi! Pie vainas, protams, ir "or", tachu kaa to apiet? Jo storeetajaas proceduuraas jau dinamiski kverijus taisiit nevar nedodot juuzerim pilnas SELECT tiesiibas uz tabulaam (cik zinu), bet cits variants man praataa nenaak... Arii konstrukcija

isnull(@id, ID) = ID

dod to pashu full table scan. Vareetu jau raxtiit vairaakus SELECTus, un izmantot vajadziigo atkariibaa no padoto parametru komplekta, tachu tas straadaa tikai pie neliela parametru daudzuma. Jau ar shiem 3 parametriem vajadzeetu 8 kverijus, kas buus pie 7 parametriem?!

 

Taatad, veelreiz - vajag, lai viss buutu storeetajaas proceduuraas un lai ar katru proceduuru vareetu filtreet peec vienas vai vairaakaam kolonnaam atkariibaa no padoto parametru komplekta.

[ventolin]

 (@id is null XOR ID=@id) 

 

Moš šitā varētu nostrādāt? :|

[Vilx-]

1. MSSQL nav XOR operatora.

2. Pat ja buutu, tas diez vai nostraadaatu, jo tas pats OR jau vien sanaaktu.

 

Citas idejas? :-|

[ventolin]

Nu, es tik pa MySQL ņemos, bet varbūt kaut kas tāds nostrādās?

 

ID = IF(@id IS NULL,ID,@id)

 

P.S. Es tik nezinu, kā ietekmēs ātrdarbību tas ID == ID

[Guest bubu]

IFs nu noteikti nemācēs izmantot indeksus. Arī MySQLā.

[Vilx-]

Nee, tas arii nestraadaas. MSSQL2005 ir taada lieta kaa isnull, bet arii ar to nekas labaaks nav. Pat veel ljaunaak kljuust - tiek izveidota temporary tabula, kuraa tiek sareekjinaatas visas isnull veertiibas, tad shai tabulai tiek veikts full-scan, un joinoti klaat ieraxti (shoreiz jau peec indexa) no iistaas tabulas.

[Borja]

Apmeeram kautko taadu iedomaajos:

declare c1 cursor for
SELECT ID, NAME, CODE
FROM MyClassifier
WHERE (ID=@id or NAME=@name or CODE=@code)

open c1

if @id != null 
DELETE  from c1 WHERE (ID!=@id)
if @name!=null
DELETE  from c1 WHERE (NAME!=@name)
if @code!=null
DELETE  from c1 WHERE (CODE!=@code)

Gan neesmu paarliecinaats vai no taa buus vajadziigais efekts, skaties pats.

[japets]

mosh "!(@id != ID)"

Neesmu paarliecinaats, kas notikaas ar not pie indeksiem

[Vilx-]

Gandriiz jau! Tikai MSSQL to maaceeja nooptimizeet par ADDR_ID = @id, un uz NULL es dabuuju tukshu kopu.

[japets]

Nav man pieredzes ar MSSQL un nav arii vinjsh pieejams.

Bet postgresql gadiijumaa, tas:

(''='' or name='asdf')

speej nooptimizeet un nosaciijumu izmet,

(null is null or name='asdf')

nespeej (negrib?) nooptimizeet un nosaciijums paliek, un index netiek izmantots,

('a'='' or name='asdf')

speej nooptimizeet un izmanto indeksu,

('a' is null or name='asdf')

nespeej (negrib?) nooptimizeet un index netiek izmantots.

Iespeejams, ka ar MSSQL ir liidziigi.

Taadaa gadiijumaa Tev atliek parametru veertiibas no null uztaisiit par kaut kaadaam pieljaujamaam konstanteem. Lai nu kaa, bet es to nosauktu par workaround.

[Vilx-]

Kameer runa ir par konstanteem, tikmeer viss ir OK. Tad MSSQL maak nooptimizeet arii 1 is null or ID=@id. Tachu, pameegjini tur ielikt parametru! Viss aiziet sviestaa.

[Ronalds]

Strādāju ar firebird, bet nu pamatnostādnēm jābūt līdzīgi ....

 

Selec caur procedūru - hmmm... Nu ātrdarbību tas grauj nemērā ......... Ja nav kaut kādu mega drošības pārbaužu (kuras ar where nevar realizēt) es tomēr view tasītu......

(man arī viens projekts ir balstīts uz select caur procedūrām - tagad pārtaisu uz view jo bremzē nemērā, kad bāzes izmēri pieaug....)

 

Ja no tomēr storētā procedūra tad

http://www.databasejournal.com/features/ms...cle.php/1438931

 

Liekas dinamiskā sql pieprasījuma ģenerācija varētu derēt šajā gadījumā.

 

Vai arī ieviest papildus paramaetru

 

WHERE ( CODE=@code or @CODE_CHECK=1)

 

Ja CODE_CHECK=1 tad parametru CODE neņem vērā.

Tomēr cik zinu, tad SQL parsēšana notiek procedūras saglabāšanas brīdī... Un šis nosacījums tiks pārbaudīts vienmēr, arī tad, kad to nevajag darīt.....

[Vilx-]

Nu, uz MSSQL nekaada bremze deelj storeetajaam proceduuraam nav noveerota. Bet, diemzjeel, Tava ideja par papildmainiigo nenostraadaaja. Tomeer doma par viewiem vareetu buut apdomaajama.

 

Nu, nevienam citam nav kaadas labas idejas par optional SQL WHERE parametriem?

[Guest PeterisP]

Varbuut nostraadaa taada fishka -

 

where ID like

case when @id is null then '*' else @id end

[VIL]

Man ir Oracle pieredze. Tajā bāzē uzdevumu risināt palīdz 3 lietas:

a) dinamiskais SQL (līmējam kritērijus klāt tikai tad, kad tie ir vajadzīgi)

b) hinti (skaidri norādām, kādus indeksus izmantot, bet use at your own risk)

c) procedūras koda strukturēšana.

 

Vairāk par c:

pieņemu, ka Tev ir zināms, kādi selekti tiks dzenāti 80% laika. Uz tiem arī strādājam, t.i., procedūrā ar IFiem pārbaudām padotos mainīgos, atkarībā no kuru vērtībām nododam vadību optimizētam selektam.

Ko darīt ar pārējiem 20%? Skatāmies darba specifiku. Ja tas Tev ir webam, pieņemu, ka strādāsi ar maziem datu apjomiem (rakstu skaits <=20 000). Tātad izpildām selektu bez NULL kritērijiem, rezultātu iemetam temporārajā tabulā, pēc tam fullskanējam temptabulu pēc NULL kritērijiem. Šāda metode ir efektīva, ja kritērija lauki ir indeksēti un rezultāta kopa ir relatīvi maza.

 

Ko darīt, ja atlasīšana pēc NULL ir norma nevis izņēmums:

denormalizēt tabulas

--> ieviešam laukus NAME_IS_NULL varchar2(1) check (NAME_IS_NULL IN ('J','N'))

--> ieviešam laukus DATE_FROM_IS_NULL varchar2(1) check (DATE_FROM_IS_NULL IN ('J','N'))

Indeksējam šos laukus. Rezultātā iegūstam iespēju IS_NULL pārbaudes veikt jau pēc indeksa. Ieguvums nebūs liels, ja NULL vērtības tabulā ir lielā pārsvarā.

 

Oracle vienā selektā izmanto vienu tabulas indeksu (pieņemu, ka MSSQL ir līdzīgi), tādēļ ir jāveido saliktie indeksi visām populārākajām kombinācijām (80% pieprasījumu). Ja nevar savādāk, tad kods jāstrukturē un jāhinto.

[Vilx-]

Nu, par struktureeshanu njemot veeraa biezjaak izpildiitos kverijus es arii jau biju izdomaajis. Dazjaas kritiskaakaas vietaas (ar 30'000 un uz augshu ieraxtiem) taa tagad arii ir. Tachu es biju cereejis, ka varbuut ir kas labaaks, lai nav taa jachakareejas. Redz - ja peekshnji tabulai naak klaat veel 1 lauks, tad tas ir jaapieliek visaas kverija versijaas.

 

Dinamiskie kveriji noteikti atkriit - lai tos taisiitu, juuzerim ir jaadod select tiesiibas pa tiesho uz tabulu. Tachu viens no storeeto proceduuru meerkjiem ir tieshi taads - lai nedotu juuzerim tiesiibas selekteet ko un kaa ienaak praataa, bet tikai to, kas vinjam ir atljauts caur storeetajaam proceduuraam.

 

Biki veel patesteejoties kljuva skaidrs, ka full-scan tabulai ar 30'000K ierakstiem nav neko juutami leenaaka par index seek. Tomeer esmu paarliecinaats, ka eventuaali pienaaks taads briidis, kad tas jau kljuus juutami. Laikam jau tad vispraatiigaak ir dazjaas shajaas vietaas izmantot struktureeshanu, bet paareejaas vietaas vienkaarshi atstaat kaa ir.

[Ronalds]

Dinamiskie kveriji noteikti atkriit - lai tos taisiitu, juuzerim ir jaadod select tiesiibas pa tiesho uz tabulu. Tachu viens no storeeto proceduuru meerkjiem ir tieshi taads - lai nedotu juuzerim tiesiibas selekteet ko un kaa ienaak praataa, bet tikai to, kas vinjam ir atljauts caur storeetajaam proceduuraam.

 

 

 

Hmm....

 

Kāpēc neder dinamiskais SQL ? Iekš storētās procedūras iekš teksta mainīgā saģenerējam sql pieprasījumu un tur pat izpildam. Vai tad šādai darbībai vajag lai lietotājām būtu select tiesības uz tabulu?

[Vilx-]

Nuja, ka vajag! Tur jau tas jox! Citaadi taa arii dariitu.

[xor]

elementaari - pieliec papildus indexus tai tabulai, ar indexa nosaciijumu => WHERE (lauks is null)

piemeeram PostgreSQL to var realizeet shaadi (manas datubaazes piemeers):

 

CREATE INDEX "klasifikatori_isnull_idx" ON "public"."klasifikatori"
USING btree ("_id")
WHERE (_id IS NULL);

 

paarbaudiiju - index darbojas!

[Vilx-]

Err... nesapratu? Varbuut, ka MSSQL taada fiicha nav, vai arii tas straadaa citaadi. Bet pat ja taa - es nesaprotu, kaa tas var paliidzeet? Un - ko tas vispaar noziimee? :oops: