>>>this is btw, one of the reasons that sometimes it is faster to
>>>"take apart" an SQL statement to several queries
>>>
>>>Arnon
>>
>>Arnon is very right on this. I have proved, to my surprise, that 2 SQLs run faster then 1 complex SQL. I do not know wether SQL in VFP is inefficient or what the cause is. But the result is undisputable -- 2 SQLs are faster then 1!!!!
>
>Do you mean a nested select: SELECT * from dba WHERE field IN (SELECT ...
>I originally had it as a nested select when I made this post. Wehn I unnseted it and just joined the tables (and made the index fields Integer rather than characcter - quite a job) performance increased about 5 times.
>
>As an asside, I'm convinced that the Integer helped things out. I am using Codebook which indexes using character fields. When I changed this to Integer, all of my querries ran faster. However, you cant set a view parameter blank on an integer field and expect to match all records. You have to set the view to select on LTRIM(STR(id)) and have an index on this. So, I traded execution speed on id searches for select join speed.
>
>-John Gore
Makes sense to me, Integers are set length fields while char are not. I just think Integers (nemeric) are handled much more efficiently in memory.
Tom