Solved

Writing data from SQL Database

Posted on 2000-03-09
12
168 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
[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
  • 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
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 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
 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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 …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

688 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