Hi guys,
Can some one help me settle an argument I'm having at work?
A consultant at my company claims SSIS can load data into SQL Server faster than the COPY command can load the same data into Vertica. When I tested I consistently saw a Vertica solution as being much faster than a SQL Server solution, even when using the SQL Server BCP command which I always thought was the fastest way to load data into SQL Server. Maybe there is someone on this site that has more experience with SQL Server than I and can shed some lite on this disagreement.
The attachment to my post shows the results of my testing where I loaded a table with 57 columns from a text file containing 7,396,971 rows into SQL Server and Vertica. The SQL Server database is on a single host where as Vertica is running on a 3 node cluster. The SQL Server host specs. are very similar to out Vertica DEV cluster node hosts. The node in Vertica prod are a little beefier
Do my times seem consistent with anyone else's testing?
Thanks!
SQL Server vs. Vertica Bulk Load
Moderator: NorbertKrupa
-
- Beginner
- Posts: 42
- Joined: Thu Apr 19, 2012 9:03 pm
SQL Server vs. Vertica Bulk Load
- Attachments
-
- SQL Server vs. Vertica Load
- SqlServer_vs_Vertica_Load.png (7.9 KiB) Viewed 17552 times
Re: SQL Server vs. Vertica Bulk Load
Hard to believe.A consultant at my company claims SSIS can load data into SQL Server faster than the COPY command can load the same data into Vertica.
-
- Beginner
- Posts: 42
- Joined: Thu Apr 19, 2012 9:03 pm
Re: SQL Server vs. Vertica Bulk Load
Well, yeah. That's why I did my test. Although it was a simple experiment (one table), I thought it might convince him he was wrong. I think its funny how some folks can''t be convinced that the database they've worked with for years is the only database out there
Re: SQL Server vs. Vertica Bulk Load
I don't have a SQL Server comparison but I was able to load 1.5 billion rows (60GB data) in 17.7 mins on a 4 node cluster using 1 thread on each node using copy. There are 24 cores so I should be able to improve that by running more threads.
Kevin
Kevin
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: SQL Server vs. Vertica Bulk Load
That's an awesome time! Out of curiosity, how many columns were in the table you loaded? And what was the file size was the data file you loaded? I remember a Vertica rep. told me that when files are bigger than 1 GB they should be broken up into smaller pieces. I wonder if that's true or not.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Intermediate
- Posts: 149
- Joined: Mon Apr 30, 2012 10:04 pm
- Location: New York
- Contact:
Re: SQL Server vs. Vertica Bulk Load
Loading financial data I have seen ~5m records/second sustained load rate in Vertica.
Re: SQL Server vs. Vertica Bulk Load
Seems consistent. Some benchmarking I did recently allowed me to load 1,228,260 rows Vertica in 16.3 seconds copying from a file.
Vertica is pretty nippy.
Vertica is pretty nippy.