hello everyone,
I have an insert performance quandary that I'm stumped on, and was wondering if any SQL mavens would be able to provide some pointers -- on SQL2014 RTM, Developer Edition. :-)
On an otherwise fairly beefy IO (4 SSD's in RAID0, on a fairly decent hardware RAID card, achieving approx. 2000 mb/sec, per SQLIO RR, RS, WS baselining of the system, and some 800mb/sec WR), on a Xeon 2x4 cores @3.5ghz and 128gb RAM DDR4 @2100mhz, power settings @max, I am only getting about 20-30 mb/sec when inserting records, in a simple, unpartitioned, uncompressed, 5 column, unindexed heap table, below, in a database in either bulk or simple logging mode:
CREATE TABLE [dbo].[bigTransactionHistory](
[TransactionID] [bigint] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[TransactionDate] [date] NULL,
[Quantity] [int] NULL,
[ActualCost] [money] NULL
) ON [PRIMARY]
Basically, I am using Adam Machanic's script, at
http://sqlblog.com/blogs/adam_machanic/archive/2011/10/17/thinking-big-adventure.aspx for a fairly large POC.
I am inserting some 31 mill records at a time, in approx. 1 min 4 secs, some approx. 1.239 gb of data -- this seems fairly slow, some approx. 20 mb/sec.
Without the slow RAND(CHECKSUM(NEWID())) , the insert executes in about 15 seconds, but still, that's about 80 mb/sec, a far cry from what I *think* I'm supposed to get. (for IO verification, once records are inserted, when I'm scanning the data, the throughput indeed reaches approx. 2000 mb/sec).
Any thoughts on further tweaking the inserts to achieve *substantially* better performance?
thanks a big bunch for any pointers,
Cos
Data pop. script:
insert INTO bigTransactionHistory (productid, transactiondate, quantity, actualcost)
SELECT
p1.ProductID,
x.TransactionDate,
x.Quantity,
CONVERT(MONEY, p1.ListPrice * x.Quantity * RAND(CHECKSUM(NEWID())) * 2) AS ActualCost
FROM
(
SELECT
p.ProductID,
p.ListPrice,
CASE
WHEN p.productid % 26 = 0 THEN 26
WHEN p.productid % 25 = 0 THEN 25
WHEN p.productid % 24 = 0 THEN 24
WHEN p.productid % 23 = 0 THEN 23
WHEN p.productid % 22 = 0 THEN 22
WHEN p.productid % 21 = 0 THEN 21
WHEN p.productid % 20 = 0 THEN 20
WHEN p.productid % 19 = 0 THEN 19
WHEN p.productid % 18 = 0 THEN 18
WHEN p.productid % 17 = 0 THEN 17
WHEN p.productid % 16 = 0 THEN 16
WHEN p.productid % 15 = 0 THEN 15
WHEN p.productid % 14 = 0 THEN 14
WHEN p.productid % 13 = 0 THEN 13
WHEN p.productid % 12 = 0 THEN 12
WHEN p.productid % 11 = 0 THEN 11
WHEN p.productid % 10 = 0 THEN 10
WHEN p.productid % 9 = 0 THEN 9
WHEN p.productid % 8 = 0 THEN 8
WHEN p.productid % 7 = 0 THEN 7
WHEN p.productid % 6 = 0 THEN 6
WHEN p.productid % 5 = 0 THEN 5
WHEN p.productid % 4 = 0 THEN 4
WHEN p.productid % 3 = 0 THEN 3
WHEN p.productid % 2 = 0 THEN 2
ELSE 1
END AS ProductGroup
FROM bigproduct p
) AS p1
CROSS APPLY
(
SELECT
transactionDate,
CONVERT(INT, (RAND(CHECKSUM(NEWID())) * 100) + 1) AS Quantity
FROM
(
SELECT
DATEADD(dd, number * 2, '20050101') AS transactionDate,
NTILE(p1.ProductGroup) OVER
(
ORDER BY number
) AS groupRange
FROM master..spt_values
WHERE
type = 'p'
) AS z
WHERE
z.groupRange % 2 = 1
) AS x
Other recent topics
