8 hour SQL runtime 
Author Message
 8 hour SQL runtime

Running Access 95 under Win95 on a Pentium 166 with 48Mb of RAM, the
following code

Sub cmdExecute_Click()
On Error GoTo Err_cmdExecute_Click

Dim dbs As DATABASE
Dim rst As Recordset
Dim varCounter As Integer

txtStartTime.Value = Time()
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT [Dept] from [Primary table]")
varCounter = 0

DoCmd.SetWarnings False
While Not (rst.EOF)
    varCounter = varCounter + 1
    txtCounter.Value = varCounter
    txtCurrentTime.Value = Time()
    txtElapsedTime.Value = DateDiff("m", [txtStartTime],
[txtCurrentTime])

    Forms!frmExport.Refresh
    Forms!frmExport.Repaint

    DoCmd.RunSQL "SELECT qryMASTER_DEPT.* INTO [TempTable] FROM
qryMASTER_DEPT WHERE qryMASTER_DEPT.Dept = '" & rst!Dept & " '"
    DoCmd.TransferSpreadsheet acExport, 5, "TempTable",
"C:\webshare\wwwroot\inventory\datafiles\" & rst!Dept
    txtDept = rst!Dept
    rst.MoveNext
Wend

dbs.TableDefs.Delete "TempTable"
txtStopTime.Value = Time()
Forms!frmExport.Refresh
Forms!frmExport.Repaint
MsgBox "Transfers complete"

rst.Close
dbs.Close

Exit_cmdExecute_Click:
    Exit Sub

Err_cmdExecute_Click:
    MsgBox Err.Description
    Resume Exit_cmdExecute_Click

End Sub

requires a timed 8 hours to run on a database of 1,800 records.  As this
is the first SQL routine that I'm running, I am unfamiliar with speed
and efficiency.  Is 8 hours realistic or is something seriously wrong
here?  The code does exactly what I want it to, but the time to execute
seems a wee bit excessive to me.



Tue, 25 Jul 2000 03:00:00 GMT  
 8 hour SQL runtime

Albert-

Are there 1800 departments or only 1800 total records?  You don't say how
many departments you're having to loop through.  Note that you don't need to
go to the trouble of defining a Temp table to export.  You can export the
result of a non-parameter query directly.  Something like:

Dim qd As QueryDef

   ' establish a dummy named query
    Set qd = db.CreateQueryDef("MyTempQuery", "Select * From [Primary
Table]")

  While Not rst.EOF
' ...
    qd.SQL =  "SELECT qryMASTER_DEPT.* FROM qryMASTER_DEPT WHERE
qryMASTER_DEPT.Dept = '" & rst!Dept & " '"
    DoCmd.TransferSpreadsheet acExport, 5, "MyTempQuery",
"C:\webshare\wwwroot\inventory\datafiles\" & rst!Dept
'...
  Wend

  Set qd = Nothing
  db.QueryDefs.Delete "MyTempQuery"

That should speed it up considerably.
--
John Viescas
author, "Running Microsoft Access 97"
http://www.amazon.com/exec/obidos/ISBN=1572313234/

Quote:
>Running Access 95 under Win95 on a Pentium 166 with 48Mb of RAM, the
>following code

>Sub cmdExecute_Click()
>On Error GoTo Err_cmdExecute_Click

>Dim dbs As DATABASE
>Dim rst As Recordset
>Dim varCounter As Integer

>txtStartTime.Value = Time()
>Set dbs = CurrentDb()
>Set rst = dbs.OpenRecordset("SELECT [Dept] from [Primary table]")
>varCounter = 0

>DoCmd.SetWarnings False
>While Not (rst.EOF)
>    varCounter = varCounter + 1
>    txtCounter.Value = varCounter
>    txtCurrentTime.Value = Time()
>    txtElapsedTime.Value = DateDiff("m", [txtStartTime],
>[txtCurrentTime])

>    Forms!frmExport.Refresh
>    Forms!frmExport.Repaint

