Slow processing

[burton1986]

Hi All, I have a question and would be welcome any help/suggestions.

I have a table that is in the below format

RA Start Days Date
James 31-07-09 2
Burton 01-08-09 2

I need to create new records in a different table to show everyday after and
including the startdate based on the days figure. For example James would be
as below.

Ra Start
James 31-07-09
James 01-08-09
James 02-08-09

I have code that does this already but it is taking about 5 secs per "day"
which when working with 500K records in the first table it will take forever
to complete considering the number of "days".

My Code is below can you please let me know if you can suggest a way to speed
it up?

DoCmd.SetWarnings False

Dim Rs As Recordset
Dim Num As Integer
Dim CurrentDate As Date
Dim I As Integer

Set Rs = CurrentDb.OpenRecordset("QryRS")

Rs.MoveFirst
Do Until Rs.EOF

Num = Rs("Days")
CurrentDate = Format(Rs("Start"), "DD/MM/YYYY")

[Forms]![Form1]![RA] = Rs("Ra")

For I = 1 To Num + 1

CurrentDate = Format(CurrentDate, "DD/MM/YYYY")

DoCmd.RunSQL "INSERT INTO TblDaysApp ( RA, Start, 1 ) SELECT QryRS.RA, QryRS.
Start, Cdate(Format('" & Format(CurrentDate, "DD/MM/YYYY") & "')) AS Expr1
FROM QryRS WHERE (((QryRS.RA)='" & [Forms]![Form1]![RA] & "'));"

CurrentDate = Format(DateAdd("D", 1, CurrentDate), "DD/MM/YYYY")

Next I

Rs.MoveNext

Loop


Thanks a lot for your time.

 

[Alex Dybenko]

Hi,
only thing, which can slow down it is QryRS, are you sure you need it in
your insert query?

you can use this syntax:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Jack Leach]

I haven't fully digested your code, but at a quick glance I would say move
the DoCmd.RunSQL to the outside of the loop. Running this inside the loop is
what's costing you a lot of performance.

As you are doing this in code, looping through a recordset and running a
query, it stands to reason that there is a certain structure based on key
variables. This leads me to believe that you can make a more complex query
to handle the entire situation rather than putting the query in a loop. It's
generally not good practice to do so.

One other thing that may help out is to get rid of all the Format()
functions on your dates. You are storing the dates into a table, and the
Format of the date has no bearing on anything (the format should be set
through forms and reports, but as far as the table is concerned it doesn't
matter what it looks like). There's a lot of typecasting going on with the
Formats, thought that is not nearly as big a performance hit as running a
query inside a loop.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[burton1986]

Thanks a lot for your replies but I need to run the SQL based on the current
Unique RA so I need to run within the loop.

Anymore ideas?

I haven't fully digested your code, but at a quick glance I would say move
the DoCmd.RunSQL to the outside of the loop. Running this inside the loop is
what's costing you a lot of performance.

As you are doing this in code, looping through a recordset and running a
query, it stands to reason that there is a certain structure based on key
variables. This leads me to believe that you can make a more complex query
to handle the entire situation rather than putting the query in a loop. It's
generally not good practice to do so.

One other thing that may help out is to get rid of all the Format()
functions on your dates. You are storing the dates into a table, and the
Format of the date has no bearing on anything (the format should be set
through forms and reports, but as far as the table is concerned it doesn't
matter what it looks like). There's a lot of typecasting going on with the
Formats, thought that is not nearly as big a performance hit as running a
query inside a loop.

hth

Hi All, I have a question and would be welcome any help/suggestions.

[quoted text clipped - 57 lines]

.