Hello. I'm generating a report that could possibly have 500 rows and over 31 columns. Most of the columns represent a single date in a date range that can go up to a month. The rows represent each of the items in the DB with data for all of those dates. The way I have the SQL now can get huge and not very fast or optimized, yet I don't know how to simplify it. Here's what I mean: SELECT a.CID, a.TID, a.Field3, a.Field4, a.Field5, locc.Field6, tmp<date1ofrange>.Balance AS '<date1ofrange>', tmp<date2ofrange>.Balance AS '<date2ofrange>', tmp<date3ofrange>.Balance AS '<date3ofrange>', ... FROM Term a LEFT JOIN Business locb ON locb.CID = a.CID AND locb.BID = a.LBID LEFT JOIN Contact locc ON locc.CID = locb.CID AND locc.CID = locb.CID JOIN TStatus b on a.TID = b.TID and b.ftrxtime <= '<endofdaterange>' LEFT JOIN (SELECT Sdate, TID, Balance FROM TSummary WHERE CID = 10 AND Sdate = '<date1ofrange>') tmp<date1ofrange> ON a.TID = tmp<date1ofrange>.TID LEFT JOIN (SELECT Sdate, TID, Balance FROM TSummary WHERE CID = 10 AND Sdate = '<date2ofrange>') tmp<date2ofrange> ON a.TID = tmp<date2ofrange>.TID LEFT JOIN (SELECT Sdate, TID, Balance FROM TSummary WHERE CID = 10 AND Sdate = '<date3ofrange>') tmp<date3ofrange> ON a.TID = tmp<date3ofrange>.TID ... ... WHERE a.CID = 10 AND a.TID in (10866,12818,13788,15810,16006,19682,...,...) Code (markup): As you can see, I can have many more items in the SELECT clause, which is fine, but the LEFT JOINs can also go up to 31 which is just too much to me. Plus the TIDs in the WHERE clause is what can go up to 500 or something (500 numbers in the IN part). That, and if I wanted to add a column total to the very last row returned, I'd have to do a UNION ALL and basically copy the query (with some minor changes) and thus double the entire query's size. There's got to be a better way... Any suggestions, please? Maybe a stored procedure? Maybe somehow using the ROLLUP clause for the column totals? Thanks!
Try posting this on stack overflow. You should get a ton of help there quickly. Off hand, this is denormalizing your data on the fly, which is difficult at best and almost always slow and complicated. Your: LEFT JOIN (SELECT Sdate, TID, Balance... statement are most likely causing the speed issues because each of these is performing a query. Without seeing your database structure, and the desired results, I can't say on how to improve, but I'm fairly sure that these are the main problem.
Thanks for the suggestion. I think I actually got it figured out using a single join instead of many, so we can consider this topic closed.