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

[Guest]

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

[Dan_Bracuk]

Maybe you can solve your problem by building a summary table in your db. You could keep it up to date with triggers or with scheduled updates depending on the complexity and your business needs.

[Guest]

So does anyone have opinions about performance between QueryNew's, Structures and SQL Temp Tables... Input on what they think is fastest? I don't think a summary table would work in this instance because the data is too flexible and ever-changing and there are so many clients it could be run for that the summary table would become repeats of our main db tables.

[Guest · Accepted solution]

I think SQL Temp Tables is the more correct approach. I also thnk that it is the
most efficient.

Scanning/filtering data in sql tables sounds like something the database
should be doing?


Good luck!

[Guest]

Changing from QueryNew to a SQL Temp table cut down the largest example of the page processing by 40 seconds. It was taking 52 seconds and now takes 10 seconds.