One other thing worth a mention is that each record has 200 fields and is almost 3k per record.
Now if I SELECT OrderNo from Orders INTO TABLE OrdPOOnly Then select from there all is fine and works at a fast speed suggesting that the index is than being used. Problem is that this OrderPOnumOnly is again taking around a minute to extract. Nick > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of NickC > Sent: 06 September 2008 12:59 > To: 'ProFox Email List' > Subject: RE: SQL > not optimised > > Thanks all for the suggestions. > > I obviously didn't explain fully. This is an ASP webpage running on IIS > which uses ADO to retrieve data from FoxPro tables. The Fox tables are > shared with thirty year old applications, so no chance of making any > substantial modifications to the table structures. > > Have tested using just plain VFP but encounter the same problem so it is > not > an ADO only issue. > > Stephen: > OrderNo is 25 characters. > > Paul: > Unfortunately "select OrderNo from orders order by OrderNo ASC" still > takes > about a minute to run, even though there is an index on OrderNo. Also the > tables change regularly throughout the day so even if I could find a way > to > create some persistence this one minute retrieval would still be happening > frequently. > > Nick > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf > > Of William Sanders / EFG > > Sent: 05 September 2008 00:12 > > To: profox@leafe.com > > Subject: Re: SQL > not optimised > > > > Yikes - what a muddle. > > > > I'll take a stab at it. > > > > Assume the data is in VFP on a web server, running IIS, and IIS is using > > ASP with an ado connector to the vfp table. > > > > The iUsr account on the server machine doesn't have rights to the index > > file, as some one else reindexed the file , and the rights got mangled. > > > > If this query runs inside the asp code, I'd be worried, as it needs to > > make an intermediate file for the TOP 1 clause. > > > > Explain a wee bit more about the environment, please? I'm good at > Triage, > > but without some more info, I feel like the handle on my oar was sheared > > off. > > > > > > -- > > William Sanders / efGroup > > > > > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com 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/!&!AAAAAAAAAAAYAAAAAAAAAOO2KhH5xw1Oq9ETMzh8UaqijQAAEAAAAD/ZqA077jROsLUmIE/[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.
