How can I calculate how many mussels have been shucked?

Question

Post reply

How can I calculate how many mussels have been shucked?

pstarling

SSC Rookie

Points: 31
More actions
August 24, 2008 at 5:18 pm

#197471

The Situation

I work for a company that farms, harvests and processes Greenshell Mussels.

The 'Mussels Shuckers' get paid per mussel they shuck.

At the shucking station each mussel is counted by an electronic eye and the data is dumped from the SCADA system into a SQL 2005 database every 180 seconds (This time period is variable). Unfortunately the number of mussels shucked is a running count for the shift rather than the sum for the 180 second time period.

The data is stored in the following table:

[ShiftCountersRawData](

[RecID] [int] IDENTITY(1,1) NOT NULL,

[ShiftID] [int] NOT NULL,

[TableID] [int] NOT NULL,

[StationID] [int] NOT NULL,

[EmployeeID] [varchar](12) NULL,

[CounterDateTimeStamp] [datetime] NOT NULL,

[MusselCount] [int] NOT NULL,

[CounterTimeSpan] [int] NULL

The selected data looks:

SELECT top 15

[ShiftID]

,[TableID]

,[StationID]

,[EmployeeID]

,[CounterDateTimeStamp]

,[MusselCount]

,[CounterTimeSpan]

FROM [PS_COUNTERS_LIVE].[dbo].[ShiftCountersRawData]

ORDER BY [ShiftID] ,[TableID],[StationID],[EmployeeID],[CounterDateTimeStamp]

7 1 1 6652 2006-12-07 13:13:22.223 10261 180

7 1 1 6652 2006-12-07 13:16:22.330 10261 180

7 1 1 6652 2006-12-07 13:19:22.470 10302 180

7 1 1 6652 2006-12-07 13:22:22.470 10388 180

7 1 1 6652 2006-12-07 13:25:22.487 10476 180

7 1 1 6652 2006-12-07 13:28:22.487 10565 180

7 1 1 6652 2006-12-07 13:31:22.487 10648 180

7 1 1 6652 2006-12-07 13:34:22.487 10734 180

7 1 1 6652 2006-12-07 13:37:22.487 10825 180

7 1 1 6652 2006-12-07 13:40:22.487 10917 180

7 1 1 6652 2006-12-07 13:43:22.503 10998 180

7 1 1 6652 2006-12-07 13:46:22.503 11084 180

7 1 1 6652 2006-12-07 13:49:22.503 11179 180

7 1 1 6652 2006-12-07 13:52:22.503 11268 180

7 1 1 6652 2006-12-07 13:55:22.503 11355 180

My Question

How do I calculate how many mussels have been shucked in the 180 second span.

I know that the number I need is (NextRecord.MusselCount - CurrentRecord.MusselCount) But do not know how to code it in SQL.

Can someone enlighten me?

Thanks

Paul

Paul

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Try this:



Select [ShiftID]

  ,[TableID]

  ,[StationID]

  ,[EmployeeID]

  ,[CounterDateTimeStamp]

  ,[MusselCount]

  ,[CounterTimeSpan]

  ,MusselCount - Coalesce( (Select B.MusselCount

	 From [ShiftCountersRawData] B

	 Where B.ShiftID = A.ShiftID

	  And B.TableID = A.TableID

	  And B.StationID = A.StationID

	  And B.EmployeeID = A.EmployeeID

	  And B.CounterDateTimeStamp

		Between DateAdd(ss, -CounterTimeSpan-5, A.CounterDateTimeStamp)

		 And DateAdd(ss, -CounterTimeSpan+5, A.CounterDateTimeStamp))

	, 0) as [MusselCountDiff]

  FROM [PS_COUNTERS_LIVE].[dbo].[ShiftCountersRawData]

I'm assuming that you have an index on ([ShiftID]

,[TableID]

,[StationID]

,[EmployeeID]

,[CounterDateTimeStamp]

,[MusselCount]

,[CounterTimeSpan]), which you will need in any event.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Rufio Hall of Fame Points: 3064 More actions · Answer 2

