Stephen Russell wrote: > 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? > All these tables are in SQL Server, but due to an error in VFP - SQLServer communication I'm getting the temporary result sets and doing the final queries over them in VFP cursors. I have other reports that combine SQLServer with Progress, access, and I do the final joining of cursors that come from the three databases in VFP. We don't use work data in VFP (just the definition tables for data driving my app). > 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? > How come? My line of thought is that a select is optimized by the server, which will probably do a much better job than that which I'll be able to do accessing the table a record at a time (unless there was a special case that justifies it, don't think this is it). > 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. > I don't have that, this is 2000. Anyway as I understand it "PIVOT" is to transpose totals from rows into columns, this is not the requirement I had in this case.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ 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.
