TPC BENCHMARK (TM) H (779138), страница 28
Текст из файла (страница 28)
The SQL3 standard, which was moved to an Approved Committee Draft in May 1996, contains the definition of common tableexpressions. TPC-H already makes extensive use of nested table expressions. Common table expressions can bethought of as shared table expressions or "inline views" that last only for the duration of the query.with revenue (supplier_no, total_revenue) as (selectl_suppkey,sum(l_extendedprice * (1-l_discount))fromlineitemwherel_shipdate >= date '[DATE]'and l_shipdate < date '[DATE]' + interval '3' monthgroup byl_suppkeyTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 127)selects_suppkey,s_name,s_address,s_phone,total_revenuefromsupplier,revenuewheres_suppkey = supplier_noand total_revenue = (selectmax(total_revenue)fromrevenue)order bys_suppkey;TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 128Appendix C: QUERY VALIDATIONThis appendix contains the output data for validation of executable query text against the qualification database.- This appendix is available in machine-readable format only To obtain a copy of the machine-readable appendices, please contact the TPC (see Cover page).TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 129Appendix D: DATA AND QUERY GENERATION PROGRAMSThe QGEN (see Clause 2.1.4) and DBGEN (see Clause 4.2.1) programs should be used to generate the executablequery text and the data that populate the TPC-H Databases.
These programs produce flat files that can be used by thetest sponsor to implement the benchmark.- This appendix is available in machine readable format only To obtain a copy of the machine readable appendices, please contact the TPC (see Cover page).TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 130Appendix E: SAMPLE EXECUTIVE SUMMARYThis appendix includes a sample Executive Summary.See Clause 8.4 for a detailed description of the required format of the Executive Summary. This sample isprovided only as an illustration of the requirements set forth in Clause 8.4 of the specification. In the event of aconflict between this example and the specification, the specification shall prevail.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 131My LogoMy SystemMy LogoMy SystemTotal System CostComposite Query perHour MetricTotal System CostComposite Query per Hour Metric$31,322 USD$31,322 USDDatabase SizeDatabase Size123,543.20123,543.20QphH@1000GBQphH@1000GBDatabase ManagerOperating SystemDatabase Manager1000GB*1000GB*MyDatabaseDatabaseMyOperating SystemMyMyOSOSTPC-H Rev.
2.14.3TPC-HRev.Rev.2.14.3TPCPricing1.6.0TPC PricingRev.1.6.0Report Date:ReportDate: 11-Nov-11November11, 2011Revised: 24-Dec-11Price/PerformancePrice/Performance$0.26 USDUSD$0.26Price/QphH@1000GBPrice/QphH@1000GBOther Software Availability DateOther SoftwareAvailability Daten/an/a4/11/20124/11/2012Database Load Time:Load Includes Backup: NMemory Ratio: 60%Total Data Storage/Database Size: 4DatabaseLoad Includes Backup: NMemory Ratio: 60%Total Data Storage/Database Size: 402:34:12Load Time: 02:34:12StorageStorageRedundancyLevel:3 3Base BaseTable:RAID-10Data Structures:RAID-10Other:RAID-10RedundancyLevel:Table:RAID-10 AuxiliaryAuxiliaryData Structures:RAID-10Other: RAID-10SystemConfigurationSystemConfigurationNumberof Nodes:Numberof Nodes:Processor/Cores/Treads/Type:Processor/Cores/Treads/Type:Memory:Memory:Disk Drives:Disk Drives:Total Disk Storage:Total LanDiskControllersStorage:LAN Controllers114/16/32myCPUmyCPU2.0GHz,2.0GHz,3MB3MBL3L3 cachecacheperper corecore4/16/32384 GB384 GB2 Storage Arrays, each with 10 x 180GB 15Krpm SATA Disks24Storageeachwith 10x 180GBx 100GBArrays,Internal15KrpmSASDisks 15Krpm SATA Disks44,000GBx 100GB Internal 15Krpm SAS Disks1 x 100Mb PCI LAN card4,000GB1 x 100Mb PCI LAN card* Database Size includes only raw data (e.g., no temp, index, redundant storage space, etc.)* Database Size includes only raw data (e.g., no temp, index, redundant storage space, etc.)TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 132My LogoTPC-H Rev.
2.14.3TPC Pricing Rev. 1.6.0Report Date: 11-Nov-11Revised: 24-Dec-11My SystemDescriptionServer HardwareMyCo ServerMyCo 4GB Reg PC3200 2X2GB Memory100GB 15Krpm U320 SAS HDDMyCo Fiber Channel AdapterMyCo Care Pack 3-year, 4-hour, 7x24MyCo rackDiscntCo KB & MouseDiscntCo 17in LCDServer SoftwareMyDB FastDBMS Core LicenseMyDB FastDBMS Support 4-hour, 7x24MyDB MyUNIX ServerStorageMyCo Storage Array180GB 15Krpm SF SATA HDDMyCo Array Care 3-year, 4-hour, 7x24MyCo SAN Switch (inc. spare)MyCo Fiber Channel Cable (5m) (inc. spares)PartNumberSourceabcd123456abcd123457abcd123458abcd123459abcd123410abcd123411Dis2345Dis234711111133xyz432xyz433xyz123stqw876stqw871stqw872stqw875stqw87322211111UnitPrice12,0003002105841,234500702004,1001,7001,5003,0004107323,00072Qty12411111Subtotal12,0003002105841631Subtotal8,200Source: 1=MyCo, 2=MyDB, 3=DiscntCo3 yr Maint.Price1,23450021060014,4041,2345,1003,00011,2005,100120133Subtotal3,0004103,000726,482732Total32,086(6,417)7,066(1,413)Grand Total25,6695,6533-year Cost of Ownership:QphH@1000GB:$/QphH@1000GB:31,321.60123,543.200.26Discount ** All discounts are based on US list prices and for similar quantities and configurationsExtendedPrice732Audited by: John Smith for AuditorCoPrices used in TPC benchmarks reflect the actual prices a customer would pay for a one-time purchase of the statedcomponents.
Individually negotiated discounts are not permitted. Special prices based on assumptions about past orfuture purchases are not permitted. All discounts reflect standard pricing policies for the listed components. Forcomplete details, see the pricing section of the TPC benchmark specifications. If you find that the stated prices are notavailable according to these terms, please inform the TPC at pricing@tpc.org. Thank you.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 133My LogoTPC-H Rev.
2.14.3TPC Pricing Rev. 1.6.0Report Date: 11-Nov-11Revised: 24-Dec-11My SystemMeasurement ResultsDatabase Scaling (SF/Size)1,000Total Data Storage/Database Size8.78Percentage Memory/Database Size102%Start of Database Load Time14/08/11 19:36:22End of Database Load Time15/08/11 16:40:41Database Load Time21:04:19Query Streams for Throughput Test (S)7TPC-H Power156,157.2TPC-H Throughput115,188.0TPC-H Composite123,543.2Total System Price Over 3 Years198,788TPC-H Price/Performance Metric ($/QphH@1000GB)1.49Measurement IntervalMeasurement Interval in Throughput Test (Ts)4,813Duration of stream execution:SeedPowerRun0815164040ThroughputStreamSeed10815164041208151640423081516404340815164044508151640456081516404670815164047Query Start TimeQuery End Time08/15/2011 19:43:2908/15/2011 20:01:12Query Start TimeQuery End Time08/15/2011 20:01:4308/15/2011 21:06:4708/15/2011 20:01:4308/15/2011 21:10:2108/15/2011 20:01:4308/15/2011 21:06:2508/15/2011 20:01:4308/15/2011 21:10:3808/15/2011 20:01:4308/15/2011 21:06:0708/15/2011 20:01:4308/15/2011 21:12:5408/15/2011 20:01:4308/15/2011 21:04:50Duration(sec)1,063Duration(sec)3,9054,1193,8824,1353,8644,2713,787TPC BenchmarkTM H Standard Specification Revision 2.17.1RF1 Start TimeRF2 Start TimeRF1 End TimeRF2 End Time08/15/2011 19:42:4808/15/2011 20:01:1308/15/2011 19:43:2908/15/2011 20:01:42RF1 Start TimeRF2 Start TimeRF1 End TimeRF2 End Time08/15/2011 21:12:5508/15/2011 21:13:5108/15/2011 21:13:5108/15/2011 21:14:2208/15/2011 21:14:2208/15/2011 21:15:0808/15/2011 21:15:0708/15/2011 21:15:3608/15/2011 21:15:3608/15/2011 21:16:1808/15/2011 21:16:1808/15/2011 21:16:4708/15/2011 21:16:4808/15/2011 21:17:3008/15/2011 21:17:2908/15/2011 21:18:0008/15/2011 21:18:0008/15/2011 21:18:4008/15/2011 21:18:4008/15/2011 21:19:1208/15/2011 21:19:1308/15/2011 21:20:0008/15/2011 21:20:0008/15/2011 21:20:3508/15/2011 21:20:3508/15/2011 21:21:2208/15/2011 21:21:2208/15/2011 21:21:55Page 134My LogoTPC-H Rev.
2.14.3TPC Pricing Rev. 1.6.0Report Date: 11-Nov-11Revised: 24-Dec-11My SystemTPC-H Timing Intervals (in seconds)Duration of query execution:Stream IDQ1Q2Q3Q4Q5Q6Q7Q8Q9Q10Q11Q12097.11.915.98.018.810.814.518.8162.211.893.851.91485.248.2127.030.978.621.3136.9163.5387.963.5240.9129.12601.727.6113.245.294.223.756.190.8404.166.7194.8427.83508.941.6100.448.292.962.8125.287.9390.860.9129.5135.04551.99.646.237.586.777.363.6121.2654.278.439.8103.85527.925.979.059.179.642.7101.280.3379.759.6136.7157.06597.320.5103.136.980.326.2150.0103.1490.652.7466.9178.17513.997.1108.238.488.720.790.081.9428.461.0107.891.5Minimum97.11.915.98.018.810.814.518.8162.211.839.851.9Maximum601.797.1127.059.194.277.3150.0163.5654.278.4466.9427.8Average485.534.186.638.077.535.792.293.4412.256.8176.3159.3RF1RF2Stream IDQ13Q14Q15Q16Q17Q18Q19Q20Q21Q22046.75.25.218.512.0151.718.511.9274.313.441.229.61119.523.428.876.139.0780.9133.779.2636.075.256.130.62193.937.235.195.082.1774.6133.4102.9363.2155.445.028.43206.022.338.693.464.6708.068.6164.1674.657.141.628.74198.322.043.479.858.2919.7124.774.7678.665.141.729.95213.126.434.086.540.8759.598.7104.2639.3132.739.432.36279.521.442.091.774.5872.681.4112.4320.668.846.834.97224.917.756.9102.744.0887.697.7108.1464.255.546.433.0Minimum46.75.25.218.512.0151.718.511.9274.313.439.428.4Maximum279.537.256.9102.782.1919.7133.7164.1678.6155.456.134.9Average185.221.935.580.551.9731.894.694.7506.377.944.830.9TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 135Appendix F: REFERENCE DATA SETThe content for this appendix is not included here.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
