Faster than what? Could you give some more information?
What hardware (esp. disk system) is used? What database system? Are the changes really UPDATEs, or INSERTs? (maybe bulk loads are feasible? See also this node about batch loads ).
Often, it helps to run many inserts/updates in a single transaction.
| [reply] |
Sorry for lack of details. The database we're using is mssql. im using perl script that runs through command prompt. actually we are updating 2 databases(no inserts and it would be 400 to 500 thousand rows per database). at each database we are updating 2 tables. im also using 3 queries(2 updates and 1 select for querying a third table). the pc's running on windows 2003 server,2.0ghz and 4gb of ram.
the main process of the script is that it loads a csv file(products) and updates the existing data on the database(price,stocks, etc..)
| [reply] |
Not withstanding javafan's excellent questions, a couple of possibilities that I've used successfully in the past to speed up updates.
- Upload the data required to make the updates into a simple, unindexed scratch table using the bulk loader.
Apply the updates from the scratch table to their destination(s) using a stored procedure.
- Temporarily delete any indexes or foreign keys from the table to be updated, that aren't explicitly required for the update process, and re-create them afterwards.
Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
"Science is about questioning the status quo. Questioning authority".
In the absence of evidence, opinion is indistinguishable from prejudice.
| [reply] |
BrowserUK ++!
It's a database question so that database way is the best for it.
| [reply] |
We are updating 400 to 500 thousand rows of data a week. our update takes about 2 days to finish.
That looks very slow.
But you don't give any useful information - perhaps 2 days is the best you can get. Some question to start with:
- What database?
- What queries?
- How many queries?
- How do the tables look like?
- Which indices are there?
- How large are the tables?
- How many other processes access the tables?
- Where's the time spend, in the database, or the program collecting the data?
- How much of the spend time are you waiting to get locks?
- Are you using transactions?
- Are other processes using transactions?
- Are you sure it's a Perl questions, and not a database/SQL question, better answered in a forum with more expertise?
| [reply] |