SQL Server vs. Vertica Bulk Load

[billykopecki]

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!

[id10t]

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.

Hard to believe.

[billykopecki]

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

[kevin]

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

[JimKnicely]

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.

[jpcavanaugh]

Loading financial data I have seen ~5m records/second sustained load rate in Vertica.

[Rick]

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.