Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 it takes too long to insert data to tables

Author  Topic 

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-20 : 10:57:16

I was trying to dump data from a Temp table to 4 different tables (Computers, ComputerInfo, Vulnerabilities, and AuditInfo) in C# with different methods I created in classes.
if (_NetBIOSName != string.Empty)
{
//Only add the computers that are not existed in Computers table
AddCompSucc = cc.AddComputer(_NetBIOSName, _IP);

//add compputer's details
CompId = cc.GetComputer(_NetBIOSName).ID;
AddCompInfoSucc = cci.AddComputerInfo(CompId, _DNSName, _MAC, _OS, _NetBIOSDomain);

//add vulneribilities
AddIavSucc = ciav.AddIAVulnerability(CompId, _IAV, _Name, _AuditDate, 0);

//add audit details
IavId = ciav.GetIAVulnerability(CompId, _IAV, _Name, _AuditDate).ID;
AddAuditInfoSucc = cai.AddAuditInfo(IavId, _AuditID, _AuditDate, _SevCode, _Risk, _PCILevel, _Exploit, _Context);
}

In each method to insert data to tables, I used “if not exists…” statement to prevent duplicated data to be inserted.
IF NOT EXISTS (
SELECT DISTINCT NetBIOSName
FROM Computers
WHERE NetBIOSName = @NetBIOSName)
BEGIN
INSERT
INTO [dbo].[Computers]([NetBIOSName], [IP])
VALUES (@NetBIOSName,@IP)
END
Issue: it takes forever to insert data to 4 tables when the Temp database is big, like having 10000 rows.
Any suggestion on how to do this differently to cut down the processing time.
Very Respectfully,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 11:00:44
seeing your insert it seems you're trying to do one insert per row. why cant you do it in a batch?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 11:08:25
I didn't quite understand the data flow. Are you getting the data from one data base into the C# code and then inserting that into a destination database? Also, is 10,000 the number of rows you are inserting, or are you inserting one row into a table that already has 10,000 rows? If you are inserting 10,000 rows, that is 10,000 x 4 round trips and inserts. That would be time consuming.

If you are inserting 10,000 rows, depending on where the source data is coming from, for the best performance you would use different approaches (such as SSIS or bulk insert if the source data also is in a database table or flat files). If you must insert the data via the C# code, consider packaging up all data into one object (such as in an XML fragment) and doing one insert into each table for all the data.
Go to Top of Page

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-20 : 12:03:01
i have to create a front end interface app to allow users dumping data from .csv files to the databases. First i fill data to a datatable. Second i read each row, then doing the insert.

OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();
objDataAdapter.SelectCommand = ExcelConnection.GetConnection(Server.MapPath("~/File/scan.xlsx"));
DataTable dt = new DataTable();
objDataAdapter.Fill(dt);
objDataAdapter.Dispose();
...
foreach (DataRow dr in dt.Rows)
{
CcpsComputers cc = new CcpsComputers();
CcpsComputerInfo cci = new CcpsComputerInfo();
CcpsIAV ciav = new CcpsIAV();
CcpsAuditInfo cai = new CcpsAuditInfo();

count = count + 1;

//Build Computers table
string _NetBIOSName = dr["NetBIOSName"].ToString();
string _IP = dr["IP"].ToString();
...
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 12:27:41
I was guessing that that might be the case. That would indeed be slow. Couple of options that I mentioned earlier that you might consider:

1. Import/Export Wizard generated package in combination with a stored proc, or an SSIS package generated using BIDs. What you would do is to use Import/Export Wizard to import the data form the xlsx file into a staging table in your database. When you use the import/export wizard, it gives you the option to save the package. From then on, you can run the package when you want to import the data in the future. Once you have the data in the staging table, you can join to that table to examine the rows that need to be inserted. There will be only four set-based queries regardless of the number of rows to be inserted.

2. Convert the DataTable into an XML fragment (using XElement it should be relatively simple to do), send the xml as a parameter to a stored procedure, shred the xml in the stored proc and update.

Either of these should perform much better than what you currently are using. If you need more help with doing either, reply back.
Go to Top of Page

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-20 : 13:00:53
I agree. It is faster if they send me a csv file, and i use import/export wizard to store data to the database. However they want me to create an inteface for users input.
I have not done your second suggestion, i will try.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 13:05:55
quote:
Originally posted by eirikr_1

I agree. It is faster if they send me a csv file, and i use import/export wizard to store data to the database. However they want me to create an interface for users input.
I have not done your second suggestion, i will try.


thats ok. you can ask them to upload the csv and create a web interface for that. But for data upload part save the file they upload to shared path and set a bit in control table you create.

On the background create a sql agent job which executes ssis packages (export/import wizard based if its simple data transfer). then execute the job if bit is set. then it will transfer the data from csv to your table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-20 : 15:12:57
The ADO.net SqlBulkCopy class enables quick, mass upload of rows into a table:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
Go to Top of Page

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-21 : 10:39:17
should i use file stream or read, write binary data to save a file? any helpful link?

~Very respectful
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 10:47:26
Actually what Lamprey suggested would be the best option. You read the data into your C# code as you have been doing, but instead of going through the loop, create an instance of SqlBulkCopy and call its WriteToServer method passing in the DataTable as the parameter ( http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy_methods.aspx ) You would of course need a staging table in the database to store the data.

After you get the data into the staging table, you will need to insert the data into the four tables using four insert statements. So they will simply be four insert statements rather than the 4xN inserts as you have now.
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-02-21 : 11:13:28
This article may be of interest:

http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/
Go to Top of Page

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-21 : 12:11:02
it is my most favorite forum. I have learned many things. Thank you all for helping.

~very respectful
Go to Top of Page

eirikr_1
Starting Member

27 Posts

Posted - 2013-02-22 : 13:41:29
i have exactly the same number of columns in TempScan table as in scan.xls file.
however the data is messing up in the database, i lost data in DnsName column
CompName DnsName IP MAC OS
com1 111.11.111.11 00:00:00:00 window7 ...

here is my code

//Upload file to server in File folder and name Scans.xlsx
UploadedFile file = txtFile.UploadedFiles[0];
string targetFolder = Server.MapPath("~/File");
file.SaveAs(Path.Combine(targetFolder, "scan.xlsx"));

//Create ExcelData Adaptor and read to DataTable
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();
objDataAdapter.SelectCommand = ExcelConnection.GetConnection(Server.MapPath("~/File/scan.xlsx"));
DataTable newScan = new DataTable();
objDataAdapter.Fill(newScan);
objDataAdapter.Dispose();

// Create the SqlBulkCopy object.
// Note that the column positions in the source DataTable
// match the column positions in the destination table so
// there is no need to map columns.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Conn))
{
bulkCopy.DestinationTableName = "dbo.TempScan";

try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(newScan);
}
catch (Exception ex)
{
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 14:37:20
Check if the data is coming in correctly into the DataTable (newscan). It may have to do with Excel interpreting the data type. You can try IMEX=1 (intermixed) as an option in the connection string (to the excel file) which may fix the problem. See here for the syntax: http://www.connectionstrings.com/excel
Go to Top of Page
   

- Advertisement -