with RawData (EmpRow, ShiftID, TableID, StationID, EmployeeID, MusselCount, CounterDateTimeStamp) as (

select

row_number() over (partition by EmployeeID order by ShiftID, TableID, StationID),

ShiftID,

TableID,

StationID,

EmployeeID,

MusselCount,

CounterDateTimeStamp

from

ShiftCountersRawData)

select

a.ShiftID,

a.TableID,

a.StationID,

a.CounterDateTimeStamp,

a.EmployeeID,

(a.MusselCount - b.MusselCount) MusselCount,

datediff(s, b.CounterDateTimeStamp, a.CounterDateTimeStamp) CounterPeriod

from

RawData a

left join

RawData b on

a.EmpRow = b.EmpRow + 1 and

a.EmployeeID = b.EmployeeID

where

b.MusselCount is not null

For further reading look at this article

http://qa.sqlservercentral.com/articles/T-SQL/62159/

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Oops, I got the order of the last 2 fields of the index wrong. Should be:

[ShiftID]

,[TableID]

,[StationID]

,[EmployeeID]

,[CounterDateTimeStamp]

,[CounterTimeSpan]

,[MusselCount]

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

pstarling SSC Rookie Points: 31 More actions · Answer 4

pstarling

SSC Rookie

Points: 31

August 25, 2008 at 4:20 pm

#862852

Thanks for the code but unfortunately it returned zero rows.

Paul

pstarling SSC Rookie Points: 31 More actions · Answer 5

Hi Rufio,

Firstly:

Thanks for the pointer to the article. It was functionality that I was unaware of in SQL Server.

Secondly:

Your code seems to be almost there.

I chose some raw data:

SELECT [ShiftID]

,[TableID]

,[StationID]

,[EmployeeID]

,[CounterDateTimeStamp]

,[MusselCount]

,[CounterTimeSpan]

FROM [PS_COUNTERS_LIVE].[dbo].[ShiftCountersRawData]

where ShiftID = 7

and TableID = 1

and StationID =1

and EmployeeID = 6652

ORDER BY [ShiftID] ,[TableID],[StationID],[EmployeeID],[CounterDateTimeStamp]

which returned:

ShiftID TableID StationID EmployeeID CounterDateTimeStamp MusselCount CounterTimeSpan

----------- ----------- ----------- ------------ ----------------------- ----------- ---------------

7 1 1 6652 2006-12-07 13:13:22.223 10261 180

7 1 1 6652 2006-12-07 13:16:22.330 10261 180

7 1 1 6652 2006-12-07 13:19:22.470 10302 180

7 1 1 6652 2006-12-07 13:22:22.470 10388 180

7 1 1 6652 2006-12-07 13:25:22.487 10476 180

7 1 1 6652 2006-12-07 13:28:22.487 10565 180

7 1 1 6652 2006-12-07 13:31:22.487 10648 180

7 1 1 6652 2006-12-07 13:34:22.487 10734 180

7 1 1 6652 2006-12-07 13:37:22.487 10825 180

7 1 1 6652 2006-12-07 13:40:22.487 10917 180

7 1 1 6652 2006-12-07 13:43:22.503 10998 180

7 1 1 6652 2006-12-07 13:46:22.503 11084 180

7 1 1 6652 2006-12-07 13:49:22.503 11179 180

7 1 1 6652 2006-12-07 13:52:22.503 11268 180

7 1 1 6652 2006-12-07 13:55:22.503 11355 180

7 1 1 6652 2006-12-07 13:58:22.503 11436 180

7 1 1 6652 2006-12-07 14:01:22.673 11521 180

7 1 1 6652 2006-12-07 14:04:22.767 11600 180

7 1 1 6652 2006-12-07 14:07:22.770 11684 180

7 1 1 6652 2006-12-07 14:10:22.770 11763 180

7 1 1 6652 2006-12-07 14:13:22.770 11839 180

7 1 1 6652 2006-12-07 14:16:22.770 11894 180

7 1 1 6652 2006-12-07 14:19:22.783 11972 180

