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

Check SYS(3054) function in HELP.
That function will help you see what could slow down query.