THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Large Query Performance and Query Parallelism

[2008/08/25] This post has been modified significantly to correct some inaccurate statements because I mis-read Joe Chang's post.

Joe Chang posted some interesting results using the TPC-H queries with the scale factor set to 10. I happened to have done something similar, and naturally noticed a rather significant difference between his results and mine. [2008/08/25: Okay the difference is not as significant as I had thought.]

My results were obtained on SQL Server 2005 Enterprise x64 Edition running on Windows Server 2003 Enterprise x64 Edition. The test server, whose make and model will remain undisclosed,  had four 2.93GHz quad-core Intel Tigerton sockets (Xeon X7350) with 64GB of RAM with 20GB allocated to the SQL Server buffer pool. The TPC-H scale factor was set to 10, and the data were generated with DBGEN from tpc.org.

My results are as follows:

Query    MAXDOP 1    MAXDOP 4   MAXDOP 8     MAXDOP 16
tpcH Query1 64845 17080 9373 4865
tpcH Query2 571 180 110 97
tpcH Query3 17687 3272 1792 1334
tpcH Query4 23079 3859 2293 1633
tpcH Query5 34255 5040 2646 1935
tpcH Query6 2250 595 332 204
tpcH Query7 18299 3562 1754 1513
tpcH Query8 9636 3245 2042 1809
tpcH Query9 50470 14094 7559 4460
tpcH Query10 19363 4432 2289 1630
tpcH Query11 4558 674 424 390
tpcH Query12 10522 3815 2718 982
tpcH Query13 26823 7577 4425 2121
tpcH Query14 2356 624 357 227
tpcH Query15 1705 1421 280 170
tpcH Query16 7960 3461 2155 1681
tpcH Query17 1070 325 220 276
tpcH Query18 68366 19719 10174 5829
tpcH Query19 2330 711 441 843
tpcH Query20 968 314 221 695
tpcH Query21 80858 14565 8149 6072
tpcH Query22 9418 1762 986 664
         
Total (second) 457 110 61 39

All the numbers in the above table are query elapsed times in milliseconds except the very last line highlighted in yellow, which are in seconds. The last line contains the total elapsed time for Query1 through Query22 for each MAXDOP setting. All the results were obtained with all the database pages cached in the buffer pool. The same TPC-H query stream (i.e. Query 1 through Query 22) was repeated for 10 times with all the results from the first run thrown out, and the max elapsed time and the min elapsed time for each query for each of the subsequent runs were also thrown out before the elapsed times were averaged.

So what's the difference between Joe's results and mine?

Although there are some differences in the elapsed times in terms of the absolute numbers, the differences are minor enough to be no cause for alarm, especially when you take into consideration the platform differences and probably some differences in the test setup.

However, the deline in the elapsed times in my test results is more significant than it is in Joe's results when MAXDOP is changed from 1 through 16. The difference is not as dramatic as I had throught, and initially commented on in the original version of this post (because I was reading the wrong line for whatever reason).

Given that TPC-H has been around for a long time and all DBMS vendors have tried very hard to optimize their products to performance well on TPC-H, I do not expect SQL Server to be an exception, and am rather pleased to see that it scales very well with MAXDOP on these queries.

I'm a bit embarrassed that I didn't read Joe's results correctly first time around, but felt that it's time well spent to be able to cross check independent tests results from realistic envrionments.

Published Sunday, August 24, 2008 2:32 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jchang said:

I do not see how you can conclude your scaling is better than mine.

plus, you should always do DOP 2, so you can compare DOP 1, 2, 4 etc.

too much happens between 1-4 to miss.

I have included my individual query times for you to look at.

There is something not right on your DOP 1, it should not take that long. I suggest confirming that you are using date conversions where appropriate, per my post.

Differences between your system and mine:

2.93GHz vs. 2.66GHz

2x4M L2 vs. 2x6M L2

1066FSB vs. 1333MHz FSB

