Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Answered: When did the version change - a sql problem

    Code:
     
    CREATE TABLE [dbo].[VersionDate](
        [DeviceID] [INT] NULL,
        [Version] [INT] NULL,
        [OnDate] [DATETIME] NULL
    ) ON [PRIMARY]
    
    
    
    
    INSERT INTO [dbo].[VersionDate]([DeviceId], [Version], [OnDate])
    SELECT 34411, 134541, '20160706 09:19:18.000' UNION ALL
    SELECT 34411, 134542, '20160706 09:29:36.000' UNION ALL
    SELECT 34411, 134543, '20160706 09:37:48.000' UNION ALL
    SELECT 34411, 134544, '20160706 10:32:44.000' UNION ALL
    SELECT 34411, 134545, '20160706 10:32:46.000' UNION ALL
    SELECT 34411, 134546, '20160706 10:32:47.000' UNION ALL
    SELECT 34411, 134547, '20160706 10:32:48.000' UNION ALL
    SELECT 34411, 134548, '20160706 10:33:00.000' UNION ALL
    SELECT 34411, 134549, '20160706 10:42:54.000' UNION ALL
    SELECT 34411, 134550, '20160706 10:51:10.000' UNION ALL
    SELECT 34411, 134551, '20160706 10:59:41.000' UNION ALL
    SELECT 34411, 134553, '20160706 11:18:52.000' UNION ALL
    SELECT 34411, 134554, '20160706 11:29:54.000' UNION ALL
    SELECT 34411, 134555, '20160706 11:40:50.000' UNION ALL
    SELECT 34411, 134556, '20160706 11:49:12.000' UNION ALL
    SELECT 34411, 134557, '20160706 12:00:07.000' UNION ALL
    SELECT 34411, 134558, '20160706 12:11:25.000' UNION ALL
    SELECT 34411, 134559, '20160706 12:19:42.000' UNION ALL
    SELECT 34411, 134560, '20160706 12:31:28.000' UNION ALL
    SELECT 34411, 134561, '20160706 12:39:41.000' UNION ALL
    SELECT 34411, 134562, '20160706 12:50:34.000' UNION ALL
    SELECT 34411, 134563, '20160706 12:59:02.000' UNION ALL
    SELECT 34411, 134564, '20160706 13:10:03.000' UNION ALL
    SELECT 34411, 134565, '20160706 13:21:06.000' UNION ALL
    SELECT 34411, 134566, '20160706 13:29:44.000' UNION ALL
    SELECT 34411, 134567, '20160706 13:40:45.000' UNION ALL
    SELECT 34411, 134568, '20160706 13:51:38.000' UNION ALL
    SELECT 34411, 134569, '20160706 14:02:44.000' UNION ALL
    SELECT 34411, 134570, '20160706 14:11:04.000' UNION ALL
    SELECT 34411, 134571, '20160706 14:22:10.000' UNION ALL
    SELECT 34411, 134572, '20160706 14:30:32.000' UNION ALL
    SELECT 34411, 134573, '20160706 14:41:23.000' UNION ALL
    SELECT 34411, 134574, '20160706 14:52:13.000' UNION ALL
    SELECT 34411, 134575, '20160706 15:00:27.000' UNION ALL
    SELECT 34411, 134576, '20160706 15:11:27.000' UNION ALL
    SELECT 34411, 134578, '20160706 15:31:04.000' UNION ALL
    SELECT 34411, 134579, '20160706 15:42:14.000' UNION ALL
    SELECT 34411, 134580, '20160706 15:53:06.000' UNION ALL
    SELECT 34411, 134581, '20160706 16:01:43.000' UNION ALL
    SELECT 34411, 134582, '20160706 16:13:29.000' UNION ALL
    SELECT 34411, 134583, '20160706 16:21:41.000' UNION ALL
    SELECT 34411, 134584, '20160706 16:32:31.000' UNION ALL
    SELECT 34411, 134585, '20160706 16:40:44.000' UNION ALL
    SELECT 34411, 134586, '20160706 16:52:04.000' UNION ALL
    SELECT 34411, 134587, '20160706 17:02:52.000' UNION ALL
    SELECT 34411, 134588, '20160706 17:14:13.000' UNION ALL
    SELECT 34411, 134589, '20160706 17:22:19.000' UNION ALL
    SELECT 34411, 134590, '20160706 17:33:25.000' UNION ALL
    SELECT 34411, 134591, '20160706 17:44:13.000' UNION ALL
    SELECT 34411, 134592, '20160706 17:52:33.000' UNION ALL
    SELECT 34411, 134593, '20160706 18:03:55.000' UNION ALL
    SELECT 34411, 134594, '20160706 18:12:37.000' UNION ALL
    SELECT 34411, 134599, '20160706 19:03:59.000' UNION ALL
    SELECT 34411, 134600, '20160706 19:14:46.000' UNION ALL
    SELECT 34411, 134601, '20160706 19:22:59.000' UNION ALL
    SELECT 34411, 134602, '20160706 20:23:17.000' UNION ALL
    SELECT 34411, 134608, '20160706 20:35:42.000' UNION ALL
    SELECT 34411, 134609, '20160706 20:43:57.000' UNION ALL
    SELECT 34411, 134610, '20160706 20:54:59.000' UNION ALL
    SELECT 34411, 134611, '20160706 21:03:08.000' UNION ALL
    SELECT 34411, 134613, '20160706 21:24:51.000' UNION ALL
    SELECT 34411, 134614, '20160706 21:35:40.000' UNION ALL
    SELECT 34411, 134615, '20160706 21:43:51.000' UNION ALL
    SELECT 34411, 134616, '20160706 21:54:57.000' UNION ALL
    SELECT 34411, 134617, '20160706 22:06:01.000' UNION ALL
    SELECT 34411, 134620, '20160706 22:34:56.000' UNION ALL
    SELECT 34411, 134621, '20160706 22:45:38.000' UNION ALL
    SELECT 34411, 134622, '20160706 22:56:54.000' UNION ALL
    SELECT 34411, 134626, '20160706 23:35:15.000' UNION ALL
    SELECT 34411, 134627, '20160706 23:46:07.000' UNION ALL
    SELECT 34411, 134628, '20160706 23:57:09.000' UNION ALL
    SELECT 34411, 134667, '20160707 04:13:14.000' UNION ALL
    SELECT 34411, 134668, '20160707 04:23:36.000' UNION ALL
    SELECT 34411, 134669, NULL UNION ALL
    SELECT 34411, 134670, NULL UNION ALL
    SELECT 34411, 134671, NULL UNION ALL
    SELECT 34411, 134672, NULL UNION ALL
    SELECT 34411, 134673, NULL UNION ALL
    SELECT 34411, 134674, NULL UNION ALL
    SELECT 34411, 134675, '20160707 04:51:40.000' UNION ALL
    SELECT 34411, 134676, '20160707 05:06:40.000' UNION ALL
    SELECT 34411, 134677, '20160707 05:17:10.000' UNION ALL
    SELECT 34411, 134678, '20160707 05:20:13.000' UNION ALL
    SELECT 34411, 134679, NULL UNION ALL
    SELECT 34411, 134680, NULL UNION ALL
    SELECT 34411, 134681, NULL UNION ALL
    SELECT 34411, 134682, NULL UNION ALL
    SELECT 34411, 134683, NULL UNION ALL
    SELECT 34411, 134684, NULL UNION ALL
    SELECT 34411, 134685, '20160707 05:30:57.000' UNION ALL
    SELECT 34411, 134686, '20160707 05:44:06.000' UNION ALL
    SELECT 34411, 134687, '20160707 05:48:22.000' UNION ALL
    SELECT 34411, 134688, '20160707 06:02:23.000' UNION ALL
    SELECT 34411, 134690, '20160707 06:20:24.000' UNION ALL
    SELECT 34411, 134691, '20160707 06:36:21.000' UNION ALL
    SELECT 34411, 134692, NULL UNION ALL
    SELECT 34411, 134693, '20160707 06:51:21.000' UNION ALL
    SELECT 34411, 134697, '20160707 07:36:27.000' UNION ALL
    SELECT 34411, 134698, NULL UNION ALL
    SELECT 34411, 134699, NULL UNION ALL
    SELECT 34411, 134700, '20160707 08:01:21.000' UNION ALL
    SELECT 34411, 134703, NULL UNION ALL
    SELECT 34411, 134704, '20160707 08:44:35.000' UNION ALL
    SELECT 34411, 134705, NULL UNION ALL
    SELECT 34411, 134706, '20160707 09:04:22.000'
    I have been struggling with this for a couple of days now, any pointers to solve this would be great.

    I have devices that are sent down versions of files every few minutes or so. The device responds back with when it changed the version of local file by inserting a row in a table similar to one above. However, if a device has missed a version, the next version would bring it up to date. Now considering the case below :

    SELECT 34411, 134668, '20160707 04:23:36.000'
    SELECT 34411, 134669, NULL
    SELECT 34411, 134670, NULL
    SELECT 34411, 134671, NULL
    SELECT 34411, 134672, NULL
    SELECT 34411, 134673, NULL
    SELECT 34411, 134674, NULL
    SELECT 34411, 134675, '20160707 04:51:40.000'


    The version 134675 would have brought the device up to date with all the versions between 134668 and 134675 . I need to run an update query which would update the dates for the versions 134669 - 134674 with the date of the next valid insert i.e '20160707 04:51:40.000' . I understand that this can be done with a correlated sub query but the data i am dealing with could easily run into millions of rows and i need to find a better and efficient way of carrying out the same thing.

    Any Ideas ?
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. Best Answer
    Posted by Pat Phelan

    "Testing with your data on my laptop:
    Code:
    IF Object_Id('[dbo].[VersionDate]', 'U') IS NOT NULL
       DROP TABLE [dbo].[VersionDate]
    GO
    
    CREATE TABLE [dbo].[VersionDate](
        [DeviceID] [INT] NULL,
        [Version] [INT] NULL,
        [OnDate] [DATETIME] NULL
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE #Enigma (
        [DeviceID] [INT] NULL,
        [Version] [INT] NULL,
        [OnDate] [DATETIME] NULL
    );
    
    INSERT INTO #Enigma ([DeviceId], [Version], [OnDate])
    SELECT 34411, 134541, '20160706 09:19:18.000' UNION ALL
    SELECT 34411, 134542, '20160706 09:29:36.000' UNION ALL
    SELECT 34411, 134543, '20160706 09:37:48.000' UNION ALL
    SELECT 34411, 134544, '20160706 10:32:44.000' UNION ALL
    SELECT 34411, 134545, '20160706 10:32:46.000' UNION ALL
    SELECT 34411, 134546, '20160706 10:32:47.000' UNION ALL
    SELECT 34411, 134547, '20160706 10:32:48.000' UNION ALL
    SELECT 34411, 134548, '20160706 10:33:00.000' UNION ALL
    SELECT 34411, 134549, '20160706 10:42:54.000' UNION ALL
    SELECT 34411, 134550, '20160706 10:51:10.000' UNION ALL
    SELECT 34411, 134551, '20160706 10:59:41.000' UNION ALL
    SELECT 34411, 134553, '20160706 11:18:52.000' UNION ALL
    SELECT 34411, 134554, '20160706 11:29:54.000' UNION ALL
    SELECT 34411, 134555, '20160706 11:40:50.000' UNION ALL
    SELECT 34411, 134556, '20160706 11:49:12.000' UNION ALL
    SELECT 34411, 134557, '20160706 12:00:07.000' UNION ALL
    SELECT 34411, 134558, '20160706 12:11:25.000' UNION ALL
    SELECT 34411, 134559, '20160706 12:19:42.000' UNION ALL
    SELECT 34411, 134560, '20160706 12:31:28.000' UNION ALL
    SELECT 34411, 134561, '20160706 12:39:41.000' UNION ALL
    SELECT 34411, 134562, '20160706 12:50:34.000' UNION ALL
    SELECT 34411, 134563, '20160706 12:59:02.000' UNION ALL
    SELECT 34411, 134564, '20160706 13:10:03.000' UNION ALL
    SELECT 34411, 134565, '20160706 13:21:06.000' UNION ALL
    SELECT 34411, 134566, '20160706 13:29:44.000' UNION ALL
    SELECT 34411, 134567, '20160706 13:40:45.000' UNION ALL
    SELECT 34411, 134568, '20160706 13:51:38.000' UNION ALL
    SELECT 34411, 134569, '20160706 14:02:44.000' UNION ALL
    SELECT 34411, 134570, '20160706 14:11:04.000' UNION ALL
    SELECT 34411, 134571, '20160706 14:22:10.000' UNION ALL
    SELECT 34411, 134572, '20160706 14:30:32.000' UNION ALL
    SELECT 34411, 134573, '20160706 14:41:23.000' UNION ALL
    SELECT 34411, 134574, '20160706 14:52:13.000' UNION ALL
    SELECT 34411, 134575, '20160706 15:00:27.000' UNION ALL
    SELECT 34411, 134576, '20160706 15:11:27.000' UNION ALL
    SELECT 34411, 134578, '20160706 15:31:04.000' UNION ALL
    SELECT 34411, 134579, '20160706 15:42:14.000' UNION ALL
    SELECT 34411, 134580, '20160706 15:53:06.000' UNION ALL
    SELECT 34411, 134581, '20160706 16:01:43.000' UNION ALL
    SELECT 34411, 134582, '20160706 16:13:29.000' UNION ALL
    SELECT 34411, 134583, '20160706 16:21:41.000' UNION ALL
    SELECT 34411, 134584, '20160706 16:32:31.000' UNION ALL
    SELECT 34411, 134585, '20160706 16:40:44.000' UNION ALL
    SELECT 34411, 134586, '20160706 16:52:04.000' UNION ALL
    SELECT 34411, 134587, '20160706 17:02:52.000' UNION ALL
    SELECT 34411, 134588, '20160706 17:14:13.000' UNION ALL
    SELECT 34411, 134589, '20160706 17:22:19.000' UNION ALL
    SELECT 34411, 134590, '20160706 17:33:25.000' UNION ALL
    SELECT 34411, 134591, '20160706 17:44:13.000' UNION ALL
    SELECT 34411, 134592, '20160706 17:52:33.000' UNION ALL
    SELECT 34411, 134593, '20160706 18:03:55.000' UNION ALL
    SELECT 34411, 134594, '20160706 18:12:37.000' UNION ALL
    SELECT 34411, 134599, '20160706 19:03:59.000' UNION ALL
    SELECT 34411, 134600, '20160706 19:14:46.000' UNION ALL
    SELECT 34411, 134601, '20160706 19:22:59.000' UNION ALL
    SELECT 34411, 134602, '20160706 20:23:17.000' UNION ALL
    SELECT 34411, 134608, '20160706 20:35:42.000' UNION ALL
    SELECT 34411, 134609, '20160706 20:43:57.000' UNION ALL
    SELECT 34411, 134610, '20160706 20:54:59.000' UNION ALL
    SELECT 34411, 134611, '20160706 21:03:08.000' UNION ALL
    SELECT 34411, 134613, '20160706 21:24:51.000' UNION ALL
    SELECT 34411, 134614, '20160706 21:35:40.000' UNION ALL
    SELECT 34411, 134615, '20160706 21:43:51.000' UNION ALL
    SELECT 34411, 134616, '20160706 21:54:57.000' UNION ALL
    SELECT 34411, 134617, '20160706 22:06:01.000' UNION ALL
    SELECT 34411, 134620, '20160706 22:34:56.000' UNION ALL
    SELECT 34411, 134621, '20160706 22:45:38.000' UNION ALL
    SELECT 34411, 134622, '20160706 22:56:54.000' UNION ALL
    SELECT 34411, 134626, '20160706 23:35:15.000' UNION ALL
    SELECT 34411, 134627, '20160706 23:46:07.000' UNION ALL
    SELECT 34411, 134628, '20160706 23:57:09.000' UNION ALL
    SELECT 34411, 134667, '20160707 04:13:14.000' UNION ALL
    SELECT 34411, 134668, '20160707 04:23:36.000' UNION ALL
    SELECT 34411, 134669, NULL UNION ALL
    SELECT 34411, 134670, NULL UNION ALL
    SELECT 34411, 134671, NULL UNION ALL
    SELECT 34411, 134672, NULL UNION ALL
    SELECT 34411, 134673, NULL UNION ALL
    SELECT 34411, 134674, NULL UNION ALL
    SELECT 34411, 134675, '20160707 04:51:40.000' UNION ALL
    SELECT 34411, 134676, '20160707 05:06:40.000' UNION ALL
    SELECT 34411, 134677, '20160707 05:17:10.000' UNION ALL
    SELECT 34411, 134678, '20160707 05:20:13.000' UNION ALL
    SELECT 34411, 134679, NULL UNION ALL
    SELECT 34411, 134680, NULL UNION ALL
    SELECT 34411, 134681, NULL UNION ALL
    SELECT 34411, 134682, NULL UNION ALL
    SELECT 34411, 134683, NULL UNION ALL
    SELECT 34411, 134684, NULL UNION ALL
    SELECT 34411, 134685, '20160707 05:30:57.000' UNION ALL
    SELECT 34411, 134686, '20160707 05:44:06.000' UNION ALL
    SELECT 34411, 134687, '20160707 05:48:22.000' UNION ALL
    SELECT 34411, 134688, '20160707 06:02:23.000' UNION ALL
    SELECT 34411, 134690, '20160707 06:20:24.000' UNION ALL
    SELECT 34411, 134691, '20160707 06:36:21.000' UNION ALL
    SELECT 34411, 134692, NULL UNION ALL
    SELECT 34411, 134693, '20160707 06:51:21.000' UNION ALL
    SELECT 34411, 134697, '20160707 07:36:27.000' UNION ALL
    SELECT 34411, 134698, NULL UNION ALL
    SELECT 34411, 134699, NULL UNION ALL
    SELECT 34411, 134700, '20160707 08:01:21.000' UNION ALL
    SELECT 34411, 134703, NULL UNION ALL
    SELECT 34411, 134704, '20160707 08:44:35.000' UNION ALL
    SELECT 34411, 134705, NULL UNION ALL
    SELECT 34411, 134706, '20160707 09:04:22.000'
    
    INSERT INTO [dbo].[VersionDate] ([DeviceId], [Version], [OnDate])
       SELECT [DeviceId], [Version], [OnDate]
          FROM #Enigma
    
    SELECT Count(*) AS All_Columns, Count(OnDate) AS With_OnDate
    ,  Count(*) - Count(OnDate) AS Null_OnDate
       FROM [dbo].[VersionDate]
    
    DECLARE @d1  DATETIME2
    ,  @d2       DATETIME2
    
    SET @d1 = GetDate()
    
    UPDATE [dbo].[VersionDate]
       SET OnDate = (SELECT Min(z.OnDate)
          FROM [dbo].[VersionDate] AS z
          WHERE  z.DeviceID = VersionDate.DeviceID
             AND VersionDate.Version < z.Version)
       WHERE  OnDate IS NULL;
    
    SET @d2 = GetDate()
    SELECT DateDiff(ms, @d1, @d2) AS ms, 'Without Index' AS Query_Type
    
    TRUNCATE TABLE [dbo].[VersionDate]
    
    INSERT INTO [dbo].[VersionDate] ([DeviceId], [Version], [OnDate])
       SELECT [DeviceId], [Version], [OnDate]
          FROM #Enigma
    
    CREATE INDEX Enigma ON [dbo].[VersionDate] ([DeviceID], [Version])
    
    SET @d1 = GetDate()
    
    UPDATE [dbo].[VersionDate]
       SET OnDate = (SELECT Min(z.OnDate)
          FROM [dbo].[VersionDate] AS z
          WHERE  z.DeviceID = VersionDate.DeviceID
             AND VersionDate.Version < z.Version)
       WHERE  OnDate IS NULL;
    
    SET @d2 = GetDate()
    SELECT DateDiff(ms, @d1, @d2) AS ms, 'Using Index' AS Query_Type
    
    DROP TABLE #Enigma
    I got:
    Code:
    (105 row(s) affected)
    
    (105 row(s) affected)
    All_Columns With_OnDate Null_OnDate
    ----------- ----------- -----------
    105         88          17
    Warning: Null value is eliminated by an aggregate or other SET operation.
    
    (1 row(s) affected)
    
    Warning: Null value is eliminated by an aggregate or other SET operation.
    
    (17 row(s) affected)
    ms          Query_Type
    ----------- -------------
    80          Without Index
    
    (1 row(s) affected)
    
    (105 row(s) affected)
    
    Warning: Null value is eliminated by an aggregate or other SET operation.
    
    (17 row(s) affected)
    ms          Query_Type
    ----------- -----------
    37          Using Index
    
    (1 row(s) affected)
    Doing essentially the same thing (many additional columns, but making sure that my update didn't alter any indexed column) with 263 million rows of data on a production server, I took a nine hour process that updated about 45 million rows down to just under eleven minutes.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I know that you want to avoid a sub-SELECT, but the best plan I could find was:
    Code:
    UPDATE [dbo].[VersionDate]
       SET OnDate = (SELECT Min(z.OnDate)
          FROM [dbo].[VersionDate] AS z
          WHERE  z.DeviceID = VersionDate.DeviceID
             AND VersionDate.Version < z.Version)
       WHERE  OnDate IS NULL;
    I really thought that a CTE would do better, especially if it could use an index. I couldn't find a way to make that produce a better plan.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Pat .. Been there .. Done that ... does not work really good with millions of rows
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Testing with your data on my laptop:
    Code:
    IF Object_Id('[dbo].[VersionDate]', 'U') IS NOT NULL
       DROP TABLE [dbo].[VersionDate]
    GO
    
    CREATE TABLE [dbo].[VersionDate](
        [DeviceID] [INT] NULL,
        [Version] [INT] NULL,
        [OnDate] [DATETIME] NULL
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE #Enigma (
        [DeviceID] [INT] NULL,
        [Version] [INT] NULL,
        [OnDate] [DATETIME] NULL
    );
    
    INSERT INTO #Enigma ([DeviceId], [Version], [OnDate])
    SELECT 34411, 134541, '20160706 09:19:18.000' UNION ALL
    SELECT 34411, 134542, '20160706 09:29:36.000' UNION ALL
    SELECT 34411, 134543, '20160706 09:37:48.000' UNION ALL
    SELECT 34411, 134544, '20160706 10:32:44.000' UNION ALL
    SELECT 34411, 134545, '20160706 10:32:46.000' UNION ALL
    SELECT 34411, 134546, '20160706 10:32:47.000' UNION ALL
    SELECT 34411, 134547, '20160706 10:32:48.000' UNION ALL
    SELECT 34411, 134548, '20160706 10:33:00.000' UNION ALL
    SELECT 34411, 134549, '20160706 10:42:54.000' UNION ALL
    SELECT 34411, 134550, '20160706 10:51:10.000' UNION ALL
    SELECT 34411, 134551, '20160706 10:59:41.000' UNION ALL
    SELECT 34411, 134553, '20160706 11:18:52.000' UNION ALL
    SELECT 34411, 134554, '20160706 11:29:54.000' UNION ALL
    SELECT 34411, 134555, '20160706 11:40:50.000' UNION ALL
    SELECT 34411, 134556, '20160706 11:49:12.000' UNION ALL
    SELECT 34411, 134557, '20160706 12:00:07.000' UNION ALL
    SELECT 34411, 134558, '20160706 12:11:25.000' UNION ALL
    SELECT 34411, 134559, '20160706 12:19:42.000' UNION ALL
    SELECT 34411, 134560, '20160706 12:31:28.000' UNION ALL
    SELECT 34411, 134561, '20160706 12:39:41.000' UNION ALL
    SELECT 34411, 134562, '20160706 12:50:34.000' UNION ALL
    SELECT 34411, 134563, '20160706 12:59:02.000' UNION ALL
    SELECT 34411, 134564, '20160706 13:10:03.000' UNION ALL
    SELECT 34411, 134565, '20160706 13:21:06.000' UNION ALL
    SELECT 34411, 134566, '20160706 13:29:44.000' UNION ALL
    SELECT 34411, 134567, '20160706 13:40:45.000' UNION ALL
    SELECT 34411, 134568, '20160706 13:51:38.000' UNION ALL
    SELECT 34411, 134569, '20160706 14:02:44.000' UNION ALL
    SELECT 34411, 134570, '20160706 14:11:04.000' UNION ALL
    SELECT 34411, 134571, '20160706 14:22:10.000' UNION ALL
    SELECT 34411, 134572, '20160706 14:30:32.000' UNION ALL
    SELECT 34411, 134573, '20160706 14:41:23.000' UNION ALL
    SELECT 34411, 134574, '20160706 14:52:13.000' UNION ALL
    SELECT 34411, 134575, '20160706 15:00:27.000' UNION ALL
    SELECT 34411, 134576, '20160706 15:11:27.000' UNION ALL
    SELECT 34411, 134578, '20160706 15:31:04.000' UNION ALL
    SELECT 34411, 134579, '20160706 15:42:14.000' UNION ALL
    SELECT 34411, 134580, '20160706 15:53:06.000' UNION ALL
    SELECT 34411, 134581, '20160706 16:01:43.000' UNION ALL
    SELECT 34411, 134582, '20160706 16:13:29.000' UNION ALL
    SELECT 34411, 134583, '20160706 16:21:41.000' UNION ALL
    SELECT 34411, 134584, '20160706 16:32:31.000' UNION ALL
    SELECT 34411, 134585, '20160706 16:40:44.000' UNION ALL
    SELECT 34411, 134586, '20160706 16:52:04.000' UNION ALL
    SELECT 34411, 134587, '20160706 17:02:52.000' UNION ALL
    SELECT 34411, 134588, '20160706 17:14:13.000' UNION ALL
    SELECT 34411, 134589, '20160706 17:22:19.000' UNION ALL
    SELECT 34411, 134590, '20160706 17:33:25.000' UNION ALL
    SELECT 34411, 134591, '20160706 17:44:13.000' UNION ALL
    SELECT 34411, 134592, '20160706 17:52:33.000' UNION ALL
    SELECT 34411, 134593, '20160706 18:03:55.000' UNION ALL
    SELECT 34411, 134594, '20160706 18:12:37.000' UNION ALL
    SELECT 34411, 134599, '20160706 19:03:59.000' UNION ALL
    SELECT 34411, 134600, '20160706 19:14:46.000' UNION ALL
    SELECT 34411, 134601, '20160706 19:22:59.000' UNION ALL
    SELECT 34411, 134602, '20160706 20:23:17.000' UNION ALL
    SELECT 34411, 134608, '20160706 20:35:42.000' UNION ALL
    SELECT 34411, 134609, '20160706 20:43:57.000' UNION ALL
    SELECT 34411, 134610, '20160706 20:54:59.000' UNION ALL
    SELECT 34411, 134611, '20160706 21:03:08.000' UNION ALL
    SELECT 34411, 134613, '20160706 21:24:51.000' UNION ALL
    SELECT 34411, 134614, '20160706 21:35:40.000' UNION ALL
    SELECT 34411, 134615, '20160706 21:43:51.000' UNION ALL
    SELECT 34411, 134616, '20160706 21:54:57.000' UNION ALL
    SELECT 34411, 134617, '20160706 22:06:01.000' UNION ALL
    SELECT 34411, 134620, '20160706 22:34:56.000' UNION ALL
    SELECT 34411, 134621, '20160706 22:45:38.000' UNION ALL
    SELECT 34411, 134622, '20160706 22:56:54.000' UNION ALL
    SELECT 34411, 134626, '20160706 23:35:15.000' UNION ALL
    SELECT 34411, 134627, '20160706 23:46:07.000' UNION ALL
    SELECT 34411, 134628, '20160706 23:57:09.000' UNION ALL
    SELECT 34411, 134667, '20160707 04:13:14.000' UNION ALL
    SELECT 34411, 134668, '20160707 04:23:36.000' UNION ALL
    SELECT 34411, 134669, NULL UNION ALL
    SELECT 34411, 134670, NULL UNION ALL
    SELECT 34411, 134671, NULL UNION ALL
    SELECT 34411, 134672, NULL UNION ALL
    SELECT 34411, 134673, NULL UNION ALL
    SELECT 34411, 134674, NULL UNION ALL
    SELECT 34411, 134675, '20160707 04:51:40.000' UNION ALL
    SELECT 34411, 134676, '20160707 05:06:40.000' UNION ALL
    SELECT 34411, 134677, '20160707 05:17:10.000' UNION ALL
    SELECT 34411, 134678, '20160707 05:20:13.000' UNION ALL
    SELECT 34411, 134679, NULL UNION ALL
    SELECT 34411, 134680, NULL UNION ALL
    SELECT 34411, 134681, NULL UNION ALL
    SELECT 34411, 134682, NULL UNION ALL
    SELECT 34411, 134683, NULL UNION ALL
    SELECT 34411, 134684, NULL UNION ALL
    SELECT 34411, 134685, '20160707 05:30:57.000' UNION ALL
    SELECT 34411, 134686, '20160707 05:44:06.000' UNION ALL
    SELECT 34411, 134687, '20160707 05:48:22.000' UNION ALL
    SELECT 34411, 134688, '20160707 06:02:23.000' UNION ALL
    SELECT 34411, 134690, '20160707 06:20:24.000' UNION ALL
    SELECT 34411, 134691, '20160707 06:36:21.000' UNION ALL
    SELECT 34411, 134692, NULL UNION ALL
    SELECT 34411, 134693, '20160707 06:51:21.000' UNION ALL
    SELECT 34411, 134697, '20160707 07:36:27.000' UNION ALL
    SELECT 34411, 134698, NULL UNION ALL
    SELECT 34411, 134699, NULL UNION ALL
    SELECT 34411, 134700, '20160707 08:01:21.000' UNION ALL
    SELECT 34411, 134703, NULL UNION ALL
    SELECT 34411, 134704, '20160707 08:44:35.000' UNION ALL
    SELECT 34411, 134705, NULL UNION ALL
    SELECT 34411, 134706, '20160707 09:04:22.000'
    
    INSERT INTO [dbo].[VersionDate] ([DeviceId], [Version], [OnDate])
       SELECT [DeviceId], [Version], [OnDate]
          FROM #Enigma
    
    SELECT Count(*) AS All_Columns, Count(OnDate) AS With_OnDate
    ,  Count(*) - Count(OnDate) AS Null_OnDate
       FROM [dbo].[VersionDate]
    
    DECLARE @d1  DATETIME2
    ,  @d2       DATETIME2
    
    SET @d1 = GetDate()
    
    UPDATE [dbo].[VersionDate]
       SET OnDate = (SELECT Min(z.OnDate)
          FROM [dbo].[VersionDate] AS z
          WHERE  z.DeviceID = VersionDate.DeviceID
             AND VersionDate.Version < z.Version)
       WHERE  OnDate IS NULL;
    
    SET @d2 = GetDate()
    SELECT DateDiff(ms, @d1, @d2) AS ms, 'Without Index' AS Query_Type
    
    TRUNCATE TABLE [dbo].[VersionDate]
    
    INSERT INTO [dbo].[VersionDate] ([DeviceId], [Version], [OnDate])
       SELECT [DeviceId], [Version], [OnDate]
          FROM #Enigma
    
    CREATE INDEX Enigma ON [dbo].[VersionDate] ([DeviceID], [Version])
    
    SET @d1 = GetDate()
    
    UPDATE [dbo].[VersionDate]
       SET OnDate = (SELECT Min(z.OnDate)
          FROM [dbo].[VersionDate] AS z
          WHERE  z.DeviceID = VersionDate.DeviceID
             AND VersionDate.Version < z.Version)
       WHERE  OnDate IS NULL;
    
    SET @d2 = GetDate()
    SELECT DateDiff(ms, @d1, @d2) AS ms, 'Using Index' AS Query_Type
    
    DROP TABLE #Enigma
    I got:
    Code:
    (105 row(s) affected)
    
    (105 row(s) affected)
    All_Columns With_OnDate Null_OnDate
    ----------- ----------- -----------
    105         88          17
    Warning: Null value is eliminated by an aggregate or other SET operation.
    
    (1 row(s) affected)
    
    Warning: Null value is eliminated by an aggregate or other SET operation.
    
    (17 row(s) affected)
    ms          Query_Type
    ----------- -------------
    80          Without Index
    
    (1 row(s) affected)
    
    (105 row(s) affected)
    
    Warning: Null value is eliminated by an aggregate or other SET operation.
    
    (17 row(s) affected)
    ms          Query_Type
    ----------- -----------
    37          Using Index
    
    (1 row(s) affected)
    Doing essentially the same thing (many additional columns, but making sure that my update didn't alter any indexed column) with 263 million rows of data on a production server, I took a nine hour process that updated about 45 million rows down to just under eleven minutes.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I agree , however this is for a dashboard drill down report and i need better performance than minutes . I will keep looking for a different way and post it here if i find one.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •