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

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

Stephen,

Bottom line, there's no harm in using the second query for both

instances. (Unless you count nano-seconds ;) The code below just

forces there to be a value in your second date. (If the ending date

is not required, the ending date will be set to the starting date.)

Here goes:

LOCAL ldDate1, ldDate2

ldDate1 = THIS.RPTT1

IF oCEObj.ByTarget

ldDate2 = THIS.RPTT1

ELSE

ldDate2 = THIS.RPTT2

ENDIF

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

HTH

Doug

> 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?

On 8/17/99 8:24 AM, Wolfe Stephen S Civ GS-11 supposedly said:

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

>inspection and fruitful critique.

I'll try to be gentle. ;-)

> WHERE Staff.hosp = ?OCEobj.hosp;

> AND Staff.Squadron = ?THIS.Squadron;

> AND Staff.Off_Sym = ?THIS.OfficeSymbol;

I would be wary of using "This." is a view parameter. It forces the

view to only be used from within a method of a particular class.

Referring to a particular object name is also problematic, for the same

reason. Instead, I would write those lines:

WHERE Staff.hosp = ?vp_hosp;

AND Staff.Squadron = ?vp_Squadron;

AND Staff.Off_Sym = ?vp_OfficeSymbol;

and then in the method where the view is opened/requeried, define the

view parameter memvars from the corresponding props:

LOCAL vp_hosp, vp_Squadron, vp_OfficeSymbol

vp_hosp = OCEobj.hosp

vp_Squadron = This.Squadron

vp_OfficeSymbol = This.OfficeSymbol

USE MyView

This way, the view isn't tightly coupled with that particular object

in that particular context, but can be re-used in other situations.

> AND Lifesupp.pals_exp >= ?THIS.RPTT1;

> AND Lifesupp.pals_exp <= ?THIS.RPTT2;

Same thing applies here. If you create view parameter variables, you

can define them conditionally based upon your initial IF statement. Then

you only need one copy of the SQL to handle both situations.

___/

/

__/

/

____/

Ed Leafe

http://leafe.com/

Wolfe

Drop all the ?. They are only used in parameterized views. There nothing you

can do to make this query better really as far as I can see. To make it run

as fully optimized make sure there are indexes on the two joined columns and

the four columns that are referenced in the WHERE clause. You'll have to

weigh the cost of maintaining the indexes against the need to minimize

execution time.

-Anders

----- Original Message -----

From: Wolfe Stephen S Civ GS-11 <Stephen.Wolfe@macdill.af.mil>

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

Sent: tisdag den 17 augusti 1999 14:24

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

|

|

|