Can I...

[robbie]

Use the open method of a recordset (Select SQL Statement) and dump the resulting recordset into a delimited ascii file?

I need ultimate speed. At work I am pulling data from a live datasource (Many, many pulls) and taking the results and inserting them into tables on a sql server. The problem is twofold:

1) the process runs overnight and it takes too long.

2) I can't seem to figure out how to design the app using asynchronous processing using either the ado paramaters in the connection and recordset objects or by using an activeX exe code component. I don't know if using these methods would speed up the process but I need better results than I am getting doing one at a time which is what I am doing now.

Any help or links to some examples would be greatly appreciated...

[Garratt]

?

Open "C:\myfile.txt" for Output as #1

Do until rs.eof = true 
   Output #1, rs.fileds("myfield1") & "," & rs.fields("myfield2") & "," & rs.fields("myfield3" )
   rs.movenext
Loop

rs.close

[Ecniv]

Originally posted by robbie
Use the open method of a recordset (Select SQL Statement) and dump the resulting recordset into a delimited ascii file?

I need ultimate speed. At work I am pulling data from a live datasource (Many, many pulls) and taking the results and inserting them into tables on a sql server. The problem is twofold:

1) the process runs overnight and it takes too long.

2) I can't seem to figure out how to design the app using asynchronous processing using either the ado paramaters in the connection and recordset objects or by using an activeX exe code component. I don't know if using these methods would speed up the process but I need better results than I am getting doing one at a time which is what I am doing now.

Any help or links to some examples would be greatly appreciated...

1) Best time to run it

2)
What process do you have at the moment?
What are you trying to achieve?
Can you change layout/table structures?
What sort of volumes of data are you talking about (approx size per row) ?
Do you need validation included?

Vince

[robbie]

Originally posted by Ecniv
1) Best time to run it

2)
What process do you have at the moment?
What are you trying to achieve?
Can you change layout/table structures?
What sort of volumes of data are you talking about (approx size per row) ?
Do you need validation included?

Vince

1) It does run overnight which is the best time run my apps. Very limited server processes are taking place at that time.
2) Right now, I use the recordset open method to read in the rows, assign the data into an array, scrub the data and write out the recordset in a sequential file. Then, I read in the scrubbed sequential file and use the connection object's execute method to append into SQL tables.
2) We hire a data storage company that stores all of our banking data. I am trying to get at this data and save it into our own SQL server tables for custom report generation for all the decision makers of the bank. This is a national bank so the size of all this data is huge.
2) I cannot change any table structures. Those are setup by the data store.
2) Its alot of volume. It's the quivalent of running around 30+ append queries with records anywhere between 10,000 and 3,500,000 records with fields from 6 to 90 fields.
2) There is no validation required. The process I use now does scrub the data in case of bad date values.

Now, this live data that I am hitting is obtained through a DSN file which was purchased by our company from the data store. We can link these tables in a access database but everytime we try to run an access append query, we have to authenticate by typing in a valid userid and password. Since this runs on a scheduler, there is no user interaction. We use to run this in the morning when we got in but it gets pretty tedious constantly entering the userid and password with every append query. I never figured out how to disable the odbc dialog box in access. In VB, I hardcode the userid and password right in the open method of the connection object.

There are a few other problems I have using the process I'm using now. Certain tables will return around a dozen records with the following ADO error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Now, I looked in ADO books and the Microsoft web site to resolve this error. I checked the registry for entries and everything matches what Microsoft suggests and I still get these errors.

Because of this error, I am unable to loop through a recordset using the movenext method because when these records are hit, vb is unable to read the field values. I basically lose those records which, of course, is unacceptable.

Late last night, I started to play around with saving a recordset as an xml file. Then, I get the entire recordset in which I could open sequentially, parse the data and insert the data into SQL.

I still need more speed. The hardware is some of the newest processors. Its just alot of data being read from one data source and being saved to another.

I still wish I could have these jobs run asynchronously. Right now, the app waits until each procedure is completed before running the next procedure. I even tried to split the app up into 2 or 3 executable files but then I run into the same user trying to log into the data source multiple times.

I get access to the data source around 2:00am. By then, all the batch processing is done by the data storage company and there are no shared or locked tables. Production starts around 7:00am every day which gives me a 5-hour window. My current process runs for about 8-1/2 hours which is too long and because of this, we still run this parallel to our old procedure which uses access to link to these live tables and run append queries into access which is very time consuming having to enter my userid and password with every append query I run, then waiting for each one to complete before moving on to the next append query. Plus, maxing out to the 2-gig access max is constant meaning I export out alot of data and suck it into SQL. All of these has not allowed me to put my app into full production.

As of now, I am practicing on how to use a ActiveX exe program and a standard exe program. The problem I am having is that my app has no user interface. It runs with module files, no forms. Because of this, I can't use the WithEvents keyword which means I am having trouble with the ActiveX exe program calling an event in the standard exe program.

I've also tried to use the asynchronous paramaters in the connection, command and recordset object methods but I can't find any useful examples on the web as to how to use if effectively. The ADO book I have (2.1) says its possible but does not give any examples on how to use it.

