SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fast inserting\reading\deleting of millions of records


Fast inserting\reading\deleting of millions of records

Author
Message
darren_ferris
darren_ferris
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 261
Hi Grant,

I've done some further testing on the inserts and it only takes about 15% longer, so not too bad. I'm bulk loading the data via a C# application, using SqlBulkCopy, in batches of around 10k records.
The explain plan on a simple insert of 1 record shows that 100% of the Operator Cost is with the index.

I've profiled the inserting of all of the data and have the following wait stats although unfortunately this is not an area that I'm totally familiar with.
I've used the query provided by Paul Randall at http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/: -


WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S
PREEMPTIVE_OS_WRITEFILEGATHER 66.59 66.59 0 6 28.71 11.0985 11.0985 0
LATCH_EX 58.84 58.84 0 8 25.37 7.3553 7.355 0.0003
ASYNC_IO_COMPLETION 41.94 41.94 0 10 18.08 4.1937 4.1937 0
ASYNC_NETWORK_IO 28.87 26.64 2.23 53798 12.45 0.0005 0.0005 0
WRITELOG 23.24 22.81 0.43 6255 10.02 0.0037 0.0036 0.0001
IO_COMPLETION 8.29 8.26 0.03 17390 3.57 0.0005 0.0005 0




Thanks again for your support.

Darren
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99493 Visits: 33014
That looks like hardware as a bottleneck. It's the writes that are going slow.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218153 Visits: 41995
I can't speak to the INSERTs but the accidental CROSS JOIN in the SELECT code will beat the hell out of the hardware making it look like a hardware problem.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
darren_ferris
darren_ferris
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 261
Hi Jeff,

I've fixed the problem with the CROSS JOIN, it looks like we've had that code in there for a good few yearsRolleyes.
Here's the revised code: -


SELECT
f1.ComponentNo
,f1.SubFormAnalyID
,f1.FeatureAnalyID
,f1.RepeatNo
,f1.SimulationNo
,f1.IsVarParam
,f1.LibItemID
,f1.LibVerNo
,f1.IntValue
,f1.RealValue
,f1.TextValue
,pc.CompAnalyID
,ft.EnumTypeID
,<<@ParentNo>>
FROM dbo.<<@NW_Feature>> AS f1
JOIN dbo.<<@NW_Component>> AS nc ON f1.ComponentNo = nc.ComponentNo
JOIN dbo.PCC_Component AS pc ON nc.PhysID = pc.PhysID
JOIN dbo.Feature AS ft ON f1.FeatureAnalyID = ft.AnalyID
WHERE f1.SimulationNo <= ( SELECT MAX(SimulationNo)
FROM <<@NW_Feature>> AS nwf1
WHERE f1.ComponentNo = nwf1.ComponentNo
AND f1.SubFormAnalyID = nwf1.SubFormAnalyID
AND f1.FeatureAnalyID = nwf1.FeatureAnalyID
AND f1.RepeatNo = nwf1.RepeatNo
)
AND ft.EnumTypeID NOT IN (14,15)
;



Thanks,

Darren
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218153 Visits: 41995
So do you still need the DISTINCT?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
darren_ferris
darren_ferris
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 261
Sorry, no...I did mean to remove that.

Darren
EdVassie
EdVassie
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14260 Visits: 3901
You mention that a lot of customers will use SQL Express with your application.

I think the capabilities of SQL Express should form part of the design limits on your application. One of these aspects is that SQL Express can use a maximum of 1GB memory and 2CPU cores, and this may well impact the decision regarding a single table or multiple tables.

My gut feel is that multiple tables are a better choice in this environment. This is because:
a) The use of SQL Express implies the use of low-end hardware. Most customers will not have the IO capability to use the capabilities of the paid-for editions of SQL Server.
b) The memory needs for a given table in a multi-table setup are likely to be less than for a single table.
c) You want to make it easy for customers to delete a simulation they do not need. Separate tables are quick to drop, row deletion is not. If you make deletion slow then customers will not do it, then they will hit the 10GB DB size limit in SQL Express.
d) Customers are unlikely to run much in the way of index maintenance. If you delete rows from a table then index maintenance is needed to reclaim space. Dropping tables needs no maintenance.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
darren_ferris
darren_ferris
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 261
Ed,

Thanks for your reply, I think you're correct on all points.

From everyone's feedback so far it looks like what we have at the moment could well be the best solution, but we definitely need to look at re-factoring some of our hideous dynamic SQL.

Regards,

Darren
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search