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

Откуда: Republic of Belarus
Сообщений: 451
[/SRC]Привет всем. 
Есть у меня в базе таблица NAVIGATIONS, которая находиться постоянно под insert-нагрузкой. Так вот, как с нее снимать лучше информацию, с использование временных таблиц или без?

Вариант 1:

[src]
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SELECT
 SUM (T5.INTERVAL_TIME)
FROM
 (
  SELECT
   INTERVAL_TIME = DATEDIFF (SS, T3.RECV_DATE, T4.RECV_DATE)
  FROM
   (
    SELECT 
     ROW_NUMBER () OVER (PARTITION BY T1.ID_CAR ORDER BY T1.RECV_DATE) AS ROWNUMBER,
     T1.ID_CAR,
     T1.RECV_DATE,
     T1.SPEED
    FROM 
     NAVIGATIONS AS T1
    WHERE
     T1.ID_CAR = 43 AND
     T1.RECV_DATE > '16-11-2009' AND
     T1.RECV_DATE <= '19-11-2009'
   ) AS T3
   JOIN
   (
    SELECT 
     ROW_NUMBER () OVER (PARTITION BY T2.ID_CAR ORDER BY T2.RECV_DATE) AS ROWNUMBER,
     T2.ID_CAR,
     T2.RECV_DATE,
     T2.SPEED
    FROM 
     NAVIGATIONS AS T2
    WHERE
     T2.ID_CAR = 43 AND
     T2.RECV_DATE > '16-11-2009' AND
     T2.RECV_DATE <= '19-11-2009'
   ) AS T4 ON T3.ROWNUMBER = T4.ROWNUMBER - 1 AND 
              T3.ID_CAR = T4.ID_CAR   
  WHERE
   T3.SPEED < 2 AND
   T4.SPEED < 2
 ) AS T5
SET STATISTICS  IO OFF
SET STATISTICS PROFILE OFF

План выполнения запроса:

