Keeping DB Table sorted using multi-field formula (Microsoft SQL Server)

Question

I have a JOB table, with two interesting columns:

• Creation Date
• Importance (high - 3, medium 2, low - 1).

A JOB record's priority calculated like this:

Priority = Importance * (time passed since creation)

The problem is, every time I would like to pick 200 jobs with highest priority, and I don't want to resort the table. Is there a way to keep rows sorted?

I was also thinking about having three tables one for High, Medium and Low and then sort those by Creation Date.

Answer 1

Tables aren't "sorted"; you query data based on your criteria and add indexes to assist in finding the orderings you want.

(First lie: tables ARE sorted; they are stored in the order of the clustered index.)

But forget the concept of the "re-sorting" the data. Put your data in, and (with some indexing) let the database server do its job to return the data you want.

If you are not getting the data you want, then perhaps your query needs more help.

Answer 2

The problem is, every time I would like to pick 200 jobs with highest priority, and I don't want to resort the table. Is there a way to keep rows sorted?

Row order is irrelevant to a database, and it's not advisable to rely on non-deterministic calculation for sorting.

Assuming SQL Server 2000+, you can use this query instead:

  SELECT TOP 200 t.*
    FROM TABLE t
ORDER BY t.importance * (time passed since creation) DESC

Answer 3

The problem you are going to have here is performance with the operation as you describe it. The engine will calculate "Importance * (time passed since creation)" for every single record in the table. That happens whenever you have a column in a function.

An idea. For a larger table, the overhead of getting 600 rows and then getting the top 200 from those is much less than performing the calculation for every query.

The SQL is not correct as is (order by and unions), but the idea is valid.

SELECT TOP 200 ident
FROM
(
SELECT TOP 200 ident, (GETDATE() - creation_date)  * 1 AS calc_order
FROM jobs
WHERE priority = 1
ORDER BY creation_date
UNION ALL
SELECT TOP 200 ident, (GETDATE() - creation_date)  * 2 AS calc_order
FROM jobs
WHERE priority = 2
ORDER BY creation_date
UNION ALL
SELECT TOP 200 ident, (GETDATE() - creation_date)  * 3 AS calc_order
FROM jobs
WHERE priority = 3
ORDER BY creation_date
) x
ORDER BY
calc_order DESC