5 Replies Latest reply on Aug 27, 2007 8:56 AM by Newsgroup_User

    Query of Queries problem

    hans blix
      Over the past several weeks we've been experiencing periodic hangs of the ColdFusion service. We finally invested in SeeFusion and found the problem, but aren't sure how to fix it

      The code that hangs is a query of queries block. Basically we are getting a result set from verity (~1000 rows), then getting a result set from SQL server (~3000 rows), and joining the two with the query of query statement. Everything will run fine for a few hours, then that block of code will bog down. When I dumped the threads during the error, everything gets hung like so:

      "jrpp-255" runnable
      at coldfusion.sql.imq.rttExpr.guesstimateJavaType(rttExpr.java:439)
      at coldfusion.sql.imq.rttExpr.guesstimateJavaType(rttExpr.java:371)
      at coldfusion.sql.imq.imqTable.guessColumnType(imqTable.java:443)
      at coldfusion.sql.QueryTableMetaData.InferMetaDataTypes(QueryTableMetaData.java:350)
      at coldfusion.sql.imq.TableList.validate(TableList.java:166)
      at coldfusion.sql.imq.rttSelectExprSpec.validate(rttSelectExprSpec.java:498)
      at coldfusion.sql.imq.rttSelectStmt.validate(rttSelectStmt.java:84)
      at coldfusion.sql.imq.jdbcStatement.fetchResult(jdbcStatement.java:538)
      at coldfusion.sql.imq.jdbcStatement.execute(jdbcStatement.java:131)
      at coldfusion.sql.Executive.executeQuery(Executive.java:775)
      at coldfusion.sql.SqlImpl.execute(SqlImpl.java:240)
      at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:500)
      at cfauctionManagement2ecfc978963305$funcJOINAUCTIONSTOVERITY.runFunction(D:\inetpub\wwwroot \CustomTags\mysite\component\auctionManagement.cfc:137)
      at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:344)
      at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47)
      at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:290)
      at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:254)
      at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:56)
      at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:207)
      at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:366)
      at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:198)
      at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:157)
      at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:1594)
      at coldfusion.tagext.lang.InvokeTag.doEndTag(InvokeTag.java:341)
      at cfact_search_auctions2ecfm1040202400._factor47(D:\inetpub\wwwroot\CustomTags\mysite\actio n\act_search_auctions.cfm:849)
      at cfact_search_auctions2ecfm1040202400.runPage(D:\inetpub\wwwroot\CustomTags\mysite\action\ act_search_auctions.cfm:1)
      at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:152)
      at coldfusion.filter.CFVariablesScopeFilter.invoke(CFVariablesScopeFilter.java:63)
      at coldfusion.tagext.lang.ModuleTag.doStartTag(ModuleTag.java:255)
      at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:1925)
      at cfindex2ecfm1952396859.runPage(D:\inetpub\wwwroot\mysite\cfml\auctions\index.cfm:18)
      at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:152)
      at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:349)
      at coldfusion.runtime.CfJspPage._emptyTag(CfJspPage.java:1915)
      at cflas2dvegas2dtimeshares2ecfm1477369516.runPage(D:\inetpub\wwwroot\mysite\cfml\las-vegas- timeshares.cfm:1)
      at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:152)
      at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:349)
      at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
      ...etc


      I believe this means CF is trying to "guesstimate" the java type of each column in the result set. We tried to work around that by CASTing each column as a varchar, but we still have the same issue.

      If anyone has an idea what the problem could be, we'd really appreciate any help. Also, if anyone can suggest a way to merge the two result sets without using SQL, that would be great too. I've already tried nested loops to manually build the final result set, but that was taking up to 5 seconds to run.

      Environment:
      CFMX 7.0.2
      Win2K
      SQL Server 2K
        • 1. Re: Query of Queries problem
          Dan Bracuk Level 5
          Another way merge the results is with QueryAddRow and QuerySetCell.
          • 2. Re: Query of Queries problem
            hans blix Level 1
            Dan,

            I did try that as well. The problem is that it took about 5 seconds to run. When the query of queries block isn't hanging, it only takes about 250 ms.
            • 3. Re: Query of Queries problem
              Level 7
              > I believe this means CF is trying to "guesstimate" the java type of each
              > column in the result set. We tried to work around that by CASTing each column
              > as a varchar, but we still have the same issue.

              CF still has to guess the column type WITHIN the CAST() expression though
              (to see HOW it should be casting the column to a varchar).

              That's an *awful lot* of columns in your query there.

              Why specifically are you joining the two columns together?

              What are you doing with the resultant recordset?

              --
              Adam
              • 4. Re: Query of Queries problem
                hans blix Level 1
                The site uses the built in Verity server to handle keyword searches. Verity returns 2 columns that are important:
                KEY: This is the ID of the row
                SCORE: This is the relevance score

                We need more information than that to display a record, so we select everything out of the database (which is fairly quick), then JOIN that to the verity results (WHERE auctionID = KEY). Note that we had to rename the verity KEY column to AUCTIONID, as KEY was not acceptable to the query of queries block.

                We include SCORE in this resultset as well, so we can sort the final query by relevance.

                Do you think the amount of columns is causing the problem? Again, this code runs quite fast for several hours under load, until it starts to hang on us.
                • 5. Re: Query of Queries problem
                  Level 7
                  > We need more information than that to display a record, so we select
                  > everything out of the database (which is fairly quick), then JOIN that to the
                  > verity results (WHERE auctionID = KEY).

                  Right. And what do you need THOUSANDS of matches for, in one hit, here?

                  And what do you need ALL those columns for, when dealing with all these
                  thousands of rows. You've not really answered my question as to "what's
                  the end result here?" What are you trying to achieve? I don't mean what
                  you're doing to aggregate the data, but simply *why*? What is the
                  requirement you have here to be engaging in this enterprise in the first
                  place? Search screen? Stock control report? What?

                  Is there any way of optimising how much processing you're doing?

                  If - say - you're doing a search results screen, you probably don't need
                  1000s of results: you probably need 20. So just ask the DB for 20: WHERE
                  id IN (#list of 20 IDs from Verity search results#)

                  If the user goes "NEXT >>", then grab the next 20 (cache the Verity query
                  somehow, rather than re-query it).

                  Obviously there's some heavy-lifting processing that might need to process
                  the whole lot. Can this not be done in a sliding window of results? Or
                  could you not pass the list of IDs from the Verity resultset into the DB
                  somehow, and do the filtering on the DB engine, rather than with CF (which
                  is not very good at bulk data processing, as you're seeing. It's not what
                  it's designed for).


                  I find QoQ to be very flaky for all ut the most basic operations. For
                  basic stuff it's fine. It does not surprise me that it seems to leak
                  memory (or whatever it's doing) and eventually give up the ghost. This
                  does not help you, I realise, but as a suggested practice: don't expect too
                  much out of QoQ. Try some other method instead.

                  --
                  Adam