Solved

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

Posted on 1998-12-01
7
314 Views
Last Modified: 2013-12-25
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
Comment
Question by:scheuring
  • 3
  • 2
  • 2
7 Comments
 
LVL 2

Accepted Solution

by:
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
0
 
LVL 3

Expert Comment

by:hakyemez
ID: 1497905
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
 

Author Comment

by:scheuring
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 ?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Expert Comment

by:hakyemez
ID: 1497907
yes you use rdConcurLock

0
 

Author Comment

by:scheuring
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 ?
0
 
LVL 2

Expert Comment

by:cedricd
ID: 1497909
Yes, use sql server :

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

and continue as an access table.
0
 
LVL 3

Expert Comment

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


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 2 47
bit defender blocks good applications 2 79
VBA: Select SQL query based on a config Sheet v2 11 38
Recommendation vb6 to vb.net or others 14 104
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now