Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите проанализировать план  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 666
День добрый, знактоки!

SQL Server 2005 Express

Стал зависать запрос на получение складских остатков.
Помогите пожалуйста понять и поправить.
В запросе закомментировал кусок, без которого он выполняется доли секунд.

SELECT COUNT(*) FROM 
(
 SELECT
  kul.cl_id, 
  CASE WHEN ISNULL(ui.ui_qnty1 - ISNULL(uilt.ld_qnty1, 0), 0) > 0 OR ISNULL(ui.ui_qnty2 - ISNULL(uilt.ld_qnty2, 0), 0) > 0 THEN 1 ELSE 0 END in_stock
 FROM dbo.stock_unload_items ui 
 LEFT OUTER JOIN logistics.k_stock_unload kul ON ui.ui_unload_id = kul.ul_id 
 LEFT OUTER JOIN 
 (
  SELECT li.li_unload_item_id ui_id, SUM(li.li_qnty1) ld_qnty1, SUM(li.li_qnty2) ld_qnty2, SUM(li.li_weight) ld_weight, SUM(li.li_volume) ld_volume
  FROM dbo.stock_load_items li 
  LEFT JOIN logistics.k_stock_load kld ON li.li_load_id = kld.ld_id
  /*LEFT JOIN 
  (
   SELECT TOP 1 WITH TIES lastli.li_unload_item_id, ld_date, [load] 
   FROM dbo.stock_load_items lastli 
   LEFT JOIN logistics.k_stock_load lastkld  ON lastli.li_load_id = lastkld.ld_id 
   ORDER BY ROW_NUMBER() OVER (PARTITION BY li_unload_item_id ORDER BY ld_date DESC)
  ) last ON last.li_unload_item_id = li.li_unload_item_id*/
  GROUP BY li.li_unload_item_id
 ) uilt ON ui.ui_id = uilt.ui_id 
) stock 
WHERE cl_id = 5733 AND in_stock=1

