Hi all,

I suspect I am shooting myself in the foot with this inefficient query.

Tables:
mtgloan - contains mortgage loan data

mtgproperty - contains property/parcel data, 1-many relationship with loans. Loans can have several properties under them.

mtgacctnum - contains taxing jurisdiction and parcel number information. 1-many to property. A property can have several taxing authorities hitting it.

All tables have autoincrenting primary keys, with FK's in the child tables as indicated.

These variables contain selection criteria coming from a web form.

	lcFromAccount = Upper(Request.Form("From_Account"))
	lcToAccount = Upper(Request.Form("To_Account"))
	lcContract = Upper(Request.Form("Contract"))
	lcAddress = Upper(Request.Form("Address"))
	lcName = Upper(Request.Form("Borrowername"))
	lcParcel = Upper(Request.Form("Parcel"))
	lcCounty = Upper(Request.Form("County"))
	lcSt = Upper(Request.Form("State"))
	lcCollector = Upper(Request.Form("Collector"))
	lcBranch = Upper(Request.Form("Branch"))

Here is the query itself. I suspect that I'm overdoing it on the trimming etc. But, it strikes me that this is the equivalent of doing a huge join before applying the selection filters, so this takes a good bit of work to run. Running time scales up poorly as the result set size increases. Thoughts are welcome.

thanks,

		Select a.wcode, Min(d.st+d.cnty+d.unit) As collector, b.display_loan, d.acct, b.borrowername, a.branch, Min(b.Id) As loan_id, Min(a.Id) As property_id ;
			from mtgproperty a , ;
			mtgloan b, ;
			mtgacctnum d;
			INTO Cursor temp ;
			where (a.client_id == users.client_id);
			AND (b.Id == a.loan_id );
			and (a.Id == d.property_id ) ;
			AND Iif(!Empty(lcFromAccount),(Padl(Alltrim(b.loan),20,[0]) >= Padl(Alltrim(lcFromAccount),20,[0])),.T.);
			AND Iif(!Empty(lcToAccount),(Padl(Alltrim(b.loan),20,[0]) <= Padl(Alltrim(lcToAccount),20,[0])),.T.);
			AND Iif(!Empty(lcContract),(Alltrim(b.contract) == Alltrim(lcContract)),.T.);
			AND Iif(!Empty(lcAddress),(Alltrim(a.situs_addr1) == Alltrim(lcAddress)),.T.);
			AND Iif(!Empty(lcName),(Alltrim(b.borrowername) == Alltrim(lcName)),.T.);
			AND Iif(!Empty(lcParcel),(Alltrim(d.acct) == Alltrim(lcParcel)),.T.);
			AND Iif(!Empty(lcCounty),(Alltrim(a.county) == Alltrim(lcCounty)),.T.);
			AND Iif(!Empty(lcBranch),(Alltrim(a.branch) == Alltrim(lcBranch)),.T.);
			AND Iif(!Empty(lcSt),(Alltrim(a.st) == Alltrim(lcSt)),.T.);
			AND Iif(!Empty(lcCollector),(d.st+d.cnty+d.unit == lcCollector),.T.);
			GROUP By  1, 3, 4, 5, 6