8 DDR ch vs. 4 DDR memory channels

Cache does not help tpch, so I think your system should be 7% faster than mine, more if it has a good memory controller.

August 25, 2008 12:35 AM
 

jchang said:

my DOP 1, 2, 4, 8 times (Total in milli-sec) for SQL 2008

375  171  94  57

vs above for DOP 1, 4, 8

457  cxx 110  61

August 25, 2008 12:40 AM
 

jchang said:

my bad, I see you are on SQL 2005, RTM or sp2? or CU9? it matters

my RTM and SP2 totals for DOP 1, 2, 4

RTM 379,563 194,199 107,409 65,094

SP2 370,374 166,579 94,844 59,388

I still do not know why you DOP 1 result is so bad, how many disks are supporting your temp?

can you find a 2-way Xeon 5400 series to run on?

August 25, 2008 8:12 AM
 

Linchi Shea said:

Joe;

After your first comment, I re-read your post. I mis-read your original post, and some statements in my post above are not accurate. I'll have to modify my post.

August 25, 2008 8:35 AM
 

Linchi Shea said:

I've modofied the post to include the corrections.

Joe,

I didn't see any material disk activity during the test. So I'm not sure whether tempdb performance matters that much. I'm double checking whether this has anything at all to do with a Perl wrapper I was using. The SQL2005 version is 9.00.3239.

Also, I didn't follow the strict TPC-H rule in randomizing the some of the query parameters. In other words, I didn't use QGEN. I used the validation queries with the 'parameters' fixed throughout my tests.

August 25, 2008 10:44 AM
 

jchang said:

I use the parameter values from HP's ProLiant pubs, I don't think they have changed in years. For Q1, the SARG is:

WHERE L_SHIPDATE <= dateadd(dd, -90, '1998-12-01')

3239 is cu7, I tested sp2 and cu6, showing minimal differences. On disk, look specifically for tempdb activity, also try increase max memory to 24GB, this might affect hash ops spooling to temp. Also, do maxdop 2, and include the tot cpu time for  DOP1, mine was 375, identical with duration, so I know I had the one CPU pegged.

August 25, 2008 11:46 AM
 

Linchi Shea said:

That's the validation parameter value as per the TPC-H specs. I think we are using the same queries. The queries I used are as follows:

/* TPC_H Query 1 - Pricing Summary Report */

SELECT L_RETURNFLAG,

L_LINESTATUS,

SUM(L_QUANTITY) AS SUM_QTY,

SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE,

SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE,

SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE,

AVG(L_QUANTITY) AS AVG_QTY,

AVG(L_EXTENDEDPRICE) AS AVG_PRICE,

AVG(L_DISCOUNT) AS AVG_DISC,

COUNT(*) AS COUNT_ORDER

 FROM LINEITEM

WHERE L_SHIPDATE <= dateadd(dd, -90, '1998-12-01')

GROUP BY L_RETURNFLAG, L_LINESTATUS

ORDER BY L_RETURNFLAG, L_LINESTATUS

/* TPC_H Query 2 - Minimum Cost Supplier */

SELECT TOP 100 S_ACCTBAL,

S_NAME, N_NAME,

P_PARTKEY,

P_MFGR,

S_ADDRESS,

S_PHONE,

S_COMMENT

 FROM PART, SUPPLIER, PARTSUPP,NATION, REGION

WHERE P_PARTKEY = PS_PARTKEY

  AND S_SUPPKEY = PS_SUPPKEY

  AND P_SIZE = 15

  AND P_TYPE LIKE '%%BRASS'

  AND S_NATIONKEY = N_NATIONKEY

  AND N_REGIONKEY = R_REGIONKEY

  AND R_NAME = 'EUROPE'

  AND PS_SUPPLYCOST = (SELECT MIN(PS_SUPPLYCOST)

                         FROM PARTSUPP, SUPPLIER, NATION, REGION

                        WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY

                          AND S_NATIONKEY= N_NATIONKEY

                          AND N_REGIONKEY = R_REGIONKEY

                          AND R_NAME = 'EUROPE')

