Error =SET OPTION SQL_BIG_SELECTS=1

[JonathanG6SWJ]

Here I was putting the finishing touches to my first, quite complex (well for me anyway) asp/db application and then I add a few more test records to my db.... the result the following error message...


CODE
Vbscript Runtime error 'ASP 0185 : 3219'
General error: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

Well to the best of my knowledge my WHERE could not be more simple and all the tables concerned don't have that many records.

I've scanned the net but cannot find an obvious answer.
Any clues... if I need to use "SET OPTION...
how do I do this within ASP.

Here's the statement

SELECT tblCompetencyGroups.intCompetencyGroup_obj_no, tblCompetencyGroups.strCompetencyGroupText, tblCompetencies.intCompetency_obj_no, tblCompetencies.strCompetencyText, tblCompetencies.strAltFormatText, tblResultsData.intDelegate_obj_no, tblResultsData.intPerson_obj_no, tblResultsData.intA_obj_no, tblResultsData.intAScore, tblResultsData.intB_obj_no, tblResultsData.intBScore FROM ((tblCompetencies INNER JOIN tblCompetencyClusters ON tblCompetencies.intCompetency_obj_no = tblCompetencyClusters.intCompetency_obj_no) LEFT JOIN tblCompetencyGroups ON tblCompetencyClusters.intCompetencyGroup_obj_no = tblCompetencyGroups.intCompetencyGroup_obj_no) INNER JOIN tblResultsData ON tblCompetencies.intCompetency_obj_no = tblResultsData.intCompetency_obj_no WHERE (((tblResultsData.intDelegate_obj_no)=1) AND ((tblResultsData.intPerson_obj_no)=57)) ORDER BY tblCompetencies.intCompetency_obj_no;

When I remove the ORDER BY statement all works OK.
I'm only returning 65 records - what am I doing wrong?


As always many thanks in advance.
Jonathan

 

[TonyGroves]

Can you format your query, to make it easier to read?

Thanks.

 

[JonathanG6SWJ]

Hopefully that's a bit easier to read!

SELECT tblCompetencyGroups.intCompetencyGroup_obj_no, tblCompetencyGroups.strCompetencyGroupText, tblCompetencies.intCompetency_obj_no, tblCompetencies.strCompetencyText, tblCompetencies.strAltFormatText, tblResultsData.intDelegate_obj_no, tblResultsData.intPerson_obj_no, tblResultsData.intA_obj_no, tblResultsData.intAScore, tblResultsData.intB_obj_no, tblResultsData.intBScore

FROM ((tblCompetencies INNER JOIN tblCompetencyClusters
ON tblCompetencies.intCompetency_obj_no = tblCompetencyClusters.intCompetency_obj_no)

LEFT JOIN tblCompetencyGroups ON tblCompetencyClusters.intCompetencyGroup_obj_no = tblCompetencyGroups.intCompetencyGroup_obj_no)

INNER JOIN tblResultsData ON tblCompetencies.intCompetency_obj_no = tblResultsData.intCompetency_obj_no

WHERE (((tblResultsData.intDelegate_obj_no)=1)
AND ((tblResultsData.intPerson_obj_no)=57)

ORDER BY tblCompetencies.intCompetency_obj_no;

Jonathan