ASP/MySql 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...

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.

As always many thanks in advance.
Jonathan

 

[steven290]

show us your select statement

 

[JonathanG6SWJ]

Hi There

Here's the statement

sSQL="SELECT tblCompetencyGroups.intCompetencyGroup_obj_no,"
sSQL=sSQL+" tblCompetencyGroups.strCompetencyGroupText,"
sSQL=sSQL+" tblCompetencies.intCompetency_obj_no," 
sSQL=sSQL+" tblCompetencies.strCompetencyText," 
sSQL=sSQL+" tblCompetencies.strAltFormatText," 
sSQL=sSQL+" tblResultsData.intDelegate_obj_no,"
sSQL=sSQL+" tblResultsData.intPerson_obj_no,"
sSQL=sSQL+" tblResultsData.intA_obj_no,"
sSQL=sSQL+" tblResultsData.intAScore,"
sSQL=sSQL+" tblResultsData.intB_obj_no,"
sSQL=sSQL+" tblResultsData.intBScore "
sSQL=sSQL+" FROM ((tblCompetencies INNER JOIN tblCompetencyClusters ON tblCompetencies.intCompetency_obj_no = tblCompetencyClusters.intCompetency_obj_no) "
sSQL=sSQL+" INNER JOIN tblCompetencyGroups ON tblCompetencyClusters.intCompetencyGroup_obj_no = tblCompetencyGroups.intCompetencyGroup_obj_no) "
sSQL=sSQL+" INNER JOIN tblResultsData ON tblCompetencies.intCompetency_obj_no = tblResultsData.intCompetency_obj_no"
sSQL=sSQL+" WHERE(((tblResultsData.intDelegate_obj_no)= " & (intDelegate_obj_no) &") AND ((tblResultsData.intPerson_obj_no)=" & (intPerson_obj_no) &"))"
sSQL=sSQL+" ORDER BY tblCompetencies.intCompetency_obj_no;"
sSQL="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;"

Regards
Jonathan

 

[JonathanG6SWJ]

An Update

If I remove the

ORDER BY tblCompetencies.intCompetency_obj_no

the query works!

Is this Select statement just too much for MySql?
Jonathan

 

[JonathanG6SWJ]

Having now done more research I think I'm just demanding too much from mysql.

Does any body know how to use SET OPTION SQL_BIG_SELECTS=1 from within ASP

Jonathan