7 1 1 6652 2006-12-07 14:22:22.783 12005 180

7 1 1 6652 2006-12-07 14:25:22.783 12025 180

7 1 1 6652 2006-12-07 14:28:22.783 12025 180

7 1 1 6652 2006-12-07 14:31:22.863 12025 180

7 1 1 6652 2006-12-07 14:34:22.923 12025 180

7 1 1 6652 2006-12-07 14:37:22.923 12025 180

7 1 1 6652 2006-12-07 14:40:22.923 12025 180

7 1 1 6652 2006-12-07 14:43:22.923 12025 180

7 1 1 6652 2006-12-07 14:46:23.050 12025 180

7 1 1 6652 2006-12-07 14:49:23.177 12025 181

7 1 1 6652 2006-12-07 14:52:23.190 12025 180

7 1 1 6652 2006-12-07 14:55:23.190 12025 180

7 1 1 6652 2006-12-07 14:58:23.190 12025 180

7 1 1 6652 2006-12-07 15:01:23.190 12025 180

7 1 1 6652 2006-12-07 15:04:23.207 12025 180

7 1 1 6652 2006-12-07 15:07:23.207 12025 180

7 1 1 6652 2006-12-07 15:10:23.207 12025 180

7 1 1 6652 2006-12-07 15:13:23.207 12025 180

7 1 1 6652 2006-12-07 15:16:23.207 12025 180

7 1 1 6652 2006-12-07 15:18:39.440 12025 136

(43 row(s) affected)

With the same 'where' clause your excellent code returned:

ShiftID TableID StationID CounterDateTimeStamp EmployeeID MusselCount CounterPeriod

----------- ----------- ----------- ----------------------- ------------ ----------- -------------

7 1 1 2006-12-07 13:16:22.330 6652 0 180

7 1 1 2006-12-07 13:19:22.470 6652 41 180

7 1 1 2006-12-07 13:22:22.470 6652 86 180

7 1 1 2006-12-07 13:25:22.487 6652 88 180

7 1 1 2006-12-07 13:28:22.487 6652 89 180

7 1 1 2006-12-07 13:31:22.487 6652 83 180

7 1 1 2006-12-07 13:34:22.487 6652 86 180

7 1 1 2006-12-07 13:37:22.487 6652 91 180

7 1 1 2006-12-07 13:40:22.487 6652 92 180

7 1 1 2006-12-07 13:43:22.503 6652 81 180

7 1 1 2006-12-07 13:46:22.503 6652 86 180

7 1 1 2006-12-07 13:49:22.503 6652 95 180

7 1 1 2006-12-07 13:52:22.503 6652 89 180

7 1 1 2006-12-07 13:55:22.503 6652 87 180

7 1 1 2006-12-07 13:58:22.503 6652 81 180

7 1 1 2006-12-07 14:01:22.673 6652 85 180

7 1 1 2006-12-07 14:04:22.767 6652 79 180

7 1 1 2006-12-07 14:07:22.770 6652 84 180

7 1 1 2006-12-07 14:10:22.770 6652 79 180

7 1 1 2006-12-07 14:13:22.770 6652 76 180

7 1 1 2006-12-07 14:16:22.770 6652 55 180

7 1 1 2006-12-07 14:19:22.783 6652 78 180

7 1 1 2006-12-07 14:22:22.783 6652 33 180

7 1 1 2006-12-07 14:25:22.783 6652 20 180

7 1 1 2006-12-07 14:28:22.783 6652 0 180

7 1 1 2006-12-07 14:31:22.863 6652 0 180

7 1 1 2006-12-07 14:34:22.923 6652 0 180

7 1 1 2006-12-07 14:37:22.923 6652 0 180

7 1 1 2006-12-07 14:40:22.923 6652 0 180

7 1 1 2006-12-07 14:43:22.923 6652 0 180

7 1 1 2006-12-07 14:46:23.050 6652 0 181

7 1 1 2006-12-07 14:49:23.177 6652 0 180

7 1 1 2006-12-07 14:52:23.190 6652 0 180

