SQL processing

If I issue an SQL query and there is no index based on the key item to
the query but other indexes should narrow down the search (and I call that
info to help it), but the process is taking about 1 minute to come up with
the results. The actual query process is only about 10 seconds, but bring up
the query meter is taking forever.
Any suggestions?

Rusty Gallo
OGES Systems
ogallo@columbus.rr.com

David
Thu Jan 29 19:54:41 CST 2004

Rusty,

You can use sys( 3054, 11 ) to help see how VFP is optimizing the select.

If that doesn't shed light, post the select and the indexes of the table
here and we can help you look it over.

--
df - Microsoft MVP FoxPro http://www.geocities.com/df_foxpro

"Rusty G" <ogallo@columbus.rr.com> wrote in message
news:kIiSb.3514$Jf2.2577@fe1.columbus.rr.com...
> If I issue an SQL query and there is no index based on the key item to
> the query but other indexes should narrow down the search (and I call that
> info to help it), but the process is taking about 1 minute to come up with
> the results. The actual query process is only about 10 seconds, but bring
up
> the query meter is taking forever.
> Any suggestions?

Cindy
Fri Jan 30 08:01:15 CST 2004

In news: kIiSb.3514$Jf2.2577@fe1.columbus.rr.com,
Rusty G <ogallo@columbus.rr.com> wrote:
> If I issue an SQL query and there is no index based on the key
> item to the query but other indexes should narrow down the search
> (and I call that info to help it), but the process is taking about 1
> minute to come up with the results. The actual query process is only
> about 10 seconds, but bring up the query meter is taking forever.

Hi Rusty,

Don't forget, VFP's SQL engine will create temporary indexes the first time
you run your query, and these will be available for subsequent runs during
the same session, so your time may vary quite a bit between the first run
and any others.

Are you returning a lot of data? There may be some slowdown caused by a
network. You can test this issue by running the same query with data on the
local machine and then on the server. Close FoxPro between test runs to
clear the cache of temporary indexes.
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org www.cindywinegarden.com

Leemi
Fri Jan 30 14:53:32 CST 2004

Hi Rusty:

Along with the other suggestions, you need to make sure the query is
optimized. See these articles for tips on optimizing your SELECT-SQL
commands:

248608 INFO: SQL SELECT Optimization Levels and Performance
http://support.microsoft.com/?id=248608

156551 HOWTO: Use SYS(3054) to Optimize a Query
http://support.microsoft.com/?id=156551

I hope this helps.


This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP8 HAS ARRIVED!! --*
Read about all the new features of VFP8 here:
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
Purchase VFP8 here:
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retires Sept. 30th, 2003


> If I issue an SQL query and there is no index based on the key
> item to the query but other indexes should narrow down the search
> (and I call that info to help it), but the process is taking about 1
> minute to come up with the results. The actual query process is only
> about 10 seconds, but bring up the query meter is taking forever.

>Hi Rusty,

>Don't forget, VFP's SQL engine will create temporary indexes the first time
>you run your query, and these will be available for subsequent runs during
>the same session, so your time may vary quite a bit between the first run
>and any others.

>Are you returning a lot of data? There may be some slowdown caused by a
>network. You can test this issue by running the same query with data on the
>local machine and then on the server. Close FoxPro between test runs to
>clear the cache of temporary indexes.
--
>Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
>cindy.winegarden@mvps.org www.cindywinegarden.com