Query is too complex... recomendations

[Matt_T_hat]

I have been using ASP, VBscript and an MS-Access 2K DB.

I have a script that produces SQL that should return search results on takeaways in a given town.

However the other day during testing I discovered the QUERY IS TOO COMPLEX error message.

I use nested queries two levels deep and a loop that builds UNION SELECTs into the heart of the SQL.

(Small note it now builds UNION ALL SELECTs)

The SQL inquestion is given below.

So now of course I need to think about a more powerfull database system or a better method for searching.

Your feedback is apprisicated.

(the app is back working as half the dictionary of fields have been commented out.)

The string we threw at it was "pizza alternative jacket potato fillg" which was caused by a cut and past error in earlier testing.

 
Select * from ListLevel 
INNER JOIN 
(SELECT EntityName, Entity.EntityID, listlevel, [Short Description], add1, add2, town, county, fullcode, Region.Region, PrimaryEmail, [Company URL], BranchPhone,  Sum(Rel) as SumOfRel FROM 
(SELECT *, 10 as rel FROM [Q_Pages] where EntityName like '%pizza alternative jacket potato fillg%' 
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%pizza%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%alternative%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%jacket%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%potato%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%fillg%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%pizza%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%alternative%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%jacket%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%potato%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%fillg%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%pizza%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%alternative%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%jacket%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%potato%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%fillg%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%pizza%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%alternative%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%jacket%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%potato%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%fillg%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%pizza%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%alternative%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%jacket%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%potato%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%fillg%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%pizza%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%alternative%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%jacket%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%potato%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%fillg%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%pizza%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%alternative%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%jacket%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%potato%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%fillg%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%pizza%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%alternative%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%jacket%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%potato%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%fillg%'  
Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%pizza%'  
Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%alternative%' 
Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%jacket%' 
 Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%potato%'  
Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%fillg%'  
) T1 
Group By 
EntityName, Entity.EntityID, listlevel, [Short Description], add1, add2, town, county, fullcode, Region.Region, PrimaryEmail, [Company URL], BranchPhone  Order by Sum(rel)
) T2 ON T2.T1.ListLevel = ListLevel.ListLevelID ;

[si_the_geek]

Ouch

I can see room for improvement there, mainly by removing the Unions (when the value of rel is the same), eg:

SELECT *, 10 as rel FROM [Q_Pages] where EntityName like '%pizza alternative jacket potato fillg%' 
Union SELECT *, 1 as rel FROM [Q_Pages] where (EntityName like '%pizza%') OR (EntityName like '%alternative%') OR (EntityName like '%jacket%' ) OR (EntityName like '%potato%') OR (EntityName like '%fillg%')
Union SELECT *, 2 as rel FROM [Q_Pages] where ([Short Description] like '%pizza%') OR ...

Note though that this will reduce the number of rows returned if more than one of the clauses match (eg: if EntityName contains "pizza" and "jacket", you will only get that row once, rather than twice as before).
edit: I realise now that with the Group By that your count wont be accurate!


An alternative would be to use a temporary table to set the rel value, using a series of updates.

[capsulecorpjx]

Dude just use one Union and a bunch of ORs:

Union
(SELECT *, 1 as rel FROM [Q_Pages] where
EntityName like '%pizza%'
or
EntityName like '%[second entry]%'
or
...
)

[Matt_T_hat]

I would have thought that a temporary table and lots of updates would be slower...

The big advantage of the complex but singular SQL is that it is only the one query... however we have about hit it's limit.

(and yes rel is used to estimate how well each row matches the string data.)

I've also just been asked if I can cause the results to show only those results for where region.region returns a rel value or all if none do... My answer was a little rude. I guess I'd better widen the question to talk about general search power and logic though.

[si_the_geek]

How about using a series of If's to calculate it?

I'm not sure if this is the right syntax, but you should get the idea:

SELECT *, If (EntityName like '%pizza%', 1,0) + If (EntityName like '%alternative%', 1,0) + If( ....  as rel FROM [Q_Pages]

[Ecniv]

I have another idea for you.
Concatenate all the fields into one calced field (sub query) then use the where clause and like to filter on the parameters.

Example:

select sqry.AllFields from
(Select table.field1 & table.field2 & table.field3 from table) as sqry
Where sqry.AllFields like "%potato%" or sqry.AllFields like "%fillg%"

Might be worth a try?