[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

Copying data from one table to another.....

Hi all,
          I am doing this project in VB6 using ADO and my database is Oracle 9i..... I am trying to copy all the contents of a table into another table while doing some calculations on some fields.... Currently I am doing it by looping....

recordset.Open "Select * from LatestPosition", ADOConnection, adOpenStatic, adLockOptimistic, adCmdTable
recordset1.Open "Select * from MyNewTable", ADOConnection, adOpenStatic, adLockOptimistic,


Do while recordset.EOF <> True

recordset1.addnew
recordset1.fields(0)=recordset.fields(0)
recordset1.fields(1)=recordset.fields(1)
recordset1.fields(2)=CalculateXY(recordset.fields(2))
recordset1.fields(3)=recordset.fields(3)
recordset1.update

recordset.movenext
Loop
This takes quite a lot of time ....I am sure that this is not the best way . Please can someone suggest a better way ? I have also tested by replacing the Addnew/Update architechture by replacing with the Insert into statement. But the results weren't that much better.... It is slight improvement but still too slow......

Do while recordset.EOF <> True

ADOConnection.Execute "Insert into MyNewTable Values (recordset.fields(0),recordset.fields(1),CalculateXY(recordset.fields(2)),recordset.fields(3) "  'With the proper syntax of course ....

recordset.movenext
Loop

Imran
0
imarshad
Asked:
imarshad
  • 8
  • 4
1 Solution
 
Ashish PatelCommented:
Why dont you run this
"Insert Into MyNewTable Select * from LatestPosition"
0
 
Ashish PatelCommented:
recordset.Open "Insert Into MyNewTable (col1, col2, col3) Select col1, col2, col3 from LatestPosition"
0
 
Ashish PatelCommented:
sorry it should be ADOConnection.execute
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Ashish PatelCommented:
and create a function CalculateXY in oracle database and use in the select statement.
0
 
imarshadAuthor Commented:
The reason being that I need to recalculate the value of a field and then insert the new value....
You can see this in
recordset1.fields(2)=CalculateXY(recordset.fields(2))
0
 
imarshadAuthor Commented:
and create a function CalculateXY in oracle database and use in the select statement.
This cant be done..... It needs to be in software... It is not a simple function but quite a complex one....
0
 
konektorCommented:
if there are larger amount of data you want to move, it's better to do it in database.
insert into new_table(column1, ...) select column1, ... from old_table where ...
when you select data to your application and insert it from there - there is data traffic, which is not present when you run insert-select query
0
 
Ashish PatelCommented:
imarshad: If the process of calculating is very complex, then you wont have much options as looping and large amout of data processing ofcourse will take time. Except there is one thing which still you can try by creating Insert statements from the values you fetch from source recordset and then fire bunch of inserts at a time having ";" as separator. This will reduce time for inserting 1 record at a time and will insert lets say at a moment 100 entries at one go.
0
 
Ashish PatelCommented:
Bulk Inserting would be much faster in your case rather than inserting/adding one calculated record at a time. Do you get my point? Not too faster by for sure faster than the logic what you are using.
0
 
imarshadAuthor Commented:
I have done some more testing.... If my backend database is MS Access (With the exact same records) the loop takes almost 3 seconds to process all the records (3100 in the test case)....
With the same table structure and design and same no. of records(3100) and same query it takes about 30 seconds to complete in Oracle.... How can Oracle be so slow? What am I missing?
0
 
Ashish PatelCommented:
Are there any indexes which you have placed in ORACLE? And did you try bulk inserting in Oracle? Pl. let me know.
0
 
imarshadAuthor Commented:
My source table LatestPosition has got Index on field(0)... I always clear the destination table before entering data into it. i.e before this code I execute a "Delete from MyNewTable" command so that MyNewTable is empty....
How can I do bulk inserting?
0
 
Ashish PatelCommented:
By creating your "Insert into MyNewTable Values (recordset.fields(0),recordset.fields(1),CalculateXY(recordset.fields(2)),recordset.fields(3) " statements concatinated with another using ";" (semi colon) as separator. And rather than using delete statement use truncate to remove all rows or empty your table.
example:
var counter as int
var str as string
str = ""
coutner = 1
Do while recordset.EOF <> True
     Str = Str & "Insert into MyNewTable Values (recordset.fields(0),recordset.fields(1),CalculateXY  (recordset.fields(2)),recordset.fields(3) " & ";"
if counter = 500 then
ADOConnection.Execute Str
str = ""
else
counter = counter + 1
end if
recordset.movenext
Loop

If str <> "" Then
ADOConnection.Execute Str
End If
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now