help with this code please. Runs WAY TO SLOW!!

[daddypost]

Okay, here was my first attempt at this code to connect to a sybase server, get a recordset of tables, find what column number contains records I need, and then inserting values into a local table. It works. HOWEVER, 1)I have open 3 connections to the same database throughout, 2)It runs slow as christmas(20,000 records takes about 45 minutes to insert.one of my tables has 450,000 records). If anyone can help me with any of those problems, or just give me some good advice on how to clean this up to make it run in production better I would be forever grateful. In spite of looking like I know nothing about VBA, here is my code:

Option Compare Database

Sub get_audit_tables()
Dim sconntable As Variant
Dim proj_audit As ADODB.Connection
Dim cmdtables As New ADODB.Command
Dim sSQLtables As String
Dim rstables As ADODB.Recordset
Dim sSQLaudit As String
Dim cmdenddate As New ADODB.Command
Dim rsenddate As ADODB.Recordset
Dim denddate As Date
Dim strenddate As String

sconntable = "DSN=SYBSSPAY;Server Name=sybsspay,14000;User ID=MCOMBEST;Password=Kassidy05;"

Set proj_audit = New ADODB.Connection
With proj_audit
.ConnectionString = sconntable
.Open
End With
'get end date
strenddate = "Select end_date From RTIhr.dbo.u_payroll_report_parms,DBShrpn.dbo.payroll_run_type_pay_pd," & _
"DBSpclh.dbo.period Where RTIhr.dbo.u_payroll_report_parms.payroll_run_type_id = " & _
"DBShrpn.dbo.payroll_run_type_pay_pd.payroll_run_type_id and " & _
"RTIhr.dbo.u_payroll_report_parms.pay_pd_id = DBShrpn.dbo.payroll_run_type_pay_pd.pay_pd_id and " & _
"DBShrpn.dbo.payroll_run_type_pay_pd.periodic_cal_id = DBSpclh.dbo.period.periodic_cal_id and " & _
"DBShrpn.dbo.payroll_run_type_pay_pd.cal_yr = DBSpclh.dbo.period.cal_yr and " & _
"DBShrpn.dbo.payroll_run_type_pay_pd.period_type_code = DBSpclh.dbo.period.period_type_code and " & _
"DBShrpn.dbo.payroll_run_type_pay_pd.pd_nbr = DBSpclh.dbo.period.pd_nbr"
cmdenddate.ActiveConnection = proj_audit
cmdenddate.CommandText = strenddate
Set rsenddate = cmdenddate.Execute
denddate = rsenddate.Fields(0).Value




sSQLtables = "select sybase_table_name from DBShrpn.dbo.hr_audit_ctrl where audit_ind='Y' and " & _
"sybase_table_name not in('position','emp_status','emp_pay_element')"
cmdtables.ActiveConnection = proj_audit
cmdtables.CommandText = sSQLtables
'get sybase Audit tables
Set rstables = cmdtables.Execute

Do While Not rstables.EOF
stablename = RTrim(rstables.Fields(0).Value)
'get recordset from audit table
sSQLaudit = "select * from DBShrpn.dbo." & stablename & "_aud "
' where " & _
' "convert(datetime,(substring(ACTION_DATETIME,6,2)+'-'+substring(" & _
' "ACTION_DATETIME,9,2)+'-'+substring(ACTION_DATETIME,1,4)+' '+substring(ACTION_DATETIME,12,12))) BETWEEN " & _
' "DATEADD(day,-6,'" & denddate & "') AND DATEADD(day,1,'" & denddate & "')"
get_secondary_column (sSQLaudit)
rstables.MoveNext
Loop
End Sub


Private Sub get_secondary_column(sSQLcolumn As String)

Dim cmdcolumn As New ADODB.Command
Dim rscolumn As ADODB.Recordset
Dim icolumn As Integer

sconntable = "DSN=SYBSSPAY;Server Name=sybsspay,14000;User ID=MCOMBEST;Password=Kassidy05;"

Set proj_audit = New ADODB.Connection
With proj_audit
.ConnectionString = sconntable
.Open
End With

cmdcolumn.ActiveConnection = proj_audit
cmdcolumn.CommandText = sSQLcolumn
Set rscolumn = cmdcolumn.Execute

'Determine what column second emp_id resides in
For I = 0 To rscolumn.Fields.Count - 1
Select Case rscolumn.Fields(I).Name
Case "A_emp_id", "A_participant_id", "A_individual_id"
icolumn = I
Dim response
response = Insert_data(sSQLcolumn, icolumn)
Exit Sub
Case Else
End Select
Next
End Sub


Private Function Insert_data(sqlInsert As String, icolumn2 As Integer)
Dim cmdinsert As New ADODB.Command
Dim rsinsert As New ADODB.Recordset
Dim ilength As Integer
Dim iwhere As Integer
Dim n As Integer



