vb.net iterative ms sql 2005 lookup.
Posted on 2007-04-10
i am pretty novice at vb.net so i ask your patience.
i have made a visual studio 2003 vb.net application that essentialy splits out a csv file to an array,
does some conditional processing and creates an xml file for each row. each file contains about 40,000 records (rows).
these xml files contain a pointer(path) to an image file for the purpose of importing both the metadata contained in the xml file(descriptor) and the image into a content mangement system.
the part i am unsure about is that i want to lookup into a mssql 2005 table using one of the array values, for each record. i then add the products from the lookup to the appropriate elements in the xml file.
and then proceed to the next row.
my question is:should i be using a data adapter, data reader or just a plain query or stored procedure.?
i have it working with a data adapter, but when i run the application on the whole 40000 record file, it (the application) looks like it freezes, but it actually completes and there are 40000 xml files with the appropriate results, after about 1.5 hours/40000 records.
it takes about 1.5 hours to process the the entire 40000 records. and the processor is maxed. does this sound normal?
so if someone could advise me if i am using the correct methodology for this, i would be very grateful.