I've updated my code and the search in general is much faster than before except in the instance of searching on Streetname or town only (These fields have indices set).

A streetname or Town only search can take 30-45 seconds to return the results. There are 450,000 property records and about 30000 master level records but this seems excessive?

I did notice that the replacement of the '==' with SET ANSI ON actually slowed these particular searches down. Would this be anything to do with the size of the fields Streetname is c(100) and town c(30)?

The new code is attached below

SET ANSI ON

SELECT ;
	p.dist_code AS dist_code, ;
	p.work_area AS ward, ;
	p.street_ref AS street, ;
	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,;
	IIF(c.change_type<>'D','LIVE','DELETED') as Record_type,;
	999999999  as dynamicforecolor,;	
	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  
**? cmemvar
	
SELECT ;
	p.dist_code AS dist_code, ;
	p.work_area AS ward, ;
	p.street_ref AS street, ;
	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,;
    'PENDING' as Record_type,;
	999999999  as dynamicforecolor,;
	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 
	
**? cmemvar
	
SELECT ;
	p.dist_code AS dist_code, ;
	p.work_area AS ward, ;
	p.street_ref AS street, ;
	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,;
	'HISTORY' as Record_type,;
	999999999  as dynamicforecolor,;
	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 	

**? cmemvar	

**Seperate sql statements to check speed	
SELECT * FROM csrCVSMastResults ;
UNION ALL;
Select * FROM csrCVSPendresults ;
UNION ALL ;
SELECT * FROM csrCVSHistresults ;
INTO CURSOR csrCVSResults ORDER BY 1,2,3,5,6
**? cmemvar