performance problem about inserting qv selections to database via macro

Tue, 30 Nov 2010 15:28:38 GMT

hi all,

i wrote a macro for creating table and inserting selections in qv to a database.that s all right.it works without any problem.BUT there is a problem about its performance.if you write only 100 rows, it works well.it does this job in a few seconds.but if you want to write data like 100.000 rows it works too slowly.i didnt wait but i can say that it may last aprx 60-70 mins.

does anybody know how to increase writng speed? or can i do the same job with another application?

you can find the code works here:

sub dbCreateInsert

dim sConn, oRS, sSQL1,sSQL2,var1
set var1= ActiveDocument.Variables("VARIABLE")
var1 = ActiveDocument.Variables("VARIABLE").GetContent().string
set doc = ActiveDocument
Set sConn = CreateObject("ADODB.Connection")
sConn.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=USERNAME;Password=PASSWORD;Data Source=DBNAME;"
set oRS =CreateObject("ADODB.Recordset")

'creating table with name gettiing from variable
sSQL1="CREATE TABLE " & var1 & " (CUSTOMERID varchar(20))"
set oRS = sConn.execute(sSQL1)

'selecting all different rows
set cLoRecord=doc.Fields("CUSTOMERID _NO").GetPossibleValues()
for i=0 to cLoRecord.Count-1
vRecord = cLoRecord.Item(i).Text
doc.Fields("CUSTOMERID _NO").Select vRecord

set CM1 = doc.Fields("CUSTOMERID").GetPossibleValues()
vm1 = cM1.item(0).Text

' writing to db
sSQL2="INSERT INTO " & var1 & " (CUSTOMERID ) VALUES (" & "'" & vm1 & "')"
set oRS = sConn.execute(sSQL2)
next

'warning after writing
doc.ClearAll
msgbox("all your selectins has been written")
set oRS = Nothing
set sConn = Nothing

end sub

performance problem about inserting qv selections to database via macro

Wed, 01 Dec 2010 13:08:32 GMT

Write to Oracle only if cLoRecord.Count < 100 . Otherwise skip writing; reading will be equally slow

-Alex

topic performance problem about inserting qv selections to database via macro in QlikView

performance problem about inserting qv selections to database via macro

performance problem about inserting qv selections to database via macro