General information
Category:
Coding, syntax & commands
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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only