PS: I forgot to mention that these data storage tables are daily data tables. They overwrite everyday which is why I need to get at them and append to our own SQL server tables so I have more of a history of data.

[Ecniv]

Phew.....
Wow...

I take it you cannot link directly between the Sql server and the Datastorage tables?? Just thinking that if you can link the data storage tables into SQL server as external then run Sql copy statements (insert intos) from one to the other.

As to the error, I think its a bog standard one that floats up. Which means it probably isn't much to do with the actual recordsets (guessing). I also assume tthis means that you'd need to be up at 2am to test manually, yourself, to step through the code at the error points, or dump the results out into a text file for viewing later?

Lemme think some more and get back to you shortly... See if I can get some viable options...


Vince

[robbie]

I appreciate the help. I can test during business hours. I use record and error logs in all my apps that gives me not only how fast each sub procedure takes but the amount of records it reads and writes and every error that occured while the app was running and in what procedure the error occured. My main sub procedure is the only procedure where I use the resume statement, otherwise, I use resume next.

If the connection fails, I time a 60 second loop, then I resume to the offending statement and try again. That part of the application has worked well. If the data store is still running batch jobs, it will not allow me to connect so my program tries every minute afterwards until it gets connected...

How about using asynchronous processing or using the async options in ADO? Have you ever used those paramaters? Right now, unless I can multi-thread or speed up by using asynchronous processing, I thought of saving returned recordsets into xml files, then, open, parse and insert into SQL.

I have never programmed using SQL. All my programming skills is using Visual Basic.

[robbie]

An example of Asynchronous Processing:

The code for my ActiveX exe program:

Option Explicit

Public Event OrdersFinished()
Public Event OrderDetailsFinished()
Public Event ConnectionComplete()
Public Event DisconnectComplete()

Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Public cs As String                     ' Connection String
Public sql As String                    ' SQL Statement

Public Sub ConnectToDatabase()

    Let cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;Persist Security Info=False"

    Set cn = New ADODB.Connection
    cn.Open cs
    RaiseEvent ConnectionComplete

End Sub

Public Sub OrderDetails()

    Let sql = "SELECT * FROM [Order Details]"

    Set rs = New ADODB.Recordset
    rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    rs.Save "C:\OrderDetails.xml", adPersistXML
    rs.Close
    Set rs = Nothing

    RaiseEvent OrderDetailsFinished

End Sub

Public Sub Orders()

    Let sql = "SELECT * FROM Orders"

    Set rs = New ADODB.Recordset
    rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    rs.Save "C:\Orders.xml", adPersistXML
    rs.Close
    Set rs = Nothing

    RaiseEvent OrdersFinished

End Sub

Public Sub DisconnectDatabase()

    cn.Close
    Set cn = Nothing
    RaiseEvent DisconnectComplete

End Sub

Now, the code from my standard exe program:

Option Explicit
Private WithEvents Test As CTest
Private vStart, vConnect, vOrders, vOrderDetails, vDisconnect

Private Sub Form_Load()

    Let vStart = Timer
    Set Test = New CTest

    Test.ConnectToDatabase
    Test.OrderDetails
    Test.Orders
    Test.DisconnectDatabase

End Sub

Private Sub Test_ConnectionComplete()

    Let vConnect = Timer

End Sub

Private Sub Test_OrderDetailsFinished()

    Let vOrderDetails = Timer

End Sub

Private Sub Test_OrdersFinished()

    Let vOrders = Timer

End Sub

Private Sub Test_DisconnectComplete()

    Let vDisconnect = Timer

End Sub

I used the Northwind database since it comes with Access for this example. The problem is that these tables are so small I cannot tell if I am speeding up my process. Maybe each procedure needs to be in its own ActiveX exe program. If anybody has thoughts on this, please me know. thanks.

[Ecniv]

Hi,

Right, whilst the Net connection at work was down this morning I roughed out an idea. May not be worth while, but then again

It depends on you having several computers at your disposal plus multi-connections to the source and destination data.

I'll assume you cannot directly link from source to dest, or that there is undesireable results if you did. So expanding on your text files......

Have an Access DB holding a list of :
source tables
(connection string as well?)
Status of the tables
StartDateTime
CompletedDateTime

1) Each comp used to transfer (1-4) connects to the access db on the local network and checks the table listing
2) Each comp then grabs a free table (status of Free or 0 or whatever) from the recordset, sets the startdatetime, and the status flags. You shouldn't need this checked for two trying at the same time, but you may want to think about a locking/waiting time loop. Also store the id of comp, so if a power failure occurs or the line drops you can resume the table from whereever it left off.
3) Store data from source to text file
4) when complete, close text file, store completeddatetime, and set status again then loop back to look for the next free table.
5) keep looping until all tables have been actioned/set and downloaded. Then stop running

1) At the same time have comp (1-4) checking the db for completed status and uploadstatus of free.
2) When found, set upload status to processing (startdatetime optional) and upload text file into sql server. I assume there is a simple import function, either that or do manually via sql statements... your choice.
3) when complete, set status to imported look again until all files are imported. Can store Comp ID for this too if you like, and add in extra checks...

Does this sound somewhat like you are doing at the moment?
Are you only using one comp atm?

Vince