4 Replies Latest reply on Sep 1, 2006 8:16 AM by KatieAron

    Faster Performance?: QueryNew, Structure or SQL temp table?

      Hi there, I'm hoping someone can help me. I've created a pretty complicated calendar app that has to loop through each week of a month and scan for orders for that day to display them out nicely. Because long term (LT) orders can span multiple days and weeks, so I am to band them together in one HTML TableRow (TR) across the week. So to do this, I have to pre-scan the week first, write all the data to a QueryNew and then later dump out the results in TRs (I can NOT simply just do a loop for query by day, unfortunately, because the long term orders need to stay banded together). And I have to do the pre-scan to determine what day of the week has the largest amount of long term orders and such so I know how many banding TR slots to account for (and it's also possible to sort other ways where the banded orders display last and so forth).

      So anyway, just trust me when I say this pre-scan into a QueryNew is necessary.

      The problem I have now is that that page is taking 52 seconds to load for some clients that have LARGE numbers of long term orders (say 20-40) that scan multiple weeks because the QueryNew is taking a while to build up and when I have to query the QueryNew for the TR position of LT order, it's taking 47ms per LT order per day (which adds up to nearly 52 seconds with all the other queries). For some reason the query of a query seems to take longer than a query to a SQL DB.

      So I'm starting to think the QueryNew wasn't the way to go?? Should I have used a structure or a SQL Temp Table instead? Anyone have ANY THOUGHTS?? I'd appreciate them sooo much! THANK YOU!!!

      Btw, here is a screenshot of a part of my CFDUMP of this QueryNew I'm talking about. DisplayOrder1 is the day of the month and DisplayOrder2 is the TR number: http://www.planet-k.com/test/queryforcalendar.cfm