Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Posted on 1998-12-01
Last Modified: 2013-12-25

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
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
4) Has anybody some better idea to solve my problem?

Thanks a lot for your help.

Nicolas Regez
Question by:scheuring
  • 3
  • 2
  • 2

Accepted Solution

cedricd earned 150 total points
ID: 1497904
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

Expert Comment

ID: 1497905
Use bellow algorithm...
'ary->(1 to n,1 to m) dimension array
on error goto errhandle
set rs=cn.openresultset("select * from yourtables",rdopenkeyset)
for i=1 to n 'rows
for j=1 to m 'columns
next j
next i
exit sub

its very fast algorithm.

Author Comment

ID: 1497906
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 ?
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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


Expert Comment

ID: 1497907
yes you use rdConcurLock


Author Comment

ID: 1497908
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 ?

Expert Comment

ID: 1497909
Yes, use sql server :

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

and continue as an access table.

Expert Comment

ID: 1497910
use ADO!!! OpenKeyset and BatchOptimisticLock
add records and last rs.UpdateBatch and rs.close


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question