>    DoCmd.RunSQL "SELECT qryMASTER_DEPT.* INTO [TempTable] FROM
>qryMASTER_DEPT WHERE qryMASTER_DEPT.Dept = '" & rst!Dept & " '"
>    DoCmd.TransferSpreadsheet acExport, 5, "TempTable",
>"C:\webshare\wwwroot\inventory\datafiles\" & rst!Dept
>    txtDept = rst!Dept
>    rst.MoveNext
>Wend

>dbs.TableDefs.Delete "TempTable"
>txtStopTime.Value = Time()
>Forms!frmExport.Refresh
>Forms!frmExport.Repaint
>MsgBox "Transfers complete"

>rst.Close
>dbs.Close

>Exit_cmdExecute_Click:
>    Exit Sub

>Err_cmdExecute_Click:
>    MsgBox Err.Description
>    Resume Exit_cmdExecute_Click

>End Sub

>requires a timed 8 hours to run on a database of 1,800 records.  As this
>is the first SQL routine that I'm running, I am unfamiliar with speed
>and efficiency.  Is 8 hours realistic or is something seriously wrong
>here?  The code does exactly what I want it to, but the time to execute
>seems a wee bit excessive to me.



Tue, 25 Jul 2000 03:00:00 GMT  
 8 hour SQL runtime

You have put a refresh and repaint inside a While...Wend, but more important
is that you actually do the entire export for each record in the recordset -
is that what you want to do ?? Would it not be better to gather all the
records you need to export and then export the hole selection into one file.
Also you should use the Nothing reserved word to clean your memory when
finished.



Tue, 25 Jul 2000 03:00:00 GMT  
 8 hour SQL runtime

Quote:

> You have put a refresh and repaint inside a While...Wend,

Yes.  The refresh/repaint lines were added so I could have an
elapsed-time clock on the form so I could tell exactly how long the
thing was running.

Quote:
> but more important is that you actually do the entire export for each record > in the recordset -  is that what you want to do ?? Would it not be better to > gather all the records you need to export and then export the hole selection > into one file.

The database is designed for asset-tracking.  At our site, we have
somewhere around 40-45 different departments, each of which has their
own equipment.  The intent of the routine is to create x number of .xls
files, each file having the name of a department and containing that and
only that department's equipment.  Each department has somebody
responsible for their own stuff, and that person gets his and only his
file.


Tue, 25 Jul 2000 03:00:00 GMT  
 8 hour SQL runtime

Quote:

> Albert-

> Are there 1800 departments or only 1800 total records?  You don't say how
> many departments you're having to loop through.  

1800 records, maybe 40-45 departments.


Tue, 25 Jul 2000 03:00:00 GMT  
 8 hour SQL runtime

I'd just add here that I'd delete the tempquerydef if it existed at the
front of the routine also. This saves your bacon if somehow the routine got
interrupted the last time it ran.

--
Buddy Springman
Transcripts Unlimited, Inc.

Quote:

>Albert-

>Are there 1800 departments or only 1800 total records?  You don't say how
>many departments you're having to loop through.  Note that you don't need
to
>go to the trouble of defining a Temp table to export.  You can export the
>result of a non-parameter query directly.  Something like:

>Dim qd As QueryDef

>   ' establish a dummy named query
>    Set qd = db.CreateQueryDef("MyTempQuery", "Select * From [Primary
>Table]")

>  While Not rst.EOF
>' ...
>    qd.SQL =  "SELECT qryMASTER_DEPT.* FROM qryMASTER_DEPT WHERE
>qryMASTER_DEPT.Dept = '" & rst!Dept & " '"
>    DoCmd.TransferSpreadsheet acExport, 5, "MyTempQuery",
>"C:\webshare\wwwroot\inventory\datafiles\" & rst!Dept
>'...
>  Wend

>  Set qd = Nothing
>  db.QueryDefs.Delete "MyTempQuery"

>That should speed it up considerably.
>--
>John Viescas
>author, "Running Microsoft Access 97"
>http://www.amazon.com/exec/obidos/ISBN=1572313234/


>>Running Access 95 under Win95 on a Pentium 166 with 48Mb of RAM, the
>>following code

>>Sub cmdExecute_Click()
>>On Error GoTo Err_cmdExecute_Click

>>Dim dbs As DATABASE
>>Dim rst As Recordset
>>Dim varCounter As Integer