ORDER BY S_ACCTBAL DESC, N_NAME, S_NAME, P_PARTKEY

/* TPC_H Query 3 - Shipping Priority */

SELECT TOP 10 L_ORDERKEY,

  SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))AS REVENUE,

  O_ORDERDATE, O_SHIPPRIORITY

 FROM CUSTOMER, ORDERS, LINEITEM

WHERE C_MKTSEGMENT = 'BUILDING'

  AND C_CUSTKEY = O_CUSTKEY

  AND L_ORDERKEY = O_ORDERKEY

  AND O_ORDERDATE < '1995-03-15'

  AND L_SHIPDATE > '1995-03-15'

GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY

ORDER BY REVENUE DESC, O_ORDERDATE

/* TPC_H Query 4 - Order Priority Checking */

SELECT O_ORDERPRIORITY,

COUNT(*) AS ORDER_COUNT

 FROM ORDERS

WHERE O_ORDERDATE >= '1993-07-01'

  AND O_ORDERDATE < dateadd (mm, 3, '1993-07-01')

  AND EXISTS (SELECT * FROM LINEITEM

               WHERE L_ORDERKEY = O_ORDERKEY

                 AND L_COMMITDATE < L_RECEIPTDATE)

GROUP BY O_ORDERPRIORITY

ORDER BY O_ORDERPRIORITY

/* TPC_H Query 5 - Local Supplier Volume */

SELECT N_NAME,

SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE

 FROM CUSTOMER, ORDERS, LINEITEM, SUPPLIER, NATION, REGION

WHERE C_CUSTKEY = O_CUSTKEY

  AND L_ORDERKEY = O_ORDERKEY

  AND L_SUPPKEY = S_SUPPKEY

  AND C_NATIONKEY = S_NATIONKEY

  AND S_NATIONKEY = N_NATIONKEY

  AND N_REGIONKEY = R_REGIONKEY

  AND R_NAME = 'ASIA'

  AND O_ORDERDATE >= '1994-01-01'

  AND O_ORDERDATE < DATEADD(YY, 1, '1994-01-01')

GROUP BY N_NAME

ORDER BY REVENUE DESC

/* TPC_H Query 6 - Forecasting Revenue Change */

SELECT SUM(L_EXTENDEDPRICE*L_DISCOUNT) AS REVENUE

 FROM LINEITEM

WHERE L_SHIPDATE >= '1994-01-01'

  AND L_SHIPDATE < dateadd (yy, 1, '1994-01-01')

  AND L_DISCOUNT BETWEEN .06 - 0.01

  AND .06 + 0.01

  AND L_QUANTITY < 24

/* TPC_H Query 7 - Volume Shipping */

SELECT SUPP_NATION,

CUST_NATION,

L_YEAR, SUM(VOLUME) AS REVENUE

 FROM ( SELECT N1.N_NAME AS SUPP_NATION,

               N2.N_NAME AS CUST_NATION,

               datepart(yy, L_SHIPDATE) AS L_YEAR,

               L_EXTENDEDPRICE*(1-L_DISCOUNT) AS VOLUME

          FROM SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2

         WHERE S_SUPPKEY = L_SUPPKEY

           AND O_ORDERKEY = L_ORDERKEY  

           AND C_CUSTKEY = O_CUSTKEY

           AND S_NATIONKEY = N1.N_NATIONKEY

           AND C_NATIONKEY = N2.N_NATIONKEY

           AND ((N1.N_NAME = 'FRANCE' AND N2.N_NAME = 'GERMANY') OR (N1.N_NAME = 'GERMANY' AND N2.N_NAME = 'FRANCE'))

           AND L_SHIPDATE BETWEEN '1995-01-01' AND '1996-12-31' ) AS SHIPPING

GROUP BY SUPP_NATION, CUST_NATION, L_YEAR

ORDER BY SUPP_NATION, CUST_NATION, L_YEAR

/* TPC_H Query 8 - National Market Share */

SELECT O_YEAR,

SUM(CASE WHEN NATION = 'BRAZIL' THEN VOLUME ELSE 0 END) / SUM(VOLUME) AS MKT_SHARE

 FROM (SELECT datepart(yy, O_ORDERDATE) AS O_YEAR,

              L_EXTENDEDPRICE * (1-L_DISCOUNT) AS VOLUME,

              N2.N_NAME AS NATION

         FROM PART, SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2, REGION

        WHERE P_PARTKEY = L_PARTKEY

          AND S_SUPPKEY = L_SUPPKEY

          AND L_ORDERKEY = O_ORDERKEY

          AND O_CUSTKEY = C_CUSTKEY

          AND C_NATIONKEY = N1.N_NATIONKEY

          AND N1.N_REGIONKEY = R_REGIONKEY

          AND R_NAME = 'AMERICA'

          AND S_NATIONKEY = N2.N_NATIONKEY

          AND O_ORDERDATE BETWEEN '1995-01-01'

          AND '1996-12-31'

          AND P_TYPE= 'ECONOMY ANODIZED STEEL') AS ALL_NATIONS

GROUP BY O_YEAR

ORDER BY O_YEAR

/* TPC_H Query 9 - Product Type Profit Measure */

SELECT NATION,

O_YEAR,

SUM(AMOUNT) AS SUM_PROFIT

 FROM (SELECT N_NAME AS NATION,

              datepart(yy, O_ORDERDATE) AS O_YEAR,

              L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY AS AMOUNT

         FROM PART, SUPPLIER, LINEITEM, PARTSUPP, ORDERS, NATION

        WHERE S_SUPPKEY = L_SUPPKEY

          AND PS_SUPPKEY= L_SUPPKEY

          AND PS_PARTKEY = L_PARTKEY

          AND P_PARTKEY= L_PARTKEY

          AND O_ORDERKEY = L_ORDERKEY

          AND S_NATIONKEY = N_NATIONKEY

          AND P_NAME LIKE '%%green%%') AS PROFIT

GROUP BY NATION, O_YEAR

ORDER BY NATION, O_YEAR DESC

/* TPC_H Query 10 - Returned Item Reporting */

SELECT TOP 20 C_CUSTKEY,

C_NAME,

SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE,

C_ACCTBAL,

N_NAME,

C_ADDRESS,

C_PHONE,

C_COMMENT

 FROM CUSTOMER, ORDERS, LINEITEM, NATION

WHERE C_CUSTKEY = O_CUSTKEY

  AND L_ORDERKEY = O_ORDERKEY

  AND O_ORDERDATE>= '1993-10-01'

  AND O_ORDERDATE < dateadd(mm, 3, '1993-10-01')

  AND L_RETURNFLAG = 'R'

  AND C_NATIONKEY= N_NATIONKEY

GROUP BY C_CUSTKEY, C_NAME, C_ACCTBAL, C_PHONE, N_NAME, C_ADDRESS, C_COMMENT

ORDER BY REVENUE DESC

/* TPC_H Query 11 - Important Stock Indentification */

SELECT PS_PARTKEY,

      SUM(PS_SUPPLYCOST*PS_AVAILQTY) AS VALUE

 FROM PARTSUPP, SUPPLIER, NATION

WHERE PS_SUPPKEY = S_SUPPKEY

  AND S_NATIONKEY = N_NATIONKEY

  AND N_NAME = 'GERMANY'

GROUP BY PS_PARTKEY

HAVING SUM(PS_SUPPLYCOST*PS_AVAILQTY) > (SELECT SUM(PS_SUPPLYCOST*PS_AVAILQTY) * 0.0001000000

                                          FROM PARTSUPP, SUPPLIER, NATION

                                         WHERE PS_SUPPKEY = S_SUPPKEY

                                           AND S_NATIONKEY = N_NATIONKEY

                                           AND N_NAME = 'GERMANY')

ORDER BY VALUE DESC

/* TPC_H Query 12 - Shipping Modes and Order Priority */

SELECT L_SHIPMODE,

SUM(CASE WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH' THEN 1 ELSE 0 END) AS HIGH_LINE_COUNT,

SUM(CASE WHEN O_ORDERPRIORITY <> '1-URGENT' AND O_ORDERPRIORITY <> '2-HIGH' THEN 1 ELSE 0 END) AS LOW_LINE_COUNT

 FROM ORDERS, LINEITEM

WHERE O_ORDERKEY = L_ORDERKEY

  AND L_SHIPMODE IN ('MAIL','SHIP')

  AND L_COMMITDATE < L_RECEIPTDATE

  AND L_SHIPDATE < L_COMMITDATE AND L_RECEIPTDATE >= '1994-01-01'

  AND L_RECEIPTDATE < dateadd(yy, 1, '1994-01-01')

GROUP BY L_SHIPMODE

ORDER BY L_SHIPMODE

/* TPC_H Query 13 - Customer Distribution */

SELECT C_COUNT, COUNT(*) AS CUSTDIST

 FROM (SELECT C_CUSTKEY,

              COUNT(O_ORDERKEY)

         FROM CUSTOMER left outer join ORDERS

                 on C_CUSTKEY = O_CUSTKEY AND O_COMMENT not like '%%special%%requests%%'

       GROUP BY C_CUSTKEY) AS C_ORDERS (C_CUSTKEY, C_COUNT)

GROUP BY C_COUNT

ORDER BY CUSTDIST DESC, C_COUNT DESC

/* TPC_H Query 14 - Promotion Effect */

SELECT 100.00 * SUM (CASE WHEN P_TYPE LIKE 'PROMO%%' THEN L_EXTENDEDPRICE*(1-L_DISCOUNT)

                         ELSE 0

                     END) / SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS PROMO_REVENUE

 FROM LINEITEM, PART

WHERE L_PARTKEY = P_PARTKEY AND L_SHIPDATE >= '1995-09-01' AND L_SHIPDATE < dateadd(mm, 1, '1995-09-01')

/* TPC_H Query 15 - Top Supplier */

SELECT S_SUPPKEY,

S_NAME,

S_ADDRESS,

S_PHONE,

TOTAL_REVENUE

 FROM SUPPLIER, REVENUE0

WHERE S_SUPPKEY = SUPPLIER_NO AND TOTAL_REVENUE = ( SELECT MAX(TOTAL_REVENUE) FROM REVENUE0)

ORDER BY S_SUPPKEY

/* TPC_H Query 16 - Parts/Supplier Relationship */

SELECT P_BRAND,

P_TYPE,

P_SIZE,

COUNT(DISTINCT PS_SUPPKEY) AS SUPPLIER_CNT

 FROM PARTSUPP, PART

WHERE P_PARTKEY = PS_PARTKEY

  AND P_BRAND <> 'Brand#45'

  AND P_TYPE NOT LIKE 'MEDIUM POLISHED%%'

  AND P_SIZE IN (49, 14, 23, 45, 19, 3, 36, 9)

  AND PS_SUPPKEY NOT IN (SELECT S_SUPPKEY FROM SUPPLIER

                          WHERE S_COMMENT LIKE '%%Customer%%Complaints%%')

GROUP BY P_BRAND, P_TYPE, P_SIZE

ORDER BY SUPPLIER_CNT DESC, P_BRAND, P_TYPE, P_SIZE

/* TPC_H Query 17 - Small-Quantity-Order Revenue */

SELECT SUM(L_EXTENDEDPRICE)/7.0 AS AVG_YEARLY

 FROM LINEITEM, PART

WHERE P_PARTKEY = L_PARTKEY

  AND P_BRAND = 'Brand#23'

  AND P_CONTAINER = 'MED BOX'

  AND L_QUANTITY<(SELECT 0.2 * AVG(L_QUANTITY) FROM LINEITEM WHERE L_PARTKEY = P_PARTKEY)

/* TPC_H Query 18 - Large Volume Customer */

SELECT TOP 100 C_NAME,

C_CUSTKEY,

O_ORDERKEY,

O_ORDERDATE,

O_TOTALPRICE,

SUM(L_QUANTITY)

 FROM CUSTOMER, ORDERS, LINEITEM

WHERE O_ORDERKEY IN (SELECT L_ORDERKEY

                       FROM LINEITEM

                     GROUP BY L_ORDERKEY

                     HAVING SUM(L_QUANTITY) > 300)

  AND C_CUSTKEY = O_CUSTKEY

  AND O_ORDERKEY = L_ORDERKEY

GROUP BY C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE

ORDER BY O_TOTALPRICE DESC, O_ORDERDATE

/* TPC_H Query 19 - Discounted Revenue */

SELECT SUM(L_EXTENDEDPRICE* (1 - L_DISCOUNT)) AS REVENUE

 FROM LINEITEM, PART

WHERE (    P_PARTKEY = L_PARTKEY

       AND P_BRAND = 'Brand#12'

       AND P_CONTAINER IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')

       AND L_QUANTITY >= 1

       AND L_QUANTITY <= 1 + 10

       AND P_SIZE BETWEEN 1 AND 5

       AND L_SHIPMODE IN ('AIR', 'AIR REG')

       AND L_SHIPINSTRUCT = 'DELIVER IN PERSON')

   OR (    P_PARTKEY = L_PARTKEY

       AND P_BRAND = 'Brand#23'

       AND P_CONTAINER IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')

       AND L_QUANTITY >= 10 AND L_QUANTITY <= 10 + 10 AND P_SIZE BETWEEN 1 AND 10

       AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON')

   OR (    P_PARTKEY = L_PARTKEY

       AND P_BRAND = 'Brand#34'

       AND P_CONTAINER IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')

       AND L_QUANTITY >= 20

       AND L_QUANTITY <= 20 + 10

       AND P_SIZE BETWEEN 1 AND 15

       AND L_SHIPMODE IN ('AIR', 'AIR REG')

       AND L_SHIPINSTRUCT = 'DELIVER IN PERSON')

/* TPC_H Query 20 - Potential Part Promotion */

SELECT S_NAME,

S_ADDRESS

 FROM SUPPLIER, NATION

WHERE S_SUPPKEY IN ( SELECT PS_SUPPKEY

                       FROM PARTSUPP

                      WHERE PS_PARTKEY in (SELECT P_PARTKEY FROM PART WHERE P_NAME like 'forest%%')

                        AND PS_AVAILQTY > (SELECT 0.5 * sum(L_QUANTITY)

                                             FROM LINEITEM

                                            WHERE L_PARTKEY = PS_PARTKEY

                                              AND L_SUPPKEY = PS_SUPPKEY

                                              AND L_SHIPDATE >= '1994-01-01'

                                              AND L_SHIPDATE < dateadd(yy,1,'1994-01-01'))

                   )

  AND S_NATIONKEY = N_NATIONKEY

  AND N_NAME = 'CANADA'

ORDER BY S_NAME

/* TPC_H Query 21 - Suppliers Who Kept Orders Waiting */

SELECT TOP 100 S_NAME,

COUNT(*) AS NUMWAIT

 FROM SUPPLIER, LINEITEM L1, ORDERS, NATION

WHERE S_SUPPKEY = L1.L_SUPPKEY

  AND O_ORDERKEY = L1.L_ORDERKEY

  AND O_ORDERSTATUS = 'F'

  AND L1.L_RECEIPTDATE> L1.L_COMMITDATE

  AND EXISTS (SELECT * FROM LINEITEM L2

               WHERE L2.L_ORDERKEY = L1.L_ORDERKEY

                 AND L2.L_SUPPKEY <> L1.L_SUPPKEY)

  AND NOT EXISTS (SELECT * FROM LINEITEM L3

                   WHERE L3.L_ORDERKEY = L1.L_ORDERKEY

                     AND L3.L_SUPPKEY <> L1.L_SUPPKEY

                     AND L3.L_RECEIPTDATE > L3.L_COMMITDATE)

  AND S_NATIONKEY = N_NATIONKEY

  AND N_NAME = 'SAUDI ARABIA'

GROUP BY S_NAME

ORDER BY NUMWAIT DESC, S_NAME

/* TPC_H Query 22 - Global Sales Opportunity */

SELECT CNTRYCODE,

COUNT(*) AS NUMCUST,

SUM(C_ACCTBAL) AS TOTACCTBAL

 FROM (SELECT SUBSTRING(C_PHONE,1,2) AS CNTRYCODE,

              C_ACCTBAL

         FROM CUSTOMER

        WHERE SUBSTRING(C_PHONE,1,2) IN ('13', '31', '23', '29', '30', '18', '17')

          AND C_ACCTBAL > (SELECT AVG(C_ACCTBAL)

                             FROM CUSTOMER WHERE C_ACCTBAL > 0.00

                              AND SUBSTRING(C_PHONE,1,2) IN ('13', '31', '23', '29', '30', '18', '17')

                          )

          AND NOT EXISTS ( SELECT * FROM ORDERS WHERE O_CUSTKEY = C_CUSTKEY)

                          ) AS CUSTSALE

GROUP BY CNTRYCODE

ORDER BY CNTRYCODE

August 25, 2008 1:52 PM
 

jchang said:

you weren't thinking I had a 16-core 4 socket system at home did you?

those monsters are 1.5kW, and will overload your typical residential 15A line, ie, I don't trust that the 15A line will handle 15A without risk of fire. I suppose I could unplug one of the appliances on a 30A line. Still, 240V is much better. I looked into renting space with a hosting company once, so I could access my servers from anywhere. I was quoted $400/month for 1/2 rack, $200 for 20A electricity, and $75 for internet. Well, I will just live with using the home office.

August 25, 2008 3:09 PM
 

Linchi Shea said:

I'm modifying a C# wrapper to run these queries in multiple streams, kind of similar to the TPC-H throughput test. A single stream even with MAXDOP set toa igher may be able to drive a server hard enough. I was using a Perl script, but Perl is lousy in controlling multiple threads/processes exactly.

August 25, 2008 6:45 PM
 

Thomas said:

We tried to bulk insert into a view within a partition table, this will be slow down it?

What is the fastest way (query) to search following in 2 Tara partition table(by datetime, in phone call records).

1 day 4 partitions X 30=120 partition

ToNumber (int)

FromNumber (int)

Duration (int)

1)The most high number of 3 ToNumber.

2)A called B During =0 and within 20 seconds B called A and duration > 300.

"Group by" will take long time to run. Who has best idea for this?

September 12, 2008 7:38 AM
 

jchang said:

for ad-hoc, ie, no pre-built indexes, queries of this nature will involve full table scans, so what is too long for you? For very large narrow tables (>100 rows per page, <80 bytes/row) figure a table scan to disk will run around 100-200MB/sec per core. On a 16 core system, figure you will get 1-2GB/sec, meaning a full table scan on 2TB will take 1000-2000 sec (or 16-32min). I would want to make sure my disk system can deliver 2-4GB/sec. For query 1, you will probably want an indexed view in case some one later want additional high callers. For 2, 1-2 tables scans are required, depending on the number of 0 min calls. This is why you need a powerful storage system.

September 19, 2008 9:20 AM
 

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:38 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement