Cursor.FindSingleValue

[Thomas Flemming]

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.

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

-- 
/****************************************
**   Dipl.-Ing. Thomas Flemming
**   Software Development
**
**   Touratech AG
**   Auf dem Zimmermann 7-9
**   D-78078 Niedereschach
**
**   mail  tf at ttqv.com
**   fon   +49 (0) 7728 9279-206
**   fax   +49 (0) 7728 9279-29
**
**   http://www.ttqv.com
**   http://www.touratech.de
**
**   ... und immer dem Pfeil nach!
***************************************/