hello...
i'm uploading data from an excel file to the server, and to check the
data values; i loop though excel file row by row and compare the value
with database value. if anything does not match i throws an error.
this makes it pretty slow if the record is big.. can someone please
advice me how to overcome this problem?
There's not much you can do speed up your process as it is a table scan.
However, there are other methods you can use... you could create a
checksum on your data.
To create a checksum, you would pass all your excel data into a checksum
function (i.e. SHA1 or MD5 - examples in VBscript for both are online).
Perhaps the spreadsheet could feed all the values as a single long
string into the checksum function. Then store the checksum value in a
predefined cell on the excel spreadsheet (or perhaps append the checksum
to the filename?).
On the server side, you would pass all the relavant database data into a
checksum algorithm in the same format as the excel spreadsheet (i.e. in
a long string). If both checksums (Excel and Database) match, there is a
very very high probabilty the data is identical. There are rare times
when different data can create the same checksum. Certain checksums are
more unique than others. CRC32 is the most lightweight but the least
unique (likely to return a duplicate if you were to run random data
through it for a couple of weeks). MD5 or SHA1 should return values that
are pretty much unique.
