I have a search form I’m working on that pulls results out of a database. The database has the following tables:
[resource]
resourceid
resource
description
resourceurl
active
[tag]
tagid
listid (this is a parent id)
tag
[resource_tag] (joins the two above together)
resourceid
tagid
The tags have 7 parents, and each parent tag category has to be treated as its own thing, so when I pull results, I don’t just want to do WHERE tagid IN (all chosen tags). So I’ve joined the resource_tag table as 7 aliases, which gives me a proper search form (drop-downs for each tag category and multiple choice checkboxes for the last one).
But my results query is VERY slow, especially when few options are chosen:
SELECT resource.resource
, resource.description
, resource.resourceurl
FROM ( ( ( ( ( ( ( cc.resource
INNER JOIN cc.resource_tag AS modules
ON modules.resourceid = cc.resource.resourceid
)
INNER JOIN cc.resource_tag AS types
ON types.resourceid = cc.resource.resourceid
)
INNER JOIN cc.resource_tag AS impacts
ON impacts.resourceid = cc.resource.resourceid
)
INNER JOIN cc.resource_tag AS strats
ON strats.resourceid = cc.resource.resourceid
)
INNER JOIN cc.resource_tag AS modes
ON modes.resourceid = cc.resource.resourceid
)
INNER JOIN cc.resource_tag AS stages
ON stages.resourceid = cc.resource.resourceid
)
INNER JOIN cc.resource_tag AS regions
ON regions.resourceid = cc.resource.resourceid
)
INNER JOIN cc.tag
ON tag.tagid = modules.tagid
OR tag.tagid = types.tagid
OR tag.tagid = impacts.tagid
OR tag.tagid = strats.tagid
OR tag.tagid = modes.tagid
OR tag.tagid = stages.tagid
OR tag.tagid = regions.tagid
WHERE cc.resource.active <> 0
<cfif FORM.tag1 neq 0>AND modules.tagid = <cfqueryparam value="#FORM.tag1#" cfsqltype="cf_sql_numeric"></cfif>
<cfif FORM.tag2 neq 0>AND types.tagid = <cfqueryparam value="#FORM.tag2#" cfsqltype="cf_sql_numeric"></cfif>
<cfif FORM.tag3 neq 0>AND impacts.tagid = <cfqueryparam value="#FORM.tag3#" cfsqltype="cf_sql_numeric"></cfif>
<cfif FORM.tag4 neq 0>AND strats.tagid = <cfqueryparam value="#FORM.tag4#" cfsqltype="cf_sql_numeric"></cfif>
<cfif FORM.tag5 neq 0>AND modes.tagid = <cfqueryparam value="#FORM.tag5#" cfsqltype="cf_sql_numeric"></cfif>
<cfif FORM.tag6 neq 0>AND stages.tagid = <cfqueryparam value="#FORM.tag6#" cfsqltype="cf_sql_numeric"></cfif>
<cfif FORM.tag8 neq "">AND regions.tagid IN (<cfqueryparam value="#FORM.tag8#" list="yes" cfsqltype="cf_sql_varchar">)</cfif>
GROUP BY resource.resource
, resource.description
, resource.resourceurl
, resource.author
ORDER BY CASE author
WHEN 'FHWA' THEN 1
WHEN 'DOT' THEN 1
ELSE 2 END, resource
(forgive the CF code, as that’s a direct paste)
If I have all 7 options chosen, then the search runs pretty quickly, but if I leave more than 3 options blank (which is treated in the form as “Any”), then it just grinds endlessly. There are only around 215 records in this database, so I have no idea why it’s so slow unless there’s something wrong with my query.
If you have 215 records and you’re joining them 7 times independently, that’s millions of possible combinations. I could be wrong, I didn’t really take the time to figure out what your query is doing, but when you join and rejoin different things you’re growing the number of possible records returned exponentially. Each one of these must now be tested, grouped, and ordered.
Like mawburn says, this query would be a beast, and you’re joining a lot of tables that you may or may not need.
If I understand CF syntax, then this should work (warning, I don’t do CF, so obviously it’s not tested). It only performs a join the table IF that tag is used, but since it’s an inner join, those additional values MUST be there (which does what the ANDs do in your example…
SELECT resource.resource
, resource.description
, resource.resourceurl
, resource.author
FROM cc.resource
<cfif FORM.tag1 neq 0>
INNER JOIN (cc.resource_tag AS modules ON modules.resourceid = cc.resource.resourceid AND modules.tagid = <cfqueryparam value="#FORM.tag1#" cfsqltype="cf_sql_numeric">)
</cfif>
<cfif FORM.tag2 neq 0>
INNER JOIN (cc.resource_tag AS types ON types.resourceid = cc.resource.resourceid AND types.tagid = <cfqueryparam value="#FORM.tag2#" cfsqltype="cf_sql_numeric">)
</cfif>
<cfif FORM.tag3 neq 0>
INNER JOIN (cc.resource_tag AS impacts ON impacts.resourceid = cc.resource.resourceid AND impacts.tagid = <cfqueryparam value="#FORM.tag3#" cfsqltype="cf_sql_numeric">)
</cfif>
<cfif FORM.tag4 neq 0>
INNER JOIN (cc.resource_tag AS strats ON strats.resourceid = cc.resource.resourceid AND strats.tagid = <cfqueryparam value="#FORM.tag4#" cfsqltype="cf_sql_numeric">)
</cfif>
<cfif FORM.tag5 neq 0>
INNER JOIN (cc.resource_tag AS modes ON modes.resourceid = cc.resource.resourceid AND modes.tagid = <cfqueryparam value="#FORM.tag5#" cfsqltype="cf_sql_numeric">)
</cfif>
<cfif FORM.tag6 neq 0>
INNER JOIN (cc.resource_tag AS stages ON stages.resourceid = cc.resource.resourceid AND stages.tagid = <cfqueryparam value="#FORM.tag6#" cfsqltype="cf_sql_numeric">)
</cfif>
<cfif FORM.tag8 neq 0>
INNER JOIN (cc.resource_tag AS regions ON regions.resourceid = cc.resource.resourceid AND AND regions.tagid IN (<cfqueryparam value="#FORM.tag8#" list="yes" cfsqltype="cf_sql_varchar">))
</cfif>
WHERE cc.resource.active <> 0
GROUP BY resource.resource
, resource.description
, resource.resourceurl
, resource.author
ORDER BY CASE author
WHEN 'FHWA' THEN 1
WHEN 'DOT' THEN 1
ELSE 2 END, resource
the best strategy – and i say this without examining your query in detail – is that if you have “any” options, you use your programming language, in this case coldfusion, to omit those lines in the query
this simplifies the query and results in faster execution
The thing about joining tables only when I need them is that I’m pretty sure I always need them. If no option is selected, the default is to show all records. When the thing does run, it returns a correct record set. It’s just that it obviously takes far too long.
I’m going to test Dave’s idea though and compare results.
It’s going to be faster since it returns a much smaller recordset to work off of. Like @mawburn says, you were joining those 215 records in 7 different ways, which means there would be millions of rows (take each combination and make them).
Since you were never returning a value from those joined tables, there was no reason to JOIN them UNLESS you had a value to look for, which is what my example gave you. I would have just used an IN clause on one join, but since it’s an AND condition in your existing example, that’s why I gave you the solution I gave you. Rudy probably could have given you a more elegant solution, but mine worked…
Yeah, I’m to the point where I can write a query to get what I want, but I need to learn to try to optimize things better. For some reason, when I saw your query, I though that unless an option was chosen, I’d get no results from it. But of course I do, because I’m selecting those records in the first place.
This entire database was set up by someone else, and that person did a search tool as well, but they just did a WHERE/IN statement, and made it all inclusive. If you chose “Reports” in “Michigan”, it would give you all reports (regardless of state) and everything from Michigan. Mine just gives you reports from Michigan, and that’s the way the site owner wants it. So my monster query was a result of just trying to get that.
My query to build the search form is also pretty monstrous, but it loads quickly for some reason, so I’m not messing with it.
