| Author |
Topic |
|
rmao
Starting Member
7 Posts |
 Posted - 2002-06-18 : 16:09:28
|
| I have one table which holds the data for several objects. Each object is identified by an instance number, and each record in the data table is identified by the instance and record number. So the primary key in the data table is the tuple (RecordNumber, Instance). When I want to get the data for a particular object:SELECT * from DataTable Where Instance = 1I want to be able to index into the data for a particular object. Say I want rows 100-200 for instance 2. I can select all the data for instance 2, then use a cursor to scroll to the row in question. Using a dynamic cursor, the query performance is excellent, but it takes several seconds to scroll to a high row number (ie if I wanted row 1000000 to 1000100). Static and keyset cursors take a really long time to execute the query when the result set is large. The DB is SQL Server so mixed cursors are not an option.I've tried using a combination of the TOP and ORDER BY statements to get only the result set I'm interested in, so that scrolling is not required, however the performance of this query is even worse.SELECT * FROM (SELECT TOP 100 x.RecordNumber, x.Instance, x.Timestamp,x.Data, FROM (SELECT TOP 200000 RecordNumber,Instance, Timestamp, Data, FROM DataTable WHEREInstance = 2 Order by RecordNumber Desc) AS x) AS z ORDER BYz.RecordNumberIt seems like a simple enough problem. Can anyone help? |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-18 : 16:28:13
|
| what's wrong with:SELECT * from DataTable Where Instance = 1 and RecordNumber between {x} and {y}setBasedIsTheTruepath<O> |
 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-06-18 : 16:28:57
|
I don't know maybe I'm missing something but here's my ideas for this: Are you saying you want listing of Instance = 2 where RecordNumber BETWEEN 100 AND 200 ?? or just the recordnumber returned? or say you get XXXX records back and you want to only return the ones from 100 to 200 ... why not use a ranking type query on the record number since you are sorting this and so SELECT dt.RecordNumber, RANK = (SELECT COUNT(*) + 1 FROM DataTable WHERE Instance = 2 AND RecordNumber < dt.RecordNumber) FROM DataTable AS dt WHERE dt.Instance = 2 AND (SELECT COUNT(*) + 1 FROM DataTable WHERE Instance = 2 AND RecordNumber < dt.RecordNumber) BETWEEN 100 AND 200 ?? I'm sure something like that should give you an idea of maybe how to do this... or maybe i'm just crazy  My first sniping! ugh it hurts ...  Edited by - onamuji on 06/18/2002 16:29:44 |
|
|
|
rmao
Starting Member
7 Posts |
Posted - 2002-06-18 : 17:16:31
|
I want records 100-200 of the result set, without the assumption that the record number will correspond. Assume that the record number is not contiguous.quote:
what's wrong with:SELECT * from DataTable Where Instance = 1 and RecordNumber between {x} and {y}setBasedIsTheTruepath<O>
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-18 : 17:37:23
|
| The problem is that you want to get a subset of data based on its position in a set, not based on its value(s). SQL Server and all relational databases manipulate sets based on value. If you want the positioned data, cursors are the most efficient way to get them. Oracle's little tricks like ROWNUM only mean that it processes everything internally as cursors.Why not use FAST_FORWARD on your cursor, it'll be faster. I'd recommend against having an updateable cursor; it's very easy to update a single row with a stored procedure and is more secure. If you absolutely have to scroll back, then use READ_ONLY. |
|
|
|
rmao
Starting Member
7 Posts |
Posted - 2002-06-18 : 19:11:17
|
Can you explain how to use FAST_FORWARD? I'm trying to implement this with an MSDE database engine. Is fast forward supported? (Documentation I've found seems to only refer to fast forward with SQL 2000, and my understanding is that MSDE is SQL Server 7.0 compatible).It's true, I don't need to scroll backwards, nor do I need to update any rows, which is why I figure this shouldn't be too difficult to get reasonable performance.quote:
The problem is that you want to get a subset of data based on its position in a set, not based on its value(s). SQL Server and all relational databases manipulate sets based on value. If you want the positioned data, cursors are the most efficient way to get them. Oracle's little tricks like ROWNUM only mean that it processes everything internally as cursors.Why not use FAST_FORWARD on your cursor, it'll be faster. I'd recommend against having an updateable cursor; it's very easy to update a single row with a stored procedure and is more secure. If you absolutely have to scroll back, then use READ_ONLY.
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-18 : 19:16:00
|
| If FAST_FORWARD is not valid, then use FORWARD_ONLY and READ_ONLY:DECLARE myCursor CURSOR FAST_FORWARD FOR SELECT * FROM myTableor DECLARE myCursor CURSOR FORWARD_ONLY, READ_ONLY FOR SELECT * FROM myTableFAST_FORWARD is a little more optimized. |
|
|
|
rmao
Starting Member
7 Posts |
Posted - 2002-06-18 : 19:32:31
|
Are you saying I should use a forward only cursor and scroll through the records one by one in a loop? Would that be faster than using a dynamic cursor and using SQLFetchScroll to scroll to the desired row?quote:
If FAST_FORWARD is not valid, then use FORWARD_ONLY and READ_ONLY:DECLARE myCursor CURSOR FAST_FORWARD FOR SELECT * FROM myTableor DECLARE myCursor CURSOR FORWARD_ONLY, READ_ONLY FOR SELECT * FROM myTableFAST_FORWARD is a little more optimized.
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-18 : 19:41:31
|
| If you have Books Online, it describes each cursor setting and their features/benefits under DECLARE CURSOR (Transact-SQL). It'll be easier if you read up on them there. |
|
|
|
rmao
Starting Member
7 Posts |
Posted - 2002-06-18 : 19:54:20
|
I don't have books online, but thanks anyway:)Is it something you have to pay for?quote:
If you have Books Online, it describes each cursor setting and their features/benefits under DECLARE CURSOR (Transact-SQL). It'll be easier if you read up on them there.
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
Data table query performance