Solved

Writing data from SQL Database

Posted on 2000-03-09
12
162 Views
Last Modified: 2010-05-02
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
Comment
Question by:mscala
  • 5
  • 2
  • 2
  • +3
12 Comments
 
LVL 4

Accepted Solution

by:
wqw earned 50 total points
ID: 2600696
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
 

Author Comment

by:mscala
ID: 2600832
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
 

Author Comment

by:mscala
ID: 2600855
Sorry the error message was - The operation requested by the application, is not supported by the provider.
0
 
LVL 1

Expert Comment

by:prozak
ID: 2600881
Have you tried doing a commit after every 50 records or so?
0
 

Author Comment

by:mscala
ID: 2600902
Sorry the error message was - The operation requested by the application, is not supported by the provider.
0
 
LVL 4

Expert Comment

by:wqw
ID: 2600923
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Expert Comment

by:Roopesh_Mohan
ID: 2600925
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
 
LVL 32

Expert Comment

by:bhess1
ID: 2601205
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
 

Author Comment

by:mscala
ID: 2603601
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
 

Author Comment

by:mscala
ID: 2603606
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
 
LVL 32

Expert Comment

by:bhess1
ID: 2605800
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
 
LVL 6

Expert Comment

by:andyclap
ID: 2608733
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now