On Wed, Jan 21, 2009 at 5:48 AM, Ricardo Aráoz <[email protected]> wrote: > Malcolm Greene wrote: >> Ricardo! >> >> Holy #&!()! How big are your tables and how long does this process take >> to run? >> > Ok, got the numbers. The tables are 122,406 recs, 62,610 recs, and > 75,117 recs., and the time it takes to perform the queries I posted is > 10 seconds including the writing of a 3,000 line excel sheet. > This system is not mine, it is a semi canned system, the tables are many > times not normalized and are generally kind of a disaster. My app > provide reports and functionalities that management needs and the canned > system does not provide. -------------------------------------------
Is all of your data in SQL Server when this is run, or is only some of it there and you need to mix from other data sources, VFP, Access, Excel? I would still say that if you put this into a SP your going to get better performance, say 10 sec could be cut in half or a third? Was this 2000 or 2005? Some of what your crunching could be done differently in 2005 if available with cross tab functionality using the PIVOT command. USE AdventureWorks; GO SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], [198], [223], [231], [233] ) ) AS pvt ORDER BY pvt.VendorID; Here is a partial result set. VendorID Emp1 Emp2 Emp3 Emp4 Emp5 1 4 3 5 4 4 2 4 1 5 5 5 3 4 3 5 4 4 It creates the temp tables you need and will dispose of them when your through. This is only on 2005 or better now. -- Stephen Russell Sr. Production Systems Programmer First Horizon Bank Memphis TN 901.246-0159 _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
