The LA Fox Developer Newsletter

March 2000

Hot Tip...

Optimizing SQL

by Ed Leafe

Question: The following Select clause is taking 10 to 15

seconds to execute. The Rushmore optimization is always

Partial. Why is it not Total?

SELECT RECNOO, DESCRIPTION, cargotype, commoditytype,;

cubicfeet, grossweight, revenuetons, wharfagechg;

FROM gltemDbf;

INTO ARRAY mResultArray;

WHERE DOCUMENTNBR == ‘123’ AND NOT DELETED()

Answer

First, you’ve used the ‘‘ operator, which is not Rushmore-

optimizable. If you need the sort of string comparison provided

by ‘==‘, you could use PADRO to pad the value being compared

so that it is the same length as the field.

Second, SQL constructions for logicals do not follow the same

rules as Xbase. You always need to include both sides of the

expression; otherwise, the results can be unpredicatable. In

Xbase, it is redundant to write something like:

IF DELETED() = .T.

since ‘IF DELETEDO’ is exactly the same. However, in SQL

code, you should always write a WHERE clause like that. In

your case, the WHERE should read:

WHERE DOCUMENTNBR = ‘123’;

AND DELETEDO = .F.

ADO Jumpstart (Con’t from page 6)

Underlying Value—I ndicates the current value in the data source.

This property corresponds most closely to the CurVal( ) function

in Visual FoxPro. To populate the Value property of each Field

object in the Fields collection, you need to invoke the Resync

method of the Recordset object. With a client-side cursor, this

property will return the same value as the OriginalValue prop-

erty, since the recordset may or may not have an active connec-

tion.

Type—Indicates the data type of the Field object. The value of

this property corresponds to a value contained in

DataTypeEnum. Examples of values in DataTypeEnum are

adBoolean, adlnteger, and adVarChar.

Defined Size—Specifies the size of the field containing a data

element in the data source. For example, in SQL Server, the

Country field in the Customers table of the Northwind database

is 15 characters long. Therefore, the DefinedSize property of the

Country Field object is 15.

(Con’t, page 9)

WHERE CAN I FIND...?

from the FoxDev TipsLetter #00-03

of the FoxPro Developers Network of San Diego

(Microsoft has rearranged the MSDN website again, so many of

the links we had have moved - and got longer!)

MSDN ON LINE:

There’s a ton of stuff here, look at the Magazines tab, and read

some of the regular columns.

<msdn.microsoft.com>

MSDN Library

<msdn.microsoft.com/library>

Look in Technical Articles | Visual Studio I Visual FoxPro

Three-Tier Apps:

MSDN Library: Building Three-Tier Client/Server Applications

with Visual FoxPro

MTS: Microsoft Transaction Server

MSDN Library: Microsoft Transaction Server for Visual FoxPro

Developers

ODBC:

MSDN Library: Using Visual FoxPro to Access Remote Data

VS Installer:

MSDN Library:

a.

Using Visual Studio Installer for VFP 6.0 Applications

b.

VFP 6.0 and VS Installer Tutorial

Windows General

<www.winfiies.com>

XML - What’s New in XML for Microsoft Windows 2000

<msdn.microsoft. com/voices/xm!.asp>

[Ed. Note: Nick Nikula and Dan Covill have agreed to keep this

list as a permanent item in the Tips letter. Contributions

solicited. Visit the website at http://www.elsoftware.com/J

NOLA

VFP DevCon

April 14-18, 2000

http://www. vfpdevcon. corn

“Laissez les bon tern ps rouler !!“

Page 8