1	1	SELECT   SUM (T5.INTERVAL_TIME)  FROM   (    SELECT     INTERVAL_TIME = DATEDIFF (SS, T3.RECV_DATE, T4.RECV_DATE)    FROM     (      SELECT        ROW_NUMBER () OVER (PARTITION BY T1.ID_CAR ORDER BY T1.RECV_DATE) AS ROWNUMBER,       T1.ID_CAR,       T1.RECV_DATE,       T1.SPEED      FROM        NAVIGATIONS AS T1      WHERE       T1.ID_CAR = 43 AND       T1.RECV_DATE > '16-11-2009' AND       T1.RECV_DATE <= '19-11-2009'     ) AS T3     JOIN     (      SELECT        ROW_NUMBER () OVER (PARTITION BY T2.ID_CAR ORDER BY T2.RECV_DATE) AS ROWNUMBER,       T2.ID_CAR,       T2.RECV_DATE,       T2.SPEED      FROM        NAVIGATIONS AS T2      WHERE       T2.ID_CAR = 43 AND       T2.RECV_DATE > '16-11-2009' AND       T2.RECV_DATE <= '19-11-2009'     ) AS T4 ON T3.ROWNUMBER = T4.ROWNUMBER - 1 AND                 T3.ID_CAR = T4.ID_CAR       WHERE     T3.SPEED < 2 AND     T4.SPEED < 2   ) AS T5	1	1	0	NULL	NULL	NULL	NULL	1	NULL	NULL	NULL	6,462665	NULL	NULL	SELECT	0	NULL
0	0	  |--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [globalagg1011]=(0) THEN NULL ELSE [globalagg1013] END))	1	3	1	Compute Scalar	Compute Scalar	DEFINE:([Expr1007]=CASE WHEN [globalagg1011]=(0) THEN NULL ELSE [globalagg1013] END)	[Expr1007]=CASE WHEN [globalagg1011]=(0) THEN NULL ELSE [globalagg1013] END	1	0	1E-07	11	6,462665	[Expr1007]	NULL	PLAN_ROW	0	1
1	1	       |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010]), [globalagg1013]=SUM([partialagg1012])))	1	4	3	Stream Aggregate	Aggregate	NULL	[globalagg1011]=SUM([partialagg1010]), [globalagg1013]=SUM([partialagg1012])	1	0	1,7E-06	19	6,462665	[globalagg1011], [globalagg1013]	NULL	PLAN_ROW	0	1
2	1	            |--Parallelism(Gather Streams)	1	5	4	Parallelism	Gather Streams	NULL	NULL	2	0	0,02850291	19	6,462663	[partialagg1010], [partialagg1012]	NULL	PLAN_ROW	1	1
2	2	                 |--Stream Aggregate(DEFINE:([partialagg1010]=COUNT_BIG(datediff(second,[NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T1].[RECV_DATE],[NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T2].[RECV_DATE])), [partialagg1012]=SUM(datediff(second,[NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T1].[RECV_DATE],[NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T2].[RECV_DATE]))))	1	6	5	Stream Aggregate	Aggregate	NULL	[partialagg1010]=COUNT_BIG(datediff(second,[NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T1].[RECV_DATE],[NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T2].[RECV_DATE])), [partialagg1012]=SUM(datediff(second,[NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T1].[RECV_DATE],[NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T2].[RECV_DATE]))	2	0	3,900442	19	6,43416	[partialagg1010], [partialagg1012]	NULL	PLAN_ROW	1	1
14667	2	                      |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Expr1002])=([Expr1015]), RESIDUAL:([Expr1002]=([Expr1005]-(1))))	1	7	6	Merge Join	Inner Join	MANY-TO-MANY MERGE:([Expr1002])=([Expr1015]), RESIDUAL:([Expr1002]=([Expr1005]-(1)))	NULL	1,300147E+07	0,008355693	1,487139	23	2,533718	[T1].[RECV_DATE], [T2].[RECV_DATE]	NULL	PLAN_ROW	1	1
15811	2	                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1002]), ORDER BY:([Expr1002] ASC))	1	8	7	Parallelism	Repartition Streams	PARTITION COLUMNS:([Expr1002]), ORDER BY:([Expr1002] ASC)	NULL	11402,4	0	0,1077766	23	0,5158737	[T1].[RECV_DATE], [Expr1002]	NULL	PLAN_ROW	1	1
15811	2	                           |    |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1008]) PARTITION ID:([PtnIds1008]))	1	9	8	Nested Loops	Inner Join	OUTER REFERENCES:([PtnIds1008]) PARTITION ID:([PtnIds1008])	NULL	11402,4	0	0,02383102	23	0,4080971	[T1].[RECV_DATE], [Expr1002]	NULL	PLAN_ROW	1	1
1	2	                           |         |--Parallelism(Distribute Streams, Demand Partitioning)	1	10	9	Parallelism	Distribute Streams	NULL	NULL	1	0	0,02850187	11	0,02850303	[PtnIds1008]	NULL	PLAN_ROW	1	1
1	1	                           |         |    |--Constant Scan(VALUES:(((3))))	1	11	10	Constant Scan	Constant Scan	VALUES:(((3)))	NULL	1	0	1,157E-06	11	1,157E-06	[PtnIds1008]	NULL	PLAN_ROW	0	1
15811	1	                           |         |--Filter(WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[SPEED] as [T1].[SPEED]<(2)))	1	13	9	Filter	Filter	WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[SPEED] as [T1].[SPEED]<(2))	NULL	11402,4	0	0,00858613	23	0,3557631	[T1].[RECV_DATE], [Expr1002]	NULL	PLAN_ROW	1	1
25248	1	                           |              |--Sequence Project(DEFINE:([Expr1002]=row_number))	1	14	13	Sequence Project	Compute Scalar	DEFINE:([Expr1002]=row_number)	[Expr1002]=row_number	17887,77	0	0,001788777	25	0,347177	[T1].[SPEED], [T1].[RECV_DATE], [Expr1002]	NULL	PLAN_ROW	1	1
0	0	                           |                   |--Compute Scalar(DEFINE:([Expr1033]=(1)))	1	15	14	Compute Scalar	Compute Scalar	DEFINE:([Expr1033]=(1))	[Expr1033]=(1)	17887,77	0	0,0003577554	25	0,3475347	[T1].[SPEED], [T1].[RECV_DATE], [Segment1032], [Expr1033]	NULL	PLAN_ROW	1	1
25248	1	                           |                        |--Segment	1	16	15	Segment	Segment	NULL	NULL	17887,77	0	0,0003577554	25	0,3475347	[T1].[SPEED], [T1].[RECV_DATE], [Segment1032]	NULL	PLAN_ROW	1	1
25248	1	                           |                             |--Clustered Index Scan(OBJECT:([NavigationEt].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS] AS [T1]),  WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR] as [T1].[ID_CAR]=(43) AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T1].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T1].[RECV_DATE]<='2009-11-19 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1008]))	1	17	16	Clustered Index Scan	Clustered Index Scan	OBJECT:([NavigationEt].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS] AS [T1]),  WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR] as [T1].[ID_CAR]=(43) AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T1].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T1].[RECV_DATE]<='2009-11-19 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1008])	[T1].[SPEED], [T1].[RECV_DATE], [T1].[ID_CAR]	17887,77	0,03868056	0,004558014	21	0,04323857	[T1].[SPEED], [T1].[RECV_DATE], [T1].[ID_CAR]	NULL	PLAN_ROW	1	1
15811	2	                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1015]), ORDER BY:([Expr1015] ASC))	1	25	7	Parallelism	Repartition Streams	PARTITION COLUMNS:([Expr1015]), ORDER BY:([Expr1015] ASC)	NULL	11402,4	0	0,1136774	31	0,5223446	[T2].[RECV_DATE], [Expr1005], [Expr1015]	NULL	PLAN_ROW	1	1
0	0	                                |--Compute Scalar(DEFINE:([Expr1015]=[Expr1005]-(1)))	1	26	25	Compute Scalar	Compute Scalar	DEFINE:([Expr1015]=[Expr1005]-(1))	[Expr1015]=[Expr1005]-(1)	11402,4	0	0,00057012	31	0,4086673	[T2].[RECV_DATE], [Expr1005], [Expr1015]	NULL	PLAN_ROW	1	1
15811	2	                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1009]) PARTITION ID:([PtnIds1009]))	1	27	26	Nested Loops	Inner Join	OUTER REFERENCES:([PtnIds1009]) PARTITION ID:([PtnIds1009])	NULL	11402,4	0	0,02383102	23	0,4080971	[T2].[RECV_DATE], [Expr1005]	NULL	PLAN_ROW	1	1
1	2	                                          |--Parallelism(Distribute Streams, Demand Partitioning)	1	28	27	Parallelism	Distribute Streams	NULL	NULL	1	0	0,02850187	11	0,02850303	[PtnIds1009]	NULL	PLAN_ROW	1	1
1	1	                                          |    |--Constant Scan(VALUES:(((3))))	1	29	28	Constant Scan	Constant Scan	VALUES:(((3)))	NULL	1	0	1,157E-06	11	1,157E-06	[PtnIds1009]	NULL	PLAN_ROW	0	1
15811	1	                                          |--Filter(WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[SPEED] as [T2].[SPEED]<(2)))	1	31	27	Filter	Filter	WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[SPEED] as [T2].[SPEED]<(2))	NULL	11402,4	0	0,00858613	23	0,3557631	[T2].[RECV_DATE], [Expr1005]	NULL	PLAN_ROW	1	1
25248	1	                                               |--Sequence Project(DEFINE:([Expr1005]=row_number))	1	32	31	Sequence Project	Compute Scalar	DEFINE:([Expr1005]=row_number)	[Expr1005]=row_number	17887,77	0	0,001788777	25	0,347177	[T2].[SPEED], [T2].[RECV_DATE], [Expr1005]	NULL	PLAN_ROW	1	1
0	0	                                                    |--Compute Scalar(DEFINE:([Expr1035]=(1)))	1	33	32	Compute Scalar	Compute Scalar	DEFINE:([Expr1035]=(1))	[Expr1035]=(1)	17887,77	0	0,0003577554	25	0,3475347	[T2].[SPEED], [T2].[RECV_DATE], [Segment1034], [Expr1035]	NULL	PLAN_ROW	1	1
25248	1	                                                         |--Segment	1	34	33	Segment	Segment	NULL	NULL	17887,77	0	0,0003577554	25	0,3475347	[T2].[SPEED], [T2].[RECV_DATE], [Segment1034]	NULL	PLAN_ROW	1	1
25248	1	                                                              |--Clustered Index Scan(OBJECT:([NavigationEt].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS] AS [T2]),  WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR] as [T2].[ID_CAR]=(43) AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T2].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T2].[RECV_DATE]<='2009-11-19 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1009]))	1	35	34	Clustered Index Scan	Clustered Index Scan	OBJECT:([NavigationEt].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS] AS [T2]),  WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR] as [T2].[ID_CAR]=(43) AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T2].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE] as [T2].[RECV_DATE]<='2009-11-19 00:00:00.000') ORDERED FORWARD PARTITION ID:([PtnIds1009])	[T2].[SPEED], [T2].[RECV_DATE], [T2].[ID_CAR]	17887,77	0,03868056	0,004558014	21	0,04323857	[T2].[SPEED], [T2].[RECV_DATE], [T2].[ID_CAR]	NULL	PLAN_ROW	1	1

