one of the things that we have learned over here at RSA (See Foxpro Advisor May

1999 issue) is that having multiple Rushmore optimizable expressions in the

WHERE from the same table can wreak havoc on performance (local or across the

LAN). I would have three suggestions for you:

1. Move all but one of the qualifications from the Staff table to the HAVING

clause (leave the one that will narrow the results down the most in the WHERE

clause)

2. Always set values of This.Rpt1 and This.Rpt2 and change the <= to always use

This.Rpt2 and the >= This.Rpt1 so that in the case of oCEOjb.ByTarget, you can

set this.Rpt1 = {} (thus accomplishing the same as the extra filter condition on

# {})

3. Use the SQL BETWEEN for your date range comparision

Wolfe Stephen S Civ GS-11 <Stephen.Wolfe@macdill.af.mil> on 08/17/99 08:24:02 AM

Please respond to profox@leafe.com

To: Multiple recipients of ProFox <profox@leafe.com>

cc: (bcc: Mary Hintermeier/RSA)

Subject: VFP6: Parametric SQL Question

SQL experts:

I need to tighten up my SQL (I will do some studying toward this

end, too, however, I need to deliver a 'cross the product line' query COB

today. I could cut and paste a bunch of the following SQL code, but I

rather do it smartly and learn something in the process.

To that end I'll expose some of my raggedy ol' SQL code for your

inspection and fruitful critique.

Code:

IF oCEObj.ByTarget

SELECT Staff.*, Lifesupp.ssan, Lifesupp.staffid,Lifesupp.staffidc,;

Lifesupp.pals_date, Lifesupp.pals_exp,Lifesupp.pals_type,;

Lifesupp.pals_insts, Lifesupp.pals_sched;

FROM ce!Staff ;

INNER JOIN ce!Lifesupp ;

ON Staff.ssan = Lifesupp.ssan;

WHERE Staff.hosp = ?OCEobj.hosp;

AND Staff.Squadron = ?THIS.Squadron;

AND Staff.Off_Sym = ?THIS.OfficeSymbol;

AND Lifesupp.pals_exp <= ?THIS.RPTT1;

AND Lifesupp.pals_exp # {};

ORDER BY Staff.Squadron, Staff.Off_Sym,Staff.last_name;

INTO CURSOR palquery

ELSE

SELECT Staff.*, Lifesupp.ssan, Lifesupp.staffid,Lifesupp.staffidc,;

Lifesupp.pals_date, Lifesupp.pals_exp,Lifesupp.pals_type,;

Lifesupp.pals_insts, Lifesupp.pals_sched;

FROM ce!Staff ;

INNER JOIN ce!Lifesupp ;

ON Staff.ssan = Lifesupp.ssan;

WHERE Staff.hosp = ?OCEobj.hosp;

AND Staff.Squadron = ?THIS.Squadron;

AND Staff.Off_Sym = ?THIS.OfficeSymbol;

AND Lifesupp.pals_exp >= ?THIS.RPTT1;

AND Lifesupp.pals_exp <= ?THIS.RPTT2;

ORDER BY Staff.Squadron, Staff.Off_Sym,Staff.last_name;

INTO CURSOR palquery

ENDIF

SELECT palquery

How can I optimize the above? Note one select does a to 'date'

query while the other does a between date query.

The 'cross product lines' portion of this question comes into play

where I want to get more than just the PALS expiration date data. Look in

the WHERE clause and see the element comparison on [AND Lifesupp.pals_exp <=

?THIS.RPTT1;]; can I parametrically substitute or build additional compares

for the BLS, ACLS, ATLS, and NRP products? All of the expiration date

elements have been named <product_name>_exp.

Will I have to build my own SQL query command and execute it like a

macro?

TIA,

Stephen S. Wolfe, GS-11, DAF

Deputy Chief, Medical Information Services

DSN 968-6930 Comm: (813) 828-6930

Email: wolfess@macdill.af.mil