Cursor.FindSingleValue

[Ivan Smahin]

Hello Thomas,

Tuesday, December 29, 2009, 10:43:19 AM, you wrote:

> Hi,

 >> in. It looks like you do something like "select * from table" and than
 >> you are trying to apply another search criteria to the query result.
> Yes, somehow....

> ok, I will give a description of the problem.
> Its actually a problem of displaying the data.

> I'm using a grid to display the data, which has a virtual-display mode.
> This means, the records are not loaded all at once in the grid, but only when
> needed.

> First I create the cursor with "select * from table order by field1"
> Second only the records, which are currently visible, are loaded into the
> grid. The row-number of the grid is the cursor-position.
> So if the grid needs to display lets say rows 10000-10020 then it goes easy
> with cursor.position=10000 etc.

> This is very fast.

> The problem comes now, when I need the grid to scroll down to show a specific
> record.
> For example, in another form I did a query:
> "select * from table where name="flemming"
> This gives me one record.

> Now I want the grid to show this record, but I can only do this in scrolling
> to this spcific grid-row, which is the cursor.position of the first query.

So,  as  far  as I get it you have two cursors - one for displaying in
grid  and  it  looks  like all the records from some table (ordered by
some  column) and second one which is used to get some key (using that
key you are able to find appropriate position in the first cursor)

Speaking  of  a  single  table  (which both cursors are over) the most
simple and natural key is RecID field. So you may do following
select recID, * from t1; -- for first cursor
select recID from t1 where ...; -- for second cursor

then  you  may  scrolling  first cursor looking for an appropriate recID.
Or you may even try to call

vCursor1.put_RecID( RecID_Of_Cursor2 );
and then
vCursor1.get_Position();



> The only solution I found until now is iterating throug the cursor, until I
> found this record. Then I have the cursor.position and can jump in the grid to
> that row.
> But this is of course slow for a large amount of data (1 mio records).

> Regards,
> Tom



> Ivan Smahin schrieb:
>> Hello Thomas,
>> 
>> Monday, December 28, 2009, 10:23:11 PM, you wrote:
>> 
>> 
>>  >>     and then in cursor you can do binary search by that field values.
>> 
>>> what is "binary search by that field value"  ?
>> 
>> 
>> The best and most natural way is to query only records you really need
>> in. It looks like you do something like "select * from table" and than
>> you are trying to apply another search criteria to the query result.
>> 
>> But  you  should  do  something like "select f1, f2 from t1 where f3 =
>> 'value'"  or  something  like  this instead -  I mean you should express your
>> search criteria inside the query.
>> 
>> Another  way  is  creating  some temporary table as a query result.
>> It would be probably helpful in case of long running queries
>> with complex search criteria. So later you could search a records in
>> that temporary table as you are trying to do with cursor.
>> 
>> One more note. Such problems may be result of poor database design.
>> One of the popular example is denormalization... In short ...
>> Assume you are developing some db schema for address book and you
>> decide  to keep all properties in different tables linked all together
>> with some sort of link. So you will get something like People table,
>> Address  table  (linked  to  People  table)  and  so on... Seems to be
>> correct...  But  finally you see that the only query you will run is -
>> get all properties for all men/women.
>> 
>> In our case it would be something like
>> SELECT * FROM tblPerson JOIN tblAddresses JOIN tblPhones JOIN....
>> WHERE  fldGender = ...
>> 
>> But  in  this  case probably it would be better to have a single table
>> with all that properties - yes it would be some repeatable records but
>> your query might be running faster than n-join-query...
>> 
>> To be more specific you should describe your needs more detailed.
>> 




-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com