Вариант 2 (с использованием временной таблицы):

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET STATISTICS PROFILE ON
SET STATISTICS IO ON

SELECT ID_CAR, RECV_DATE, SPEED
INTO #TEMP_NAV
FROM 
 NAVIGATIONS
WHERE
 ID_CAR = 43 AND
 RECV_DATE > '16-11-2009' AND
 RECV_DATE <= '19-11-2009'


SELECT
 SUM (T5.INTERVAL_TIME)
FROM
 (
  SELECT
   INTERVAL_TIME = DATEDIFF (SS, T3.RECV_DATE, T4.RECV_DATE)
  FROM
   (
    SELECT 
     ROW_NUMBER () OVER (PARTITION BY T1.ID_CAR ORDER BY T1.RECV_DATE) AS ROWNUMBER,
     T1.ID_CAR,
     T1.RECV_DATE,
     T1.SPEED
    FROM 
     #TEMP_NAV AS T1
    WHERE
     T1.ID_CAR = 43 AND
     T1.RECV_DATE > '16-11-2009' AND
     T1.RECV_DATE <= '19-11-2009'
   ) AS T3
   JOIN
   (
    SELECT 
     ROW_NUMBER () OVER (PARTITION BY T2.ID_CAR ORDER BY T2.RECV_DATE) AS ROWNUMBER,
     T2.ID_CAR,
     T2.RECV_DATE,
     T2.SPEED
    FROM 
     #TEMP_NAV AS T2
    WHERE
     T2.ID_CAR = 43 AND
     T2.RECV_DATE > '16-11-2009' AND
     T2.RECV_DATE <= '19-11-2009'
   ) AS T4 ON T3.ROWNUMBER = T4.ROWNUMBER - 1 AND 
              T3.ID_CAR = T4.ID_CAR   
  WHERE
   T3.SPEED < 2 AND
   T4.SPEED < 2
 ) AS T5
SET STATISTICS  IO OFF
SET STATISTICS PROFILE OFF

План-выполнения запроса:
25248	1	SELECT ID_CAR, RECV_DATE, SPEED  INTO #TEMP_NAV  FROM    NAVIGATIONS  WHERE   ID_CAR = 43 AND   RECV_DATE > '16-11-2009' AND   RECV_DATE <= '19-11-2009'	1	1	0	NULL	NULL	NULL	NULL	17887,77	NULL	NULL	NULL	0,1989265	NULL	NULL	SELECT INTO	0	NULL
25248	1	  |--Table Insert(OBJECT:([#TEMP_NAV]), SET:([#TEMP_NAV].[ID_CAR] = [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR],[#TEMP_NAV].[RECV_DATE] = [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE],[#TEMP_NAV].[SPEED] = [NavigationEt].[dbo].[NAVIGATIONS].[SPEED]))	1	2	1	Table Insert	Insert	OBJECT:([#TEMP_NAV]), SET:([#TEMP_NAV].[ID_CAR] = [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR],[#TEMP_NAV].[RECV_DATE] = [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE],[#TEMP_NAV].[SPEED] = [NavigationEt].[dbo].[NAVIGATIONS].[SPEED])	NULL	17887,77	0,1360276	0,01788777	9	0,1989265	NULL	NULL	PLAN_ROW	0	1
25248	1	       |--Top(ROWCOUNT est 0)	1	3	2	Top	Top	TOP EXPRESSION:((0))	NULL	17887,77	0	0,001788777	21	0,04501109	[NavigationEt].[dbo].[NAVIGATIONS].[SPEED], [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE], [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]	NULL	PLAN_ROW	0	1
25248	1	            |--Clustered Index Scan(OBJECT:([NavigationEt].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS]), WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]=(43) AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE]<='2009-11-19 00:00:00.000') PARTITION ID:((3)))	1	4	3	Clustered Index Scan	Clustered Index Scan	OBJECT:([NavigationEt].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS]), WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]=(43) AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE]<='2009-11-19 00:00:00.000') PARTITION ID:((3))	[NavigationEt].[dbo].[NAVIGATIONS].[SPEED], [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE], [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]	17887,77	0,03835396	0,0001472658	21	0,03850123	[NavigationEt].[dbo].[NAVIGATIONS].[SPEED], [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE], [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]	NULL	PLAN_ROW	0	1



2	1	            |--Parallelism(Gather Streams)	2	5	4	Parallelism	Gather Streams	NULL	NULL	2	0	0,02850291	19	13,43178	[partialagg1010], [partialagg1012]	NULL	PLAN_ROW	1	1
2	2	                 |--Stream Aggregate(DEFINE:([partialagg1010]=COUNT_BIG(datediff(second,[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T1].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T2].[RECV_DATE])), [partialagg1012]=SUM(datediff(second,[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T1].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T2].[RECV_DATE]))))	2	6	5	Stream Aggregate	Aggregate	NULL	[partialagg1010]=COUNT_BIG(datediff(second,[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T1].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T2].[RECV_DATE])), [partialagg1012]=SUM(datediff(second,[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T1].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T2].[RECV_DATE]))	2	0	7,499632	19	13,40327	[partialagg1010], [partialagg1012]	NULL	PLAN_ROW	1	1
14667	2	                      |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Expr1003])=([Expr1015]), RESIDUAL:([Expr1003]=([Expr1007]-(1))))	2	7	6	Merge Join	Inner Join	MANY-TO-MANY MERGE:([Expr1003])=([Expr1015]), RESIDUAL:([Expr1003]=([Expr1007]-(1)))	NULL	2,499877E+07	0,009839295	2,580433	23	5,903643	[T1].[RECV_DATE], [T2].[RECV_DATE]	NULL	PLAN_ROW	1	1
15811	2	                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1003]), ORDER BY:([Expr1003] ASC))	2	8	7	Parallelism	Repartition Streams	PARTITION COLUMNS:([Expr1003]), ORDER BY:([Expr1003] ASC)	NULL	15811	0	0,138428	23	1,652196	[T1].[RECV_DATE], [Expr1003]	NULL	PLAN_ROW	1	1
15811	2	                           |    |--Filter(WHERE:([tempdb].[dbo].[#TEMP_NAV].[SPEED] as [T1].[SPEED]<(2)))	2	9	8	Filter	Filter	WHERE:([tempdb].[dbo].[#TEMP_NAV].[SPEED] as [T1].[SPEED]<(2))	NULL	15811	0	0,00605952	23	1,513768	[T1].[RECV_DATE], [Expr1003]	NULL	PLAN_ROW	1	1
25248	2	                           |         |--Parallelism(Distribute Streams, RoundRobin Partitioning)	2	10	9	Parallelism	Distribute Streams	NULL	NULL	25248	0	0,3770613	25	1,507709	[T1].[RECV_DATE], [T1].[SPEED], [Expr1003]	NULL	PLAN_ROW	1	1
25248	1	                           |              |--Sequence Project(DEFINE:([Expr1003]=row_number))	2	11	10	Sequence Project	Compute Scalar	DEFINE:([Expr1003]=row_number)	[Expr1003]=row_number	25248	0	0,0025248	25	1,130648	[T1].[RECV_DATE], [T1].[SPEED], [Expr1003]	NULL	PLAN_ROW	0	1
0	0	                           |                   |--Compute Scalar(DEFINE:([Expr1017]=(1)))	2	12	11	Compute Scalar	Compute Scalar	DEFINE:([Expr1017]=(1))	[Expr1017]=(1)	25248	0	0,00050496	25	1,131153	[T1].[RECV_DATE], [T1].[SPEED], [Segment1016], [Expr1017]	NULL	PLAN_ROW	0	1
25248	1	                           |                        |--Segment	2	13	12	Segment	Segment	NULL	NULL	25248	0	0,00050496	25	1,131153	[T1].[RECV_DATE], [T1].[SPEED], [Segment1016]	NULL	PLAN_ROW	0	1
25248	1	                           |                             |--Parallelism(Gather Streams, ORDER BY:([T1].[RECV_DATE] ASC))	2	14	13	Parallelism	Gather Streams	ORDER BY:([T1].[RECV_DATE] ASC)	NULL	25248	0	0,1897148	17	1,128123	[T1].[RECV_DATE], [T1].[SPEED]	NULL	PLAN_ROW	1	1
25248	2	                           |                                  |--Sort(ORDER BY:([T1].[RECV_DATE] ASC))	2	15	14	Sort	Sort	ORDER BY:([T1].[RECV_DATE] ASC)	NULL	25248	0,005630631	0,8474185	17	0,938408	[T1].[RECV_DATE], [T1].[SPEED]	NULL	PLAN_ROW	1	1
25248	2	                           |                                       |--Table Scan(OBJECT:([tempdb].[dbo].[#TEMP_NAV] AS [T1]), WHERE:([tempdb].[dbo].[#TEMP_NAV].[ID_CAR] as [T1].[ID_CAR]=(43) AND [tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T1].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T1].[RECV_DATE]<='2009-11-19 00:00:00.000'))	2	16	15	Table Scan	Table Scan	OBJECT:([tempdb].[dbo].[#TEMP_NAV] AS [T1]), WHERE:([tempdb].[dbo].[#TEMP_NAV].[ID_CAR] as [T1].[ID_CAR]=(43) AND [tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T1].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T1].[RECV_DATE]<='2009-11-19 00:00:00.000')	[T1].[RECV_DATE], [T1].[SPEED]	25248	0,05653683	0,01392565	21	0,07046248	[T1].[RECV_DATE], [T1].[SPEED]	NULL	PLAN_ROW	1	1
15811	2	                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1015]), ORDER BY:([Expr1015] ASC))	2	24	7	Parallelism	Repartition Streams	PARTITION COLUMNS:([Expr1015]), ORDER BY:([Expr1015] ASC)	NULL	15811	0	0,1466101	31	1,661169	[T2].[RECV_DATE], [Expr1007], [Expr1015]	NULL	PLAN_ROW	1	1
0	0	                                |--Compute Scalar(DEFINE:([Expr1015]=[Expr1007]-(1)))	2	25	24	Compute Scalar	Compute Scalar	DEFINE:([Expr1015]=[Expr1007]-(1))	[Expr1015]=[Expr1007]-(1)	15811	0	0,00079055	31	1,514559	[T2].[RECV_DATE], [Expr1007], [Expr1015]	NULL	PLAN_ROW	1	1
15811	2	                                     |--Filter(WHERE:([tempdb].[dbo].[#TEMP_NAV].[SPEED] as [T2].[SPEED]<(2)))	2	26	25	Filter	Filter	WHERE:([tempdb].[dbo].[#TEMP_NAV].[SPEED] as [T2].[SPEED]<(2))	NULL	15811	0	0,00605952	23	1,513768	[T2].[RECV_DATE], [Expr1007]	NULL	PLAN_ROW	1	1
25248	2	                                          |--Parallelism(Distribute Streams, RoundRobin Partitioning)	2	27	26	Parallelism	Distribute Streams	NULL	NULL	25248	0	0,3770613	25	1,507709	[T2].[RECV_DATE], [T2].[SPEED], [Expr1007]	NULL	PLAN_ROW	1	1
25248	1	                                               |--Sequence Project(DEFINE:([Expr1007]=row_number))	2	28	27	Sequence Project	Compute Scalar	DEFINE:([Expr1007]=row_number)	[Expr1007]=row_number	25248	0	0,0025248	25	1,130648	[T2].[RECV_DATE], [T2].[SPEED], [Expr1007]	NULL	PLAN_ROW	0	1
0	0	                                                    |--Compute Scalar(DEFINE:([Expr1019]=(1)))	2	29	28	Compute Scalar	Compute Scalar	DEFINE:([Expr1019]=(1))	[Expr1019]=(1)	25248	0	0,00050496	25	1,131153	[T2].[RECV_DATE], [T2].[SPEED], [Segment1018], [Expr1019]	NULL	PLAN_ROW	0	1
25248	1	                                                         |--Segment	2	30	29	Segment	Segment	NULL	NULL	25248	0	0,00050496	25	1,131153	[T2].[RECV_DATE], [T2].[SPEED], [Segment1018]	NULL	PLAN_ROW	0	1
25248	1	                                                              |--Parallelism(Gather Streams, ORDER BY:([T2].[RECV_DATE] ASC))	2	31	30	Parallelism	Gather Streams	ORDER BY:([T2].[RECV_DATE] ASC)	NULL	25248	0	0,1897148	17	1,128123	[T2].[RECV_DATE], [T2].[SPEED]	NULL	PLAN_ROW	1	1
25248	2	                                                                   |--Sort(ORDER BY:([T2].[RECV_DATE] ASC))	2	32	31	Sort	Sort	ORDER BY:([T2].[RECV_DATE] ASC)	NULL	25248	0,005630631	0,8474185	17	0,938408	[T2].[RECV_DATE], [T2].[SPEED]	NULL	PLAN_ROW	1	1
25248	2	                                                                        |--Table Scan(OBJECT:([tempdb].[dbo].[#TEMP_NAV] AS [T2]), WHERE:([tempdb].[dbo].[#TEMP_NAV].[ID_CAR] as [T2].[ID_CAR]=(43) AND [tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T2].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T2].[RECV_DATE]<='2009-11-19 00:00:00.000'))	2	33	32	Table Scan	Table Scan	OBJECT:([tempdb].[dbo].[#TEMP_NAV] AS [T2]), WHERE:([tempdb].[dbo].[#TEMP_NAV].[ID_CAR] as [T2].[ID_CAR]=(43) AND [tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T2].[RECV_DATE]>'2009-11-16 00:00:00.000' AND [tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T2].[RECV_DATE]<='2009-11-19 00:00:00.000')	[T2].[RECV_DATE], [T2].[SPEED]	25248	0,05653683	0,01392565	21	0,07046248	[T2].[RECV_DATE], [T2].[SPEED]	NULL	PLAN_ROW	1	1

По моим тестам у меня получилось, что вариант 1 работает быстрее. ОДнако, у меныя мало опыта в таких делах. Может что чего подскажет?
20 ноя 09, 15:22    [7957187]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше использовать в запросах ?  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
я б второй вариант выбрал, только из него это убрать нужно:
WHERE
     T1.ID_CAR = 43 AND
     T1.RECV_DATE > '16-11-2009' AND
     T1.RECV_DATE <= '19-11-2009'
.....

WHERE
     T2.ID_CAR = 43 AND
     T2.RECV_DATE > '16-11-2009' AND
     T2.RECV_DATE <= '19-11-2009'
.....

AND T3.ID_CAR = T4.ID_CAR   
затем, во временной таблице создать поле ROWNUMBER identity primary key
и по нему потом джойнить предыдущую запись

ну и на таблицу NAVIGATIONS индекс навесить по полям ID_CAR, RECV_DATE... ну или наоборот, RECV_DATE, ID_CAR - в зависимости от селективности
20 ноя 09, 16:06    [7957552]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше использовать в запросах ?  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
ura
я б второй вариант выбрал, только из него это убрать нужно:
WHERE
     T1.ID_CAR = 43 AND
     T1.RECV_DATE > '16-11-2009' AND
     T1.RECV_DATE <= '19-11-2009'
.....

WHERE
     T2.ID_CAR = 43 AND
     T2.RECV_DATE > '16-11-2009' AND
     T2.RECV_DATE <= '19-11-2009'
.....

AND T3.ID_CAR = T4.ID_CAR   
затем, во временной таблице создать поле ROWNUMBER identity primary key
и по нему потом джойнить предыдущую запись

ну и на таблицу NAVIGATIONS индекс навесить по полям ID_CAR, RECV_DATE... ну или наоборот, RECV_DATE, ID_CAR - в зависимости от селективности


Спасибо за подсказку. На счет where во втором варианте я тупанул, зыбыл удалить. А вот на счет поля с идентити во временной таблице, хз, идентити у меня всегда с разрывами идет, а мне без разрывов надо.
20 ноя 09, 16:16    [7957624]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше использовать в запросах ?  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
На 100.000 записях Вариант 1 в 3 раза быстрее все равно работает, чем:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET STATISTICS PROFILE ON
SET STATISTICS IO ON

if object_id('tempdb..#TEMP_NAV')>0 
 drop table #TEMP_NAV

CREATE TABLE #TEMP_NAV
(
 ROWNUMBER INT IDENTITY (1, 1) NOT NULL, 
 ID_CAR INT NOT NULL,
 RECV_DATE DATETIME NOT NULL,
 SPEED INT NOT NULL,
 CONSTRAINT [PK_ROWNUMBER] PRIMARY KEY CLUSTERED 
 (
	ROWNUMBER ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

INSERT INTO #TEMP_NAV (ID_CAR, RECV_DATE, SPEED)
       SELECT ID_CAR, RECV_DATE, SPEED
       FROM NAVIGATIONS
       WHERE
        ID_CAR = 43 AND
        RECV_DATE > '01-11-2009' AND
        RECV_DATE <= '19-11-2009'
       ORDER BY RECV_DATE

SELECT
 SUM (T5.INTERVAL_TIME)
FROM
 (
  SELECT
   INTERVAL_TIME = DATEDIFF (SS, T3.RECV_DATE, T4.RECV_DATE)
  FROM
   #TEMP_NAV AS T3
   JOIN #TEMP_NAV AS T4 ON T3.ROWNUMBER = T4.ROWNUMBER - 1 AND 
                           T3.ID_CAR = T4.ID_CAR   
  WHERE
   T3.SPEED < 2 AND
   T4.SPEED < 2
 ) AS T5
SET STATISTICS  IO OFF
SET STATISTICS PROFILE OFF

20 ноя 09, 16:41    [7957812]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше использовать в запросах ?  [new]
iljy
Member

Откуда:
Сообщений: 8711
LexMinsk,
план покажите. Может помочь подсказка OPTION(MERGE JOIN. Почему-то оптимизатор такие
T3.ROWNUMBER = T4.ROWNUMBER - 1
соединения иногда криво отрабатывает без нее. Условие T3.ID_CAR = T4.ID_CAR можно попробовать убрать в WHERE.
20 ноя 09, 16:50    [7957863]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше использовать в запросах ?  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
LexMinsk
А вот на счет поля с идентити во временной таблице, хз, идентити у меня всегда с разрывами идет, а мне без разрывов надо.

у вас временная таблица и один инсерт в нее, никаких разрывов не будет

LexMinsk
На 100.000 записях Вариант 1 в 3 раза быстрее все равно работает, чем:

100.000 - это в таблице NAVIGATIONS или попадает во временную таблицу ?

ID_CAR во временной таблице лишний.. так чуть проще:

SELECT
   sum(DATEDIFF (SS, T3.RECV_DATE, T4.RECV_DATE))
  FROM
   #TEMP_NAV AS T3
   JOIN #TEMP_NAV AS T4 ON T3.ROWNUMBER = T4.ROWNUMBER - 1 AND 
  WHERE
   T3.SPEED < 2 AND
   T4.SPEED < 2



P.S.
и насчет этого не забудьте: ну и на таблицу NAVIGATIONS индекс навесить по полям ID_CAR, RECV_DATE... ну или наоборот, RECV_DATE, ID_CAR - в зависимости от селективности
20 ноя 09, 16:52    [7957889]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше использовать в запросах ?  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
iljy,
План варианта 1 см. выше, а последнего варианта:

122709	1	INSERT INTO #TEMP_NAV (ID_CAR, RECV_DATE, SPEED)         SELECT ID_CAR, RECV_DATE, SPEED         FROM NAVIGATIONS         WHERE          ID_CAR = 43 AND          RECV_DATE > '01-11-2009' AND          RECV_DATE <= '19-11-2009'         ORDER BY RECV_DATE	1	1	0	NULL	NULL	NULL	NULL	77846,62	NULL	NULL	NULL	0,7302741	NULL	NULL	INSERT	0	NULL
122709	1	  |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#TEMP_NAV]), SET:([tempdb].[dbo].[#TEMP_NAV].[ID_CAR] = [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] = [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[SPEED] = RaiseIfNull([Expr1007]),[tempdb].[dbo].[#TEMP_NAV].[ROWNUMBER] = [Expr1006]))	1	2	1	Clustered Index Insert	Insert	OBJECT:([tempdb].[dbo].[#TEMP_NAV]), SET:([tempdb].[dbo].[#TEMP_NAV].[ID_CAR] = [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] = [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[SPEED] = RaiseIfNull([Expr1007]),[tempdb].[dbo].[#TEMP_NAV].[ROWNUMBER] = [Expr1006])	NULL	77846,62	0,5811139	0,07784662	9	0,7302741	NULL	NULL	PLAN_ROW	0	1
0	0	       |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[NavigationEt].[dbo].[NAVIGATIONS].[SPEED],0)))	1	3	2	Compute Scalar	Compute Scalar	DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[NavigationEt].[dbo].[NAVIGATIONS].[SPEED],0))	[Expr1007]=CONVERT_IMPLICIT(int,[NavigationEt].[dbo].[NAVIGATIONS].[SPEED],0)	77846,62	0	0,007784662	27	0,07131364	[NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE], [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR], [Expr1006], [Expr1007]	NULL	PLAN_ROW	0	1
122709	1	            |--Compute Scalar(DEFINE:([Expr1006]=getidentity((249820002),(2),NULL)))	1	4	3	Compute Scalar	Compute Scalar	DEFINE:([Expr1006]=getidentity((249820002),(2),NULL))	[Expr1006]=getidentity((249820002),(2),NULL)	77846,62	0	0,007784662	25	0,06352898	[NavigationEt].[dbo].[NAVIGATIONS].[SPEED], [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE], [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR], [Expr1006]	NULL	PLAN_ROW	0	1
122709	1	                 |--Top(ROWCOUNT est 0)	1	5	4	Top	Top	TOP EXPRESSION:((0))	NULL	77846,62	0	0,007784662	21	0,05574432	[NavigationEt].[dbo].[NAVIGATIONS].[SPEED], [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE], [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]	NULL	PLAN_ROW	0	1
122709	1	                      |--Clustered Index Scan(OBJECT:([NavigationEt].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS]),  WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]=(43) AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE]>'2009-11-01 00:00:00.000' AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE]<='2009-11-19 00:00:00.000') ORDERED FORWARD PARTITION ID:((3)))	1	6	5	Clustered Index Scan	Clustered Index Scan	OBJECT:([NavigationEt].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS]),  WHERE:([NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]=(43) AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE]>'2009-11-01 00:00:00.000' AND [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE]<='2009-11-19 00:00:00.000') ORDERED FORWARD PARTITION ID:((3))	[NavigationEt].[dbo].[NAVIGATIONS].[SPEED], [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE], [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]	77846,62	0,03868056	0,004558014	21	0,04323857	[NavigationEt].[dbo].[NAVIGATIONS].[SPEED], [NavigationEt].[dbo].[NAVIGATIONS].[RECV_DATE], [NavigationEt].[dbo].[NAVIGATIONS].[ID_CAR]	NULL	PLAN_ROW	0	1



