{"id":75530,"date":"2020-03-26T19:42:23","date_gmt":"2020-03-26T17:42:23","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/vyvod-rezultatov-poiska-i-problemy-s-proizvoditelnostyu"},"modified":"2020-03-26T19:42:23","modified_gmt":"2020-03-26T17:42:23","slug":"vyvod-rezultatov-poiska-i-problemy-s-proizvoditelnostyu","status":"publish","type":"post","link":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/vyvod-rezultatov-poiska-i-problemy-s-proizvoditelnostyu","title":{"rendered":"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Jedn\u00edm z b\u011b\u017en\u00fdch sc\u00e9n\u00e1\u0159\u016f ve v\u0161ech zn\u00e1m\u00fdch aplikac\u00edch je vyhled\u00e1v\u00e1n\u00ed dat podle ur\u010dit\u00fdch krit\u00e9ri\u00ed a jejich zobrazen\u00ed v \u010diteln\u00e9 podob\u011b. Mohou zde b\u00fdt i dal\u0161\u00ed mo\u017enosti jako t\u0159\u00edd\u011bn\u00ed, seskupov\u00e1n\u00ed a str\u00e1nkov\u00e1n\u00ed. \u00dakol je na prvn\u00ed pohled trivi\u00e1ln\u00ed, ale p\u0159i jeho \u0159e\u0161en\u00ed d\u011bl\u00e1 \u0159ada v\u00fdvoj\u00e1\u0159\u016f chyby, kter\u00e9 n\u00e1sledn\u011b ovliv\u0148uj\u00ed v\u00fdkon. Pokus\u00edme se prozkoumat r\u016fzn\u00e9 varianty \u0159e\u0161en\u00ed tohoto \u00fakolu a formulovat doporu\u010den\u00ed pro v\u00fdb\u011br nejefektivn\u011bj\u0161\u00ed implementace.<\/p>\n<p><img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/364ab29c4a6117ff441b933a38ec8401.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>Varianta str\u00e1nkov\u00e1n\u00ed #1<\/h2>\n<p>\nNejjednodu\u0161\u0161\u00ed varianta, kter\u00e1 m\u011b napadne, je str\u00e1nkov\u00e1n\u00ed v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed v jeho nejklasi\u010dt\u011bj\u0161\u00ed podob\u011b.<\/p>\n<p><img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/4957d9ad21a8a0c70390b224fe76cb33.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nP\u0159edpokl\u00e1d\u00e1me, \u017ee v aplikaci se pou\u017e\u00edv\u00e1 rela\u010dn\u00ed datab\u00e1ze. V tomto p\u0159\u00edpad\u011b pro zobrazen\u00ed informac\u00ed v takov\u00e9 podob\u011b bude pot\u0159eba prov\u00e9st dva SQL dotazy:<\/p>\n<ul>\n<li>Z\u00edskat \u0159\u00e1dky pro aktu\u00e1ln\u00ed str\u00e1nku.<\/li>\n<li>Spo\u010d\u00edtat celkov\u00fd po\u010det \u0159\u00e1dk\u016f odpov\u00eddaj\u00edc\u00edch vyhled\u00e1vac\u00edm krit\u00e9ri\u00edm \u2014 to je pot\u0159eba pro zobrazen\u00ed str\u00e1nek.<\/li>\n<\/ul>\n<p>\nPod\u00edvejme se na prvn\u00ed dotaz na p\u0159\u00edkladu testovac\u00ed MS SQL datab\u00e1ze <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Microsoft\/sql-server-samples\/releases\/download\/adventureworks\/AdventureWorks2016_EXT.bak\">AdventureWorks <\/a><\/noindex>pro server 2016. Pro tento \u00fa\u010del budeme pou\u017e\u00edvat tabulku Sales.SalesOrderHeader:<\/p>\n<pre><code class=\"sql\">SELECT * FROM Sales.SalesOrderHeader\nORDER BY OrderDate DESC\nOFFSET 0 ROWS\nFETCH NEXT 50 ROWS ONLY\n<\/code><\/pre>\n<p>\nV\u00fd\u0161e uveden\u00fd dotaz vr\u00e1t\u00ed prvn\u00edch 50 objedn\u00e1vek ze seznamu, se\u0159azen\u00fdch podle data p\u0159id\u00e1n\u00ed, jin\u00fdmi slovy \u2014 50 posledn\u00edch objedn\u00e1vek.<\/p>\n<p>Proveden\u00fd dotaz se rychle vykon\u00e1v\u00e1 na testovac\u00ed datab\u00e1zi, ale pod\u00edvejme se na pl\u00e1n vykon\u00e1n\u00ed a statistiku vstupu a v\u00fdstupu:<\/p>\n<p><img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/248e4b64593c7000216b46777183d639.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<pre><code class=\"plaintext\">Tabulka 'SalesOrderHeader'. Po\u010d\u00edt\u00e1n\u00ed skenov\u00e1n\u00ed 1, logick\u00e9 \u010dten\u00ed 698, fyzick\u00e9 \u010dten\u00ed 0, p\u0159e\u010dten\u00ed p\u0159edem 0, logick\u00e9 \u010dten\u00ed lob 0, fyzick\u00e9 \u010dten\u00ed lob 0, p\u0159e\u010dten\u00ed p\u0159edem lob 0.<\/code><\/pre>\n<p>\n<i>Z\u00edskat statistiku vstupu\/v\u00fdstupu pro ka\u017ed\u00fd dotaz lze proveden\u00edm p\u0159\u00edkazu SET STATISTICS IO ON v prost\u0159ed\u00ed vykon\u00e1v\u00e1n\u00ed dotaz\u016f.<\/i><\/p>\n<p>Jak vidno z pl\u00e1nu vykon\u00e1n\u00ed, nejn\u00e1ro\u010dn\u011bj\u0161\u00ed je t\u0159\u00edd\u011bn\u00ed v\u0161ech \u0159\u00e1dk\u016f z p\u016fvodn\u00ed tabulky podle data p\u0159id\u00e1n\u00ed. A probl\u00e9m je v tom, \u017ee \u010d\u00edm v\u00edce \u0159\u00e1dk\u016f se v tabulce objev\u00ed, t\u00edm n\u00e1ro\u010dn\u011bj\u0161\u00ed bude t\u0159\u00edd\u011bn\u00ed. V praxi by se takov\u00e9mu situac\u00edm m\u011blo vyh\u00fdbat, proto p\u0159id\u00e1me index na datum p\u0159id\u00e1n\u00ed a pod\u00edv\u00e1me se, zda se zm\u011bnilo spot\u0159ebov\u00e1n\u00ed zdroj\u016f:<\/p>\n<p><img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/a33e1eadf6f8f8b516b9bb834ad7ad86.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<pre><code class=\"plaintext\">Tabulka 'SalesOrderHeader'. Po\u010d\u00edt\u00e1n\u00ed skenov\u00e1n\u00ed 1, logick\u00e9 \u010dten\u00ed 165, fyzick\u00e9 \u010dten\u00ed 0, p\u0159e\u010dten\u00ed p\u0159edem 5, logick\u00e9 \u010dten\u00ed lob 0, fyzick\u00e9 \u010dten\u00ed lob 0, p\u0159e\u010dten\u00ed p\u0159edem lob 0.\n<\/code><\/pre>\n<p>\nJe z\u0159ejm\u00e9, \u017ee se to v\u00fdrazn\u011b zlep\u0161ilo. Ale byly v\u0161echny probl\u00e9my vy\u0159e\u0161eny? Zm\u011b\u0148me dotaz na hled\u00e1n\u00ed objedn\u00e1vek, jejich\u017e celkov\u00e1 cena zbo\u017e\u00ed p\u0159ekra\u010duje 100 dolar\u016f:<\/p>\n<pre><code class=\"sql\">SELECT * FROM Sales.SalesOrderHeader\nWHERE SubTotal &gt; 100\nORDER BY OrderDate DESC\nOFFSET 0 ROWS\nFETCH NEXT 50 ROWS ONLY\n<\/code><\/pre>\n<p>\n<img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/e057bfc89e11a31c6d2855b93ec3c747.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<pre><code class=\"plaintext\">Tabulka 'SalesOrderHeader'. Po\u010det skenov\u00e1n\u00ed 1, logick\u00e9 \u010dten\u00ed 1081, fyzick\u00e9 \u010dten\u00ed 0, p\u0159e\u010dten\u00ed dop\u0159edu 0, logick\u00e9 \u010dten\u00ed lob 0, fyzick\u00e9 \u010dten\u00ed lob 0, p\u0159e\u010dten\u00ed dop\u0159edu lob 0.<\/code><\/pre>\n<p>\nM\u00e1me legra\u010dn\u00ed situaci: pl\u00e1n dotazu nen\u00ed o moc hor\u0161\u00ed ne\u017e ten p\u0159edchoz\u00ed, ale skute\u010dn\u00fd po\u010det logick\u00fdch \u010dten\u00ed je t\u00e9m\u011b\u0159 dvakr\u00e1t vy\u0161\u0161\u00ed ne\u017e p\u0159i pln\u00e9m skenov\u00e1n\u00ed tabulky. \u0158e\u0161en\u00ed existuje \u2014 pokud z ji\u017e existuj\u00edc\u00edho indexu vytvo\u0159\u00edme slo\u017een\u00fd a jako druh\u00e9 pole p\u0159id\u00e1me celkovou cenu zbo\u017e\u00ed, op\u011bt z\u00edsk\u00e1me 165 logick\u00fdch \u010dten\u00ed:<\/p>\n<pre><code class=\"sql\">CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);\n<\/code><\/pre>\n<p>\nTuto s\u00e9rii p\u0159\u00edklad\u016f lze je\u0161t\u011b dlouho pokra\u010dovat, ale dv\u011b hlavn\u00ed my\u0161lenky, kter\u00e9 zde chci vyj\u00e1d\u0159it, jsou:<\/p>\n<ul>\n<li>P\u0159id\u00e1n\u00ed jak\u00e9hokoli nov\u00e9ho krit\u00e9ria nebo po\u0159ad\u00ed \u0159azen\u00ed do vyhled\u00e1vac\u00edho dotazu m\u016f\u017ee m\u00edt z\u00e1sadn\u00ed vliv na rychlost jeho vykon\u00e1n\u00ed.<\/li>\n<li>Ale pokud pot\u0159ebujeme na\u010d\u00edst pouze \u010d\u00e1st dat a ne v\u0161echny v\u00fdsledky spl\u0148uj\u00edc\u00ed podm\u00ednky vyhled\u00e1v\u00e1n\u00ed \u2014 existuje mnoho zp\u016fsob\u016f, jak takov\u00fd dotaz optimalizovat.<\/li>\n<\/ul>\n<p>\nNyn\u00ed se p\u0159esuneme k druh\u00e9mu dotazu, zm\u00edn\u011bn\u00e9mu na sam\u00e9m za\u010d\u00e1tku \u2014 k tomu, kter\u00fd po\u010d\u00edt\u00e1 po\u010det z\u00e1znam\u016f, kter\u00e9 spl\u0148uj\u00ed vyhled\u00e1vac\u00ed krit\u00e9rium. Vezm\u011bme stejn\u00fd p\u0159\u00edklad \u2014 hled\u00e1n\u00ed objedn\u00e1vek, kter\u00e9 stoj\u00ed v\u00edce ne\u017e 100 dolar\u016f:<\/p>\n<pre><code class=\"sql\">SELECT COUNT(1) FROM Sales.SalesOrderHeader\nWHERE SubTotal &gt; 100\n<\/code><\/pre>\n<p>\nP\u0159i existenci slo\u017een\u00e9ho indexu, uveden\u00e9ho v\u00fd\u0161e, z\u00edsk\u00e1me:<\/p>\n<p><img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/7beab0c83a50e2d68a83a965df555ec9.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<pre><code class=\"plaintext\">Tabulka 'SalesOrderHeader'. Po\u010d\u00edt\u00e1n\u00ed skenov\u00e1n\u00ed 1, logick\u00e9 \u010dten\u00ed 698, fyzick\u00e9 \u010dten\u00ed 0, p\u0159e\u010dten\u00ed p\u0159edem 0, logick\u00e9 \u010dten\u00ed lob 0, fyzick\u00e9 \u010dten\u00ed lob 0, p\u0159e\u010dten\u00ed p\u0159edem lob 0.<\/code><\/pre>\n<p>\nTo, \u017ee dotaz proch\u00e1z\u00ed cel\u00fdm indexem, nen\u00ed p\u0159ekvapuj\u00edc\u00ed, proto\u017ee pole SubTotal nen\u00ed na prvn\u00ed pozici, a proto dotaz nem\u016f\u017ee toho vyu\u017e\u00edt. Probl\u00e9m se \u0159e\u0161\u00ed p\u0159id\u00e1n\u00edm dal\u0161\u00edho indexu na pole SubTotal, a nakonec d\u00e1v\u00e1 pouze 48 logick\u00fdch \u010dten\u00ed.<\/p>\n<p>M\u016f\u017eeme uv\u00e9st je\u0161t\u011b n\u011bkolik p\u0159\u00edklad\u016f dotaz\u016f na po\u010d\u00edt\u00e1n\u00ed, ale podstata z\u016fstane stejn\u00e1: <b>na\u010dten\u00ed porce dat a v\u00fdpo\u010det celkov\u00e9ho po\u010dtu \u2014 to jsou dva z\u00e1sadn\u011b odli\u0161n\u00e9 dotazy<\/b>, a ka\u017ed\u00fd vy\u017eaduje sv\u00e1 opat\u0159en\u00ed pro optimalizaci. Obecn\u011b plat\u00ed, \u017ee nenajdeme kombinaci index\u016f, kter\u00e1 by stejn\u011b dob\u0159e fungovala pro oba dotazy.<\/p>\n<p>Jedn\u00edm z d\u016fle\u017eit\u00fdch po\u017eadavk\u016f, kter\u00e9 je t\u0159eba up\u0159esnit p\u0159i v\u00fdvoji takov\u00e9ho vyhled\u00e1vac\u00edho \u0159e\u0161en\u00ed, je, zda je pro podnik skute\u010dn\u011b d\u016fle\u017eit\u00e9 vid\u011bt celkov\u00fd po\u010det nalezen\u00fdch objekt\u016f. \u010casto tomu tak nen\u00ed. A navigace na konkr\u00e9tn\u00ed \u010d\u00edsla str\u00e1nek je podle m\u00e9ho n\u00e1zoru \u0159e\u0161en\u00ed s velmi \u00fazkou oblast\u00ed pou\u017eit\u00ed, proto\u017ee v\u011bt\u0161ina sc\u00e9n\u00e1\u0159\u016f s str\u00e1nkov\u00e1n\u00edm vypad\u00e1 jako \u201ep\u0159ej\u00edt na dal\u0161\u00ed str\u00e1nku\u201c.<\/p>\n<h2>Varianta str\u00e1nkov\u00e1n\u00ed #2<\/h2>\n<p>\nP\u0159edpokl\u00e1dejme, \u017ee u\u017eivatel\u00e9 necht\u011bj\u00ed zn\u00e1t celkov\u00fd po\u010det nalezen\u00fdch objekt\u016f. Pokus\u00edme se zjednodu\u0161it vyhled\u00e1vac\u00ed str\u00e1nku:<\/p>\n<p><img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/66f5ee6dc1a52f14e55ae31e05c502b2.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nVe skute\u010dnosti se zm\u011bnilo jen to, \u017ee nen\u00ed mo\u017en\u00e9 p\u0159ech\u00e1zet na konkr\u00e9tn\u00ed \u010d\u00edsla str\u00e1nek, a nyn\u00ed tato tabulka pro zobrazen\u00ed nemus\u00ed v\u011bd\u011bt, kolik jich celkem m\u016f\u017ee b\u00fdt. Ale vznik\u00e1 ot\u00e1zka \u2014 jak se tabulka dozv\u00ed, zda jsou data pro dal\u0161\u00ed str\u00e1nku (aby spr\u00e1vn\u011b zobrazila odkaz \u201eDal\u0161\u00ed\u201c)?<\/p>\n<p>Odpov\u011b\u010f je velmi jednoduch\u00e1: m\u016f\u017eete na\u010d\u00edst z datab\u00e1ze o jeden z\u00e1znam v\u00edce, ne\u017e je t\u0159eba pro zobrazen\u00ed, a p\u0159\u00edtomnost tohoto \u201edal\u0161\u00edho\u201c z\u00e1znamu uk\u00e1\u017ee, zda je dal\u0161\u00ed v\u00e1rka. T\u00edmto zp\u016fsobem bude pro z\u00edsk\u00e1n\u00ed jedn\u00e9 str\u00e1nky dat t\u0159eba prov\u00e9st pouze jeden dotaz, co\u017e v\u00fdrazn\u011b zlep\u0161\u00ed v\u00fdkon a usnadn\u00ed podporu takov\u00e9 funk\u010dnosti. M\u011bl jsem v praxi p\u0159\u00edpad, kdy odm\u00edtnut\u00ed po\u010d\u00edt\u00e1n\u00ed celkov\u00e9ho po\u010dtu z\u00e1znam\u016f zrychlilo v\u00fdstup v\u00fdsledk\u016f 4-5kr\u00e1t.<\/p>\n<p>Pro tento p\u0159\u00edstup existuje n\u011bkolik variant u\u017eivatelsk\u00e9ho rozhran\u00ed: tla\u010d\u00edtka \u201ezp\u011bt\u201c a \u201evp\u0159ed\u201c, jak je uvedeno v\u00fd\u0161e, tla\u010d\u00edtko \u201ena\u010d\u00edst v\u00edce\u201c, kter\u00e9 jednodu\u0161e p\u0159id\u00e1 novou v\u00e1rku do zobrazen\u00fdch v\u00fdsledk\u016f, a \u201enekone\u010dn\u00e9 scrollov\u00e1n\u00ed\u201c, kter\u00e9 funguje na principu \u201ena\u010d\u00edst v\u00edce\u201c, ale sign\u00e1lem pro z\u00edsk\u00e1n\u00ed dal\u0161\u00ed v\u00e1rky je proch\u00e1zen\u00ed u\u017eivateli v\u0161ech zobrazen\u00fdch v\u00fdsledk\u016f a\u017e na konec. A\u0165 ji\u017e je vizu\u00e1ln\u00ed \u0159e\u0161en\u00ed jak\u00e9koli, princip v\u00fdb\u011bru dat z\u016fst\u00e1v\u00e1 stejn\u00fd.<\/p>\n<h2>Nuance implementace str\u00e1nkov\u00e1n\u00ed<\/h2>\n<p>\nVe v\u0161ech p\u0159\u00edkladech dotaz\u016f uveden\u00fdch v\u00fd\u0161e se pou\u017e\u00edv\u00e1 p\u0159\u00edstup \u201eoffset + limit\u201c, kdy je v samotn\u00e9m dotazu uvedeno, od kter\u00e9 \u0159ady v\u00fdsledk\u016f a kolik \u0159ad se m\u00e1 vr\u00e1tit. Nejprve se pod\u00edv\u00e1me, jak nejl\u00e9pe organizovat p\u0159ed\u00e1n\u00ed parametr\u016f v tomto p\u0159\u00edpad\u011b. V praxi jsem se setkal s n\u011bkolika zp\u016fsoby:<\/p>\n<ul>\n<li>Po\u0159adov\u00e9 \u010d\u00edslo po\u017eadovan\u00e9 str\u00e1nky (pageIndex), velikost str\u00e1nky (pageSize).<\/li>\n<li>Po\u0159adov\u00e9 \u010d\u00edslo prvn\u00ed polo\u017eky, kterou je t\u0159eba vr\u00e1tit (startIndex), maxim\u00e1ln\u00ed po\u010det polo\u017eek ve v\u00fdsledku (count).<\/li>\n<li>Po\u0159adov\u00e9 \u010d\u00edslo prvn\u00ed polo\u017eky, kterou je t\u0159eba vr\u00e1tit (startIndex), po\u0159adov\u00e9 \u010d\u00edslo posledn\u00ed polo\u017eky, kterou je t\u0159eba vr\u00e1tit (endIndex).<\/li>\n<\/ul>\n<p>\nNa prvn\u00ed pohled se m\u016f\u017ee zd\u00e1t, \u017ee je to tak prost\u00e9, \u017ee na tom nez\u00e1le\u017e\u00ed. Ale nen\u00ed tomu tak \u2013 nejpohodln\u011bj\u0161\u00ed a nejuniverz\u00e1ln\u011bj\u0161\u00ed mo\u017enost\u00ed je druh\u00e1 (startIndex, count). Na to existuje n\u011bkolik d\u016fvod\u016f:<\/p>\n<ul>\n<li>Pro p\u0159\u00edstup s ode\u010dten\u00edm +1 polo\u017eky, jak je uvedeno v\u00fd\u0161e, je prvn\u00ed varianta s pageIndex a pageSize extr\u00e9mn\u011b nepraktick\u00e1. Nap\u0159\u00edklad chceme zobrazit 50 polo\u017eek na str\u00e1nce. Podle uveden\u00e9ho algoritmu je t\u0159eba \u010d\u00edst o jednu polo\u017eku v\u00edce, ne\u017e je nutn\u00e9. Pokud toto \u201e+1\u201c nen\u00ed zaji\u0161t\u011bno na serveru, z\u00edsk\u00e1me to, \u017ee pro prvn\u00ed str\u00e1nku mus\u00edme \u017e\u00e1dat o polo\u017eky od 1 do 51, pro druhou \u2013 od 51 do 101 atd. Pokud nastav\u00edme velikost str\u00e1nky na 51 a zvy\u0161ujeme pageIndex, druh\u00e1 str\u00e1nka vr\u00e1t\u00ed od 52 do 102 atd. T\u00edm p\u00e1dem, v prvn\u00ed variant\u011b je jedin\u00fd zp\u016fsob, jak norm\u00e1ln\u011b realizovat tla\u010d\u00edtko pro p\u0159echod na dal\u0161\u00ed str\u00e1nku, zajistit na serveru ode\u010dten\u00ed \u201ep\u0159ebyte\u010dn\u00ed\u201c polo\u017eky, co\u017e je velmi nejednozna\u010dn\u00fd nuance.<\/li>\n<li>T\u0159et\u00ed varianta nem\u00e1 v\u016fbec \u017e\u00e1dn\u00fd smysl, proto\u017ee pro prov\u00e1d\u011bn\u00ed dotaz\u016f v drtiv\u00e9 v\u011bt\u0161in\u011b datab\u00e1z\u00ed bude stejn\u011b nutn\u00e9 p\u0159edat po\u010det, nikoli index posledn\u00ed polo\u017eky. A\u010dkoliv je ode\u010dten\u00ed startIndex od endIndex jednoduch\u00e1 aritmetick\u00e1 operace, je zde nadbyte\u010dn\u00e1.<\/li>\n<\/ul>\n<p>\nNyn\u00ed je t\u0159eba popsat nev\u00fdhody implementace str\u00e1nkov\u00e1n\u00ed pomoc\u00ed \u201eoffset + po\u010det\u201c:<\/p>\n<ul>\n<li>Z\u00edsk\u00e1n\u00ed ka\u017ed\u00e9 dal\u0161\u00ed str\u00e1nky bude n\u00e1kladn\u011bj\u0161\u00ed a pomalej\u0161\u00ed ne\u017e p\u0159edchoz\u00ed, proto\u017ee datab\u00e1zi bude st\u00e1le muset proj\u00edt v\u0161echny z\u00e1znamy \u201eod za\u010d\u00e1tku\u201c podle krit\u00e9ri\u00ed vyhled\u00e1v\u00e1n\u00ed a t\u0159\u00edd\u011bn\u00ed, a pot\u00e9 se zastavit na pot\u0159ebn\u00e9m \u00faseku.<\/li>\n<li>Ne v\u0161echny DBMS mohou tento p\u0159\u00edstup podporovat.<\/li>\n<\/ul>\n<p>\nAlternativy existuj\u00ed, ale ani ty nejsou dokonal\u00e9. Prvn\u00ed z takov\u00fdch p\u0159\u00edstup\u016f se naz\u00fdv\u00e1 \u201ekeyset paging\u201c nebo \u201eseek method\u201c a spo\u010d\u00edv\u00e1 v tom, \u017ee po z\u00edsk\u00e1n\u00ed d\u00e1vky si m\u016f\u017eete zapamatovat hodnoty pol\u00ed v posledn\u00ed polo\u017ece na str\u00e1nce a pot\u00e9 je pou\u017e\u00edt pro z\u00edsk\u00e1n\u00ed dal\u0161\u00ed d\u00e1vky. Nap\u0159\u00edklad jsme provedli takov\u00fd dotaz:<\/p>\n<pre><code class=\"sql\">SELECT * FROM Sales.SalesOrderHeader\nORDER BY OrderDate DESC\nOFFSET 0 ROWS\nFETCH NEXT 50 ROWS ONLY\n<\/code><\/pre>\n<p>\nA v posledn\u00edm z\u00e1pisu jsme z\u00edskali hodnotu data objedn\u00e1vky '2014-06-29'. Pak bude mo\u017en\u00e9 zkusit prov\u00e9st n\u00e1sleduj\u00edc\u00ed pro z\u00edsk\u00e1n\u00ed dal\u0161\u00ed str\u00e1nky:<\/p>\n<pre><code class=\"sql\">SELECT * FROM Sales.SalesOrderHeader\nWHERE OrderDate &lt; &#039;2014-06-29&#039;\nORDER BY OrderDate DESC\nOFFSET 0 ROWS\nFETCH NEXT 50 ROWS ONLY\n<\/code><\/pre>\n<p>\nProbl\u00e9m je v tom, \u017ee OrderDate je neunik\u00e1tn\u00ed pole a uveden\u00e9 podm\u00ednky s vysokou pravd\u011bpodobnost\u00ed vynechaj\u00ed mnoho pot\u0159ebn\u00fdch \u0159\u00e1dk\u016f. Pro zp\u0159esn\u011bn\u00ed tohoto dotazu je nutn\u00e9 k podm\u00ednce p\u0159idat unik\u00e1tn\u00ed pole (p\u0159edpokl\u00e1dejme, \u017ee 75074 je posledn\u00ed hodnota prim\u00e1rn\u00edho kl\u00ed\u010de z prvn\u00ed d\u00e1vky):<\/p>\n<pre><code class=\"sql\">SELECT * FROM Sales.SalesOrderHeader\nWHERE (OrderDate = '2014-06-29' AND SalesOrderID &lt; 75074)\n   OR (OrderDate &lt; &#039;2014-06-29&#039;)\nORDER BY OrderDate DESC, SalesOrderID DESC\nOFFSET 0 ROWS\nFETCH NEXT 50 ROWS ONLY\n<\/code><\/pre>\n<p>\nTato varianta bude fungovat spr\u00e1vn\u011b, ale obecn\u011b ji bude t\u011b\u017ek\u00e9 optimalizovat, proto\u017ee podm\u00ednka obsahuje oper\u00e1tor OR. Pokud s r\u016fstem OrderDate roste hodnota prim\u00e1rn\u00edho kl\u00ed\u010de, lze podm\u00ednku zjednodu\u0161it a ponechat pouze filtr podle SalesOrderID. Ale pokud mezi hodnotami prim\u00e1rn\u00edho kl\u00ed\u010de a polem, podle kter\u00e9ho je v\u00fdsledek se\u0159azen, neexistuje p\u0159\u00edsn\u00e1 korelace \u2014 v\u011bt\u0161ina DBMS nebude moci t\u00e9to OR uniknout. Jedinou v\u00fdjimkou, kterou zn\u00e1m, je PostgreSQL, kde je pln\u011b podporov\u00e1no porovn\u00e1n\u00ed n-tic, a uvedenou podm\u00ednku lze zapsat jako \u201eWHERE (OrderDate, SalesOrderID) &lt; (&#039;2014-06-29&#039;, 75074)\u201c. P\u0159i existenci slo\u017een\u00e9ho kl\u00ed\u010de s t\u011bmito dv\u011bma poli by m\u011bl b\u00fdt takov\u00fd dotaz dostate\u010dn\u011b lehk\u00fd.<\/p>\n<p>Druh\u00fd alternativn\u00ed p\u0159\u00edstup lze nal\u00e9zt nap\u0159\u00edklad v <noindex><a rel=\"nofollow\" href=\"https:\/\/www.elastic.co\/guide\/en\/elasticsearch\/reference\/6.8\/search-request-scroll.html\">ElasticSearch scroll API<\/a><\/noindex> nebo <noindex><a rel=\"nofollow\" href=\"https:\/\/medium.com\/@gary.strange\/understanding-cosmosdb-continuation-tokens-hasmoreresults-and-connectionpolicy-requesttimeouts-3ed1fadfa81d\">Cosmos DB<\/a><\/noindex> \u2014 kdy dotaz krom\u011b dat vrac\u00ed speci\u00e1ln\u00ed identifik\u00e1tor, pomoc\u00ed kter\u00e9ho lze z\u00edskat dal\u0161\u00ed d\u00e1vku dat. Pokud m\u00e1 tento identifik\u00e1tor neomezenou \u017eivotnost (jako v Cosmos DB), je to vynikaj\u00edc\u00ed zp\u016fsob realizace str\u00e1nkov\u00e1n\u00ed s postupn\u00fdm p\u0159echodem mezi str\u00e1nkami (zm\u00edn\u011bn\u00e1 varianta #2). Jeho mo\u017en\u00e9 nev\u00fdhody: nen\u00ed podporov\u00e1no ve v\u0161ech DBMS; z\u00edskan\u00fd identifik\u00e1tor dal\u0161\u00ed d\u00e1vky m\u016f\u017ee m\u00edt omezenou \u017eivotnost, co\u017e obecn\u011b nen\u00ed vhodn\u00e9 pro realizaci interakce s u\u017eivateli (jako nap\u0159\u00edklad ElasticSearch scroll API).<\/p>\n<h2>Slo\u017eit\u00e1 filtrace<\/h2>\n<p>\nD\u00e1le zt\u011b\u017eujeme \u00fakol. P\u0159edpokl\u00e1dejme, \u017ee se objevila po\u017eadavek na implementaci takzvan\u00e9ho faceted search, kter\u00fd je v\u0161em dob\u0159e zn\u00e1m\u00fd z internetov\u00fdch obchod\u016f. V\u00fd\u0161e uveden\u00e9 p\u0159\u00edklady zalo\u017een\u00e9 na tabulce objedn\u00e1vek nejsou v tomto p\u0159\u00edpad\u011b p\u0159\u00edli\u0161 v\u00fdsti\u017en\u00e9, proto se p\u0159epneme na tabulku Product z datab\u00e1ze AdventureWorks:<\/p>\n<p><img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/070890a3cb79de3edc69b60b5300efe7.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nJak\u00fd je princip faceted search? Je to v tom, \u017ee pro ka\u017edou polo\u017eku filtru se zobrazuje po\u010det z\u00e1znam\u016f odpov\u00eddaj\u00edc\u00edch tomuto krit\u00e9riu. <i>s ohledem na filtry vybran\u00e9 ve v\u0161ech ostatn\u00edch kategori\u00edch.<\/i>.<\/p>\n<p>Nap\u0159\u00edklad pokud v tomto p\u0159\u00edkladu vybereme kategorii Bikes a barvu Black, tabulka zobraz\u00ed pouze \u010dern\u00e9 bicykle, ale p\u0159itom:<\/p>\n<ul>\n<li>Pro ka\u017ed\u00e9 krit\u00e9rium skupiny \u201eCategories\u201c se zobraz\u00ed po\u010det produkt\u016f z t\u00e9to kategorie \u010dern\u00e9 barvy.<\/li>\n<li>Pro ka\u017ed\u00e9 krit\u00e9rium skupiny \u201eColors\u201c se zobraz\u00ed po\u010det bicykl\u016f t\u00e9to barvy.<\/li>\n<\/ul>\n<p>\nZde je p\u0159\u00edklad v\u00fdstupu v\u00fdsledku pro takov\u00e9 podm\u00ednky:<\/p>\n<p><img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/7c8ec4d8b427f1f59f0129afebe74b08.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nPokud nav\u00edc za\u0161krtneme kategorii \u201eClothing\u201c, tabulka zobraz\u00ed tak\u00e9 \u010dern\u00e9 oble\u010den\u00ed, kter\u00e9 je skladem. Po\u010det produkt\u016f \u010dern\u00e9 barvy v sekci \u201eColor\u201c bude tak\u00e9 p\u0159epo\u010d\u00edt\u00e1n podle nov\u00fdch podm\u00ednek, pouze v sekci \u201eCategories\u201c se nic nezm\u011bn\u00ed\u2026 Douf\u00e1m, \u017ee t\u011bchto p\u0159\u00edklad\u016f je dostatek, abychom pochopili obvykl\u00fd algoritmus fungov\u00e1n\u00ed faceted search.<\/p>\n<p>Nyn\u00ed si p\u0159edstavme, jak by to bylo mo\u017en\u00e9 implementovat na rela\u010dn\u00ed datab\u00e1zi. Ka\u017ed\u00e1 skupina krit\u00e9ri\u00ed, jako je Category a Color, bude vy\u017eadovat samostatn\u00fd dotaz:<\/p>\n<pre><code class=\"sql\">SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p\n  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID\n  INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID\nWHERE p.Color = 'Black'\nGROUP BY pc.ProductCategoryID, pc.Name\nORDER BY COUNT(1) DESC\n<\/code><\/pre>\n<p>\n<img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/3b59b68ce934c4ec1630ec649e68ccdb.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<pre><code class=\"sql\">SELECT Color, COUNT(1) FROM Production.Product p\n  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID\nWHERE ps.ProductCategoryID = 1 --Bikes\nGROUP BY Color\nORDER BY COUNT(1) DESC\n<\/code><\/pre>\n<p>\n<img decoding=\"async\" alt=\"V\u00fdstup v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonnost\u00ed\" src=\"\/wp-content\/uploads\/2020\/03\/82e1e6ab0fae683d383e3fecf4e3a15a.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nCo nen\u00ed v tomto \u0159e\u0161en\u00ed v po\u0159\u00e1dku? Velmi jednodu\u0161e \u2014 \u0161patn\u011b se \u0161k\u00e1luje. Ka\u017ed\u00e1 sekce filtru vy\u017eaduje samostatn\u00fd dotaz pro v\u00fdpo\u010det mno\u017estv\u00ed a tyto dotazy nejsou nejleh\u010d\u00ed. V internetov\u00fdch obchodech m\u016f\u017ee v n\u011bkter\u00fdch sekc\u00edch b\u00fdt i n\u011bkolik des\u00edtek filtr\u016f, co\u017e se m\u016f\u017ee uk\u00e1zat jako v\u00e1\u017en\u00fd probl\u00e9m pro v\u00fdkon.<\/p>\n<p>Obvykle po t\u011bchto tvrzen\u00edch mi navrhuj\u00ed n\u011bkter\u00e1 \u0159e\u0161en\u00ed, a to konkr\u00e9tn\u011b:<\/p>\n<ul>\n<li>Slou\u010dit v\u0161echny po\u010dty do jednoho dotazu. Technicky je to mo\u017en\u00e9 pomoc\u00ed kl\u00ed\u010dov\u00e9ho slova UNION, ale v\u00fdkonu to p\u0159\u00edli\u0161 nepom\u016f\u017ee \u2014 datab\u00e1ze bude muset stejn\u011b prov\u00e9st \u201eod za\u010d\u00e1tku\u201c ka\u017ed\u00fd z fragment\u016f.<\/li>\n<li>Kdy\u017e se pt\u00e1m na probl\u00e9m, skoro poka\u017ed\u00e9 mi navrhuj\u00ed ke\u0161ov\u00e1n\u00ed po\u010dt\u016f. Nuance je v tom, \u017ee to obecn\u011b nelze prov\u00e9st. P\u0159edpokl\u00e1dejme, \u017ee m\u00e1me 10 \u201efascet\u201c, z nich\u017e ka\u017ed\u00e1 m\u00e1 5 hodnot. To je velmi \u201eskromn\u00e1\u201c situace ve srovn\u00e1n\u00ed s t\u00edm, co lze vid\u011bt v internetov\u00fdch obchodech. V\u00fdb\u011br jednoho prvku fascety ovliv\u0148uje po\u010dty v ostatn\u00edch 9, jin\u00fdmi slovy, pro ka\u017edou kombinaci krit\u00e9ri\u00ed mohou b\u00fdt po\u010dty r\u016fzn\u00e9. Celkov\u011b v na\u0161em p\u0159\u00edkladu existuje 50 krit\u00e9ri\u00ed, kter\u00e9 si m\u016f\u017ee u\u017eivatel vybrat, a tedy bude 250 mo\u017en\u00fdch kombinac\u00ed. Na napln\u011bn\u00ed takov\u00e9 datov\u00e9 matice nebude dost pam\u011bti ani \u010dasu. M\u016f\u017eete nam\u00edtnout, \u017ee ne v\u0161echny kombinace jsou re\u00e1ln\u00e9 a u\u017eivatel si z\u0159\u00eddka vyb\u00edr\u00e1 v\u00edce ne\u017e 5-10 krit\u00e9ri\u00ed. Ano, m\u016f\u017eete prov\u00e9st lazy loading a ke\u0161ov\u00e1n\u00ed po\u010dt\u016f pouze pro to, co bylo kdy vybr\u00e1no, ale \u010d\u00edm v\u00edce mo\u017enost\u00ed v\u00fdb\u011bru, t\u00edm m\u00e9n\u011b efektivn\u00ed bude takov\u00e1 ke\u0161 a t\u00edm v\u00edce v\u00fdrazn\u00e9 budou probl\u00e9my s \u010dasem odezvy (zejm\u00e9na pokud se datov\u00fd soubor pravideln\u011b m\u011bn\u00ed).<\/li>\n<\/ul>\n<p>\nNa\u0161t\u011bst\u00ed je zde \u0159e\u0161en\u00ed, kter\u00e9 je ji\u017e dlouho dost efektivn\u00ed a p\u0159edv\u00eddateln\u011b funguje na velk\u00fdch objemech dat. Pro jakoukoli z t\u011bchto mo\u017enost\u00ed m\u00e1 smysl rozd\u011blit p\u0159epo\u010det fascet a na\u010d\u00edt\u00e1n\u00ed str\u00e1nky v\u00fdsledk\u016f na dva paraleln\u00ed po\u017eadavky na server a uspo\u0159\u00e1dat u\u017eivatelsk\u00e9 rozhran\u00ed tak, aby na\u010d\u00edt\u00e1n\u00ed dat ohledn\u011b fascet \u201enebr\u00e1nilo\u201c zobrazen\u00ed v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed.<\/p>\n<ul>\n<li>Prov\u00e1d\u011bjte \u00fapln\u00e9 p\u0159epo\u010dty \u201efascet\u016f\u201c co nejm\u00e9n\u011b. Nap\u0159\u00edklad neprov\u00e1d\u011bjte p\u0159epo\u010det p\u0159i ka\u017ed\u00e9 zm\u011bn\u011b hledac\u00edch krit\u00e9ri\u00ed, m\u00edsto toho nalezn\u011bte celkov\u00fd po\u010det v\u00fdsledk\u016f odpov\u00eddaj\u00edc\u00edch aktu\u00e1ln\u00edm podm\u00ednk\u00e1m a nab\u00eddn\u011bte u\u017eivateli, aby je zobrazil \u2014 \u201enalezeno 1425 z\u00e1znam\u016f, zobrazit?\u201c U\u017eivatel m\u016f\u017ee pokra\u010dovat ve zm\u011bn\u011b podm\u00ednek hled\u00e1n\u00ed nebo stisknout tla\u010d\u00edtko \u201ezobrazit\u201c. Teprve ve druh\u00e9m p\u0159\u00edpad\u011b budou provedeny v\u0161echny po\u017eadavky na z\u00edsk\u00e1n\u00ed v\u00fdsledk\u016f a p\u0159epo\u010det mno\u017estv\u00ed na v\u0161ech \u201efascetech\u201c. Jak nen\u00ed t\u011b\u017ek\u00e9 si v\u0161imnout, bude nutn\u00e9 se vypo\u0159\u00e1dat s po\u017eadavkem na z\u00edsk\u00e1n\u00ed celkov\u00e9ho po\u010dtu v\u00fdsledk\u016f a jeho optimalizac\u00ed. Tento zp\u016fsob m\u016f\u017eete vid\u011bt v mnoha mal\u00fdch internetov\u00fdch obchodech. Je z\u0159ejm\u00e9, \u017ee to nen\u00ed v\u0161el\u00e9k na tento probl\u00e9m, ale v jednoduch\u00fdch p\u0159\u00edpadech m\u016f\u017ee b\u00fdt dobr\u00fdm kompromisem.<\/li>\n<li>Pou\u017e\u00edvejte vyhled\u00e1vac\u00ed enginy pro vyhled\u00e1v\u00e1n\u00ed v\u00fdsledk\u016f a po\u010d\u00edt\u00e1n\u00ed fascet\u016f, jako jsou Solr, ElasticSearch, Sphinx a dal\u0161\u00ed. V\u0161echny jsou navr\u017eeny pro vytv\u00e1\u0159en\u00ed \u201efascet\u016f\u201c a d\u011blaj\u00ed to dost efektivn\u011b d\u00edky invertovan\u00e9mu indexu. Jak funguj\u00ed vyhled\u00e1vac\u00ed syst\u00e9my, pro\u010d jsou v t\u011bchto p\u0159\u00edpadech efektivn\u011bj\u0161\u00ed ne\u017e datab\u00e1ze obecn\u00fdch \u00fa\u010del\u016f, jak\u00e9 existuj\u00ed praktiky a n\u00e1strahy \u2014 to je t\u00e9ma pro samostatn\u00fd \u010dl\u00e1nek. Zde v\u0161ak chci upozornit, \u017ee vyhled\u00e1vac\u00ed engine nem\u016f\u017ee b\u00fdt n\u00e1hradou hlavn\u00edho \u00falo\u017ei\u0161t\u011b dat, pou\u017e\u00edv\u00e1 se jako dopln\u011bk: jak\u00e9koliv zm\u011bny v z\u00e1kladn\u00ed datab\u00e1zi, kter\u00e9 jsou pro vyhled\u00e1v\u00e1n\u00ed relevantn\u00ed, jsou synchronizov\u00e1ny do vyhled\u00e1vac\u00edho indexu; mechanismus vyhled\u00e1v\u00e1n\u00ed obvykle interaguje pouze s vyhled\u00e1vac\u00edm enginem a neodkazuje na z\u00e1kladn\u00ed datab\u00e1zi. Jeden z nejd\u016fle\u017eit\u011bj\u0161\u00edch aspekt\u016f zde je, jak spolehliv\u011b tuto synchronizaci organizovat. V\u0161echno z\u00e1vis\u00ed na po\u017eadavc\u00edch na \u201e\u010das odezvy\u201c. Pokud nen\u00ed \u010das mezi zm\u011bnou v z\u00e1kladn\u00ed datab\u00e1zi a jej\u00edm \u201eprojeven\u00edm\u201c ve vyhled\u00e1v\u00e1n\u00ed kritick\u00fd, lze vytvo\u0159it slu\u017ebu, kter\u00e1 ka\u017ed\u00fdch n\u011bkolik minut hled\u00e1 ned\u00e1vno zm\u011bn\u011bn\u00e9 z\u00e1znamy a indexuje je. Pokud je pot\u0159eba minimalizovat mo\u017en\u00fd \u010das odezvy, lze implementovat n\u011bco jako <noindex><a rel=\"nofollow\" href=\"https:\/\/microservices.io\/patterns\/data\/transactional-outbox.html\">transactional outbox<\/a><\/noindex> pro odes\u00edl\u00e1n\u00ed aktualizac\u00ed do vyhled\u00e1vac\u00ed slu\u017eby.<\/li>\n<\/ul>\n<p><\/p>\n<h2>Z\u00e1v\u011bry<\/h2>\n<p><\/p>\n<ol>\n<li>Implementace paginace na stran\u011b serveru je v\u00e1\u017en\u00e9 zt\u00ed\u017een\u00ed a m\u00e1 smysl ji pou\u017e\u00edvat pouze pro rychle rostouc\u00ed nebo jednodu\u0161e velk\u00e9 datov\u00e9 sady. Jak posoudit, co je \u201evelk\u00e9\u201c nebo \u201erychle rostouc\u00ed\u201c \u2014 neexistuje \u017e\u00e1dn\u00fd absolutn\u011b p\u0159esn\u00fd recept, ale dr\u017eel bych se takov\u00e9ho p\u0159\u00edstupu:\n<ul>\n<li>Pokud z\u00edsk\u00e1n\u00ed cel\u00e9 kolekce dat s ohledem na serverov\u00fd \u010das a p\u0159enos p\u0159es s\u00ed\u0165 norm\u00e1ln\u011b spl\u0148uje po\u017eadavky na v\u00fdkon, nem\u00e1 smysl implementovat paginaci na stran\u011b serveru.<\/li>\n<li>M\u016f\u017ee nastat situace, kdy v bl\u00edzk\u00e9 budoucnosti nebudou s v\u00fdkonem \u017e\u00e1dn\u00e9 probl\u00e9my, proto\u017ee dat je m\u00e1lo, ale kolekce dat neust\u00e1le roste. Pokud by n\u011bjak\u00e1 datov\u00e1 sada mohla p\u0159estat spl\u0148ovat p\u0159edchoz\u00ed bod v budoucnu, je lep\u0161\u00ed paginaci implementovat hned.<\/li>\n<\/ul>\n<\/li>\n<li>Pokud z pohledu podnik\u00e1n\u00ed neexistuje \u017e\u00e1dn\u00e1 p\u0159\u00edsn\u00e1 po\u017eadavek na zobrazen\u00ed celkov\u00e9ho po\u010dtu v\u00fdsledk\u016f nebo na zobrazen\u00ed \u010d\u00edsel str\u00e1nek, a nav\u00edc ve va\u0161em syst\u00e9mu nen\u00ed vyhled\u00e1vac\u00ed engine \u2014 je lep\u0161\u00ed tyto momenty neimplementovat a zv\u00e1\u017eit variantu \u010d. 2.<\/li>\n<li>Pokud existuje jasn\u00fd po\u017eadavek na faceted search, m\u00e1te dv\u011b mo\u017enosti, jak neztratit v\u00fdkon:\n<ul>\n<li>Neprov\u00e1d\u011bt znovu p\u0159epo\u010det v\u0161ech mno\u017estv\u00ed p\u0159i ka\u017ed\u00e9 zm\u011bn\u011b kriteri\u00ed vyhled\u00e1v\u00e1n\u00ed.<\/li>\n<li>Pou\u017e\u00edt vyhled\u00e1vac\u00ed engine jako Solr, ElasticSearch, Sphinx a dal\u0161\u00ed. Ale je t\u0159eba ch\u00e1pat, \u017ee nem\u016f\u017ee b\u00fdt n\u00e1hradou hlavn\u00ed datab\u00e1ze a m\u011bl by b\u00fdt pou\u017e\u00edv\u00e1n jako dopln\u011bk k hlavn\u00edmu \u00falo\u017ei\u0161ti pro \u0159e\u0161en\u00ed vyhled\u00e1vac\u00edch \u00fakol\u016f. <\/li>\n<\/ul>\n<\/li>\n<li>Tak\u00e9 v p\u0159\u00edpad\u011b faceted search m\u00e1 smysl rozd\u011blit z\u00edsk\u00e1n\u00ed str\u00e1nky v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a po\u010d\u00edt\u00e1n\u00ed mno\u017estv\u00ed na dva paraleln\u00ed dotazy. Po\u010d\u00edt\u00e1n\u00ed mno\u017estv\u00ed m\u016f\u017ee zabrat v\u00edce \u010dasu ne\u017e z\u00edsk\u00e1n\u00ed v\u00fdsledk\u016f, zat\u00edmco v\u00fdsledky jsou d\u016fle\u017eit\u011bj\u0161\u00ed pro u\u017eivatele.<\/li>\n<li>Pokud pou\u017e\u00edv\u00e1te SQL datab\u00e1zi pro vyhled\u00e1v\u00e1n\u00ed, jak\u00e1koli zm\u011bna k\u00f3du t\u00fdkaj\u00edc\u00ed se t\u00e9to \u010d\u00e1sti by m\u011bla b\u00fdt d\u016fkladn\u011b testov\u00e1na z hlediska v\u00fdkonu na odpov\u00eddaj\u00edc\u00edm objemu dat (p\u0159evy\u0161uj\u00edc\u00edm objem v \u201e\u017eiv\u00e9\u201c datab\u00e1zi). Tak\u00e9 je \u017e\u00e1douc\u00ed vyu\u017e\u00edvat monitoring doby prov\u00e1d\u011bn\u00ed dotaz\u016f na v\u0161ech instanc\u00edch datab\u00e1ze, a zejm\u00e9na \u2014 na \u201e\u017eiv\u00e9m\u201c. I kdy\u017e ve f\u00e1zi v\u00fdvoje byly pl\u00e1ny dotaz\u016f v po\u0159\u00e1dku, se vzr\u016fstaj\u00edc\u00edm objemem dat se situace m\u016f\u017ee v\u00fdrazn\u011b zm\u011bnit.<\/li>\n<\/ol>\n<p>Zdroj: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/epam_systems\/blog\/493438\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041e\u0434\u0438\u043d \u0438\u0437 \u0442\u0438\u043f\u043e\u0432\u044b\u0445 \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0435\u0432 \u0432\u043e \u0432\u0441\u0435\u0445 \u043f\u0440\u0438\u0432\u044b\u0447\u043d\u044b\u0445 \u043d\u0430\u043c \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445 \u2014 \u043f\u043e\u0438\u0441\u043a \u0434\u0430\u043d\u043d\u044b\u0445 \u043f\u043e \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u044b\u043c \u043a\u0440\u0438\u0442\u0435\u0440\u0438\u044f\u043c \u0438 \u0432\u044b\u0432\u043e\u0434 \u0438\u0445 \u0432 \u0443\u0434\u043e\u0431\u043d\u043e\u043c \u0434\u043b\u044f \u0447\u0442\u0435\u043d\u0438\u044f \u0432\u0438\u0434\u0435. \u0422\u0443\u0442 \u0436\u0435 \u043c\u043e\u0433\u0443\u0442 \u0431\u044b\u0442\u044c \u0434\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u0438 \u043f\u043e \u0441\u043e\u0440\u0442\u0438\u0440\u043e\u0432\u043a\u0435, \u0433\u0440\u0443\u043f\u043f\u0438\u0440\u043e\u0432\u043a\u0435, \u043f\u043e\u0441\u0442\u0440\u0430\u043d\u0438\u0447\u043d\u043e\u043c\u0443 \u0432\u044b\u0432\u043e\u0434\u0443. \u0417\u0430\u0434\u0430\u0447\u0430, \u043f\u043e \u0438\u0434\u0435\u0435, \u0442\u0440\u0438\u0432\u0438\u0430\u043b\u044c\u043d\u0430\u044f, \u043d\u043e \u043f\u0440\u0438 \u0435\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u0438 \u043c\u043d\u043e\u0433\u0438\u0435 \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u0438 \u0434\u0435\u043b\u0430\u044e\u0442 \u0440\u044f\u0434 \u043e\u0448\u0438\u0431\u043e\u043a, \u0438\u0437-\u0437\u0430 \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043f\u043e\u0442\u043e\u043c \u0441\u0442\u0440\u0430\u0434\u0430\u0435\u0442 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c. \u041f\u043e\u043f\u0440\u043e\u0431\u0443\u0435\u043c \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c \u0440\u0430\u0437\u043b\u0438\u0447\u043d\u044b\u0435 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":75531,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-75530","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.3 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041e\u0434\u0438\u043d \u0438\u0437 \u0442\u0438\u043f\u043e\u0432\u044b\u0445 \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0435\u0432 \u0432\u043e \u0432\u0441\u0435\u0445 \u043f\u0440\u0438\u0432\u044b\u0447\u043d\u044b\u0445 \u043d\u0430\u043c \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445 \u2014 \u043f\u043e\u0438\u0441\u043a \u0434\u0430\u043d\u043d\u044b\u0445 \u043f\u043e \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u044b\u043c \u043a\u0440\u0438\u0442\u0435\u0440\u0438\u044f\u043c \u0438 \u0432\u044b\u0432\u043e\u0434 \u0438\u0445 \u0432 \u0443\u0434\u043e\u0431\u043d\u043e\u043c \u0434\u043b\u044f \u0447\u0442\u0435\u043d\u0438\u044f \u0432\u0438\u0434\u0435.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/vyvod-rezultatov-poiska-i-problemy-s-proizvoditelnostyu\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"cs_CZ\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u0412\u044b\u0432\u043e\u0434 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u043e\u0432 \u043f\u043e\u0438\u0441\u043a\u0430 \u0438 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u044b \u0441 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041e\u0434\u0438\u043d \u0438\u0437 \u0442\u0438\u043f\u043e\u0432\u044b\u0445 \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0435\u0432 \u0432\u043e \u0432\u0441\u0435\u0445 \u043f\u0440\u0438\u0432\u044b\u0447\u043d\u044b\u0445 \u043d\u0430\u043c \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445 \u2014 \u043f\u043e\u0438\u0441\u043a \u0434\u0430\u043d\u043d\u044b\u0445 \u043f\u043e \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u044b\u043c \u043a\u0440\u0438\u0442\u0435\u0440\u0438\u044f\u043c \u0438 \u0432\u044b\u0432\u043e\u0434 \u0438\u0445 \u0432 \u0443\u0434\u043e\u0431\u043d\u043e\u043c \u0434\u043b\u044f \u0447\u0442\u0435\u043d\u0438\u044f \u0432\u0438\u0434\u0435.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/vyvod-rezultatov-poiska-i-problemy-s-proizvoditelnostyu\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-03-26T17:42:23+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-03-26T17:42:23+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Zobrazen\u00ed v\u00fdsledk\u016f vyhled\u00e1v\u00e1n\u00ed a probl\u00e9my s v\u00fdkonem | ProHoster","description":"Jedn\u00edm z b\u011b\u017en\u00fdch sc\u00e9n\u00e1\u0159\u016f ve v\u0161ech zn\u00e1m\u00fdch aplikac\u00edch je hled\u00e1n\u00ed dat podle ur\u010dit\u00fdch krit\u00e9ri\u00ed a jejich zobrazen\u00ed ve snadno \u010diteln\u00e9 podob\u011b.","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/vyvod-rezultatov-poiska-i-problemy-s-proizvoditelnostyu","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0412\u044b\u0432\u043e\u0434 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u043e\u0432 \u043f\u043e\u0438\u0441\u043a\u0430 \u0438 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u044b \u0441 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e | ProHoster","og:description":"\u041e\u0434\u0438\u043d \u0438\u0437 \u0442\u0438\u043f\u043e\u0432\u044b\u0445 \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0435\u0432 \u0432\u043e \u0432\u0441\u0435\u0445 \u043f\u0440\u0438\u0432\u044b\u0447\u043d\u044b\u0445 \u043d\u0430\u043c \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445 \u2014 \u043f\u043e\u0438\u0441\u043a \u0434\u0430\u043d\u043d\u044b\u0445 \u043f\u043e \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u044b\u043c \u043a\u0440\u0438\u0442\u0435\u0440\u0438\u044f\u043c \u0438 \u0432\u044b\u0432\u043e\u0434 \u0438\u0445 \u0432 \u0443\u0434\u043e\u0431\u043d\u043e\u043c \u0434\u043b\u044f \u0447\u0442\u0435\u043d\u0438\u044f \u0432\u0438\u0434\u0435.","og:url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/vyvod-rezultatov-poiska-i-problemy-s-proizvoditelnostyu","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-03-26T17:42:23+00:00","article:modified_time":"2020-03-26T17:42:23+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"75530","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 17:55:26","updated":"2022-10-02 02:12:16","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/75530","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=75530"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/75530\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/75531"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=75530"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=75530"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=75530"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}