• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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
0
daronow
Asked:
daronow
1 Solution
 
Melih SARICACommented:
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now