PDA

View Full Version : Efficiency



doubtfire
09-09-2009, 05:25 AM
I have a new project that
1. Load Customer data (500,000 rows) into a Excel spreadsheet (Ver 2007) from MS-SQL
2. Update same set of data after user modify them back to SQL

My question is what is the most effective way to do :
a. Should I update them by calling the Stored procedure to send each Excel row by row ?
b. Should I send the whole (500,000 rows) set back to SQL for updating ? Arrays !?

Any suggestions ? Any examples ? Thanks !: pray2:

CreganTur
09-09-2009, 06:15 AM
What kind of modifications are you doing to this data? I ask because you will not be able to pull all of these records into Excel at the same time because it exceeds the maximum number of rows that Excel can hold.

doubtfire
09-09-2009, 06:36 AM
The Customer table has thirty fields like CustomerID, Name, Address1, Address2, City, Country, CreditLimit, Rate, ... Excel 2007 could accomodate more than 1,000,000 rows. User can change any fields other than CustomerID which is a primary key. Thanks.

CreganTur
09-09-2009, 07:40 AM
Personally, I really dislike the idea of pulling hundreds of thousands of records into Excel just so a user can change a bare percentage of the records. Depending on what the user needs to change, and why, is going to determine how you should approach this.

It would be best if you could identify the records that the user needs to touch, and hten pull down only those records. This could be where a databsae application like Access would come in handy... I really can't say more without understanding what you're trying to accomplish.

doubtfire
09-09-2009, 07:48 AM
Thanks again for the comments.
The idea of like having a new column (at end of row) with a "x" to indicate the row(s) being updated (and which need to be sent back for database updating). That's acceptable. My next issue is if there are two hundred rows that need to be sent/updated back to database. What is the best way to do do in terms of efficiency. Updating the Excel row by row would not lock too many tables but more communication. Send them back with an array would ease the front but the SQL server has a lot more data and locks to handle.
If you buy the second one with the array, do you have code sample I could refer.
Any suggestions !? Thank you!

CreganTur
09-09-2009, 08:11 AM
Why would your users be changing this data? What is this data used for- some sort of customer database? Please provide more information about this process; knowing this will allow me to make a suggestion/point you in a direction.

doubtfire
09-09-2009, 08:25 AM
The Customer service person is responsible to change existing customer information besides the CustomerID. After changing fields like address, credit limit she updates through Excel then she sends back the data to SQL. Excel is a tool that she could see everything in a sheet and does the changing.
My key point here is the efficient way to send data back to SQL for saving.
THANKS.

CreganTur
09-09-2009, 09:37 AM
The Customer service person is responsible to change existing customer information besides the CustomerID. After changing fields like address, credit limit

how does she know which customers need updating?

doubtfire
09-09-2009, 09:52 AM
The Customers information could be coming from fax, phone, or seasoning changing like credit rating updating, credit amount yearly change... Could you please provide an efficient way to transfer those "new" pieces of data from Excel to SQL? (for simplicity all fields of the three hundred rows besides CustomerID need to be updated and data validation has been done)
Thanks.:(