FoxPro Help and Support - using Select SQL syntax and performance issues

Author

using Select SQL syntax and performance issues

ddobier

2005-12-30, 11:24 am

Hello. I'm new to foxpro but have a VB and sql server background. I'm trying
to rewrite some existing foxpro programs that are very inefficient. What I'm
noticing is that the majority of my sql statements are very slow to process.
The table I'm either updating or searching through has approx 150000 records.
Here's what I'm seeing:

update cust set is_new = .T. - This statement takes about 4 minutes to
execute. If I change it to use the replace function it processes immediately.
I would think it would to the same for the update statement as I'm updating
all records. Any thoughts as to why this is taking so long?

select field1, field2, field3, field4 into cursor1 readwrite - this takes
1.5 minutes to process 150000 records. I would think this would be almost
instantaneous as it's displaying all records. What gives? I moved the data
files locally and see the same sluggish performance.

To summerize, any sql command I give is very slow. Is this just how Foxpro
is with sql statements? On some other SELECTS that are slow I checked and
it's using the indexes for the joins. Is there some configuration option I
must set like packet size, batch size, ....?

Thanks for any assistance you may provide.

Dan Freeman

2005-12-30, 11:24 am

Foxpro's SQL implementation works best with subsets.

If you just want to move an entire table to a temp table, use the COPY
command.

With a table of any size (and without joins), REPLACE will almost always be
faster than UPDATE.

Generally, slow queries are the result of a lack of Rushmore optimization.
See the help file for more information.

Dan

ddobier wrote:
> Hello. I'm new to foxpro but have a VB and sql server background. I'm
> trying to rewrite some existing foxpro programs that are very
> inefficient. What I'm noticing is that the majority of my sql
> statements are very slow to process. The table I'm either updating or
> searching through has approx 150000 records. Here's what I'm seeing:
>
> update cust set is_new = .T. - This statement takes about 4 minutes to
> execute. If I change it to use the replace function it processes
> immediately. I would think it would to the same for the update
> statement as I'm updating all records. Any thoughts as to why this is
> taking so long?
>
> select field1, field2, field3, field4 into cursor1 readwrite - this
> takes
> 1.5 minutes to process 150000 records. I would think this would be
> almost instantaneous as it's displaying all records. What gives? I
> moved the data files locally and see the same sluggish performance.
>
> To summerize, any sql command I give is very slow. Is this just how
> Foxpro is with sql statements? On some other SELECTS that are slow I
> checked and it's using the indexes for the joins. Is there some
> configuration option I must set like packet size, batch size, ....?
>
> Thanks for any assistance you may provide.

Lee Mitchell

2005-12-30, 11:24 am

Hi ddobier:

The first step is to make sure the SELECT-SQL commands are optimized. Use
the SYS(3054) function to accomplish this. See:

248608 How to determine SELECT-SQL optimization levels to affect
performance in Visual FoxPro 6.0 and later versions
http://support.microsoft.com/defaul...kb;EN-US;248608
156551 How To Use SYS(3054) to Optimize a Query
http://support.microsoft.com/defaul...kb;EN-US;156551

Secondly, you also want to optimize memory usage in VFP when it handles
data. Use the SYS(3050) function for this task. See:

176483 Computers that have lots of RAM installed seem to more slowly
process data in Visual FoxPro
http://support.microsoft.com/defaul...kb;EN-US;176483

You may need to try both higher and lower numbers for the memory amount
until you find the optimal settings.

I hope this helps.

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

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 HAS ARRIVED!! --*
Read about all the new features of VFP9 here:
http://msdn.microsoft.com/vfoxpro/

*--Purchase VFP 9.0 here:
http://www.microsoft.com/PRODUCTS/i...cid=54787e64-52
69-4500-8bf2-3f06689f4ab3&type=ovr

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/gp/lifeselectindex
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

>Hello. I'm new to foxpro but have a VB and sql server background. I'm
trying
>to rewrite some existing foxpro programs that are very inefficient. What
I'm
>noticing is that the majority of my sql statements are very slow to
process.
>The table I'm either updating or searching through has approx 150000
records.
>Here's what I'm seeing:

>update cust set is_new = .T. - This statement takes about 4 minutes to
>execute. If I change it to use the replace function it processes
immediately.
>I would think it would to the same for the update statement as I'm
updating
>all records. Any thoughts as to why this is taking so long?

>select field1, field2, field3, field4 into cursor1 readwrite - this takes
>1.5 minutes to process 150000 records. I would think this would be almost
>instantaneous as it's displaying all records. What gives? I moved the data
>files locally and see the same sluggish performance.

>To summerize, any sql command I give is very slow. Is this just how Foxpro
>is with sql statements? On some other SELECTS that are slow I checked and
>it's using the indexes for the joins. Is there some configuration option I
>must set like packet size, batch size, ....?

>Thanks for any assistance you may provide.

ddobier

2005-12-30, 1:24 pm

Can I copy the data from one table into a cursor with the copy command? It
looks like from the help menu that it has to be to a file or array.

Thanks.

"Dan Freeman" wrote:

> Foxpro's SQL implementation works best with subsets.
>
> If you just want to move an entire table to a temp table, use the COPY
> command.
>
> With a table of any size (and without joins), REPLACE will almost always be
> faster than UPDATE.
>
> Generally, slow queries are the result of a lack of Rushmore optimization.
> See the help file for more information.
>
> Dan
>
>
>
>
>
> ddobier wrote:
>
>
>

Lee Mitchell

2005-12-30, 1:24 pm

Hi ddobier:

You could use the CREATE CURSOR command to make the cursor and then the
APPEND FROM command to populate the empty cursor.

