Hi again
I have found that using SELECT * from anytable where cond1=cond2 etc.. very fast.
It is when my select has 57 fields from 3 tables and a search criteria varaible that looks like this...
&SQLString="BETWEEN(num1,num2) AND INLIST(Filed,A,B,C,D) AND ALLTRIM(UPPER(STATE) = ALLTRIM(UPPER(STATE)) AND AND AND etc.." upto perhaps 20 criteria.

SELECT Field1,Field2 - Field57;
+WHERE t1.Key=t1.Key etc. etc.;
+&SQLString;
+etc... etc... INTO CURSOR MYCURSE. **(Pun Intended)**


Things slow down real quick! Even with just one BETWEEN() cond. used!!!

I have an index on everything too.

I have found that if I use small simple SELECTs things are fast, but when I add a decent criteria to it, things change. I realise rushmore is important and have checked to the best of my ability to see that I have max advantage.

Has Anyone else out there had to write an intensice dynamic search engine of this size.. I would guess that this must be tiny compared to what most of you have been involved in.

I am currently not using any select SQL's at the moment, I am using complex indexes and setting keys according to the useres selefctions for the search.

This is ok on one table bound to a grid but where I gain great speed I loose heaps of flexibility.

P.S
I can't select from the first cursor created inn the first SELECT. I had to SELECT into a tmp table instead of a cursor. Very slow, no index on first cursor etc... I wont bore you with the rest, I hope you get the gist.

This string gets to long for a filter to, even when I use eval() or & on a var.
I also tried splitting things into 2 cursors with separate SELECTS, selecting subset B form Cursor of SubSet A.


I really would like to use the SELECT statement, Can you help?
TIA