Way to speed up comparison of 1300 query result rows with

[saadabc]

I have the following code:


'connection string for AD sql database
Dim connString As String = "Initial Catalog=WCI_Global;Data Source=SAC_DB5_SRV;Integrated Security=SSPI;"

'connection object for AD sql database
Dim myConnection As New SqlConnection(connString)
Dim ExcelObj As Excel.Application
Dim strsql As String
ExcelObj = New Excel.Application

If (ExcelObj Is Nothing) Then

MsgBox("ERROR: EXCEL couldn't be started!")
System.Windows.Forms.Application.Exit()
End If


ExcelObj.Visible = True

myConnection.Open()

'Dim ExcelReader As OleDbDataReader
Dim ADReader As SqlDataReader

'Dim objCmd As New OleDbCommand(strExcelSql, objConn)
Dim myCommand As New SqlCommand
'myConnection.Open()
myCommand.Connection = myConnection

Dim theWorkbook As Excel.Workbook
Dim sheets As Excel.Sheets
Dim worksheet As Excel.Worksheet
Dim range As Excel.Range
Dim i As Int32
Dim myvalues As System.Array
Dim strArray As System.Array

theWorkbook = ExcelObj.Workbooks.Open("\\Sacinf01\isdept$\Development\Programs\Security Audit Tool\All_active_and_leave_employees_9-2-05.xls", 0, True)

sheets = theWorkbook.Worksheets

worksheet = sheets.Item(1)

'Clear Listview
lvCompareResults.Items.Clear()
lvCompareResults.ListViewItemSorter = Nothing
lvCompareResults.BeginUpdate()
lvCompareResults.ListViewItemSorter = Nothing
Dim lvi As ListViewItem
Dim compare As Boolean
Dim FirstName As String
Dim LastName As String
Dim yoyo As String
Dim NumFound As Int32
NumFound = 0
Dim found As Boolean
Dim count As Int32
Dim ADFirstName As String
Dim ADLastName As String
Dim spacepos As Int32
Dim j As Int32
j = 0
Dim rowfoundat As Int32
rowfoundat = 0
strsql = "Select cn,sn,whenCreated,whenChanged,displayName,memberOf,givenName from ActiveDirectoryUsers"


myCommand.CommandText = strsql
Try
ADReader = myCommand.ExecuteReader()

Catch ex As SqlException
MsgBox(ex.ToString())
End Try

While ADReader.Read()
i = 2
found = False
'count = lvi.SubItems.Count
'ADPName = myvalues(1, 1) & " " & myvalues(1, 2)
'memberof = '%" & cmbGroup.SelectedText & "%'"
ADFirstName = ADReader.GetString(6)
ADLastName = ADReader.GetString(1)

'If InStr(ADFirstName, " ") = 0 Then

'Else
' ADFirstName = Microsoft.VisualBasic.Left(ADFirstName, InStr(ADFirstName, " "))
'End If
While (i < 4081)
range = worksheet.Range("A" + i.ToString(), "B" + i.ToString())
myvalues = range.Cells.Value
FirstName = myvalues(1, 1)
LastName = myvalues(1, 2)

spacepos = InStr(FirstName, " ")
If spacepos = 0 Then

Else
FirstName = Microsoft.VisualBasic.Left(FirstName, spacepos - 1)
End If

If FirstName = ADFirstName And LastName = ADLastName Then
rowfoundat = i
i = 4080 ' job done get out of loop
found = True
ElseIf i = 4080 Then
NumFound = NumFound + 1
lvi = New ListViewItem
lvCompareResults.Items.Add(lvi)
lvi.SubItems.Add(NumFound)
lvi.SubItems.Add(myvalues(1, 1))
lvi.SubItems.Add(myvalues(1, 2))
count = lvi.SubItems.Count
'i = 4080 ' job done get out of loop
End If

i = i + 1
End While

j = j + 1
End While

lvCompareResults.EndUpdate()

'Update LastScanDate
myCommand.CommandText = "Update ActiveDirectoryUsers Set LastScanDate = GETDATE()"
myCommand.ExecuteNonQuery()


so i've ran a query against a table producing 1300 records. Then, I'm running down the resultset and for each row, I read the FirstName and LastName columns, then do a search for the FirstName and LastName in the Excel spreadsheet containing 4080 rows. (Please note how I'm doing the search). Basically, iterating down each column on the spreadsheet. This is not an efficient way. It's taking the process ages to complete. Can someone tell me a more efficient way to search for the values in the Excel spreadsheet.

Would really appreciate it.

Thanks

 

[ThatRickGuy]

Pull all the values from the excel spread sheet into data tables. You may even be able to use an MS driver to pull them directly through an ole connection to the text file. Once you have all of the data in datatables, use .select to find the matches.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[saadabc]

To pull the excel values in a datatable i need a sqldataadapter (so i can call the Fill method to fill the datatable) and I need an OleDbCommand (to connect to the excel spreadsheet - the only way to connect to excel spreadsheet is through OleDBCommand - I cannot use a SQLCommand object).


But when I try to create a SQLDataadapter, it needs a SQLCommand as property associated with it, as in:

Dim tmpCmd as SQLCommand
Dim s as SQLDataAdapter(tmpCmd)


I cannot do this:

Dim tmpCmd as OleDBCommand
Dim s as SQLDataAdapter(tmpCmd) - 'error cannot convert OleDBCommand to SQLCommand.


so i'm stuck.

 

[ThatRickGuy]

Excel files are basicly just comma delimited text files. So you can either read them in with a text stream, or use the Microsoft OLE text database driver to connect to them.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[tEkHEd]

So change your SQLDataAdapter/Command to and OLEDataAdapter/Command

 

[RiverGuy]

Rick--I wouldn't categorize Excel files as equivalent to csv. I think anyone would have a hard time reading its data into a stream and attempting to parse out cell values from everything else. So, I don't think a text database driver will work.

However, if you go to connectionstrings.com, you can find a connection string to use Jet to connect to Excel. You can do a simple select query with an oledb data adapter to fill up your data table.

 

[saadabc]

I figured it out using THatRightGuy's method 'reading excel values into a Data Table. And thanks TekHed for suggesting the OleDataAdapter. it worked. It's a lot faster now!

Thanks