Hi all,
Has anyone found a super fast way of grabbing data from a remote
database (e.g. MS SQL Server, MySQL etc) an stuffing it into a
REALSQLDatabase (in-memory or file)?
I need to populate a REALSQLDatabase with records from a remote
database, that's easy enough, but I'm not getting the kind of speed I
really need. I need it to be about 10x faster than I'm currently
getting to be acceptable.
Currently I go through a do ... Loop through a RecordSet, create a
new DatabaseRecord, for each field I assign a column of the correct
type and then insert the DatabaseRecord.
This is all on an in-memory database table with no indexes, they are
intentionally created after the data is loaded and copied to the file
based db for speed, and the inserts are wrapped by a begin and
commit, with a commit and begin every 1000 records to ensure things
go smoothly.
The copy to the attached file db is fast, works fine, so I'm thinking
it's the traversing the recordset (MS SQL Server via ODBC, but this
isn't really relevant) that is slow.
What else can I do to make things go faster? Here is the bit of code
that seems to be slowest:
memDB.SQLExecute("begin;")
TransCount = 0
do
TransCount = TransCount + 1
NewRecord = New DatabaseRecord
for SchemaIndex As Integer = 1 to FieldCount
FieldNameString = SelectRS.IdxField(SchemaIndex).Name
Select Case dFields.Value(FieldNameString)
Case "VarChar"
NewRecord.Column(FieldNameString) =
SelectRS.IdxField(SchemaIndex).StringValue
Case "SmallInt", "Integer"
NewRecord.IntegerColumn(FieldNameString) =
SelectRS.IdxField(SchemaIndex).IntegerValue
Case "Double"
NewRecord.DoubleColumn(FieldNameString) =
SelectRS.IdxField(SchemaIndex).DoubleValue
Case "TimeStamp"
NewRecord.DateColumn(FieldNameString) =
SelectRS.IdxField(SchemaIndex).DateValue
Case "Boolean"
NewRecord.BooleanColumn(FieldNameString) =
SelectRS.IdxField(SchemaIndex).BooleanValue
Case "Clob"
NewRecord.Column(FieldNameString) =
SelectRS.IdxField(SchemaIndex).Value
Case "Blob"
NewRecord.BlobColumn(FieldNameString) =
SelectRS.IdxField(SchemaIndex).Value
Else
NewRecord.Column(FieldNameString) =
SelectRS.IdxField(SchemaIndex).StringValue
End Select
next
memDB.InsertRecord(TableNameString, NewRecord)
if App.Quiting then
memDB.SQLExecute("commit;")
Return True
end if
if TransCount > 999 then
memDB.SQLExecute("commit;")
memDB.SQLExecute("begin;")
TransCount = 0
end if
SelectRS.MoveNext
Loop Until SelectRS.EOF
memDB.SQLExecute("commit;")
dFields is just a dictionary with field type keyed by field name.
Hope someone has any kind of I idea on how I could improve the above
or can suggest a better method, I'd be eternally grateful.
Regards,
--
Ian M. Jones
___________________________________
http://www.imijsoft.com
http://www.ianmjones.net (blog)
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