7 1 1 2006-12-07 14:55:23.190 6652 0 180

7 1 1 2006-12-07 14:58:23.190 6652 0 180

7 1 1 2006-12-07 15:01:23.190 6652 0 180

7 1 1 2006-12-07 15:04:23.207 6652 0 180

7 1 1 2006-12-07 15:07:23.207 6652 0 180

7 1 1 2006-12-07 15:10:23.207 6652 0 180

7 1 1 2006-12-07 15:13:23.207 6652 0 180

7 1 1 2006-12-07 15:16:23.207 6652 0 180

7 1 1 2006-12-07 15:18:39.440 6652 0 136

(42 row(s) affected)

However as you can see above, the code assumes that the count starts from zero which may not always be the case, it depends if the supervisor remembers to start the shift on the computer as well as on the SCADA system. If they 'forget' the counter system picks up at the current count, in this case 10261 mussels.

Can I impose upon your good will to include this senario in the code?

Regards

Paul

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

pstarling (8/25/2008)

Thanks for the code but unfortunately it returned zero rows.

That cannot possibly be the fault of my code. You will note the lack of a where clause, a join or any other means of filtering the data at all. I grant you that my code could be in error, or could return the wrong results, but if it returns NO rows, that must be because of something that you did.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 7

Here is my contribution:

declare @ShiftCountersRawData table(



    RecID               int IDENTITY(1,1) NOT NULL,

    ShiftID             int NOT NULL,

    TableID             int NOT NULL,

    StationID           int NOT NULL,

    EmployeeID          varchar(12) NULL,

    CounterDateTimeStamp datetime NOT NULL,

    MusselCount         int NOT NULL,

    CounterTimeSpan     int NULL

);

Insert  @ShiftCountersRawData

        (ShiftID, TableID, StationID, EmployeeID, CounterDateTimeStamp, MusselCount, CounterTimeSpan)

select  7, 1, 1, 6652, '2006-12-07 13:13:22.223', 10261, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:16:22.330', 10261, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:19:22.470', 10302, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:22:22.470', 10388, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:25:22.487', 10476, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:28:22.487', 10565, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:31:22.487', 10648, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:34:22.487', 10734, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:37:22.487', 10825, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:40:22.487', 10917, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:43:22.503', 10998, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:46:22.503', 11084, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:49:22.503', 11179, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:52:22.503', 11268, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:55:22.503', 11355, 180	union all

select  7, 1, 1, 6652, '2006-12-07 13:58:22.503', 11436, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:01:22.673', 11521, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:04:22.767', 11600, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:07:22.770', 11684, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:10:22.770', 11763, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:13:22.770', 11839, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:16:22.770', 11894, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:19:22.783', 11972, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:22:22.783', 12005, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:25:22.783', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:28:22.783', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:31:22.863', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:34:22.923', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:37:22.923', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:40:22.923', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:43:22.923', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:46:23.050', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:49:23.177', 12025, 181	union all

select  7, 1, 1, 6652, '2006-12-07 14:52:23.190', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:55:23.190', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 14:58:23.190', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 15:01:23.190', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 15:04:23.207', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 15:07:23.207', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 15:10:23.207', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 15:13:23.207', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 15:16:23.207', 12025, 180	union all

select  7, 1, 1, 6652, '2006-12-07 15:18:39.440', 12025, 136;

-- Now find out how many mussels a mussleshucker could shuck if a mussleshucker could shuck mussles.

-- (Sorry 'bout that :))

select  s1.ShiftID, s1.TableID, s1.StationID, s1.EmployeeID,

        s1.CounterDateTimeStamp as CounterStart, s2.CounterDateTimeStamp as CounterEnd,

        s1.MusselCount as StartingMusselCount, s2.MusselCount as EndingMusselCount,

        s2.MusselCount - s1.MusselCount as MusselCount

from    @ShiftCountersRawData   s1

join    @ShiftCountersRawData   s2

    on  s1.ShiftID      = s2.ShiftID

    and s1.TableID      = s2.TableID

    and s1.StationID    = s2.StationID

    and s1.EmployeeID   = s2.EmployeeID

    and s2.CounterDateTimeStamp  = (

        select  Min( s3.CounterDateTimeStamp )

        from    @ShiftCountersRawData   s3

        where  s3.CounterDateTimeStamp > s1.CounterDateTimeStamp

            and  s3.ShiftID  = s1.ShiftID

            and  s3.TableID  = s1.TableID

            and  s3.StationID= s1.StationID

            and  s3.EmployeeID= s1.EmployeeID

        group by s3.ShiftID, s3.TableID, s3.StationID, s3.EmployeeID

    )

order by s1.ShiftID, s1.TableID, s1.StationID, s1.EmployeeID, s1.CounterDateTimeStamp

You'll notice there is no assumption that the counter is reset to zero. I have displayed more fields than you need, but you will get a better picture of what is going on.

For performance, create a covering index on ShiftID, TableID, StationID, EmployeeID, CounterDateTimeStamp. In fact, as the Identity field may rarely be used in ordering, you will probably get a bit more performance if you define the PK index nonclustered and create the covering index clustered. In fact, although the query above seems to be quite convoluted, you will get impressive performance from it (with the index) even if the table is quite large.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

pstarling SSC Rookie Points: 31 More actions · Answer 8

Thanks for all your help, as the man in the Visa advert said 'That will do nicely Sir'

Re : Now find out how many mussels a mussleshucker could shuck if a mussleshucker could shuck mussles. :w00t:

Just to show how fast these guys actually are, in 2007 one of our staff, Kannha Keo, set a Guinness World record of 2 min 11 sec for opening 100 mussels, beating her brother who set it the year before. Thats a mussel every 1.3 seconds if you can imagine that.

The clicp is at: http://community.guinnessworldrecords.com/kickapps/_Mussel-opening-fastest-100/video/93225/7691.html

Paul

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Tomm Carr (8/26/2008)

Here is my contribution:

Ummmm... nicely done, but be careful. If you take a look at the actual execution plan, there are two flow arrows of great concern... one with 1849 rows and the other 902. Notice that there are only 43 original rows of data and that 1849 = 43² which is a full "Square" or Cartesian Join and 903 = ((43-1)²+(43-1))/2 which is a Triangular join.

If anyone wants to know more about "Triangular Joins", please take a look at the article at the following link...

http://qa.sqlservercentral.com/articles/T-SQL/61539/

Perhaps something like this, instead...

;With



cteMussels AS

(

 SELECT ROW_NUMBER() OVER (PARTITION BY ShiftID, TableID, StationID, EmployeeID, DATEADD(dd,DATEDIFF(dd,0,CounterDateTimeStamp),0)

                               ORDER BY ShiftID, TableID, StationID, EmployeeID, CounterDateTimeStamp) AS RowNum,

        *

   FROM #ShiftCountersRawData

)

 SELECT m1.ShiftID, m1.TableID, m1.StationID, m1.EmployeeID,

        m1.CounterDateTimeStamp AS StartTime,

        m2.CounterDateTimeStamp AS EndTime,

        CONVERT(CHAR(12),m2.CounterDateTimeStamp-m1.CounterDateTimeStamp,114) AS Duration,

        m1.MusselCount AS StartCount,

        m2.MusselCount AS EndCount,

        m2.MusselCount-m1.MusselCount AS PeriodCount

   FROM cteMussels m1

  INNER JOIN cteMussels m2 ON m1.RowNum+1 = m2.RowNum

Because of the relatively low original row count, it won't make much of a difference in performance, but it will cut the scan counts from 45 down to 2 and the logical reads from 87 down to 2.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Jeff, dude:

Jeff Moden (8/26/2008)

SELECT ROW_NUMBER() OVER (PARTITION BY ShiftID, TableID, StationID, EmployeeID, DATEADD(dd,DATEDIFF(dd,0,CounterDateTimeStamp),0) ORDER BY ShiftID, TableID, StationID, EmployeeID, CounterDateTimeStamp) AS RowNum, *

... Uhm, ... what the blankety-blank is this !?!?!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 11

Jeff, you are correct. However, I have to avoid things like CTEs. Not because I have anything against them but because 90% of my dbs are still SS2k, not to mention the Oracle side.

The query I submitted follows a pattern I developed about 8 months ago (though I doubt it is original with me) for joining a row with the chronologically next row of a table. The key to performance is the index (hmm, come to think of it, isn't it almost always the index? :)).

Change the table definition in my code from a table variable to a permanent table. Like this:create table ShiftCountersRawData (



    ShiftID             int         NOT NULL,

    TableID             int         NOT NULL,

    StationID           int         NOT NULL,

    EmployeeID          varchar(12) NOT NULL,

    CounterDateTimeStamp datetime   NOT NULL,

    MusselCount         int         NOT NULL,

    CounterTimeSpan     int             NULL,

    constraint PK_ShiftCountersRawData primary key (ShiftID, TableID,

                             StationID, EmployeeID, CounterDateTimeStamp)

);

I've eliminated the Identity key field because it is not needed by this query (indeed, I have a feeling it is not used much at all in any of that company's queries) and the PK index is also the covering index. Change the query only to remove the "@" from the table names and look at the actual execution plan. Viola!

I use this pattern on tables with over a million records. Performance is excellent. The index is critical, of course. With it, you get the bear :smooooth:; without it, the bear gets you :crying:. In fact, in answer possibly to Barry's question, isn't that what you are accomplishing on the fly with the partition -- getting the search range down to one day?

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

noeld SSC Guru Points: 96590 More actions · Answer 12

I felt that I had to post my version 😀



    SELECT    a.ShiftID

		, a.TableID

		, a.StationID

		, a.EmployeeID

		, a.CounterDateTimeStamp

		, a.MusselCount

		, a.CounterTimeSpan

		,(SELECT TOP 1 b.MusselCount

                   FROM dbo.ShiftCountersRawData b

                   WHERE     b.ShiftID = a.ShiftID

                         AND b.TableID = a.TableID

                         AND b.StationID = a.StationID

                         AND b.EmployeeID = a.EmployeeID

                         AND b.CounterDateTimeStamp > a.CounterDateTimeStamp

          ORDER BY  b.CounterDateTimeStamp )

         - a.MusselCount as MusselCountDiff

  FROM dbo.ShiftCountersRawData a

  ORDER BY a.ShiftID, a.TableID, a.StationID, a.EmployeeID, a.CounterDateTimeStamp

* Noel

pstarling SSC Rookie Points: 31 More actions · Answer 13

Hi Guys,

Thanks for the continued input. I stopped DBA'ing about five years ago hence a bit rusty.

I'll test all the solutions against the database (c2M rows) and post back the best performing.

For those that are interested, the database server is a HP DL385 with 2 x AMD Dual Core Dual Processor,4GB RAM running on Windows Server 2003 Standard using SQL Server 2005 latest patch. The OS and logs are mirrored, tempdb separate and the data is spread over 8 disks (RAID5) in attached an HP MSA 500 box.

Paul

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Modified version: for some reason the Coalesce() makes the optimizer go stupid:



Select [ShiftID]

  ,[TableID]

  ,[StationID]

  ,[EmployeeID]

  ,[CounterDateTimeStamp]

  ,[MusselCount]

  ,[CounterTimeSpan]

  ,MusselCount - (Select TOP 1 B.MusselCount

	 From [ShiftCountersRawData] B

	 Where B.ShiftID = A.ShiftID

	  And B.TableID = A.TableID

	  And B.StationID = A.StationID

	  And B.EmployeeID = A.EmployeeID

	  And B.CounterDateTimeStamp

		Between DateAdd(ss, -CounterTimeSpan-5, A.CounterDateTimeStamp)

		 And DateAdd(ss, -CounterTimeSpan+5, A.CounterDateTimeStamp))

	 as [MusselCountDiff]

  FROM [PS_COUNTERS_LIVE].[dbo].[ShiftCountersRawData]

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]