SQL Server 2005 data import - best practices?
-
Hello, I'm getting data via file transfer-interface and I'd like to import it to a sql server 2005 database. Now my question: What is the best way to write the data to the database (I'm using C#)? Open a SqlConnection, transfer each single data row and the close the connection again? This can't be the best solution I think. Is there a better, faster way to get the data into the database - e.g. with a dataset? It's a little complicated, because I've corresponding data tables and I've got to query these tables for primary keys, to be able to fill the new data rows and import the new data properly. I already know DataSet.Update() but then there's still the problem that I've got to query the related tables for the ID (primary key) before I can update the DataSet and the database. I simply can't figure out how I can avoid the query for these IDs. Maybe someone got an idea for this problem?! Thanks in advance. Tobias
-
Hello, I'm getting data via file transfer-interface and I'd like to import it to a sql server 2005 database. Now my question: What is the best way to write the data to the database (I'm using C#)? Open a SqlConnection, transfer each single data row and the close the connection again? This can't be the best solution I think. Is there a better, faster way to get the data into the database - e.g. with a dataset? It's a little complicated, because I've corresponding data tables and I've got to query these tables for primary keys, to be able to fill the new data rows and import the new data properly. I already know DataSet.Update() but then there's still the problem that I've got to query the related tables for the ID (primary key) before I can update the DataSet and the database. I simply can't figure out how I can avoid the query for these IDs. Maybe someone got an idea for this problem?! Thanks in advance. Tobias
Tobias Schoenig wrote:
What is the best way to write the data to the database (I'm using C#)?
There are many ways, it depends on your situation.
Tobias Schoenig wrote:
Open a SqlConnection, transfer each single data row and the close the connection again?
Probably not, you are receiving the data in a constant stream are you not? Open connection, INSERT many rows, close connection. Remember that you can perform multiple actions in a single SqlCommand if needed. Or you can repeatedly execute the SqlCommand with a different set of parameters each time.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
Tobias Schoenig wrote:
What is the best way to write the data to the database (I'm using C#)?
There are many ways, it depends on your situation.
Tobias Schoenig wrote:
Open a SqlConnection, transfer each single data row and the close the connection again?
Probably not, you are receiving the data in a constant stream are you not? Open connection, INSERT many rows, close connection. Remember that you can perform multiple actions in a single SqlCommand if needed. Or you can repeatedly execute the SqlCommand with a different set of parameters each time.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
Colin Angus Mackay wrote:
Probably not, you are receiving the data in a constant stream are you not?
I read out the data line per line and not in a constant stream. I know that i can perform multiple actions in a single SqlCommand but then i still got the problem that i've got to query the related tables for the IDs of corresponding rows. Is there no way to avoid these queries? Is it possible to query the ID of a related row and insert the new data row into the database? Maybe anyone has an example for such an SqlCommand.
-
Colin Angus Mackay wrote:
Probably not, you are receiving the data in a constant stream are you not?
I read out the data line per line and not in a constant stream. I know that i can perform multiple actions in a single SqlCommand but then i still got the problem that i've got to query the related tables for the IDs of corresponding rows. Is there no way to avoid these queries? Is it possible to query the ID of a related row and insert the new data row into the database? Maybe anyone has an example for such an SqlCommand.
Tobias Schoenig wrote:
I read out the data line per line and not in a constant stream.
Sorry, what I meant was: You are running as a batch process rather than an interactive one. The data will be read in line by line, but it does not require user interaction to keep this process going.
Tobias Schoenig wrote:
Is there no way to avoid these queries?
If you need to keep the data related, then you'll have to perform the queries. If the relationship is in the form of a lookup with only a few entries then you could do one query to bring the data to your .NET application. If the joined data is more dynamic (i.e. it will also be changing during the upload) then you might consider using Stored Procedures. The stored procedure takes as parameters the data to be inserted and does the lookup internally - thus you only have one round trip to the database per insert.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
Tobias Schoenig wrote:
I read out the data line per line and not in a constant stream.
Sorry, what I meant was: You are running as a batch process rather than an interactive one. The data will be read in line by line, but it does not require user interaction to keep this process going.
Tobias Schoenig wrote:
Is there no way to avoid these queries?
If you need to keep the data related, then you'll have to perform the queries. If the relationship is in the form of a lookup with only a few entries then you could do one query to bring the data to your .NET application. If the joined data is more dynamic (i.e. it will also be changing during the upload) then you might consider using Stored Procedures. The stored procedure takes as parameters the data to be inserted and does the lookup internally - thus you only have one round trip to the database per insert.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
Colin Angus Mackay wrote:
Sorry, what I meant was: You are running as a batch process rather than an interactive one. The data will be read in line by line, but it does not require user interaction to keep this process going.
The process is a batch process called every x seconds (10s - 30s) and does not require user interaction.
Colin Angus Mackay wrote:
If the relationship is in the form of a lookup with only a few entries then you could do one query to bring the data to your .NET application.
The problem is, that I've got to do the lookup for each data row (about 100 entries) I want to insert and this adds the same number of queries to my application, which takes its time. The data is not that dynamic that stored procedures would be effective. The question is, is it possible to do this query together with the insert in one SqlCommand?
-
Colin Angus Mackay wrote:
Sorry, what I meant was: You are running as a batch process rather than an interactive one. The data will be read in line by line, but it does not require user interaction to keep this process going.
The process is a batch process called every x seconds (10s - 30s) and does not require user interaction.
Colin Angus Mackay wrote:
If the relationship is in the form of a lookup with only a few entries then you could do one query to bring the data to your .NET application.
The problem is, that I've got to do the lookup for each data row (about 100 entries) I want to insert and this adds the same number of queries to my application, which takes its time. The data is not that dynamic that stored procedures would be effective. The question is, is it possible to do this query together with the insert in one SqlCommand?
Tobias Schoenig wrote:
The process is a batch process called every x seconds
Okay - Open the connection at the start of a batch, issue your commands then close the connection. 20-30 seconds later you repeat the process.
Tobias Schoenig wrote:
The problem is, that I've got to do the lookup for each data row (about 100 entries)
100 entries in the look up is not much. You could pull that across once and put in in a hashtable.
Tobias Schoenig wrote:
The question is, is it possible to do this query together with the insert in one SqlCommand?
Yes.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
Tobias Schoenig wrote:
The process is a batch process called every x seconds
Okay - Open the connection at the start of a batch, issue your commands then close the connection. 20-30 seconds later you repeat the process.
Tobias Schoenig wrote:
The problem is, that I've got to do the lookup for each data row (about 100 entries)
100 entries in the look up is not much. You could pull that across once and put in in a hashtable.
Tobias Schoenig wrote:
The question is, is it possible to do this query together with the insert in one SqlCommand?
Yes.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
Ok, thanks for your answers! I'll try to improve the performance of the data import by merging the query and the insert together in one SqlCommand.