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.
| Author |
Topic |
|
tskmjk
Starting Member
11 Posts |
 Posted - 2009-09-25 : 09:36:41
|
| Hi All, I developed a tool for infosec department of my company to find internal and external duplicates in an excel sheet. Internal duplicates means that duplicates within the excel sheet which I got but external duplicates needs to check other excel sheet data which takes quite a long time. Currently I am doing like this... - loop the excel data by mobilenumber which is an unique id - Check the internal duplicates by checking the count of mobilnumber > 1 with the same calltablename (same excel sheet data) - External duplicates by checking the count of mobile > 1 but with different calltablename (different excel sheet data) This takes some time..How to optimize the query? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-09-25 : 09:38:57
|
| Can you post the query you used?MadhivananFailing to plan is Planning to fail |
 |
|
|
tskmjk
Starting Member
11 Posts |
Posted - 2009-09-25 : 09:44:05
|
| Thanks for your super fast response...here it is..front end is vb.net and backend is sql server 2005... Dim con3 As New OleDbConnection Dim da3 As New OleDbDataAdapter Dim ds3 As New DataSet Dim dse3 As New DataSet Dim comm As New OleDbCommand Dim dupcounted, dupcountid, result, count As Integer Dim stor, stor1, stor2 As String con3 = New OleDbConnection(My.Settings.sqlcon.ToString()) con3.Open() stor = "select distinct mobilenumber from scrubmaster where calltablename='" & calltablenamename & "'" da3 = New OleDbDataAdapter(stor, con3) da3.Fill(ds3, "dup") stor = "" ToolStripProgressBar1.Visible = True ToolStripProgressBar1.Maximum = ds3.Tables("dup").Rows.Count dupcountid = 0 dupcounted = 0 For jj As Integer = 0 To ds3.Tables("dup").Rows.Count - 1 stor = "select id,count(id) from scrubmaster where mobilenumber='" & ds3.Tables("dup").Rows(jj).Item(0) & "' and calltablename='" & calltablenamename & "' group by id" da3 = New OleDbDataAdapter(stor, con3) da3.Fill(dse3, "id") 'comm.Connection = con3 'comm.CommandText = stor 'count = comm.ExecuteNonQuery() 'stor = "" If dse3.Tables("id").Rows.Count > 1 Then For kk As Integer = 1 To dse3.Tables("id").Rows.Count dupcountid += 1 stor2 = "update scrubmaster set flag='ID' where ID=" & dse3.Tables("id").Rows(kk).Item(0) & " and flag IS NULL" comm.Connection = con3 comm.CommandText = stor2 comm.ExecuteNonQuery() stor2 = "" Next End If stor1 = "SELECT count(mobilenumber), calltablename from scrubmaster WHERE mobilenumber ='" & ds3.Tables("dup").Rows(jj).Item(1) & "'and flag is NULL GROUP BY calltablename" da3 = New OleDbDataAdapter(stor1, con3) da3.Fill(dse3, "dupp") If dse3.Tables("dupp").Rows.Count > 1 Then dupcounted += 1 stor2 = "update scrubmaster set flag='ED' where ID=" & ds3.Tables("dup").Rows(jj).Item(0) & " and flag IS NULL" comm.Connection = con3 comm.CommandText = stor2 comm.ExecuteNonQuery() stor2 = "" End If stor1 = "" dse3.Clear() ToolStripProgressBar1.Value += 1 Next con3.Close() ToolStripProgressBar1.Value = 0 ToolStripProgressBar1.Visible = False If dupcountid > 0 Or dupcounted > 0 Then result = MsgBox(dupcountid & " Internal duplicate(s) found and " & dupcounted & " External Duplicate(s) found") LoginForm1.WriteToLog(dupcountid & " Internal duplicate(s) found and " & dupcounted & " External Duplicate(s) found") clearboxes() Else MsgBox("No Duplicates found") LoginForm1.WriteToLog("No Duplicates found") clearboxes() End If comm.Dispose() ToolStripStatusLabel1.Text = "Finished Duplicate Checking" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
How to find external and internal duplicates?