The scenario is a user of a .NET web application can select a text file, each line will be imported as a new record in an SQL Server database.
The project has an existing data access layer, which opens SqlConnection for each method in the DAL. This works fine for individual records, for example when the user uses the web forms to insert data, but this is running very slow when importing ~ 5000 records from the text file.
Need to use about 4 or 5 methods on the DAL, each which open/close SqlConnections, and then some of those methods are calling other methods which do the same.
This question was not very helpful http://stackoverflow.com/questions/487545/how-to-handle-connections-in-data-access-layer-net in answering this issue.
Now apparently the driver can do connection pooling, any information on how I could increase the performance or any knobs to turn?
Is there anyway to increase performance without modifying the data access layer?
If I do have to modify the data access layer to support calling bulk create statements, is there a suggested pattern for doing so?
30 minutes and still only 400 records imported...