• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Writing data from SQL Database

I currently have code that reads data from an Sql Database, and inserts the data (records) into a delphi database. The code takes too long as it takes about 2 seconds for each entry, so when there are a lot of entries it takes too long. I would just like to know if someone could give me some code that does the same thing, but which would take a lot quicker. I have code that writes to the Sql database, and transfereing 2000 records takes about 6 minutes, which is fine but returning the same data, takes an hour.
Here is the code for writing the records as well as retieving them.

This is the code that writes to the Sql Database:
WriteEntry = "INSERT INTO tblEliteTransaction" _
                            & " (BatchNo,TransLineNo,BankBranchCode,BankAcctNumber,Amount,BankAcctHolderName,UserReference,ErrorCode) VALUES " _
                            & "('" & WEBatchNumber & "','" & WEEntryNumber & "','" & WEBranchCode & "','" & WEAccountNumber & "','" & WEAmount & "'" _
                            & ",'" & WEAccountHolderName & "','" & WEUserReference & "','" & 0 & "');"
                            'On Error GoTo ehEntry:
                            SQLDB.Execute WriteEntry
                           
                            RsReadEntry.MoveNext

This is the code to retrieve the records, which i would like made faster:
WriteEntry = "INSERT INTO Entry_de" _
                    & "([Batch Number],[Entry Number],[Branch Code],[Account Number],Amount,[Account Holder's Name],[Transaction Type],[User Reference]) VALUES " _
                    & "('" & WEBatchNo & "','" & WETransLineNo & "','" & WEBankBranchCode & "','" & WEBankAcctNumber & "','" & WEAmount & "'" _
                    & ",'" & WEBankAcctHolderName & "','" & "CR" & "','" & WEUserReference & "');"
                    'On Error GoTo ehEntry:
                    ELITEDB.Execute WriteEntry
                     
                    RsTransTransaction.MoveNext

If someone could help please.
Thanks!
0
mscala
Asked:
mscala
  • 5
  • 2
  • 2
  • +3
1 Solution
 
wqwCommented:
have you tried using recordsets:

Dim rs As New Recordset
rs.Open "Select BatchNo,TransLineNo,BankBranchCode,BankAcctNumber,Amount,BankAcctHolderName,UserReference,ErrorCode From tblEliteTransaction", YourSQLConnStr, adOpenKeyset
rs.AddNew Array("BatchNo", "TransLineNo", "BankBranchCode", "BankAcctNumber", "Amount", "BankAcctHolderName", "UserReference", "ErrorCode"), _
    Array(WEBatchNumber, WEEntryNumber, WEBranchCode, WEAccountNumber, WEAmount, WEAccountHolderName, WEUserReference, 0)
rs.Update

this is using ADO for db access. what are you using for db access actually?

</wqw>
0
 
mscalaAuthor Commented:
Yes i am using ADO.

I used your code, but when you say rs.addnew Array(...  ) on this line of code, it gives the following error message - The operation requested by the operator is not supported by the provider.  Somewhere shouldn't we say where the data must be put, as in which table the data must be inserted into?
0
 
mscalaAuthor Commented:
Sorry the error message was - The operation requested by the application, is not supported by the provider.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
prozakCommented:
Have you tried doing a commit after every 50 records or so?
0
 
mscalaAuthor Commented:
Sorry the error message was - The operation requested by the application, is not supported by the provider.
0
 
wqwCommented:
you've opened the recordset with "select ... from <TableName>", haven't you? try optimistic locking this way:

rs.Open "select..", ConnStr, adOpenKeyset, adLockOptimistic

</wqw>
0
 
Roopesh_MohanCommented:
As i dont understand the way you populate RsTransTransaction.

I am just giving you a example

WriteEntry = "INSERT INTO tblEliteTransaction" _
                            & " (BatchNo,TransLineNo,BankBranchCode,BankAcctNumber,Amount,BankAcctHolderName,UserReference,ErrorCode)

SELECT (BatchNo,TransLineNo,BankBranchCode,BankAcctNumber,Amount,BankAcctHolderName,UserReference,ErrorCode)
FROM RsTransTransaction

I am sorry for not applying string properly...

now if you run

SQLDB.Execute WriteEntry

I hope to be faster... Post me on the updates....

-Roopesh
0
 
Brendt HessSenior DBACommented:
If you are using SQL Server 7.0 (the only version I've used, so the only one I can speak with any authority about), you might try using DTS to transfer the data.

First, try making a package to do the transfer in the Data Import and Export wizard.  Execute it, and see how fast this works.  

If this is significantly faster (as I have found that it usually is), then your program can execute the DTS package to perform the transfer.  Examples of how to do this are discussed in the SQL Server Books Online topic "DTS Samples".  Sample code may have to be retrieved from your SQL Server CD, as the samples are not installed by default.

If you're not using SQL Server 7.0, please ignore the above information :)
0
 
mscalaAuthor Commented:
wqw,

I am using ODBC, can this still be done using ODBC, as in using the array? I was mixed up with something else using ADO.
0
 
mscalaAuthor Commented:
bhess1,

HI, can you give me some sample code for the DTS, i am not familiar with this, and yes i am working with SQL7, but do not have the disks with the sample code on it. If you could please send me some sample that will do what i am trying to achieve here, which is basically reading information from a table on SQL, and transfereing all the data to a table on my computer.

This would be much appreciated.
Thanks!
0
 
Brendt HessSenior DBACommented:
The examples that I have are too large for posting here.  Send me your E-Mail address to brendthess@yahoo.com and I will forward you some VB5 code examples.
0
 
andyclapCommented:
Have you tried wrapping the whole thing in a transaction? Some database engines (Access included) will do these inserts faster.
I'm not sure about the delphi engine - it may be it's just slow at inserting. If you're creating the table from scratch, try removing the indexes and adding them again at the end.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now