Indexing DateTime

Question

Indexing DateTime

RandyFerris

Old Hand

Points: 373
More actions
December 21, 2005 at 5:34 am

#92531

I have a table of production data with this basic schema. We need to query based on the time stamp. The time stamp consists of the year, (2005) the month (12) the day(12), the hour(16), the minute(55) and the second(24) and the line number(07) in the case of 005121216552407

ProductID                    WO_ID            TimeStamp
A2C20009365             9432101          2005121216552407
A2C20009365             9432102          2005121216552407
A2C20009365             9432103          2005121216552407
A2C20009365             9432104          2005121216552407
A2C20009365             9432105          2005121318532807

I have a UDF to return a datetime for reporting, but we need to search and group by this field and therefore we need to index this timestamp as a datetime. We will have several million rows of data. Any Ideas?

CREATE function dbo.PCRBuildDate(@BN varchar(32))
Returns DateTime
as
BEGIN

            declare @buildate datetime
            select @buildate = left(@BN,4) + '-' + substring(@BN,5,2) + '-'
            + substring(@BN,7,2) + ' ' + substring(@BN,9,2)
            + ':' + substring(@BN,9,2) + ':' + substring(@BN,11,2) + '.000'

            RETURN @buildate

END

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

I am not sure what you want to do, but I think I would add a Date column and a LineNumber column, then transfer data in the TimeStamp column to these columns and perhaps then drop the TimeStamp column... Does this sound reasonable?

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 2

You could make the extra Date and Linenumber fields computed fields and add an index on them. Everything stays in sync.

Any reason not to use left(BN,14) instead of all those substrings?

RandyFerris Old Hand Points: 373 More actions · Answer 3

Thanks for the replys.

This is what I am trying to do. This data is generated by a manufacturing line we received from Germany. The line can build up to 36 parts per minute. Each part will have a record with this type of data. With these files we need to be able to calculate rates per minute etc. Trying to query on the table and use the UDF to sort was inefficient.

After reading your posts I have created a view that derives a datetime from the TimeStamp column, then I have indexed that derived datetime column in the view. Does this sound like an efficient solution?

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 4

A side remark: Why sort by the result of the UDF? Why not sort by the TimeStamp column (or perhaps left(TimeStamp, 14))? That will amount to the same thing (as far as I can see) but be much more efficient...

RandyFerris Old Hand Points: 373 More actions · Answer 5

RandyFerris

Old Hand

Points: 373

December 21, 2005 at 8:16 am

#610849

Good Idea, Thanks

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 6

In fact, forget about extra columns, views and UDFs. Put an index on the TimeStamp column, then I think you can extract all the information you want from this table (if you don't agree, please post a more detailed example of what you want).