reason to use SQL select 2

[samsnead]

We had an old program that programmer wrote using a scan statement - when processing a report took about 4 minutes to get to last page of report if you previewed report.

Changed the scan to a sum statement - time went down to about 35 seconds

Changed the sum to a select sum(amount)...... etc - time went down to a few seconds. Just thought I would post this for new programmers.

 

[Stella740pl]

samsnead,

It doesn't always work like you describe. It depends on many factors, including having indices on all the expressions you use for optimization, etc.

I once did nearly the opposite, replaced two SELECT-SQL statements with one SCAN. Yes, it did involve more code, and yes, SELECTs did look neater - but I improved performance drastically with that.

 

[Mike Lewis]

Samsnead,

I agree with Stella. It's simply not true to say that SELECT will necessarily be faster than a SCAN loop.

When it comes to accessing data, by the far the most important factor is the presence or absence of suitable indexes. Which commands you has a much smaller impact.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[JimWWinter]

Mike,

While the performance of SQL Selects are certainly affected by the available indexes, I don't believe they have much, if any, effect on the performance of a SCAN loop, other than facilitating processes within the loop. Please correct me if I'm wrong on this.

Regards,
Jim

 

[Mike Lewis]

Jim,

Please correct me if I'm wrong on this.

OK, I will <g>.

Here's what the Help says about the SCAN command:

Rushmore optimizes a query created with SCAN ... FOR if lExpression1 is an optimizable expression. For best performance, use an optimizable expression in the FOR clause.

In other words, if the SCAN has a FOR clause, and if the expression in that clause is Rushmore-optimisable (i.e. there are appropriate indexes in place), then the loop will be optimised.

If it hasn't got a FOR clause, then the issue doesn't arise.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

Mike, I know that SCAN..FOR is optimizable. What I was thinking of, what if I need to scan through all the records, can I optimize it somehow? Say, put it as SCAN FOR .t. and create an index on .t.? Or another meaningless expression? Will it be optimized or would it create an unnecessary overhead?

 

[Mike Lewis]

Stella,

But if you need to scan all the records, there is nothing to optimise. The whole point of optimisation (in this context) is to obtain a subset of records in the most efficient way.

That said, I suppose you might want to know the most efficient way of doing something with all the records from a table (not necessarily all the fields), such as copying them to a new cursor. Is it better to scan the source table, copying each record in turn, or to do a SELECT ... INTO?

Intuitively, I feel the SELECT would be faster, but I wouldn't put money on it. In any case, it's not a Rushmore issue -- it just reflects how VFP interprests the code.

Hope I'm making sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

But if you need to scan all the records, there is nothing to optimise. The whole point of optimisation (in this context) is to obtain a subset of records in the most efficient way.
Makes sense.

That said, I suppose you might want to know the most efficient way of doing something with all the records from a table (not necessarily all the fields), such as copying them to a new cursor.
I meant a more complex case than can be processed with a single SELECT, like going through all the records and doing something on the way - can't think now of an example.

But I got the idea. Thanks.

 

[Mike Lewis]

Stella,

I know what you mean, but I can't think of a good example either.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

One thing you can do to speed up SCAN is SET ORDER TO. That is, run it with no index order set, so records are processed in their order in the file.

Tamar

 

[Mike Lewis]

Tamar,

One thing you can do to speed up SCAN is SET ORDER TO.

The same is true of SET FILTER. (Come to think of it, it was you who told me that.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[samsnead]

Hi Mike and Tamar - thanks for all the tips regarding processing of records with scans, selects etc. I thought I was just passing on something interesting I had found - you guys turned it into a seminar! I think you deserve stars.

P.s. - Mike - Monty is playing well in the US open (if you are a golfer)