Solved

What does 'Internal Query Processor Error: The query processor ran out of stack space during query optimization.' mean?

Posted on 2007-03-23
4
937 Views
Last Modified: 2013-11-07
I am using C# and connecting to a SQL 2000 database. I am building a dynamic query. I am adding to my where clause. I am using this in a loop to create this query and am making it a parameterized query. It worked fine here in  my office. What is wrong?  When I sent it to the customer they received this error:

Exception Searching Object :Internal Query Processor Error: The query processor ran out of stack space during query optimization.
Details:
System.Data.SqlClient.SqlException: Internal Query Processor Error: The query processor ran out of stack space during query optimization.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at TADS.ODEObjectManager.ODEObjectDBClass.GetMultipleParameterSupportRecords(SqlConnection DBConn, ParamInfoDB paramInfo, String strSupportRecWhere)
   at TADS.ODEObjectManager.ODEObjectDBClass.GetParamInfoByName(Guid ObjectID, String strSearch, ArrayList arrParameters, String[] roles)
   at TADS.ODEObjectManager.ODEDataObjectReader.GetSearchParameterIDsTest(String Expression)
   at TADS.ODEObjectManager.ODEDataObjectReader.CoarseSearch(String expression)
   at TADS.ODEObjectManager.SearchPlugin.SearchObjec

Here is the code:

public ParamInfoDB GetParamInfoByName(Guid ObjectID, string strSearch, ArrayList arrParameters,
                                                string[] roles)
        {
            SqlCommand sqlCmd = new SqlCommand();
            ParamInfoDB paramInfo = new ParamInfoDB();
           
            string strSQLStatement = BuildParamInfoSelect(sqlCmd, arrParameters, strSearch, ObjectID);
            strSQLStatement += BuildParameterRolesSelect(roles);
           
            using (SqlConnection DBConn = DatabaseConnection(strDBName, strDBServer))
            {
               
                sqlCmd.Connection = DBConn;
                sqlCmd.CommandText = strSQLStatement;
                sqlCmd.CommandType = CommandType.Text;
                SqlDataAdapter ParamAdapter = new SqlDataAdapter(sqlCmd);
                ParamAdapter.TableMappings.Add(new DataTableMapping("Table", "ParamInfo"));
                ParamAdapter.Fill(paramInfo);
               
                if (paramInfo.ParamInfo.Count > 0)
                {
                    string strSupportRecWhere = "where";
                   
                    foreach (ParamInfoDB.ParamInfoRow rowParam in paramInfo.ParamInfo)
                    {
                        strSupportRecWhere += " paraminfoid = '" + rowParam.ID.ToString() + "' or ";
                    }
                    //***remove last or
                    strSupportRecWhere = strSupportRecWhere.Substring(0, strSupportRecWhere.LastIndexOf(" or "));
                   
                    GetMultipleParameterSupportRecords(DBConn, paramInfo, strSupportRecWhere);
                   
                }
            }
            return paramInfo;
        }

 private string BuildParamInfoSelect(SqlCommand sqlCmd,ArrayList arrParameters,string strSearch,Guid ObjectID)
        {
            string strSQLStatement = "select * from ParamInfo where ODEPropertiesID = @ODEPropertiesID and " + strSearch + " = @Value ";
            sqlCmd.Parameters.Add("@Value", SqlDbType.VarChar).Value = arrParameters[0].ToString();

            for (int intCount = 1; intCount < arrParameters.Count; intCount++)
            {
                strSQLStatement += " or " + strSearch + " = @Value" + intCount;
                sqlCmd.Parameters.Add("@Value" + intCount, SqlDbType.VarChar).Value = arrParameters[intCount].ToString();

            }
            sqlCmd.Parameters.Add("@ODEPropertiesID", SqlDbType.UniqueIdentifier).Value = ObjectID;
            return strSQLStatement;
        }

private void GetMultipleParameterSupportRecords(SqlConnection DBConn, ParamInfoDB paramInfo,string strSupportRecWhere)
        {
            SqlDataAdapter attributeRecords =
                new SqlDataAdapter(
                    "select * from ParameterAttributes " + strSupportRecWhere + " order by AttributeID", DBConn);
            attributeRecords.TableMappings.Add(new DataTableMapping("Table", "ParameterAttributes"));
            attributeRecords.SelectCommand.CommandTimeout = 120;

            SqlDataAdapter DerivedRecords =
                new SqlDataAdapter(
                    "select * from DerivedParameterInfo " + strSupportRecWhere,
                    DBConn);
            DerivedRecords.TableMappings.Add(new DataTableMapping("Table", "DerivedParameterInfo"));
            DerivedRecords.SelectCommand.CommandTimeout = 120;

            SqlDataAdapter DerivedReferenceRecords =
                new SqlDataAdapter(
                    "select * from DerivedReferences where DerivedParameterInfoID in(select ID from DerivedParameterInfo " + strSupportRecWhere + ")",
                    DBConn);
            DerivedReferenceRecords.TableMappings.Add(new DataTableMapping("Table", "DerivedReferences"));
            DerivedReferenceRecords.SelectCommand.CommandTimeout = 120;
            attributeRecords.Fill(paramInfo);
            DerivedRecords.Fill(paramInfo);
            DerivedReferenceRecords.Fill(paramInfo);
        }


0
Comment
Question by:lmred
  • 2
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
graye earned 250 total points
Comment Utility
This error isn't directly related to your program...  instead it's a message from the SQL Server itself, tell you that SQL Server is running out of stack space.

SQL Server has some very good database "Tuning Wizards" that you can use to analyise a days worth of logs and give you  some recommendations on how to "tune" SQL Server.   That might help.... or it might just be that you need more memory/disk/whatever on the server.

I'd be looking at the event logs on the server where SQL Server is hosted...  It might shed some light.
0
 
LVL 1

Author Comment

by:lmred
Comment Utility
Is there a way to change my code so they won't run out of stack space on their server? Is the query statement that uses the 'in' clause causing the problem? The whole purpose for writing this code is to speed up their search. So whatever I do, it needs to be fast.

Lmred
0
 
LVL 41

Assisted Solution

by:graye
graye earned 250 total points
Comment Utility
Yes, any "nested query" would require a lot of stack (and probably wouldn't be the most efficient way to accomplish the task).   An quick/easy solution is to rewrite the query to use an SQL join operation (instead of a nested query).  It'd look something like this:

Select * from DerivedReferences where <something goes here> join DerivedParameterInfo on DerivedReferences.DerivedParameterInfoID = DerivedParameterInfo.ID

BTW:  SQL Server has some wonderful tools to analyze a query and show the "cost" via the proposed execution plan.   I encourage you to try both techniques and compare the "cost" via the Query Analyizer.

0
 
LVL 1

Author Comment

by:lmred
Comment Utility
Thanks you so much for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now