Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Speeding up multiple rows update (Read 4425 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Speeding up multiple rows update
26.09.06 at 14:11:44
 
Speeding up multiple rows update
 
Howard from the USA asked the following question:
 
Question:
I have been testing the multi-record update. It takes a long time to process because I think it is matching on every column in the where clause except the column that changed. Is there a way to specify the where condition on just the primary key or natural key.
 
Answer:
If you bring back a lot of data first and then run the multiple rows update function, it will take SQL*XL some time to find all changes. The best thing to do is to query as few records as possible and apply your changes to that. That way, running the multiple rows update function will not take a lot of time.
 
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Speeding up multiple rows update
Reply #1 - 26.09.06 at 22:13:04
 
Howard posted the following reply to me by email:
 
Thanks for the update. Unfortunately, If we cannot have a more efficient update process (use PK only in WHERE clause), I'm not sure we will be able to use the product for the project we have in mind. Instead of writing a forms-based application, we were hoping to use your tool within Excel to maintain shadow reference tables, similar to the way you can browse and update an Access table within Access. The requirement is to bring down the whole table from Oracle into Excel, do some sorting, change whole ranges of values (using copy/paste down several rows), and then update the Oracle table. The average size of a table would be 5,000-10,000 rows. We can't use Access Browse because we can't do the copy and paste down several rows like we can with Excel.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Speeding up multiple rows update
Reply #2 - 26.09.06 at 22:14:08
 
Hi Howard,
 
Yes, you can still use my product. I have been experimenting with another way of facilitating what you require.
 
First, please realise what the multiple rows update feature really does. When fetching your table in Excel, SQL*XL also keeps a copy in memory. When you run the multiple rows update process, SQL*XL checks row by row and field by field whether data has changed. It would be short of a disaster when you reorder the rows in Excel. The rows will not match up and SQL*XL will see all data as changes...
 
What you require can be achieved in a simpel way. SQL*XL can also build SQL statements in a cell. Experiment with the insert worksheet functions menu in SQL*XL. You can create an update statement. Try to build one for your table. You will manually need to specify the column names and the primary key column(s).
When you can create an update statement for each row I suggest you require the user to put a specific value in the column to the right of the data. Let them e.g. put a "U" in the cell to the right of a row that they have made a change in. With a simpel Excel if statement around the update statements that you can put in the column next to that you can only make the update statement visible where there is a U in the cell before it.
 
When you do it this way you end up with a set of visible update statements for SQL*XL to execute. Running them is easy. Select the column containing the update statements and open the SQL dialog. Specify to take the SQL from the worksheet and that each cell should be regarded as a separate statement. SQL*XL will run all the update statements. This will be a very fast way to achieve what you want.
 
You can automate this...
-The if statements and update statements can be prebuild, even be hidden from the user
-The marking of a changed row with the "U" can be automated using some VBA code
-The running of the SQL update statements can be automated as well. Record it once as a macro using SQL*XL and that's it.
 
With the above proposed solution you can reorder the data however you please. You can even delete rows. Inserting rows is a little tricky as you would actually need an insert statement.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1