InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 21:54:04 GMT20http://www.sqlservercentral.com/Forums/Topic1422131-1292-1.aspxWhy do 1 insert call per temp table row on a 10,000 row temp table?justINSERTINTO [dbo].[Computers]([NetBIOSName], [IP])select netbiosname,ip from temptablewhere temptable.netbiosname not in ( select netbiosname from computers))Thu, 21 Feb 2013 13:05:11 GMTbdloving 4446http://www.sqlservercentral.com/Forums/Topic1422131-1292-1.aspxthank you for tips. Very respectful.Thu, 21 Feb 2013 08:33:16 GMThuy1002http://www.sqlservercentral.com/Forums/Topic1422131-1292-1.aspxAgreed. Make sure you have a PK on the table. If it were me, I would make an integer field as the primary key and add a unique nonclustered index to the computer name field so SQL won't worry about sort order based on a string field.One more tip - use an appropriate data type for the computer name before you index it. If you're storing computer names, you probably don't need an nvarchar(max) or anything like that. More companies have strict policies on computer names, so get yours and go 2 bytes beyond it for when they exceed their own limit.Thu, 21 Feb 2013 06:44:44 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1422131-1292-1.aspx[quote][b]huy1002 (2/20/2013)[/b][hr]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 tableAddCompSucc = cc.AddComputer(_NetBIOSName, _IP); //add compputer's detailsCompId = cc.GetComputer(_NetBIOSName).ID;AddCompInfoSucc = cci.AddComputerInfo(CompId, _DNSName, _MAC, _OS, _NetBIOSDomain);//add vulneribilitiesAddIavSucc = ciav.AddIAVulnerability(CompId, _IAV, _Name, _AuditDate, 0);//add audit detailsIavId = 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 NetBIOSNameFROM ComputersWHERE NetBIOSName = @NetBIOSName) BEGIN INSERT INTO [dbo].[Computers]([NetBIOSName], [IP])VALUES (@NetBIOSName,@IP) ENDIssue: 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,[/quote]Define a PK constraint - or at least a unique index on NetBIOSName column, get rid of the "IF NOT EXIST" block, handle the duplicate key exception.Hope this helps.Wed, 20 Feb 2013 17:37:56 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1422131-1292-1.aspxI 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 tableAddCompSucc = cc.AddComputer(_NetBIOSName, _IP); //add compputer's detailsCompId = cc.GetComputer(_NetBIOSName).ID;AddCompInfoSucc = cci.AddComputerInfo(CompId, _DNSName, _MAC, _OS, _NetBIOSDomain);//add vulneribilitiesAddIavSucc = ciav.AddIAVulnerability(CompId, _IAV, _Name, _AuditDate, 0);//add audit detailsIavId = 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 NetBIOSNameFROM ComputersWHERE NetBIOSName = @NetBIOSName) BEGIN INSERT INTO [dbo].[Computers]([NetBIOSName], [IP])VALUES (@NetBIOSName,@IP) ENDIssue: 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,Wed, 20 Feb 2013 08:54:57 GMThuy1002