Help with query please

Ok, cannot seem to get this to work. I get a stack overflow due to the IN clause in the query. Microsoft says this is an issue with SQL2K and I need to do the following... <!---MS STUFF---> CREATE TABLE #IN_values (au_id char(4)) INSERT INTO #IN_values select au_id FROM Table_with_values_123456789... SELECT max(au_id) FROM authors as A JOIN #IN_values as I ON (A.au_id = I.au_id) <!---MS STUFF---> Here is the portion of my query that is breaking, but I am unsure of how to write this out what MS is saying   SELECT   #APPLICATION.DBPRE#Advertisements.AdID,   #APPLICATION.DBPRE#Advertisements.quantity_option,   #APPLICATION.DBPRE#Advertisements.MemberID,   #APPLICATION.DBPRE#MemberProfile.RscreenName,   #APPLICATION.DBPRE#Advertisements.CategoryID,   #APPLICATION.DBPRE#Advertisements.SCategoryID,   #APPLICATION.DBPRE#Advertisements.ItemPhoto,   #APPLICATION.DBPRE#Advertisements.title,   #APPLICATION.DBPRE#Advertisements.Price,   #APPLICATION.DBPRE#Advertisements.AQuantity,   #APPLICATION.DBPRE#Advertisements.State,   #APPLICATION.DBPRE#Advertisements.zipcode,   #APPLICATION.DBPRE#Advertisements.County,   #APPLICATION.DBPRE#Advertisements.City   FROM   #APPLICATION.DBPRE#Advertisements   LEFT OUTER JOIN  #APPLICATION.DBPRE#MemberProfile   ON   #APPLICATION.DBPRE#Advertisements.MemberID = #APPLICATION.DBPRE#MemberProfile.MemberID WHERE BLAH BLAH BLAH   <CFIF IsDefined("form.zip") and form.zip neq 0 and IsDefined("form.radius") and form.radius neq 0 and len(trim(form.zip)) eq 5>     AND    ( #APPLICATION.DBPRE#Advertisements.zipcode IN (#ListQualify(ValueList(results.zip),"'")#)     OR (#APPLICATION.DBPRE#Advertisements.city IN (#listQualify(ValueList(results.city),"'")#)     AND #APPLICATION.DBPRE#Advertisements.State IN (#listqualify(valuelist(results.State), "'")#)))   </CFIF> Doug B. > I get the following during a radius check on zipcodes when I choose say 500 miles from point x > > Internal Query Processor Error: The query processor ran out of stack space during query optimization > > Any Ideas? > > Doug B.

Hey Doug, Not sure what is exactly happening, but what I would recommend is first, capture the output SQL from your CF. To do this, take all the SQL and put it into a CFSAVECONTENT Then dump that out and try using it in SQL Query Analyzer. It should help narrow down where the issue is. -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer

Well, this is the issue according to Microsoft. http://support.microsoft.com/kb/288095 Doug B. ----- Excess quoted text cut - see Original Post for more -----

That makes sense, you have several IN statements and without knowing much about your app, I'd say based on the names in your IN statements, you may have a lot of them. ZIPCODES, CITIES and STATES could be very large lists... That many lists in 3 different IN statements tied together using ORs... I could see that choking a SQL server. You may want to look into to do some JOINS off of those fields and combining some queries, you'll get a huge performance boost. I can't offer much more based on what little code is there. How deep are you into this project? It may be necessary to do some DB re-design to make it possible to JOIN instead of passing in long lists of data. ----- Excess quoted text cut - see Original Post for more -----

Well here is the offending code. Some of it is quite long especially the zip locator, so I will attempt to shorten it for the lists sake. Maybe someone can show me the best ways to handle this. First the zip locator runs and selects all zip codes that are within the specified criteria. 0-500 miles of point X. So say someone types in their zipcode and selects 500 miles, then the radius search returns all zip codes within 500 miles of that zip. At this point another query is ran and does a search of Advertisements where the Advertisements zipcode is IN the list of zipcodes returned by the radius search. So... Part of the radius search that is ran first.   <CFQUERY name="results" dbtype="query">   select * from qSquareSearch where dist < <CFQUERYPARAM value="#radius#" cfsqltype="CF_SQL_INTEGER">   </CFQUERY>   <CFRETURN results> Query to get advertisements.. SELECT   #APPLICATION.DBPRE#Advertisements.AdID,   #APPLICATION.DBPRE#Advertisements.quantity_option,   #APPLICATION.DBPRE#Advertisements.MemberID,   #APPLICATION.DBPRE#MemberProfile.RscreenName,   #APPLICATION.DBPRE#Advertisements.CategoryID,   #APPLICATION.DBPRE#Advertisements.SCategoryID,   #APPLICATION.DBPRE#Advertisements.ItemPhoto,   #APPLICATION.DBPRE#Advertisements.title,   #APPLICATION.DBPRE#Advertisements.Price,   #APPLICATION.DBPRE#Advertisements.AQuantity,   #APPLICATION.DBPRE#Advertisements.State,   #APPLICATION.DBPRE#Advertisements.zipcode,   #APPLICATION.DBPRE#Advertisements.County,   #APPLICATION.DBPRE#Advertisements.City   FROM   #APPLICATION.DBPRE#Advertisements   JOIN   #APPLICATION.DBPRE#MemberProfile   ON   #APPLICATION.DBPRE#Advertisements.MemberID = #APPLICATION.DBPRE#MemberProfile.MemberID   WHERE   #APPLICATION.DBPRE#Advertisements.Status =   <CFQUERYPARAM cfsqltype="CF_SQL_BIT" value="#Val(ARGUMENTS.Status)#">   AND   #APPLICATION.DBPRE#Advertisements.AdExpires >=   <CFQUERYPARAM cfsqltype="CF_SQL_CHAR" value="#Trim(ARGUMENTS.AdExpires)#"> This is where the problem is since the radius returns thousands of results if someone chooses like 500 mile radius   <CFIF IsDefined("form.zip") AND form.zip neq 0 AND IsDefined("form.radius") AND form.radius neq 0 AND len(trim(form.zip)) eq 5>     AND    ( #APPLICATION.DBPRE#Advertisements.zipcode IN (#ListQualify(ValueList(results.zip),"'")#)     OR(#APPLICATION.DBPRE#Advertisements.city IN (#listQualify(ValueList(results.city),"'")#)    AND #APPLICATION.DBPRE#Advertisements.State IN(#listqualify(valuelist(results.State), "'")#)))   </CFIF>

Doug, Rick Root posted a thread which reminded me of the issue you are having.  He's talking about a way to optimize a zip-lookup function perhaps similar to yours.   That post is at: http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50216 Hope this is of some help.  Seems to me that the resultset is too large for SQL Server to handle.  You could try running SQL Profiler to view memory statistics or reconfiguring SQL Server to have more memory if you have some to spare.