SQL Server - Why does importing lots of columns with few rows takes way longer than few columns with lots of rows? I am using the import wizard in SQL Server 2014. When I import a text file with 40 columns with 1 million row it is pretty fast. On the other side when I am importing a few rows with 268 columns it is taken AGES ! The import seems to proceed very slowly "blocks" of 128 records. And between each "block" there are minutes going by...
Could someone give me some hints to try understanding what is happening?
Thank you in advance
At first, I guess the size of the 268 columns can be pretty big. So just look at number of bytes in one row, multiply by 128 and you'll see how much data SQL can cache at one time.

Second, there might be a lot of data type conversions from text to numerics and dates.

At third, there might be foreign keys, indexes, constraints and even triggers on the table you are writing into.

At fourth, the table can be in another database, which is placed on a slower drive.

At fifth, I guess all 268 columns can't get into the same page, and records go to LOB pages.

So, there might be even more reasons. I might suggest you to use clustered Columnstore index on that table, that might improve the loading situation.