>>txtStartTime.Value = Time()
>>Set dbs = CurrentDb()
>>Set rst = dbs.OpenRecordset("SELECT [Dept] from [Primary table]")
>>varCounter = 0

>>DoCmd.SetWarnings False
>>While Not (rst.EOF)
>>    varCounter = varCounter + 1
>>    txtCounter.Value = varCounter
>>    txtCurrentTime.Value = Time()
>>    txtElapsedTime.Value = DateDiff("m", [txtStartTime],
>>[txtCurrentTime])

>>    Forms!frmExport.Refresh
>>    Forms!frmExport.Repaint

>>    DoCmd.RunSQL "SELECT qryMASTER_DEPT.* INTO [TempTable] FROM
>>qryMASTER_DEPT WHERE qryMASTER_DEPT.Dept = '" & rst!Dept & " '"
>>    DoCmd.TransferSpreadsheet acExport, 5, "TempTable",
>>"C:\webshare\wwwroot\inventory\datafiles\" & rst!Dept
>>    txtDept = rst!Dept
>>    rst.MoveNext
>>Wend

>>dbs.TableDefs.Delete "TempTable"
>>txtStopTime.Value = Time()
>>Forms!frmExport.Refresh
>>Forms!frmExport.Repaint
>>MsgBox "Transfers complete"

>>rst.Close
>>dbs.Close

>>Exit_cmdExecute_Click:
>>    Exit Sub

>>Err_cmdExecute_Click:
>>    MsgBox Err.Description
>>    Resume Exit_cmdExecute_Click

>>End Sub

>>requires a timed 8 hours to run on a database of 1,800 records.  As this
>>is the first SQL routine that I'm running, I am unfamiliar with speed
>>and efficiency.  Is 8 hours realistic or is something seriously wrong
>>here?  The code does exactly what I want it to, but the time to execute
>>seems a wee bit excessive to me.



Tue, 25 Jul 2000 03:00:00 GMT  
 8 hour SQL runtime

There is something seriously wrong with your code, you should be able to
achieve what you want with the following

Sub cmdExecute_Click()
    dim dbs as database
    dim strSQl as string

'The following only selects records to append from qryMASTER_DEPT
'where there are matching records in [Primary Table], which is what
'your code was doing in a round about way
    strSQl= "INSERT INTO TempTable SELECT qryMASTER_DEPT.* "
    strSQl=strSql & "FROM qryMASTER_DEPT INNER JOIN [Primary Table] "
    strSQl=strSql & "ON qryMASTER_DEPT.Dept = [Primary Table].Dept"

    Set Dbs = CurrentDb
    dbs.execute strSQl
    DoCmd.TransferSpreadsheet acExport, 5,
"TempTable","C:\webshare\wwwroot\inventory\datafiles\" & rst!Dept

    set dbs=nothing
End Sub

Which should only take moments.


Quote:
>Running Access 95 under Win95 on a Pentium 166 with 48Mb of RAM, the
>following code

>Sub cmdExecute_Click()
>On Error GoTo Err_cmdExecute_Click

>Dim dbs As DATABASE
>Dim rst As Recordset
>Dim varCounter As Integer

>txtStartTime.Value = Time()
>Set dbs = CurrentDb()
>Set rst = dbs.OpenRecordset("SELECT [Dept] from [Primary table]")
>varCounter = 0

>DoCmd.SetWarnings False
>While Not (rst.EOF)
>    varCounter = varCounter + 1
>    txtCounter.Value = varCounter
>    txtCurrentTime.Value = Time()
>    txtElapsedTime.Value = DateDiff("m", [txtStartTime],
>[txtCurrentTime])

>    Forms!frmExport.Refresh
>    Forms!frmExport.Repaint

>    DoCmd.RunSQL "SELECT qryMASTER_DEPT.* INTO [TempTable] FROM
>qryMASTER_DEPT WHERE qryMASTER_DEPT.Dept = '" & rst!Dept & " '"
>    DoCmd.TransferSpreadsheet acExport, 5, "TempTable",
>"C:\webshare\wwwroot\inventory\datafiles\" & rst!Dept
>    txtDept = rst!Dept
>    rst.MoveNext
>Wend