I hope this helps.

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

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 HAS ARRIVED!! --*
Read about all the new features of VFP9 here:
http://msdn.microsoft.com/vfoxpro/

*--Purchase VFP 9.0 here:
http://www.microsoft.com/PRODUCTS/i...cid=54787e64-52
69-4500-8bf2-3f06689f4ab3&type=ovr

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/gp/lifeselectindex
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

>Can I copy the data from one table into a cursor with the copy command?
It
>looks like from the help menu that it has to be to a file or array.

>Thanks.

>"Dan Freeman" wrote:

> Foxpro's SQL implementation works best with subsets.
>
> If you just want to move an entire table to a temp table, use the COPY
> command.
>
> With a table of any size (and without joins), REPLACE will almost always
be
> faster than UPDATE.
>
> Generally, slow queries are the result of a lack of Rushmore optimization.
> See the help file for more information.
>
> Dan
>
>
>
>
>
> ddobier wrote:
>
>
>

Paul Pedersen

2006-01-02, 3:24 am

FWIW, I have noticed that FoxPro native commands eg REPLACE often vastly
outperform SQL commands eg UPDATE. I suspect it has something to do with
locking individual records during the UPDATE, while the entire table gets
header locked for REPLACE.

You might get even better performance using
REPLACE is_new WITH .T. FOR NOT is_new
Try it both ways, see which works better.

The READWRITE might be slow because it forces the result set to be written
to disk, whereas a simple select into cursor might be held in memory, or
even just as a query, unevaluated until required. The same thing could
happen if you use NOFILTER. Again, I'm guessing.

FoxPro actually has the reputation, I think deserved, of being very fast
handling data. But like I said, native commands probably outperform SQL
commands.

"ddobier" <ddobier@discussions.microsoft.com> wrote in message
news:55093046-C07D-4A97-8A3E- 462148CC130F@microso
ft.com...
> Hello. I'm new to foxpro but have a VB and sql server background. I'm
> trying
> to rewrite some existing foxpro programs that are very inefficient. What
> I'm
> noticing is that the majority of my sql statements are very slow to
> process.
> The table I'm either updating or searching through has approx 150000
> records.
> Here's what I'm seeing:
>
> update cust set is_new = .T. - This statement takes about 4 minutes to
> execute. If I change it to use the replace function it processes
> immediately.
> I would think it would to the same for the update statement as I'm
> updating
> all records. Any thoughts as to why this is taking so long?
>
> select field1, field2, field3, field4 into cursor1 readwrite - this takes
> 1.5 minutes to process 150000 records. I would think this would be almost
> instantaneous as it's displaying all records. What gives? I moved the data
> files locally and see the same sluggish performance.
>
> To summerize, any sql command I give is very slow. Is this just how Foxpro
> is with sql statements? On some other SELECTS that are slow I checked and
> it's using the indexes for the joins. Is there some configuration option I
> must set like packet size, batch size, ....?
>
> Thanks for any assistance you may provide.

Olaf Doschke

2006-01-04, 7:24 am

Hi ddobier and Paul,

> FWIW, I have noticed that FoxPro native commands eg REPLACE often vastly outperform SQL commands eg UPDATE. I suspect it has
> something to do with locking individual records during the UPDATE, while the entire table gets header locked for REPLACE.

While the different locking may have an impact on speed, a REPLACE without the
ALL scope only updates a single record, NEXT 1 is it's default scope. On the other
side, UPDATE without a WHERE clause updates each record.

I think you really just wanted to set a single new customer record to is_new=.T. with
the Update, right? You should use the default value .T. in that is_new field, then you'd
even only need to set this .f. whenever the customer is not considered new anymore.

Bye, Olaf.

Alan Sheffield

2006-01-04, 11:24 am

To get good peformance from SQL in VFP you need to have good supporting
indexes. MAke sure the column in your where clauses are indexed and indexed
the ways you use them. For example if you have "where upper(cust_num) =
myVar" make sure that you have an index on "upper(cust_num)".

For your update issue:
It will depend on how you wrote the replace.
update cust set is_new = .T. - updates all records in the table.
repalce set_is_new with .t. in cust - update the current record.
repalce ALL set_is_new with .t. in cust - updates all records
repalce set_is_new with .t. in cust for <some expression> - updates the
records that match the expression.

When you were selecting "select field1, field2, field3, field4 into cursor1
readwrite" is there a reason your transfering the entire table to a cursor?
Depending on your goals there is probably a better option that moving the
entire set to a cursor.

Alan

"ddobier" <ddobier@discussions.microsoft.com> wrote in message
news:55093046-C07D-4A97-8A3E- 462148CC130F@microso
ft.com...
> Hello. I'm new to foxpro but have a VB and sql server background. I'm
> trying
> to rewrite some existing foxpro programs that are very inefficient. What
> I'm
> noticing is that the majority of my sql statements are very slow to
> process.
> The table I'm either updating or searching through has approx 150000
> records.
> Here's what I'm seeing:
>
> update cust set is_new = .T. - This statement takes about 4 minutes to
> execute. If I change it to use the replace function it processes
> immediately.
> I would think it would to the same for the update statement as I'm
> updating
> all records. Any thoughts as to why this is taking so long?
>
> select field1, field2, field3, field4 into cursor1 readwrite - this takes
> 1.5 minutes to process 150000 records. I would think this would be almost
> instantaneous as it's displaying all records. What gives? I moved the data
> files locally and see the same sluggish performance.
>
> To summerize, any sql command I give is very slow. Is this just how Foxpro
> is with sql statements? On some other SELECTS that are slow I checked and
> it's using the indexes for the joins. Is there some configuration option I
> must set like packet size, batch size, ....?
>
> Thanks for any assistance you may provide.
>

Sponsored Links