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

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

VB5 / RDO : a problem with SQL-Server 6.5 when inserting

Hello.

My problem is the following:
In my vb5 project, I have two longinteger arrays of dimension n (n between
10 to 1000), which I would like to store in a table in SQL-Server 6.5 via
RDO.
If I run the INSERT query n times passing the contents of the arrays row by
row, it takes about 15 seconds for n=1000. If I generate a stored procedure
which takes 2*m parameters, so that I can store m rows in a single query, it
takes exactly the same time to store the 1000 rows in the table.
An idea I had was to pass all data in the arrays as varbinary parameter and
then to extract the information with substring in the stored procedure, so I
would have only one query to call and would call it assynchronously. But
this would not work because I did not succeed in storing a whole array in
one rdoQuery parameter from vb. Another idea would consist in passing the
information in a variable of type text. This is not straight forward because
I could not manage to extract parts of the variable to use as value for the
INSERT statement.

Now my questions:
1) Can I expect performance gains by limiting the number of queries for my
1000 INSERT statements or is the bottleneck in the INSERT itself?
2) Is there a way of storing the contents of an array of longint or an array
of byte in an rdoQuery parameter of type varbinary?
3) How could I possibly extract parts of the contents of variable of type
text?
4) Has anybody some better idea to solve my problem?

Thanks a lot for your help.

Nicolas Regez
0
scheuring
Asked:
scheuring
  • 3
  • 2
  • 2
1 Solution
 
cedricdCommented:
why don't you use a temporaly access table (creating by you on the programs), put the data on it and when it's finish then use the insert sql statement : insert into (sql server table) (field1,field2...)
select field1,field2 ... from access table
0
 
hakyemezCommented:
Use bellow algorithm...
'ary->(1 to n,1 to m) dimension array
'cn-->rdoConnection
'rs-->rdoResultset
on error goto errhandle
cn.begintran
set rs=cn.openresultset("select * from yourtables",rdopenkeyset)
for i=1 to n 'rows
rs.addnew
for j=1 to m 'columns
rs(j)=arj(i,j)
next j
rs.update
next i
rs.close
cn.committran
exit sub
errhandle:
cn.rollbacktran

its very fast algorithm.
0
 
scheuringAuthor Commented:
If I use : set rs=cn.openresultset("select * from yourtables",rdopenkeyset)
the resultset is always readonly. Allthought I checked the other options (rdConcurLock,rdconcurRowVer,rdConcurValues) it does not work.

Do you have an advice hakyemez ?
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.

 
hakyemezCommented:
yes you use rdConcurLock

0
 
scheuringAuthor Commented:
Finally I found a way to manage the data access. But the best performance I got was around 100 INSERTS/Sec. Compared to our SQL-Server this is still very slow (approx. 3000 INSERTS/Sec. if I run a stored procedure).

Do you have an idea how to improved the data throught-put ?
0
 
cedricdCommented:
Yes, use sql server :

set con = db.openconnection("dbname",,false,"odbc;dsn=;database=;uid=;passwd=")

and continue as an access table.
0
 
hakyemezCommented:
ok.
use ADO!!! OpenKeyset and BatchOptimisticLock
add records and last rs.UpdateBatch and rs.close


0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now