>dbs.TableDefs.Delete "TempTable"
>txtStopTime.Value = Time()
>Forms!frmExport.Refresh
>Forms!frmExport.Repaint
>MsgBox "Transfers complete"

>rst.Close
>dbs.Close

>Exit_cmdExecute_Click:
>    Exit Sub

>Err_cmdExecute_Click:
>    MsgBox Err.Description
>    Resume Exit_cmdExecute_Click

>End Sub

>requires a timed 8 hours to run on a database of 1,800 records.  As this
>is the first SQL routine that I'm running, I am unfamiliar with speed
>and efficiency.  Is 8 hours realistic or is something seriously wrong
>here?  The code does exactly what I want it to, but the time to execute
>seems a wee bit excessive to me.



Tue, 25 Jul 2000 03:00:00 GMT  
 8 hour SQL runtime

Albert-

Did you try my suggestion?  It's not the 1800 records, it's the 40-45 times
through the loop.  There's a lot of overhead in creating and deleting the
working table, and because the working table has no Primary Key (Make Table
won't create one), accessing the rows is a bit slower.  Using filtered
queries as the direct target of the TransferSpreadsheet should be tons
faster.

--
John Viescas
author, "Running Microsoft Access 97"
http://www.amazon.com/exec/obidos/ISBN=1572313234/



Quote:

> > Albert-

> > Are there 1800 departments or only 1800 total records?  You don't say
how
> > many departments you're having to loop through.  

> 1800 records, maybe 40-45 departments.



Thu, 27 Jul 2000 03:00:00 GMT  
 8 hour SQL runtime

Quote:

> Albert-

> Did you try my suggestion?  It's not the 1800 records, it's the 40-45 times
> through the loop.  There's a lot of overhead in creating and deleting the
> working table, and because the working table has no Primary Key (Make Table
> won't create one), accessing the rows is a bit slower.  Using filtered
> queries as the direct target of the TransferSpreadsheet should be tons
> faster.

I worked in the code on Friday afternoon but didn't get a chance to
execute yet (I had to go, and they were shutting down the power over the
weekend), but I will be runing a test this morning and will let you know
how it goes.


Fri, 28 Jul 2000 03:00:00 GMT  
 8 hour SQL runtime

Should rst be defined exactly like the other one?  Compiling this module
generates an error.  Also, when run, it comes up with a message
indicating that TempTable is not found - shouldn't it create it
automatically?  The other code that I'm running didn't seem to have a
specific "Define table here" line, so I'm not sure why this one doesn't
work?

Quote:

> There is something seriously wrong with your code, you should be able to
> achieve what you want with the following

> Sub cmdExecute_Click()
>     dim dbs as database
>     dim strSQl as string

> 'The following only selects records to append from qryMASTER_DEPT
> 'where there are matching records in [Primary Table], which is what
> 'your code was doing in a round about way
>     strSQl= "INSERT INTO TempTable SELECT qryMASTER_DEPT.* "
>     strSQl=strSql & "FROM qryMASTER_DEPT INNER JOIN [Primary Table] "
>     strSQl=strSql & "ON qryMASTER_DEPT.Dept = [Primary Table].Dept"

>     Set Dbs = CurrentDb
>     dbs.execute strSQl
>     DoCmd.TransferSpreadsheet acExport, 5,
> "TempTable","C:\webshare\wwwroot\inventory\datafiles\" & rst!Dept

>     set dbs=nothing
> End Sub

> Which should only take moments.



Fri, 28 Jul 2000 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. 8 hour runtime now 2 minute runtime

2. How do I calculate Working Hours (Man Hours)

3. Hour Alarm on the Hour?

4. VB Front end dll loses connection to back end SQL server db after an hour

5. force sql to sum hours and not date?

6. Runtime error using Date() in SQL query

7. Help needed with SQL (runtime Error 91)

8. Connectig to SQL at Runtime

9. Change SQL for Crystal Reports at runtime

10. Create Table with SQL at Runtime

11. Datareport with SQL at runtime

12. Using SQL in datareport at runtime?

 

 
Powered by phpBB® Forum Software