taxing process, reading from csv

[tester321]

Hi I trying to create a cross matching tool. CSV uploaded versus data in sql2005 table. I'm trying to find records in the db that match whats in the csv, this is what i have, it works but is very taxing on iis. 10 records in the csv to 5000 records in the db yields about 20 minutes to produce a matched list, any thoughts on how i could speed this up/not make it so taxing. This is the code I wrote to do the crossMatching. Thanks!

        While dr.Read()

            TextBoxFirstName.Text = TextBoxFirstName.Text & dr("ContactID")

            If Not IsDBNull(dr("FirstName")) Or IsDBNull(dr("LastName")) Or IsDBNull(dr("WorkPhone")) Or IsDBNull(dr("FaxNumber")) Or IsDBNull(dr("WorkCity")) Or IsDBNull(dr("WorkPostalCode")) Or IsDBNull(dr("WorkStateOrProvinceID")) Then

                For i = 0 To (table.Rows.Count - 1)

                    If Not IsDBNull(table.Rows(i).Item(0).ToString) Or IsDBNull(table.Rows(i).Item(1).ToString) Or IsDBNull(table.Rows(i).Item(5).ToString) Then

                        If Left(dr("FirstName"), 3) = Left(table.Rows(i).Item(0).ToString, 3) And dr("LastName") = table.Rows(i).Item(1).ToString And Left(dr("WorkPostalCode"), 3) = Left(table.Rows(i).Item(5).ToString, 3) Then

                            'AddToDataTable(dr("FirstName"), dr("LastName"), dr("WorkPhone"), dr("FaxNumber"), dr("WorkCity"), dr("WorkPostalCode"), dr("WorkStateOrProvinceID"))

                            workRow = workTable.NewRow()

                            workRow("FirstName") = dr("FirstName")

                            workRow("LastName") = dr("LastName")

                            workRow("PhoneNumber") = dr("WorkPhone")

                            workRow("FaxNumber") = dr("FaxNumber")

                            workRow("City") = dr("WorkCity")

                            workRow("PostalCode") = dr("WorkPostalCode")

                            workRow("Province") = dr("WorkStateOrProvinceID")

                            workTable.Rows.Add(workRow)

                        End If

                    End If

                Next

            End If


        End While

 

[Turkbear]

Hi,
Why not import the CSV stuff into a SqlServer table and use that to compare..you can always drop it after..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jmeckley]

your process involves the 2 system operations which are most taxing on a system. Database connecions and opening files.

only keep files/db connections open when necessary. read the data, dump to memory and close. then process your logic. when your done open another db connection/file to persist the data and close.

5000 records is a lot. You may want to break this into chuncks and compare 100 or 200 db records at a time. this will also save resources instead of loading all 5000 records into memory.

a properly indexed database will also increase effeciency of querying records.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[tester321]

Thanks Turkbear, i think i will have to go that method or using a storedProcedure