> The fact that selecting on the full, wide table is slow, while > instantaneous extraction of the single column table occurs points to > an issue with the structure of the table and its indexes.
Yes, this is something I was suspicious about but have selected data out of original table into a clean new table in an effort to avoid any table structure problems. > Do you have a tag on DELETED()? No, only index is on OrdNo Ascending. > And are your running your tests all local on the machine that has the > DBFs on it, or over a network? All tests running over network in native VFP. > Is the IIS/ADO/SQL solution also reading from a local DBF or over a > network? Over the network, when data local speed is respectable, say 4 seconds. I have now established that with fifty fields it is ok and takes less than a second but with sixty fields it takes around a minute. Nick > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Ted Roche > Sent: 06 September 2008 19:23 > To: [email protected] > Subject: Re: SQL > not optimised > > On Sat, Sep 6, 2008 at 1:03 PM, NickC <[EMAIL PROTECTED]> wrote: > > > > I like your theory but unfortunately still doesn't help speed this up. > > According to SYS(3054) it is being optimised but even so still takes > almost > > a minute to extract one record. > > > > The fact that selecting on the full, wide table is slow, while > instantaneous extraction of the single column table occurs points to > an issue with the structure of the table and its indexes. > > Do you have a tag on DELETED()? > > Your test may also tilt the playing field a little: when you create > the test table, you've cached all the file blocks in memory. You don't > show your work, but I presume you create an index on the single > column. Do you also create one on DELETED()? > > And are your running your tests all local on the machine that has the > DBFs on it, or over a network? > > Is the IIS/ADO/SQL solution also reading from a local DBF or over a > network? > > > -- > Ted Roche > Ted Roche & Associates, LLC > http://www.tedroche.com > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/!&[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