sconntable = "DSN=SYBSSPAY;Server Name=sybsspay,14000;User ID=MCOMBEST;Password=Kassidy05;"

Set proj_audit = New ADODB.Connection
With proj_audit
.ConnectionString = sconntable
.Open
End With

cmdinsert.ActiveConnection = proj_audit
cmdinsert.CommandText = sqlInsert
Set rsinsert = cmdinsert.Execute


'ilength = Len(sqlInsert)
'iwhere = InStr(sqlInsert, "where")
stablename = Mid(sqlInsert, 15)
icolumn = icolumn2


If rsinsert.EOF = True Then
Exit Function
Else
n = 0

Do While Not rsinsert.EOF
n = n + 1
Select Case IsNull(rsinsert.Fields(icolumn).Value)

Case False

sqlcurrentdb = "INSERT INTO weekly_changes(ACTION_USER,ACTION_CODE,action_date,emp_id,change_table)" & _
"Values('" & rsinsert.Fields(1).Value & "','" & rsinsert.Fields(0).Value & "','" & _
Left(rsinsert.Fields(2).Value, 10) & "','" & rsinsert.Fields(icolumn).Value & "','" & _
stablename & "')"
CurrentDb.Execute sqlcurrentdb
rsinsert.MoveNext

Case Else

sqlcurrentdb = "INSERT INTO weekly_changes(ACTION_USER,ACTION_CODE,action_date,emp_id,change_table)" & _
"Values('" & rsinsert.Fields(1).Value & "','" & rsinsert.Fields(0).Value & "','" & _
Left(rsinsert.Fields(2).Value, 10) & "','" & rsinsert.Fields(3).Value & "','" & _
stablename & "')"
CurrentDb.Execute sqlcurrentdb
rsinsert.MoveNext

End Select
Loop
End If

End Function



Thanks again if anyone has help.

 

[jadams0173]

Are you just more or less moving records from one or more tables to another table (looks like append query?) I think one thing is you don't have to open 3 connections to the same DB as long as you never close the connection. I think that's correct.

You should always close your connections and recordsets when you are done with them. Try to explain a little more exactly what you are doing.

Also are the tables you are quering indexed?

 

[easyit]

Hi,

Have you tried to create the queries in the querypane as passtrough queries - and execute those? I ask because I'm not sure that they are executed on the sybase server, but on your access client instead.

EasyIT

 

[JerryKlmns]

EyasyIT has a strong point.

Also if you open one connection keep using the same till the end! You could even declare it [blue]Public[/blue] to keep it handy everywhere. Plus close recordsets and set them = Nothing and do the same for connections when done.

Select Case evaluates every case. If there are only two cases prefere If .. Then .. Else .. End If

Joins in Access should be better defined as Inner or Left/Right and not at the WHERE clause.

 

[daddypost]

Thank you all for your help. The reason I'm not using pass through queries is that the tables that I'm pulling from in sybase will change from week to week and you cannot use a variable as a table name in transact-sql(from what I understand). So each time this runs, I have to get the tablenames from a seperate sybase table where there is a Y indicator. Once I get those tables, I am appending 5 columns(out of 50+ from each table) into an access table to build a report. Most tables have a column named either *_emp_id,*_participant_id,or * individual_id. I am appending all of the data from tables with these columns into one access table. I have to check first to see if these columns exist in the table I'm looking at , and I have to find the column number within that table that houses those values(there is both an A_, and B_emp_id). So once I get the recordset, I have to scan each row to see whether or not is has an A_emp_ID, or a B_emp_id and append the record accordingly. I've tried setting up the connection once, then just adding different command objects in the seperate subs, but it always give me an error. The only thing I thought of that I could do was append the entire recordset(whether it has A_ or B_ values) and then run an access query to determine which column to use. Hope this clears up a little of what I'm trying to do. Maybe I'm making it harder than it has to be. But thank you again for any help you can give.

 

[easyit]

Hm,

I believe that once the queries are present, you can modify the connectionstring hence the tablename (querydef etc).
Just try one query to see if the perfomance is improved - and work from there to a final solution.

EasyIT

 

[cmmrfrds]

The ADO query is going directly to Sybase so in effect this is a pass-thru query.

The logic is allocating memory that never gets released, think of reworking the logic to avoid this.
For example, this sub, get_secondary_column creates a new recordset each time it is entered and does not release the memory and then allocates more memory the next time it is entered.

A couple of techniques to think about.
Instead of creating a recordset each time bring all of the data back to the client one time which is practical for a few thousand rows or less. This could be done in a recordset and then disconnected from the source(i.e. Sybase) and then search the recordset as needed. Look up disconnected recordsets and the Filter Method to see how this is done.

Another technique is using the Sybase Temp table area. Instead of bringing back a recordset, create a temp table in Sybase. Once a temp table is created it will stay in the tempdb area until the connection is dropped - it belongs to the user creating the connection. You can then do repeated queries against the temp table as needed.