План запроса:
+
StmtText                                                                                                                                                                                                                                                         StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                         DefinedValues                                                                                                                                                                                                                                                    EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                                                                                                                                    Warnings Type                                                             Parallel EstimateExecutions
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
SELECT COUNT(*) FROM 
(
 SELECT
  kul.cl_id, 
  CASE WHEN ISNULL(ui.ui_qnty1 - ISNULL(uilt.ld_qnty1, 0), 0) > 0 OR ISNULL(ui.ui_qnty2 - ISNULL(uilt.ld_qnty2, 0), 0) > 0 THEN 1 ELSE 0 END in_stock
 FROM dbo.stock_unload_items ui 
 LEFT OUTER JOIN logi 1           1           0           NULL                           NULL                           1                                                                                                                                                                                                                                                                NULL                                                                                                                                                                                                                                                             1             NULL          NULL          NULL        0,3801427        NULL                                                                                                                                                                                                                          NULL     SELECT                                                           0        NULL
  |--Compute Scalar(DEFINE:([Expr1268]=CONVERT_IMPLICIT(int,[Expr1292],0)))                                                                                                                                                                                      1           2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1268]=CONVERT_IMPLICIT(int,[Expr1292],0))                                                                                                                                                                                                           [Expr1268]=CONVERT_IMPLICIT(int,[Expr1292],0)                                                                                                                                                                                                                    1             0             1,1E-06       11          0,3801427        [Expr1268]                                                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
       |--Stream Aggregate(DEFINE:([Expr1292]=Count(*)))                                                                                                                                                                                                         1           3           2           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1292]=Count(*)                                                                                                                                                                                                                                              1             0             1,1E-06       11          0,3801427        [Expr1292]                                                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
            |--Filter(WHERE:(CASE WHEN isnull([logistics].[dbo].[all_stock_unload_items].[ui_qnty1]-isnull([Expr1260],(0)),(0))>(0) OR isnull([logistics].[dbo].[all_stock_unload_items].[ui_qnty2]-isnull([Expr1261],(0)),(0))>(0) THEN (1) ELSE (0) END=(1)))  1           4           3           Filter                         Filter                         WHERE:(CASE WHEN isnull([logistics].[dbo].[all_stock_unload_items].[ui_qnty1]-isnull([Expr1260],(0)),(0))>(0) OR isnull([logistics].[dbo].[all_stock_unload_items].[ui_qnty2]-isnull([Expr1261],(0)),(0))>(0) THEN (1) ELSE (0) END=(1))                         NULL                                                                                                                                                                                                                                                             1             0             2,687456E-06  9           0,3801416        NULL                                                                                                                                                                                                                          NULL     PLAN_ROW                                                         0        1
                 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([logistics].[dbo].[all_stock_unload_items].[ui_id]))                                                                                                                                         1           5           4           Nested Loops                   Left Outer Join                OUTER REFERENCES:([logistics].[dbo].[all_stock_unload_items].[ui_id])                                                                                                                                                                                            NULL                                                                                                                                                                                                                                                             1,04165       0             4,354095E-06  23          0,3801389        [logistics].[dbo].[all_stock_unload_items].[ui_qnty1], [logistics].[dbo].[all_stock_unload_items].[ui_qnty2], [Expr1260], [Expr1261]                                                                                          NULL     PLAN_ROW                                                         0        1
                      |--Nested Loops(Inner Join, WHERE:([logistics].[dbo].[all_stock_unload_items].[ui_company_id]=[Expr1270]))                                                                                                                                 1           6           5           Nested Loops                   Inner Join                     WHERE:([logistics].[dbo].[all_stock_unload_items].[ui_company_id]=[Expr1270])                                                                                                                                                                                    NULL                                                                                                                                                                                                                                                             1,04165       0             1,103303E-05  19          0,07914015       [logistics].[dbo].[all_stock_unload_items].[ui_id], [logistics].[dbo].[all_stock_unload_items].[ui_qnty1], [logistics].[dbo].[all_stock_unload_items].[ui_qnty2]                                                              NULL     PLAN_ROW                                                         0        1
                      |    |--Assert(WHERE:(CASE WHEN [Expr1269]>(1) THEN (0) ELSE NULL END))                                                                                                                                                                    1           7           6           Assert                         Assert                         WHERE:(CASE WHEN [Expr1269]>(1) THEN (0) ELSE NULL END)                                                                                                                                                                                                          NULL                                                                                                                                                                                                                                                             1             0             1,8E-07       11          0,00657166       [Expr1270]                                                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |    |    |--Stream Aggregate(DEFINE:([Expr1269]=Count(*), [Expr1270]=ANY([logistics].[dbo].[all_companies].[cmp_id])))                                                                                                                    1           8           7           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1269]=Count(*), [Expr1270]=ANY([logistics].[dbo].[all_companies].[cmp_id])                                                                                                                                                                                  1             0             1,1E-06       19          0,00657148       [Expr1269], [Expr1270]                                                                                                                                                                                                        NULL     PLAN_ROW                                                         0        1
                      |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id]))                                                                                                                         1           9           8           Nested Loops                   Inner Join                     OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id])                                                                                                                                                                                           NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      11          0,00657038       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |    |              |--Index Seek(OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD)                                                         1           10          9           Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD                                                                                                                  [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                                                              1             0,003125      0,0001581     11          0,0032831        [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                           NULL     PLAN_ROW                                                         0        1
                      |    |              |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FORWARD)                    1           11          9           Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FORWARD                                                                                       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                                                       1             0,003125      0,0001581     11          0,0032831        [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [logistics].[dbo].[all_stock_unload_items].[ui_company_id], [logistics].[dbo].[all_stock_unload_items].[ui_id]))                                                            1           16          6           Nested Loops                   Inner Join                     OUTER REFERENCES:([Uniq1002], [logistics].[dbo].[all_stock_unload_items].[ui_company_id], [logistics].[dbo].[all_stock_unload_items].[ui_id])                                                                                                                    NULL                                                                                                                                                                                                                                                             2,639481      0             1,103303E-05  23          0,07255667       [logistics].[dbo].[all_stock_unload_items].[ui_company_id], [logistics].[dbo].[all_stock_unload_items].[ui_id], [logistics].[dbo].[all_stock_unload_items].[ui_qnty1], [logistics].[dbo].[all_stock_unload_items].[ui_qnty2]  NULL     PLAN_ROW                                                         0        1
                      |         |--Nested Loops(Inner Join, OUTER REFERENCES:([logistics].[dbo].[all_stock_unloads].[ul_id]))                                                                                                                                    1           17          16          Nested Loops                   Inner Join                     OUTER REFERENCES:([logistics].[dbo].[all_stock_unloads].[ul_id])                                                                                                                                                                                                 NULL                                                                                                                                                                                                                                                             2,639481      0             1,103303E-05  19          0,06623133       [Uniq1002], [logistics].[dbo].[all_stock_unload_items].[ui_company_id], [logistics].[dbo].[all_stock_unload_items].[ui_id]                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |--Nested Loops(Inner Join, WHERE:([logistics].[dbo].[all_stock_unloads].[ul_company_id]=[Expr1272]))                                                                                                                       1           18          17          Nested Loops                   Inner Join                     WHERE:([logistics].[dbo].[all_stock_unloads].[ul_company_id]=[Expr1272])                                                                                                                                                                                         NULL                                                                                                                                                                                                                                                             1,498258      0             5,045004E-05  11          0,06155498       [logistics].[dbo].[all_stock_unloads].[ul_id]                                                                                                                                                                                 NULL     PLAN_ROW                                                         0        1
                      |         |    |    |--Assert(WHERE:(CASE WHEN [Expr1271]>(1) THEN (0) ELSE NULL END))                                                                                                                                                     1           19          18          Assert                         Assert                         WHERE:(CASE WHEN [Expr1271]>(1) THEN (0) ELSE NULL END)                                                                                                                                                                                                          NULL                                                                                                                                                                                                                                                             1             0             1,8E-07       11          0,00657166       [Expr1272]                                                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |    |    |--Stream Aggregate(DEFINE:([Expr1271]=Count(*), [Expr1272]=ANY([logistics].[dbo].[all_companies].[cmp_id])))                                                                                                     1           20          19          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1271]=Count(*), [Expr1272]=ANY([logistics].[dbo].[all_companies].[cmp_id])                                                                                                                                                                                  1             0             1,1E-06       19          0,00657148       [Expr1271], [Expr1272]                                                                                                                                                                                                        NULL     PLAN_ROW                                                         0        1
                      |         |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id]))                                                                                                          1           21          20          Nested Loops                   Inner Join                     OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id])                                                                                                                                                                                           NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      11          0,00657038       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |    |              |--Index Seek(OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD)                                          1           22          21          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD                                                                                                                  [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                                                              1             0,003125      0,0001581     11          0,0032831        [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                           NULL     PLAN_ROW                                                         0        1
                      |         |    |    |              |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FORWARD)     1           23          21          Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FORWARD                                                                                       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                                                       1             0,003125      0,0001581     11          0,0032831        [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([logistics].[dbo].[all_jobs].[job_id]))                                                                                                                                  1           28          18          Nested Loops                   Inner Join                     OUTER REFERENCES:([logistics].[dbo].[all_jobs].[job_id])                                                                                                                                                                                                         NULL                                                                                                                                                                                                                                                             12,06939      0             5,045004E-05  15          0,05492925       [logistics].[dbo].[all_stock_unloads].[ul_company_id], [logistics].[dbo].[all_stock_unloads].[ul_id]                                                                                                                          NULL     PLAN_ROW                                                         0        1
                      |         |    |         |--Nested Loops(Inner Join, WHERE:([Expr1274]=[logistics].[dbo].[all_jobs].[job_company_id]))                                                                                                                     1           29          28          Nested Loops                   Inner Join                     WHERE:([Expr1274]=[logistics].[dbo].[all_jobs].[job_company_id])                                                                                                                                                                                                 NULL                                                                                                                                                                                                                                                             12,04545      0             0,001109234   11          0,02230258       [logistics].[dbo].[all_jobs].[job_id]                                                                                                                                                                                         NULL     PLAN_ROW                                                         0        1
                      |         |    |         |    |--Assert(WHERE:(CASE WHEN [Expr1273]>(1) THEN (0) ELSE NULL END))                                                                                                                                           1           30          29          Assert                         Assert                         WHERE:(CASE WHEN [Expr1273]>(1) THEN (0) ELSE NULL END)                                                                                                                                                                                                          NULL                                                                                                                                                                                                                                                             1             0             1,8E-07       11          0,00657166       [Expr1274]                                                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |         |    |    |--Stream Aggregate(DEFINE:([Expr1273]=Count(*), [Expr1274]=ANY([logistics].[dbo].[all_companies].[cmp_id])))                                                                                           1           31          30          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1273]=Count(*), [Expr1274]=ANY([logistics].[dbo].[all_companies].[cmp_id])                                                                                                                                                                                  1             0             1,1E-06       19          0,00657148       [Expr1273], [Expr1274]                                                                                                                                                                                                        NULL     PLAN_ROW                                                         0        1
                      |         |    |         |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id]))                                                                                                1           32          31          Nested Loops                   Inner Join                     OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id])                                                                                                                                                                                           NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      11          0,00657038       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |         |    |              |--Index Seek(OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD)                                1           33          32          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD                                                                                                                  [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                                                              1             0,003125      0,0001581     11          0,0032831        [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                           NULL     PLAN_ROW                                                         0        1
                      |         |    |         |    |              |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FO 1           34          32          Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FORWARD                                                                                       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                                                       1             0,003125      0,0001581     11          0,0032831        [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |         |    |--Nested Loops(Inner Join)                                                                                                                                                                                  1           39          29          Nested Loops                   Inner Join                     NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             265,3671      0             0,001109234   15          0,01454208       [logistics].[dbo].[all_jobs].[job_company_id], [logistics].[dbo].[all_jobs].[job_id]                                                                                                                                          NULL     PLAN_ROW                                                         0        1
                      |         |    |         |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1276]))                                                                                                                                              1           40          39          Nested Loops                   Inner Join                     OUTER REFERENCES:([Expr1276])                                                                                                                                                                                                                                    NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      9           0,00985894       NULL                                                                                                                                                                                                                          NULL     PLAN_ROW                                                         0        1
                      |         |    |         |         |    |--Assert(WHERE:(CASE WHEN [Expr1275]>(1) THEN (0) ELSE NULL END))                                                                                                                                 1           41          40          Assert                         Assert                         WHERE:(CASE WHEN [Expr1275]>(1) THEN (0) ELSE NULL END)                                                                                                                                                                                                          NULL                                                                                                                                                                                                                                                             1             0             1,8E-07       11          0,00657166       [Expr1276]                                                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |         |         |    |    |--Stream Aggregate(DEFINE:([Expr1275]=Count(*), [Expr1276]=ANY([logistics].[dbo].[all_companies].[cmp_id])))                                                                                 1           42          41          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1275]=Count(*), [Expr1276]=ANY([logistics].[dbo].[all_companies].[cmp_id])                                                                                                                                                                                  1             0             1,1E-06       19          0,00657148       [Expr1275], [Expr1276]                                                                                                                                                                                                        NULL     PLAN_ROW                                                         0        1
                      |         |    |         |         |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id]))                                                                                      1           43          42          Nested Loops                   Inner Join                     OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id])                                                                                                                                                                                           NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      11          0,00657038       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |         |         |    |              |--Index Seek(OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD)                      1           44          43          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD                                                                                                                  [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                                                              1             0,003125      0,0001581     11          0,0032831        [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                           NULL     PLAN_ROW                                                         0        1
                      |         |    |         |         |    |              |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id])  1           45          43          Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FORWARD                                                                                       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                                                       1             0,003125      0,0001581     11          0,0032831        [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1
                      |         |    |         |         |    |--Index Seek(OBJECT:([logistics].[dbo].[all_contacts].[ct_is_client]), SEEK:([logistics].[dbo].[all_contacts].[ct_is_client]=(1) AND [logistics].[dbo].[all_contacts].[ct_company_id]=[Expr1276]  1           50          40          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_contacts].[ct_is_client]), SEEK:([logistics].[dbo].[all_contacts].[ct_is_client]=(1) AND [logistics].[dbo].[all_contacts].[ct_company_id]=[Expr1276] AND [logistics].[dbo].[all_contacts].[ct_id]=(5733)) ORDERED FORWARD         NULL                                                                                                                                                                                                                                                             1             0,003125      0,0001581     9           0,0032831        NULL                                                                                                                                                                                                                          NULL     PLAN_ROW                                                         0        1
                      |         |    |         |         |--Index Seek(OBJECT:([logistics].[dbo].[all_jobs].[job_client_id]), SEEK:([logistics].[dbo].[all_jobs].[job_client_id]=(5733)) ORDERED FORWARD)                                                        1           51          39          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_jobs].[job_client_id]), SEEK:([logistics].[dbo].[all_jobs].[job_client_id]=(5733)) ORDERED FORWARD                                                                                                                                [logistics].[dbo].[all_jobs].[job_company_id], [logistics].[dbo].[all_jobs].[job_id]                                                                                                                                                                             265,3671      0,003125      0,0004489038  15          0,003573904      [logistics].[dbo].[all_jobs].[job_company_id], [logistics].[dbo].[all_jobs].[job_id]                                                                                                                                          NULL     PLAN_ROW                                                         0        1
                      |         |    |         |--Index Seek(OBJECT:([logistics].[dbo].[all_stock_unloads].[ul_job_id]), SEEK:([logistics].[dbo].[all_stock_unloads].[ul_job_id]=[logistics].[dbo].[all_jobs].[job_id]) ORDERED FORWARD)                         1           53          28          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_stock_unloads].[ul_job_id]), SEEK:([logistics].[dbo].[all_stock_unloads].[ul_job_id]=[logistics].[dbo].[all_jobs].[job_id]) ORDERED FORWARD                                                                                       [logistics].[dbo].[all_stock_unloads].[ul_company_id], [logistics].[dbo].[all_stock_unloads].[ul_id]                                                                                                                                                             1,001987      0,003125      0,0001581022  15          0,03257622       [logistics].[dbo].[all_stock_unloads].[ul_company_id], [logistics].[dbo].[all_stock_unloads].[ul_id]                                                                                                                          NULL     PLAN_ROW                                                         0        12,04545
                      |         |    |--Index Seek(OBJECT:([logistics].[dbo].[all_stock_unload_items].[ui_unload_id]), SEEK:([logistics].[dbo].[all_stock_unload_items].[ui_unload_id]=[logistics].[dbo].[all_stock_unloads].[ul_id]) ORDERED FORWARD)           1           55          17          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_stock_unload_items].[ui_unload_id]), SEEK:([logistics].[dbo].[all_stock_unload_items].[ui_unload_id]=[logistics].[dbo].[all_stock_unloads].[ul_id]) ORDERED FORWARD                                                               [Uniq1002], [logistics].[dbo].[all_stock_unload_items].[ui_company_id], [logistics].[dbo].[all_stock_unload_items].[ui_id]                                                                                                                                       1,7617        0,003125      0,0001589379  19          0,004665321      [Uniq1002], [logistics].[dbo].[all_stock_unload_items].[ui_company_id], [logistics].[dbo].[all_stock_unload_items].[ui_id]                                                                                                    NULL     PLAN_ROW                                                         0        1,498258
                      |         |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_stock_unload_items].[ui_company_id]), SEEK:([logistics].[dbo].[all_stock_unload_items].[ui_company_id]=[logistics].[dbo].[all_stock_unload_items].[ui_company_id] AND [lo 1           57          16          Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_stock_unload_items].[ui_company_id]), SEEK:([logistics].[dbo].[all_stock_unload_items].[ui_company_id]=[logistics].[dbo].[all_stock_unload_items].[ui_company_id] AND [logistics].[dbo].[all_stock_unload_items].[ui_id]=[logisti [logistics].[dbo].[all_stock_unload_items].[ui_qnty1], [logistics].[dbo].[all_stock_unload_items].[ui_qnty2]                                                                                                                                                     1             0,003125      0,0001581     15          0,006314297      [logistics].[dbo].[all_stock_unload_items].[ui_qnty1], [logistics].[dbo].[all_stock_unload_items].[ui_qnty2]                                                                                                                  NULL     PLAN_ROW                                                         0        2,639481
                      |--Compute Scalar(DEFINE:([Expr1260]=[Expr1260], [Expr1261]=[Expr1261]))                                                                                                                                                                   1           64          5           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1260]=[Expr1260], [Expr1261]=[Expr1261])                                                                                                                                                                                                            [Expr1260]=[Expr1260], [Expr1261]=[Expr1261]                                                                                                                                                                                                                     1             0             1E-07         15          0,3009944        [Expr1260], [Expr1261]                                                                                                                                                                                                        NULL     PLAN_ROW                                                         0        1,04165
                           |--Compute Scalar(DEFINE:([Expr1260]=CASE WHEN [Expr1288]=(0) THEN NULL ELSE [Expr1289] END, [Expr1261]=CASE WHEN [Expr1290]=(0) THEN NULL ELSE [Expr1291] END))                                                                      1           65          64          Compute Scalar                 Compute Scalar                 DEFINE:([Expr1260]=CASE WHEN [Expr1288]=(0) THEN NULL ELSE [Expr1289] END, [Expr1261]=CASE WHEN [Expr1290]=(0) THEN NULL ELSE [Expr1291] END)                                                                                                                    [Expr1260]=CASE WHEN [Expr1288]=(0) THEN NULL ELSE [Expr1289] END, [Expr1261]=CASE WHEN [Expr1290]=(0) THEN NULL ELSE [Expr1291] END                                                                                                                             1             0             1,1E-06       15          0,3009943        [Expr1260], [Expr1261]                                                                                                                                                                                                        NULL     PLAN_ROW                                                         0        1,04165
                                |--Stream Aggregate(DEFINE:([Expr1288]=COUNT_BIG([logistics].[dbo].[all_stock_load_items].[li_qnty1]), [Expr1289]=SUM([logistics].[dbo].[all_stock_load_items].[li_qnty1]), [Expr1290]=COUNT_BIG([logistics].[dbo].[all_stock_lo 1           66          65          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1288]=COUNT_BIG([logistics].[dbo].[all_stock_load_items].[li_qnty1]), [Expr1289]=SUM([logistics].[dbo].[all_stock_load_items].[li_qnty1]), [Expr1290]=COUNT_BIG([logistics].[dbo].[all_stock_load_items].[li_qnty2]), [Expr1291]=SUM([logistics].[dbo].[all 1             0             1,1E-06       15          0,3009943        [Expr1288], [Expr1289], [Expr1290], [Expr1291]                                                                                                                                                                                NULL     PLAN_ROW                                                         0        1,04165
                                     |--Nested Loops(Left Outer Join, OUTER REFERENCES:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id]))                                                                                                           1           67          66          Nested Loops                   Left Outer Join                OUTER REFERENCES:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id])                                                                                                                                                                                  NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      15          0,3009931        [logistics].[dbo].[all_stock_load_items].[li_qnty1], [logistics].[dbo].[all_stock_load_items].[li_qnty2]                                                                                                                      NULL     PLAN_ROW                                                         0        1,04165
                                          |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1124], [logistics].[dbo].[all_stock_load_items].[li_company_id], [logistics].[dbo].[all_stock_load_items].[li_id]))                                                 1           68          67          Nested Loops                   Inner Join                     OUTER REFERENCES:([Uniq1124], [logistics].[dbo].[all_stock_load_items].[li_company_id], [logistics].[dbo].[all_stock_load_items].[li_id])                                                                                                                        NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      19          0,01320568       [logistics].[dbo].[all_stock_load_items].[li_unload_item_id], [logistics].[dbo].[all_stock_load_items].[li_qnty1], [logistics].[dbo].[all_stock_load_items].[li_qnty2]                                                        NULL     PLAN_ROW                                                         0        1,04165
                                          |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1278]))                                                                                                                                                        1           69          68          Nested Loops                   Inner Join                     OUTER REFERENCES:([Expr1278])                                                                                                                                                                                                                                    NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      23          0,009895334      [Uniq1124], [logistics].[dbo].[all_stock_load_items].[li_company_id], [logistics].[dbo].[all_stock_load_items].[li_id], [logistics].[dbo].[all_stock_load_items].[li_unload_item_id]                                          NULL     PLAN_ROW                                                         0        1,04165
                                          |    |    |--Assert(WHERE:(CASE WHEN [Expr1277]>(1) THEN (0) ELSE NULL END))                                                                                                                                           1           70          69          Assert                         Assert                         WHERE:(CASE WHEN [Expr1277]>(1) THEN (0) ELSE NULL END)                                                                                                                                                                                                          NULL                                                                                                                                                                                                                                                             1             0             1,8E-07       11          0,006585057      [Expr1278]                                                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                          |    |    |    |--Stream Aggregate(DEFINE:([Expr1277]=Count(*), [Expr1278]=ANY([logistics].[dbo].[all_companies].[cmp_id])))                                                                                           1           71          70          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1277]=Count(*), [Expr1278]=ANY([logistics].[dbo].[all_companies].[cmp_id])                                                                                                                                                                                  1             0             1,1E-06       19          0,00658487       [Expr1277], [Expr1278]                                                                                                                                                                                                        NULL     PLAN_ROW                                                         0        1,04165
                                          |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id]))                                                                                                1           72          71          Nested Loops                   Inner Join                     OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id])                                                                                                                                                                                           NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      11          0,006583724      [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                          |    |    |              |--Index Seek(OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD)                                1           73          72          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD                                                                                                                  [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                                                              1             0,003125      0,0001581     11          0,003289685      [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                           NULL     PLAN_ROW                                                         0        1,04165
                                          |    |    |              |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FO 1           74          72          Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FORWARD                                                                                       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                                                       1             0,003125      0,0001581     11          0,003289685      [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                          |    |    |--Index Seek(OBJECT:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id]), SEEK:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id]=[logistics].[dbo].[all_stock_unload_items].[ui_id] A 1           79          69          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id]), SEEK:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id]=[logistics].[dbo].[all_stock_unload_items].[ui_id] AND [logistics].[dbo].[all_stock_load_items].[li_company_id]=[Expr1 [Uniq1124], [logistics].[dbo].[all_stock_load_items].[li_company_id], [logistics].[dbo].[all_stock_load_items].[li_id], [logistics].[dbo].[all_stock_load_items].[li_unload_item_id]                                                                             1             0,003125      0,0001581     23          0,003305923      [Uniq1124], [logistics].[dbo].[all_stock_load_items].[li_company_id], [logistics].[dbo].[all_stock_load_items].[li_id], [logistics].[dbo].[all_stock_load_items].[li_unload_item_id]                                          NULL     PLAN_ROW                                                         0        1,04165
                                          |    |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_stock_load_items].[li_company_id]), SEEK:([logistics].[dbo].[all_stock_load_items].[li_company_id]=[logistics].[dbo].[all_stock_load_items].[li_company_id 1           81          68          Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_stock_load_items].[li_company_id]), SEEK:([logistics].[dbo].[all_stock_load_items].[li_company_id]=[logistics].[dbo].[all_stock_load_items].[li_company_id] AND [logistics].[dbo].[all_stock_load_items].[li_id]=[logistics].[dbo [logistics].[dbo].[all_stock_load_items].[li_qnty1], [logistics].[dbo].[all_stock_load_items].[li_qnty2]                                                                                                                                                         1             0,003125      0,0001581     15          0,003305989      [logistics].[dbo].[all_stock_load_items].[li_qnty1], [logistics].[dbo].[all_stock_load_items].[li_qnty2]                                                                                                                      NULL     PLAN_ROW                                                         0        1,04165
                                          |--Filter(WHERE:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id]=[logistics].[dbo].[all_stock_load_items].[li_unload_item_id]))                                                                           1           87          67          Filter                         Filter                         WHERE:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id]=[logistics].[dbo].[all_stock_load_items].[li_unload_item_id])                                                                                                                                NULL                                                                                                                                                                                                                                                             1             0             4,8E-07       9           0,2877831        NULL                                                                                                                                                                                                                          NULL     PLAN_ROW                                                         0        1,04165
                                               |--Top(TOP EXPRESSION:((1)))                                                                                                                                                                                      1           88          87          Top                            Top                            TOP EXPRESSION:((1)), TIE COLUMNS:([Expr1258])                                                                                                                                                                                                                   NULL                                                                                                                                                                                                                                                             1             0             1E-07         11          0,2877826        [logistics].[dbo].[all_stock_load_items].[li_unload_item_id]                                                                                                                                                                  NULL     PLAN_ROW                                                         0        1,04165
                                                    |--Sort(ORDER BY:([Expr1258] ASC))                                                                                                                                                                           1           89          88          Sort                           Sort                           ORDER BY:([Expr1258] ASC)                                                                                                                                                                                                                                        NULL                                                                                                                                                                                                                                                             1             0,01126126    0,06025695    19          0,2877825        [logistics].[dbo].[all_stock_load_items].[li_unload_item_id], [Expr1258]                                                                                                                                                      NULL     PLAN_ROW                                                         0        1,04165
                                                         |--Sequence Project(DEFINE:([Expr1258]=row_number))                                                                                                                                                     1           90          89          Sequence Project               Compute Scalar                 DEFINE:([Expr1258]=row_number)                                                                                                                                                                                                                                   [Expr1258]=row_number                                                                                                                                                                                                                                            3299,308      0             0,0003299308  19          0,2132856        [logistics].[dbo].[all_stock_load_items].[li_unload_item_id], [Expr1258]                                                                                                                                                      NULL     PLAN_ROW                                                         0        1,04165
                                                              |--Compute Scalar(DEFINE:([Expr1287]=(1)))                                                                                                                                                         1           91          90          Compute Scalar                 Compute Scalar                 DEFINE:([Expr1287]=(1))                                                                                                                                                                                                                                          [Expr1287]=(1)                                                                                                                                                                                                                                                   3299,308      0             6,598615E-05  19          0,2133516        [logistics].[dbo].[all_stock_load_items].[li_unload_item_id], [logistics].[dbo].[all_stock_loads].[ld_date], [Segment1286], [Expr1287]                                                                                        NULL     PLAN_ROW                                                         0        1,04165
                                                                   |--Segment                                                                                                                                                                                    1           92          91          Segment                        Segment                        [logistics].[dbo].[all_stock_load_items].[li_unload_item_id]                                                                                                                                                                                                     NULL                                                                                                                                                                                                                                                             3299,308      0             6,598615E-05  19          0,2133516        [logistics].[dbo].[all_stock_load_items].[li_unload_item_id], [logistics].[dbo].[all_stock_loads].[ld_date], [Segment1286]                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                                                        |--Sort(ORDER BY:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id] ASC, [logistics].[dbo].[all_stock_loads].[ld_date] DESC))                                                 1           93          92          Sort                           Sort                           ORDER BY:([logistics].[dbo].[all_stock_load_items].[li_unload_item_id] ASC, [logistics].[dbo].[all_stock_loads].[ld_date] DESC)                                                                                                                                  NULL                                                                                                                                                                                                                                                             3299,308      0,01126126    0,06025695    19          0,2129419        [logistics].[dbo].[all_stock_load_items].[li_unload_item_id], [logistics].[dbo].[all_stock_loads].[ld_date]                                                                                                                   NULL     PLAN_ROW                                                         0        1,04165
                                                                             |--Hash Match(Right Outer Join, HASH:([logistics].[dbo].[all_stock_loads].[ld_id])=([logistics].[dbo].[all_stock_load_items].[li_load_id]))                                         1           94          93          Hash Match                     Right Outer Join               HASH:([logistics].[dbo].[all_stock_loads].[ld_id])=([logistics].[dbo].[all_stock_load_items].[li_load_id])                                                                                                                                                       NULL                                                                                                                                                                                                                                                             3299,308      0             0,05633497    19          0,138445         [logistics].[dbo].[all_stock_load_items].[li_unload_item_id], [logistics].[dbo].[all_stock_loads].[ld_date]                                                                                                                   NULL     PLAN_ROW                                                         0        1,04165
                                                                                  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1282]))                                                                                                                     1           95          94          Nested Loops                   Inner Join                     OUTER REFERENCES:([Expr1282])                                                                                                                                                                                                                                    NULL                                                                                                                                                                                                                                                             1551,692      0             0,006486074   19          0,0354448        [logistics].[dbo].[all_stock_loads].[ld_id], [logistics].[dbo].[all_stock_loads].[ld_date]                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                                                                  |    |--Assert(WHERE:(CASE WHEN [Expr1281]>(1) THEN (0) ELSE NULL END))                                                                                                        1           96          95          Assert                         Assert                         WHERE:(CASE WHEN [Expr1281]>(1) THEN (0) ELSE NULL END)                                                                                                                                                                                                          NULL                                                                                                                                                                                                                                                             1             0             1,8E-07       11          0,006585057      [Expr1282]                                                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                                                                  |    |    |--Stream Aggregate(DEFINE:([Expr1281]=Count(*), [Expr1282]=ANY([logistics].[dbo].[all_companies].[cmp_id])))                                                        1           97          96          Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1281]=Count(*), [Expr1282]=ANY([logistics].[dbo].[all_companies].[cmp_id])                                                                                                                                                                                  1             0             1,1E-06       19          0,00658487       [Expr1281], [Expr1282]                                                                                                                                                                                                        NULL     PLAN_ROW                                                         0        1,04165
                                                                                  |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id]))                                                             1           98          97          Nested Loops                   Inner Join                     OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id])                                                                                                                                                                                           NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      11          0,006583724      [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                                                                  |    |              |--Index Seek(OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORW 1           99          98          Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD                                                                                                                  [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                                                              1             0,003125      0,0001581     11          0,003289685      [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                           NULL     PLAN_ROW                                                         0        1,04165
                                                                                  |    |              |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signa 1           100         98          Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FORWARD                                                                                       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                                                       1             0,003125      0,0001581     11          0,003289685      [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                                                                  |    |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_stock_loads].[ld_company_id]), SEEK:([logistics].[dbo].[all_stock_loads].[ld_company_id]=[Expr1282]) ORDERED FORWA 1           105         95          Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_stock_loads].[ld_company_id]), SEEK:([logistics].[dbo].[all_stock_loads].[ld_company_id]=[Expr1282]) ORDERED FORWARD                                                                                                              [logistics].[dbo].[all_stock_loads].[ld_id], [logistics].[dbo].[all_stock_loads].[ld_date]                                                                                                                                                                       1551,692      0,02016204    0,001863862   19          0,02210353       [logistics].[dbo].[all_stock_loads].[ld_id], [logistics].[dbo].[all_stock_loads].[ld_date]                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                                                                  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1280]))                                                                                                                     1           106         94          Nested Loops                   Inner Join                     OUTER REFERENCES:([Expr1280])                                                                                                                                                                                                                                    NULL                                                                                                                                                                                                                                                             3299,308      0             0,01379111    15          0,04431579       [logistics].[dbo].[all_stock_load_items].[li_load_id], [logistics].[dbo].[all_stock_load_items].[li_unload_item_id]                                                                                                           NULL     PLAN_ROW                                                         0        1,04165
                                                                                       |--Assert(WHERE:(CASE WHEN [Expr1279]>(1) THEN (0) ELSE NULL END))                                                                                                        1           107         106         Assert                         Assert                         WHERE:(CASE WHEN [Expr1279]>(1) THEN (0) ELSE NULL END)                                                                                                                                                                                                          NULL                                                                                                                                                                                                                                                             1             0             1,8E-07       11          0,006585057      [Expr1280]                                                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                                                                       |    |--Stream Aggregate(DEFINE:([Expr1279]=Count(*), [Expr1280]=ANY([logistics].[dbo].[all_companies].[cmp_id])))                                                        1           108         107         Stream Aggregate               Aggregate                      NULL                                                                                                                                                                                                                                                             [Expr1279]=Count(*), [Expr1280]=ANY([logistics].[dbo].[all_companies].[cmp_id])                                                                                                                                                                                  1             0             1,1E-06       19          0,00658487       [Expr1279], [Expr1280]                                                                                                                                                                                                        NULL     PLAN_ROW                                                         0        1,04165
                                                                                       |         |--Nested Loops(Inner Join, OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id]))                                                             1           109         108         Nested Loops                   Inner Join                     OUTER REFERENCES:([logistics].[dbo].[all_signatures].[sgn_company_id])                                                                                                                                                                                           NULL                                                                                                                                                                                                                                                             1             0             4,18E-06      11          0,006583724      [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                                                                       |              |--Index Seek(OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORW 1           110         109         Index Seek                     Index Seek                     OBJECT:([logistics].[dbo].[all_signatures].[sgn_login]), SEEK:([logistics].[dbo].[all_signatures].[sgn_login]=original_login()) ORDERED FORWARD                                                                                                                  [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                                                              1             0,003125      0,0001581     11          0,003289685      [logistics].[dbo].[all_signatures].[sgn_company_id]                                                                                                                                                                           NULL     PLAN_ROW                                                         0        1,04165
                                                                                       |              |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signa 1           111         109         Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_companies].[cmp_id]), SEEK:([logistics].[dbo].[all_companies].[cmp_id]=[logistics].[dbo].[all_signatures].[sgn_company_id]) ORDERED FORWARD                                                                                       [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                                                       1             0,003125      0,0001581     11          0,003289685      [logistics].[dbo].[all_companies].[cmp_id]                                                                                                                                                                                    NULL     PLAN_ROW                                                         0        1,04165
                                                                                       |--Clustered Index Seek(OBJECT:([logistics].[dbo].[all_stock_load_items].[li_company_id]), SEEK:([logistics].[dbo].[all_stock_load_items].[li_company_id]=[Expr1280]) ORD 1           116         106         Clustered Index Seek           Clustered Index Seek           OBJECT:([logistics].[dbo].[all_stock_load_items].[li_company_id]), SEEK:([logistics].[dbo].[all_stock_load_items].[li_company_id]=[Expr1280]) ORDERED FORWARD                                                                                                    [logistics].[dbo].[all_stock_load_items].[li_load_id], [logistics].[dbo].[all_stock_load_items].[li_unload_item_id]                                                                                                                                              3299,308      0,0194213     0,003786239   15          0,02336523       [logistics].[dbo].[all_stock_load_items].[li_load_id], [logistics].[dbo].[all_stock_load_items].[li_unload_item_id]                                                                                                           NULL     PLAN_ROW                                                         0        1,04165


Спасибо!
9 июл 09, 16:11    [7397845]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Call
Member

Откуда: SFBA
Сообщений: 481
Уберите повторяющийся блок из запроса.

declare @load_items table(li_unload_item_id int)

insert into @load_items
SELECT TOP 1 WITH TIES lastli.li_unload_item_id
   FROM dbo.stock_load_items lastli 
   LEFT JOIN logistics.k_stock_load lastkld  ON lastli.li_load_id = lastkld.ld_id 
   ORDER BY ROW_NUMBER() OVER (PARTITION BY li_unload_item_id ORDER BY ld_date DESC)


SELECT COUNT(*) FROM 
(
 SELECT
  kul.cl_id, 
  CASE WHEN ISNULL(ui.ui_qnty1 - ISNULL(uilt.ld_qnty1, 0), 0) > 0 OR ISNULL(ui.ui_qnty2 - ISNULL(uilt.ld_qnty2, 0), 0) > 0 THEN 1 ELSE 0 END in_stock
 FROM dbo.stock_unload_items ui 
 LEFT OUTER JOIN logistics.k_stock_unload kul ON ui.ui_unload_id = kul.ul_id 
 LEFT OUTER JOIN 
 (
  SELECT li.li_unload_item_id ui_id, SUM(li.li_qnty1) ld_qnty1, SUM(li.li_qnty2) ld_qnty2, SUM(li.li_weight) ld_weight, SUM(li.li_volume) ld_volume
  FROM dbo.stock_load_items li 
  LEFT JOIN logistics.k_stock_load kld ON li.li_load_id = kld.ld_id
  LEFT JOIN @load_items last ON last.li_unload_item_id = li.li_unload_item_id
  GROUP BY li.li_unload_item_id
 ) uilt ON ui.ui_id = uilt.ui_id 
) stock 
WHERE cl_id = 5733 AND in_stock=1
9 июл 09, 18:03    [7398817]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 666
Call,

Это работает! =)

Правда пришлось обернуть подзапрос в multi-statement table-valued function.

Холмс, как вы догадались?
10 июл 09, 00:07    [7399531]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Call
Member

Откуда: SFBA
Сообщений: 481
Student007
Call,

Это работает! =)

Правда пришлось обернуть подзапрос в multi-statement table-valued function.

Холмс, как вы догадались?


Ради интереса сравните планы исполнения table-valued function с insert into @load_items, что приведен выше.
10 июл 09, 01:32    [7399730]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 666
Call,

Ковырялся и поставил LEFT MERGE JOIN там где /*LEFT JOIN - уменьшение времени выполнения было практически до нуля. Помогла статейка для чайников от Pinal Dave. Но почему оптимизатор не догадался?
10 июл 09, 13:26    [7401711]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Glory
Member

Откуда:
Сообщений: 104760
Student007
Call,

Ковырялся и поставил LEFT MERGE JOIN там где /*LEFT JOIN - уменьшение времени выполнения было практически до нуля. Помогла статейка для чайников от Pinal Dave. Но почему оптимизатор не догадался?

А что вы сделали, чтобы оптимизатор догадался ?
Сделали нужные индексы ? Статистики ?
10 июл 09, 13:35    [7401771]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 666
Glory
А что вы сделали, чтобы оптимизатор догадался ?
Сделали нужные индексы ? Статистики ?

К своему стыду, не знаю, сделал ли я всё что нужно. Равно как и не знаю как это проверить.
Как выяснить что я не сделал?
10 июл 09, 13:46    [7401854]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Glory
Member

Откуда:
Сообщений: 104760
Student007
Glory
А что вы сделали, чтобы оптимизатор догадался ?
Сделали нужные индексы ? Статистики ?

К своему стыду, не знаю, сделал ли я всё что нужно. Равно как и не знаю как это проверить.
Как выяснить что я не сделал?

Вы знаете, что такое индексы ?
10 июл 09, 13:47    [7401867]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 666
Glory,

Это мой первый в жизни хинт! Что такое индексы и статистика я читал и читаю, но сказать что знаю - думаю нет, так как не знаю как прочитанное применить к своему случаю. Как понять что надо сделать, чтобы оптимизатор сам догадался, что Merge Join быстрее?
10 июл 09, 14:09    [7402053]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Glory
Member

Откуда:
Сообщений: 104760
Student007
Glory,

Это мой первый в жизни хинт! Что такое индексы и статистика я читал и читаю, но сказать что знаю - думаю нет, так как не знаю как прочитанное применить к своему случаю. Как понять что надо сделать, чтобы оптимизатор сам догадался, что Merge Join быстрее?

Начнем с простого.
У ваших таблиц есть индексы ?
10 июл 09, 14:11    [7402074]     Ответить | Цитировать Сообщить модератору
 Re: Помогите проанализировать план  [new]
Student007
Member

Откуда: Санкт-Петербург
Сообщений: 666
Glory
У ваших таблиц есть индексы ?


Да. Я расставлял их "интуитивно".
10 июл 09, 14:16    [7402117]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить