性能测试详细情况
执行以下的测试手段以验证性能：
◆基于6千亿条记录的多表连接
◆在已有6千亿条记录的情况下追加事实数据
◆基于6千亿条记录的并行加载与查询
◆基于1万亿条记录的多表连接
多表连接– 定义了一组跨表连接（见后述），并基于6千亿条记录执行查询。这些查询被一条一条的执行以获取基准响应时间。被测试的响应时间在5秒到500秒之间。
追加事实数据– 在已有6千亿条记录的情况下，建立新的事实表。向新建的事实表追加新的数据，测量该加载速度以建立一个基准。接着删除该表，建立两张新表，同时向两张新表中追加数据，同样，测量并行加载的速度也可以建立一个基准。在并行加载的情况下，合计的加载速度为每分钟3千万条。
并行加载与查询– 在执行两表并行加载的同时，不断增加执行的查询数目，直到有8条查询并行运行于同一个节点服务器。然后在另一台节点服务器上同时执行8条查询，共享同一个数据库。
在两台节点服务器上并行执行16条查询对两表并行加载的影响非常小，如下图所示，25分钟内有6700万条记录被加载。
包含10个连接查询的16条查询彼此之间对响应时间的影响也非常小（增加了不到10秒），相比于增加了5倍的查询流量。如下图所示：
基于1万亿行的连接查询– 在事实表行数增加到1万亿行之后，从查询集中挑选了一些查询进行执行。对查询语句“Where”从句中的时间参数进行调整以覆盖所有事实表中的时间范围。
通过对由Sybase IQ查询优化器选择的执行计划进行研究，证实了在这个巨大的一万亿行的“UNION ALL”的视图中，所有事实表都可以被这些查询访问到。尽管数据库的大小增加了65%，达到创记录的1万亿行，查询响应速度与先前测试的并行查询相比，并没有明显的影响。
多表查询集
用于大部分性能测试的查询集由下列10个查询组成，“WHERE”从句中的参数值根据每条查询而改变。
Query 1
SELECT LOCATION.STORE_NAME,
AVG(ALL_FACTS.TOTAL),
AVG(ALL_FACTS.QUANTITY)
FROM ALL_FACTS,
LOCATION,
CHANNEL
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND ALL_FACTS.CHANNEL_ID = CHANNEL.CHANNEL_ID
AND CHANNEL.CHANNEL_ID in ( 90, 61, 20)
AND FACT_DATE = CAST('2002-11-01' AS DATE)
AND LOCATION.STORE_NUMBER = 18242
GROUP BY
LOCATION.STORE_NAME
ORDER BY SUM(ALL_FACTS.TOTAL) DESC
|
Query 2
SELECT LOCATION.STORE_NAME,
AVG(ALL_FACTS.TOTAL),
MIN(ALL_FACTS.TOTAL),
MAX(ALL_FACTS.TOTAL)
FROM ALL_FACTS,
LOCATION,
CHANNEL
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND ALL_FACTS.CHANNEL_ID = CHANNEL.CHANNEL_ID
AND CHANNEL.CHANNEL_ID = 94
AND FACT_DATE = CAST('2003-01-01' AS DATE)
AND LOCATION.STORE_NUMBER IN ( 5401, 6319, 18950)
GROUP BY LOCATION.STORE_NAME
ORDER BY SUM(ALL_FACTS.TOTAL) DESC
|
Query 3
SELECT STORE_NAME,
COUNT(*)
FROM ALL_FACTS,
CUSTOMER,
LOCATION
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND STORE_NUMBER = 29743
AND CUSTOMER_SCORE < 5
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2003-01-25' AND '2003-02-03'
GROUP BY STORE_NAME
|
Query 4
SELECT LOCATION.STORE_NAME,
MAX(ALL_FACTS.TOTAL),
MAX(ALL_FACTS.QUANTITY)
FROM ALL_FACTS,
LOCATION,
CHANNEL
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID AND
ALL_FACTS.CHANNEL_ID = CHANNEL.CHANNEL_ID AND
CHANNEL.CHANNEL_ID = 59 AND
ALL_FACTS.FACT_DATE = CAST('2003-06-01' AS DATE) AND
LOCATION.STORE_NUMBER = 25341
GROUP BY _LOCATION.STORE_NAME
ORDER BY SUM(ALL_FACTS.TOTAL) DESC
|
Query 5
SELECT CUSTOMER_FNAME,
STORE_NAME,
ALL_FACTS.CUSTOMER_ID,
PAYMENT_METHOD,
FACT_DATE,
ORDER_DATE,
SHIP_DATE,
DELIVERY_DATE,
SHIPPING,
DISCOUNT,
TAX,
TOTAL,
QUANTITY
FROM ALL_FACTS,
CUSTOMER,
LOCATION
WHERE CUSTOMER.CUSTOMER_ID = 345123
AND ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND STORE_NUMBER = 29343
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2002-07-01' AND '2002-08-07'
|
Query 6
SELECT STORE_NAME,
COUNT(*)
FROM ALL_FACTS,
CUSTOMER,
LOCATION,
PRODUCT
WHERE ALL_FACTS.LOCATION_ID = LOCATION.LOCATION_ID
AND ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND STORE_NUMBER = 12744
AND CUSTOMER_SCORE < 5
AND PRODUCT_TYPE = '0000000006'
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2001-05-25' AND '2001-06-03'
GROUP BY STORE_NAME
|
Query 7
SELECT CUSTOMER_FNAME,
ALL_FACTS.CUSTOMER_ID,
PAYMENT_METHOD,
FACT_DATE,
ORDER_DATE,
SHIP_DATE,
DELIVERY_DATE,
SHIPPING,
DISCOUNT,
TAX,
TOTAL,
QUANTITY
FROM ALL_FACTS,
CUSTOMER
WHERE CUSTOMER.CUSTOMER_ID = 2838456
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2002-06-01' AND '2002-07-07'
|
Query 8
SELECT CUSTOMER_SCORE,
SUM(TOTAL)
FROM ALL_FACTS,
CUSTOMER,
PRODUCT
WHERE ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND CUSTOMER_SCORE BETWEEN 85 AND 90
AND PRODUCT_TYPE = '0000000016'
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2001-08-25' AND '2001-09-03'
GROUP BY CUSTOMER_SCORE
|
Query 9
SELECT CUSTOMER_SCORE,
SUM(TOTAL)
FROM ALL_FACTS,
CUSTOMER,
PRODUCT
WHERE ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND CUSTOMER_SCORE BETWEEN 85 AND 90
AND PRODUCT_TYPE = '0000000015'
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2001-08-25' AND '2001-08-31'
GROUP BY CUSTOMER_SCORE
HAVING SUM(TOTAL) > (
SELECT SUM(TOTAL)
FROM FACT_2000,
PRODUCT
WHERE FACT_2000.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND PRODUCT_TYPE = '0000000015'
AND FACT_DATE = '2000-08-25'
)
|
Query 10
SELECT CUSTOMER_SCORE,
SUM(TOTAL)
FROM ALL_FACTS,
CUSTOMER,
PRODUCT
WHERE ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND CUSTOMER_SCORE BETWEEN 75 AND 80
AND PRODUCT_TYPE = '0000000041'
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND FACT_DATE BETWEEN '2001-08-25' AND '2001-08-31'
AND ALL_FACTS.CUSTOMER_ID IN (
SELECT CUSTOMER_ID
FROM FACT_2002
CUSTOMER,
PRODUCT
WHERE ALL_FACTS.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND ALL_FACTS.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID
AND CUSTOMER_SCORE = 80
AND CUSTOMER_ID BETWEEN 13000 AND 14000
AND PRODUCT.PRODUCT_ID = 4323
AND FACT_DATE = '2001-08-20'
)
GROUP BY CUSTOMER_SCORE
|
