?
Solved

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

Posted on 1998-12-01
7
Medium Priority
?
322 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 450 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
Independent Software Vendors: 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!

 
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

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.

Question has a verified solution.

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

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 …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…
Suggested Courses
Course of the Month12 days, 6 hours left to enroll

752 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