1	1	SELECT   SUM (T5.INTERVAL_TIME)  FROM   (    SELECT     INTERVAL_TIME = DATEDIFF (SS, T3.RECV_DATE, T4.RECV_DATE)    FROM     #TEMP_NAV AS T3     JOIN #TEMP_NAV AS T4 ON T3.ROWNUMBER = T4.ROWNUMBER - 1 AND                              T3.ID_CAR = T4.ID_CAR       WHERE     T3.SPEED < 2 AND     T4.SPEED < 2   ) AS T5	2	1	0	NULL	NULL	NULL	NULL	1	NULL	NULL	NULL	1,430026	NULL	NULL	SELECT	0	NULL
0	0	  |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013] END))	2	2	1	Compute Scalar	Compute Scalar	DEFINE:([Expr1005]=CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013] END)	[Expr1005]=CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013] END	1	0	0,04556639	11	1,430026	[Expr1005]	NULL	PLAN_ROW	0	1
1	1	       |--Stream Aggregate(DEFINE:([Expr1012]=COUNT_BIG(datediff(second,[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T3].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T4].[RECV_DATE])), [Expr1013]=SUM(datediff(second,[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T3].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T4].[RECV_DATE]))))	2	3	2	Stream Aggregate	Aggregate	NULL	[Expr1012]=COUNT_BIG(datediff(second,[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T3].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T4].[RECV_DATE])), [Expr1013]=SUM(datediff(second,[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T3].[RECV_DATE],[tempdb].[dbo].[#TEMP_NAV].[RECV_DATE] as [T4].[RECV_DATE]))	1	0	0,04556639	11	1,430026	[Expr1012], [Expr1013]	NULL	PLAN_ROW	0	1
70443	1	            |--Merge Join(Inner Join, MERGE:([T3].[ROWNUMBER], [T3].[ID_CAR])=([Expr1006], [T4].[ID_CAR]), RESIDUAL:([tempdb].[dbo].[#TEMP_NAV].[ID_CAR] as [T3].[ID_CAR]=[tempdb].[dbo].[#TEMP_NAV].[ID_CAR] as [T4].[ID_CAR] AND [tempdb].[dbo].[#TEMP_NAV].[ROWNUMBER] as [T3].[ROWNUMBER]=[Expr1006]))	2	4	3	Merge Join	Inner Join	MERGE:([T3].[ROWNUMBER], [T3].[ID_CAR])=([Expr1006], [T4].[ID_CAR]), RESIDUAL:([tempdb].[dbo].[#TEMP_NAV].[ID_CAR] as [T3].[ID_CAR]=[tempdb].[dbo].[#TEMP_NAV].[ID_CAR] as [T4].[ID_CAR] AND [tempdb].[dbo].[#TEMP_NAV].[ROWNUMBER] as [T3].[ROWNUMBER]=[Expr1006])	NULL	75943,15	0	0,3321633	23	1,38446	[T3].[RECV_DATE], [T4].[RECV_DATE]	NULL	PLAN_ROW	0	1
75945	1	                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#TEMP_NAV] AS [T3]),  WHERE:([tempdb].[dbo].[#TEMP_NAV].[SPEED] as [T3].[SPEED]<(2)) ORDERED FORWARD)	2	5	4	Clustered Index Scan	Clustered Index Scan	OBJECT:([tempdb].[dbo].[#TEMP_NAV] AS [T3]),  WHERE:([tempdb].[dbo].[#TEMP_NAV].[SPEED] as [T3].[SPEED]<(2)) ORDERED FORWARD	[T3].[ROWNUMBER], [T3].[ID_CAR], [T3].[RECV_DATE]	75945	0,3283102	0,1351369	27	0,4634471	[T3].[ROWNUMBER], [T3].[ID_CAR], [T3].[RECV_DATE]	NULL	PLAN_ROW	0	1
0	0	                 |--Compute Scalar(DEFINE:([Expr1006]=[tempdb].[dbo].[#TEMP_NAV].[ROWNUMBER] as [T4].[ROWNUMBER]-(1)))	2	6	4	Compute Scalar	Compute Scalar	DEFINE:([Expr1006]=[tempdb].[dbo].[#TEMP_NAV].[ROWNUMBER] as [T4].[ROWNUMBER]-(1))	[Expr1006]=[tempdb].[dbo].[#TEMP_NAV].[ROWNUMBER] as [T4].[ROWNUMBER]-(1)	75945	0	0,0075945	23	0,5299419	[T4].[ID_CAR], [T4].[RECV_DATE], [Expr1006]	NULL	PLAN_ROW	0	1
75945	1	                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#TEMP_NAV] AS [T4]),  WHERE:([tempdb].[dbo].[#TEMP_NAV].[SPEED] as [T4].[SPEED]<(2)) ORDERED FORWARD)	2	7	6	Clustered Index Scan	Clustered Index Scan	OBJECT:([tempdb].[dbo].[#TEMP_NAV] AS [T4]),  WHERE:([tempdb].[dbo].[#TEMP_NAV].[SPEED] as [T4].[SPEED]<(2)) ORDERED FORWARD	[T4].[ROWNUMBER], [T4].[ID_CAR], [T4].[RECV_DATE], [T4].[SPEED]	75945	0,3283102	0,1351369	27	0,4634471	[T4].[ROWNUMBER], [T4].[ID_CAR], [T4].[RECV_DATE], [T4].[SPEED]	NULL	PLAN_ROW	0	1
20 ноя 09, 16:55    [7957907]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше использовать в запросах ?  [new]
iljy
Member

Откуда:
Сообщений: 8711
LexMinsk,

у вас для первого случая план параллельный, отсюда и выигрыш. Смотрите чего вам вкуснее - загрузка проца или разница в скорости.
20 ноя 09, 16:57    [7957936]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше использовать в запросах ?  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
ura
LexMinsk
А вот на счет поля с идентити во временной таблице, хз, идентити у меня всегда с разрывами идет, а мне без разрывов надо.

у вас временная таблица и один инсерт в нее, никаких разрывов не будет

LexMinsk
На 100.000 записях Вариант 1 в 3 раза быстрее все равно работает, чем:

100.000 - это в таблице NAVIGATIONS или попадает во временную таблицу ?

ID_CAR во временной таблице лишний.. так чуть проще:

SELECT
   sum(DATEDIFF (SS, T3.RECV_DATE, T4.RECV_DATE))
  FROM
   #TEMP_NAV AS T3
   JOIN #TEMP_NAV AS T4 ON T3.ROWNUMBER = T4.ROWNUMBER - 1 AND 
  WHERE
   T3.SPEED < 2 AND
   T4.SPEED < 2
P.S.
и насчет этого не забудьте: ну и на таблицу NAVIGATIONS индекс навесить по полям ID_CAR, RECV_DATE... ну или наоборот, RECV_DATE, ID_CAR - в зависимости от селективности


В Navigations гораздо больше записей, чем 100.000. Во времянку попадает около 100.000.
20 ноя 09, 17:07    [7957998]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше использовать в запросах ?  [new]
Валдай
Member

Откуда:
Сообщений: 113
А как насчет такого финта?:)
use tempdb
go
--drop table #t
if object_id('tempdb..#t')is null
CREATE TABLE #t (ROWNUMBER  int identity(1,1),
 ID_CAR INT NOT NULL,
 RECV_DATE DATETIME NOT NULL,
 --SPEED INT NOT NULL, --<<-- если по этой колонке сразу фильтровать, то она и не нужна
 CONSTRAINT [PK_ROWNUMBER] PRIMARY KEY CLUSTERED (ROWNUMBER) )

truncate table #t

insert #t(ID_CAR,RECV_DATE)
select ID_CAR,RECV_DATE
from
(select ID_CAR=1,RECV_DATE='20090101 12:00:00',SPEED=1
union all select 1,'20090101 12:01:15',1
union all select 1,'20090101 12:00:55',1
union all select 1,'20090101 12:00:35',2
union all select 2,'20090101 12:01:25',1
union all select 2,'20090101 12:02:45',1)x
where SPEED<2 --<<-- это если я правильно понял задачу,то надо фильтровать тут. Иначе потом могут появиться дыры в ROWNUMBER  
order by ID_CAR,RECV_DATE --<<-- при вставке не забыть отсортировать и по машинам

-- Одно чтение! :)
select ID_CAR,si=sum(datediff(ss,d2,d1))
from(
select ID_CAR,d1=max(RECV_DATE),d2=min(RECV_DATE)
from(
select rn2=ROWNUMBER+d --<<-- вот такой вот фокус :)
,ID_CAR,RECV_DATE
from #t cross join (select d=0 union all select 1)d)x
group by ID_CAR,rn2
having count(*)=2)x
group by ID_CAR
20 ноя 09, 17:44    [7958280]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить