Fastest way to generate a column containing the order of a subset of a table

I have a table with the following structure:

CREATE TABLE DATATABLE
(
D_ID INT IDENTITY(1,1) NOT NULL,
NAME VARCHAR(10) NOT NULL,
DATAVALUE MONEY NOT NULL,
DATADATETIME DATETIME NOT NULL,
UNIQUE_ORDER INT
)

Assuming the table already has the data loaded, I want to calculate the values of the UNIQUE_ORDER column for a specific value of the NAME column based on the order of the DATADATETIME column.  So for example I want my data to look like this:

D_ID    NAME    DATAVALUE    DATADATETIME     UNIQUE_ORDER
1        'A'            100.2            '1/1/2000 16:00'         3
2        'B'            111.6            '1/1/2000 16:01'         1
3        'A'            938.4            '1/1/2000 16:02'         4
4        'C'            256.7            '1/1/2000 13:20'         1
5        'A'            538.5            '1/1/2000 14:00'         2
6        'A'            344.4            '1/1/2000 11:00'         1

Note the UNIQUE_ORDER column can be filled using:
UPDATE DATATABLE SET UNIQUE_ORDER = (SELECT COUNT(*) + 1 FROM DATATABLE D2 WHERE D1.NAME = D2.NAME AND D2.D_ID < D1.D_ID)
FROM DATATABLE D1

First, is there a faster way to populate the UNIQUE_ORDER column?  Maybe select to a temp table in order (using an identity column) and then put the data back in?  Also, would a clustered index on the NAME and DATADATETIME help?  Should I add the index before or after I put the data into the table?

Dave
daronowAsked:
Who is Participating?
 
stevetheskiCommented:
Unique order looks like it is a combination of the name and the datetime column
if that is the case

CREATE
  INDEX [] ON [dbo].[yourTable] ([Name]  asc, [DATADATETIME]  desc )

then select * from the table
where confditions
order by Name asc, datadatetime desc

thats just as fast as adding another column
0
 
Melih SARICAOwnerCommented:
What is Uniqe_Order column used for ?
0
 
lluthienCommented:
a clustered index on name and datatable is not a option if you have a Primary Key on D_ID, which i assume.
it is just physically not possible, you could create a non-clustered one though.
if it will help, will depend on the volume of yourdata.

although, the index would be either over name, or over ID and Name,
because datadatetime doesnt have anything to do with the order.

furthermore, non_zero's question is kinda important,
what is it used for and HOW...

do you REALLY need this column to exist physically,
can you just calculate it when needed (COMPUTED column?)
do you want to update the order once per day or upon every update.?

when do you want to use the order.
this kind of things determine what to do with such a column.

under circumstances, you could even consider creating a view over this table,
which calculates the column, or just adjust your sql.

the idea: we kinda need more input
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
daronowAuthor Commented:
The data may come in out of order and I need the ability to have it pre-ordered for maximum speed when retrieving in order.  The column is really just the order of the data based on the DATADATETIME field.  The D_ID column can be dropped, it's not really needed.  The query I posted is off a bit -- here's the correct query:

UPDATE DATATABLE SET UNIQUE_ORDER = (SELECT COUNT(*) + 1 FROM DATATABLE D2 WHERE D1.NAME = D2.NAME AND D2.DATADATETIME < D1.DATADATETIME) FROM DATATABLE D1

The goal is to order the data offline (for example overnight) so during the day it can be read in the correct order as quickly as possible.

Let me know if you need more info still....

Dave
0
 
stevetheskiCommented:
if you are having problems with performance look into making a partitioned view of your table
basically it would be 4 seperate tables
DATATABLE_A
DATATABLE_B
DATATABLE_C
DATATABLE_D to howevermany you have

then add a checkconstraint to the "Name" column allowing only "A" records to go into this table

then index the datetime column for whatever way you wanna see your results
then create a view of the above tables which are all unioned together

when you select from the view if you want the "A" records it will only hit that table
in a perfect scenario with 4 tables you will have 25% data in each one
therefore reducing your seeks by 75%

0
 
stevetheskiCommented:
oh by the way you can insert directly into a partitioned view
and just name the view what your original table was

Its a little slower for loading but a heck of a lot faster for retreiving
Steve
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.