July 30, 2003 at 9:55 am
I am running an Access .adp file, and am having trouble with a module that keeps timing out on what I would think should be relatively easy queries to the database.
First - I have pinged the database, and response time to the client machine is good.
Second - the .adp file is running on a local machine, not a network file share.
Third - I have built a clustered index on the 'filedate' and 'lettercode' fields of the source table.
Example query:
Insert into summary_table SELECT eventcode, compcode, ltrim(rtrim(swbt_hold.lettercode)) lettercode, CONVERT(varchar, filedate, 101) as filedate, 'swbt_hold' as tblname, COUNT(*) COUNT, 'lettercode' as code FROM swbt_hold WHERE release = 0 GROUP BY eventcode, compcode, swbt_hold.lettercode, filedate order by lettercode
Can anyone help me figure out why this query is timing out?
Thanks very much!
July 30, 2003 at 10:08 am
1) Have you run the code in Query Analyzer and seen any difference?
2) If you have, look at the execution plan and see where the most processing time is being taken up.
3) Can you convert to a stored procedure?
July 30, 2003 at 11:01 am
I have tried running it as a stored proc and gotten the same result. I will run it in query analyzer next and see if I can see anything. Thanks.
July 30, 2003 at 11:12 am
It ran fine (82 seconds) in query analyzer, so I'm not sure the execution plan will hold any answers to why it hangs when the same query is posed to the database via the .adp access program.
The query is written in a module as ADO.
Thanks for your help!
July 30, 2003 at 11:25 am
82 seconds is still a really long time for a simple query. Regardless, what is the ADO code you are executing? There are 2 timeouts (I know, it's confusing); one for the connection and one for the command object. Go ahead and post some code, if possible.
July 30, 2003 at 11:47 am
The majority of that time (82 seconds) seemed to be from scanning clustered index.
Code:
Sub ReleaseHold()
Dim cmd1 As New ADODB.Command
Set cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = "delete from swbt_summary"
cmd1.Execute
cmd1.CommandText = "Insert into swbt_summary SELECT eventcode, compcode, ltrim(rtrim(swbt_hold.lettercode)) lettercode, CONVERT(varchar, filedate, 101) filedate, 'swbt_hold' tblname, COUNT(*) COUNT, 'lettercode' as code FROM swbt_hold WHERE release = 0 GROUP BY eventcode, compcode, swbt_hold.lettercode, filedate order by lettercode"
cmd1.Execute
The error is 'Run time error (bunch of numbers) Timeout Expired'
Thanks!
July 30, 2003 at 9:19 pm
before you execute, add the following:
cmd1.CommandTimeout = 0
This will cause the command to not timeout. The default timeout period in Access is 30 seconds.
I would also suggest verifying that the fields eventcode, compcode, lettercode, filedate and release are indexed in the swbt_hold table. (These are the fields in your WHERE and GROUP BY clauses).
July 31, 2003 at 6:29 am
Hmm, I've never seen an Insert statement like that...would someone enlighten me as to why you would need a Group By / Order By in an Insert Statement? I'm not being sarcastic here =P.
July 31, 2003 at 7:49 am
quote:
Hmm, I've never seen an Insert statement like that...would someone enlighten me as to why you would need a Group By / Order By in an Insert Statement? I'm not being sarcastic here =P.
You need the GROUP BY because it's a SELECT statement taking details into a summary table. The ORDER BY is useful if subsequent SELECTs from the summary table have no ORDER BY clause and the author wants a default ordering. This, of course, assumes that the inserted into summary table is a HEAP, and will not reorder the inserted rows based on its clustering key.
Edited by - jpipes on 07/31/2003 07:50:20 AM
July 31, 2003 at 8:00 am
Ok, I think I read it wrong, I thought the statement was only Adding one Row to a Table...sorry about that.
July 31, 2003 at 8:18 am
Thank you very much! Setting the command timeout to zero seems to have solved the problem.
July 31, 2003 at 8:22 am
No problem - glad I could help.
I am the dba for my company and we run our business on a SQL database with an ADP front end. I have had to learn a lot of tricks to keep this working correctly (including this one).
A side effect of this timeout thing is how it affects Access reports - the default timeout in Access is 30 seconds, as I mentioned earlier. If you are running a command or opening a recordset, you can get around that with the CommandTimeout setting. However, if you are basing a report on a recordset, you cannot override the timeout property (bummer).
The only workaround I have been able to come up with is to open a recordset based on a command and then assign that recordset to the recordsource of the report.
Anyway, enough rambling. Have fun!
July 31, 2003 at 8:30 am
Actually, for your Recordset Issue, all you should have to do is set the "CommandTimeOut" Property of your Connection Object to = 0 or a very high number as well. I usually try to keep my Connection and Command TimeOuts to be the same thing in my programs so I can determine if it's the Connection or the Command that is timing out.
July 31, 2003 at 8:33 am
*The only workaround I have been able to come up with is to open a recordset based on a command and then assign that recordset to the recordsource of the report.*
I have actually had to do that myself as well in order to make the report load properly. Its always nice to know I'm not the only one scratching my head with these products 🙂
July 31, 2003 at 8:41 am
The problem with the timeout issue on the connection for an Access ADP is that the Project is the connection and I have not found a way to set the timeout on that.
In case you don't use ADPs, they are 'always connected' to the SQL Server. You are not establishing a connection to it each time you need access.
If you were, you could set the timeout of the connection object.
This is something that MS should absolutely address in Access for Office System 2003 if they want more people to use Access projects.
I myself and moving more and more to Web based VB.NET front ends, and away from Access front ends (of course, then I lose the wonderful Access Reporting Engine - have to see how Reporting Services will be!!)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply