I think I recall posting this before but I can't find it. I have to =
implement a select top (x) records feature. I remember top(x) is not =
supported and the following ideas are off the top of my head. VS2008, =
EDB 2.03 with all data being called via stored procs:

 1.. create a temp table with the fields being returned to the data set
 2.. create a cursor with the select to the database
 3.. populate the temp table while counting the records and stop when =
record # x is reached
Speed of record retrieval is my goal.=20
 a.. Are these steps about right or is there a more efficient way =
(efficent=3Dspeed for the records to be returned to the app I mean?=20
 b.. Am I better off creating a temp table, or a memory resident temp =
table?
 c.. Can I created a memory table with a regular db connection or do I =
need to create a memory db, then the temp table?
 d.. What is the life of temp tables and how do I destroy them if they =
contain the rows being returned? The app opens a db connection and keeps =
it open, so releasing temp tables when connections are closed won't work
 e.. Any thoughts with any best practices should I be sticking with for =
top(x) functionality?

Thanks


jr

James


<<Are these steps about right or is there a more efficient way (efficent=speed for the records to be returned to the app I mean?>>
I'd suggest having a look at RANGE first eg

select * from companies range 1 to 2

I think that does what you want without any other processing

But to go over your other questions as well

<<Am I better off creating a temp table, or a memory resident temp table?>>
I seem to remember from questions I asked Tim that he prefers temp tables. I prefer memory tables (see below) but there isn't much difference.

<<Can I created a memory table with a regular db connection or do I need to create a memory db, then the temp table?>>
Not sure since I use the standard VCL version not ODBC (which is I think is what you're using). I'm guessing that its almost the same in both cases. Its easier (slightly) to create a temp table than a memory table. In both cases you need to use the same session, with a temp table it shares the database with the other tables, with a memory table its a separate database so you have to get the references right. If you want I can post examples of populating a memory table but using standard VCL rather than ODBC.

I think (but I'm not sure) that temp tables are created on app's PC in fileserver mode and the server in c/s mode.

You should be able to create either type of table without having to create the other.

<<What is the life of temp tables and how do I destroy them if they contain the rows being returned? The app opens a db connection and keeps it open, so releasing temp tables when connections are closed won't work>>
This is the bit why I prefer memory tables. I can't remember if its when the session, engine or app closes. They are designed to be cleaned up automatically (not sure if Tim has sorted it so that .OLDs are zapped as well) but if the app crashes they are left behind.

<<Any thoughts with any best practices should I be sticking with for top(x) functionality?>>
Yup - use RANGE <vbg>

Roy Lambert [Team Elevate]

Thanks Roy!!


Range works perfectly for what I need....... also means I don't have to =
deal with any temp tables now....fast to run, fast to write-- has =
everything!


jr