Solved

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

Posted on 1998-12-01
7
319 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
[X]
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
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

688 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