slow query thousands of records.

[Steve]

I have a query that returns raw tick data from a table. Unfortunately after
even a few days there are hundreds of thousands of rows so the following
query is not efficient.

SELECT * FROM RAWTICKDATA WHERE Status = 'I' AND ContractCode = ? AND
RawTickID = (SELECT Max(RawTickID) FROM RAWTICKDATA WHERE Status = 'I' AND
ContractCode = ? AND PRICE =(SELECT Min(Price) FROM RAWTICKDATA WHERE Status
= 'I' AND ContractCode = ?))

The most obvious solution then is to get all tick data with status ='I'
(Imported) for a contract, process it and then move it to another table for
archiving. I am faced with a problem however: After selecting all data for a
contract with status='I' the application was updating these records to a new
status of 'P' (processed). Unfortunately another application is continuning
to feed in live data and so it is possible that we will inadvertantly update
unprocessed data to 'P'.

Question: Is it possible to select all records with status 'I' (from above
query) and update their status to 'P' in one sequence?

I am not a programmer, but if this is possible I should be able to implement
the query.

Many thanks.
Steve

[Erland Sommarskog]

Steve ([e-mail]) writes:

I have a query that returns raw tick data from a table. Unfortunately
after even a few days there are hundreds of thousands of rows so the
following query is not efficient.

SELECT * FROM RAWTICKDATA WHERE Status = 'I' AND ContractCode = ? AND
RawTickID = (SELECT Max(RawTickID) FROM RAWTICKDATA WHERE Status = 'I' AND
ContractCode = ? AND PRICE =(SELECT Min(Price) FROM RAWTICKDATA WHERE
Status = 'I' AND ContractCode = ?))

The most obvious solution then is to get all tick data with status ='I'
(Imported) for a contract, process it and then move it to another table
for archiving. I am faced with a problem however: After selecting all
data for a contract with status='I' the application was updating these
records to a new status of 'P' (processed). Unfortunately another
application is continuning to feed in live data and so it is possible
that we will inadvertantly update unprocessed data to 'P'.

Before trying to rearrange the data flow, it could be worth to see if
you can speed up the query.

I assumed that RawTickID is the primary key of the table. For this
query, it would probably be best to have the clustered index on
ConstractCode. (But there may be other queries which this is not good.)

Here is also rewrite which you can try together with a clustered index on
ContractCode:

SELECT *
FROM RAWTICKDATA a
JOIN (SELECT maxid = MAX(b.RawTickId)
FROM RAWTICKDATA b
JOIN (SELECT price = MIN(c.Price)
FROM RAWTICKDATA c
WHERE c.Status = 'I'
AND c.ContractCode = ?) AS d
ON b.price = d.price
WHERE c.Status = 'I'
AND c.ContractCode = ?) AS e
ON a.RawTickId = e.maxid
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve]

Hi Erland,

I tried your query but it gives me the following error. "The column prefix
"c" does not match with a table name or alias name used in the query.

Just out of interest, how many rows can be stored in sql2000. Is it a good
medium for storage of tickdata or am I better off archiving to something
else?

Steve
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Steve ([e-mail]) writes:

I have a query that returns raw tick data from a table. Unfortunately
after even a few days there are hundreds of thousands of rows so the
following query is not efficient.

SELECT * FROM RAWTICKDATA WHERE Status = 'I' AND ContractCode = ? AND
RawTickID = (SELECT Max(RawTickID) FROM RAWTICKDATA WHERE Status = 'I' AND ContractCode = ? AND PRICE =(SELECT Min(Price) FROM RAWTICKDATA WHERE
Status = 'I' AND ContractCode = ?))

The most obvious solution then is to get all tick data with status ='I'
(Imported) for a contract, process it and then move it to another table
for archiving. I am faced with a problem however: After selecting all
data for a contract with status='I' the application was updating these
records to a new status of 'P' (processed). Unfortunately another
application is continuning to feed in live data and so it is possible
that we will inadvertantly update unprocessed data to 'P'.

Before trying to rearrange the data flow, it could be worth to see if
you can speed up the query.

I assumed that RawTickID is the primary key of the table. For this
query, it would probably be best to have the clustered index on
ConstractCode. (But there may be other queries which this is not good.)

Here is also rewrite which you can try together with a clustered index on
ContractCode:

SELECT *
FROM RAWTICKDATA a
JOIN (SELECT maxid = MAX(b.RawTickId)
FROM RAWTICKDATA b
JOIN (SELECT price = MIN(c.Price)
FROM RAWTICKDATA c
WHERE c.Status = 'I'
AND c.ContractCode = ?) AS d
ON b.price = d.price
WHERE c.Status = 'I'
AND c.ContractCode = ?) AS e
ON a.RawTickId = e.maxid
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Steve ([e-mail]) writes:

I tried your query but it gives me the following error. "The column prefix
"c" does not match with a table name or alias name used in the query.
The usual recommendation is to provide CREATE TABLE statements together
with sample data as INSERT statements. That makes it possible to post
a tested solution.

When this is missing, it is common to post just an example, and the
person who is having the problem is assumed to be able to cope with
simple syntax errors. Yeah, I know you said that you are not a programmer,
but there is some basic idea in these newsgroups that you get help to
help yourself. After all, suggestions here need to be tested and integrated.

For this case, try replacing the offending c with b.
Just out of interest, how many rows can be stored in sql2000. Is it a good
medium for storage of tickdata or am I better off archiving to something
else?

SQL Server is an enterprise DBMS, so you would have to get a really huge
amount of data to push the limits. The maximum size for a database is
1,048,516 TB (except in MSDE where it is 2GB.) There is no particular max
number for rows in a table.

In Books Online, SQL Server Architecture, Implementation Details there
is a topic on maximum specifications.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp