Hi
I'm looking to speed up a query that is used for the main search in a program I've written. I've got 3 tables of similar structure and a property(address) level table that I need to query.

The user can enter search criteria that queries data that may be held in either the property level table or the one of the 3 underlying tables.

At the moment I'm carrying out 3 separate SQL statements each with a join to the property table then unioning the results.
Is this the quickest way to search?
Would I be be better to do one large SQL statement?

I've set indices on the most commonly searched fields.
At the moment some searches can take 30+ seconds which will be longer once the system is released and used over the network.

At the moment the size of the tables are:
Property table - 450,000 records (only about 30,000 that I am interested in)
Master table - 33,000 records
History table - 5,000 records
pending table - 500 records

The code I'm using is:

SELECT DISTINCT ;
	p.dist_code AS dist_code, ;
	p.work_area AS ward, ;
	p.street_ref AS street, ;
	p.work_area+"/"+p.street_ref AS wdst, ;
	p.propkey AS propkey, ;
	p.hyphsuff AS hyphsuff, ;
	p.postcode as postcode, ;
	s.streetname as streetname,;	
	ALLTRIM(s.streetname)+IIF(!EMPTY(s.locality),', '+ALLTRIM(s.locality),', ')+ALLTRIM(s.town) AS address, ;
	p.propno AS propno,;
	c.recordid AS recordid,;		
	c.pprn AS pprn,;		
	c.DESC AS DESC,;
	c.vcode AS vcode,;
	c.Nav as NAV,;
	c.RV as RV,;
	c.eff_date as Eff_Date,;
	c.method as Method,;
        p.appealed as Appealed,;
        p.cre_matched as Cre_matched,;
	c.vsuff as vsuff,;	
	c.TITLE AS TITLE,;
	s.town AS townname,;
	ALLTRIM(s.streetname)+ALLTRIM(s.locality)+ALLTRIM(s.town) AS address2,;
	RECNO() AS recnotable,;
	999999999  as dynamicbackcolor;
	FROM cvs!master c ;
	INNER JOIN (property p ;
	INNER JOIN street s ON p.street_ref == s.street_ref) ;
	ON c.pprn == p.pprn ;
	WHERE &lcSQL ;
	INTO CURSOR csrCVSMastResults NOFILTER READWRITE 

	
SELECT DISTINCT ;
	p.dist_code AS dist_code, ;
	p.work_area AS ward, ;
	p.street_ref AS street, ;
	p.work_area+"/"+p.street_ref AS wdst, ;
	p.propkey AS propkey, ;
	p.hyphsuff AS hyphsuff, ;
	p.postcode as postcode, ;
	s.streetname as streetname,;
	ALLTRIM(s.streetname)+IIF(!EMPTY(s.locality),', '+ALLTRIM(s.locality),', ')+ALLTRIM(s.town) AS address, ;
	p.propno AS propno,;
	c.recordid AS recordid,;		
	c.pprn AS pprn,;		
	c.DESC AS DESC,;
	c.vcode AS vcode,;
	c.Nav as NAV,;
	c.RV as RV,;
	c.eff_date as Eff_Date,;
	c.method as Method,;
        p.appealed as Appealed,;
        p.cre_matched as Cre_matched,;
	c.vsuff as vsuff,;	
	c.TITLE AS TITLE,;
	s.town AS townname,;
	ALLTRIM(s.streetname)+ALLTRIM(s.locality)+ALLTRIM(s.town) AS address2,;
	RECNO() AS recnotable,;
	999999999  as dynamicbackcolor;
	FROM cvs!PENDING c ;
	INNER JOIN (property p ;
	INNER JOIN street s ON p.street_ref == s.street_ref) ;
	ON c.pprn == p.pprn ;
	WHERE &lcSQL ;
	INTO CURSOR csrCVSPendresults NOFILTER READWRITE 
	
SELECT DISTINCT ;
	p.dist_code AS dist_code, ;
	p.work_area AS ward, ;
	p.street_ref AS street, ;
	p.work_area+"/"+p.street_ref AS wdst, ;
	p.propkey AS propkey, ;
	p.hyphsuff AS hyphsuff, ;
	p.postcode as postcode, ;
	s.streetname as streetname,;	
	ALLTRIM(s.streetname)+IIF(!EMPTY(s.locality),', '+ALLTRIM(s.locality),', ')+ALLTRIM(s.town) AS address, ;
	p.propno AS propno,;
	c.recordid AS recordid,;		
	c.pprn AS pprn,;		
	c.DESC AS DESC,;
	c.vcode AS vcode,;
	c.Nav as NAV,;
	c.RV as RV,;
	c.eff_date as Eff_Date,;
	c.method as Method,;
        p.appealed as Appealed,;
        p.cre_matched as Cre_matched,;
	c.vsuff as vsuff,;	
	c.TITLE AS TITLE,;
	s.town AS townname,;
	ALLTRIM(s.streetname)+ALLTRIM(s.locality)+ALLTRIM(s.town) AS address2,;
	RECNO() AS recnotable,;
	999999999  as dynamicbackcolor;
	FROM cvs!History c ;
	INNER JOIN (property p ;
	INNER JOIN street s ON p.street_ref == s.street_ref) ;
	ON c.pprn == p.pprn ;
	WHERE &lcSQL ;
	INTO CURSOR csrCVSHistresults NOFILTER READWRITE 	
	
SELECT * FROM csrCVSMastResults ;
UNION ALL;
Select * FROM csrCVSPendresults ;
UNION ALL ;
SELECT * FROM csrCVSHistresults ;
INTO CURSOR csrCVSResults ORDER BY 1,2,3,5,6