April 15, 2008 at 6:57 pm
I am using SQL 2005 x32 with SP2 installed.
The Access database is connected to the SQL Server using ODBC linked tables. It ran slow but managed to work in SQL 2000. After converting to SQL 2005, the user had difficulty adding a record into the "Customers" table and got an error message "ODBC Timeout." Most of time they had to ask everyone (except one or two) to close the Access so a record can be inserted into the table.
I used sp_who2 to find out the the insert command is blocked by another suspended process (a select command), but sp_who2 does not tell what table is involved in the suspension. Further diagnosis shows the startup screen of the Access database is a continuous form and contains a full list of customers (about 13,000). I created a stored procedure for loading the list and used a pass-through query as the data source of the continuous form. But the problem persists. I still got "suspsended process."
Here is something interesting. After the startup screen appeared. I scrolled the list down to the end. Then I checked sp_who2. There is no suspended process. I do not know why and could hardly say that I found a solution. There are many other continuous forms in the Access database and I could not make them all go to the last record once they are open.
Moreover, I am frustrated by the fact that there is official explanation about the status column, which, as I can see, has four values.
- Background (only in master db so I am not interested in.)
- sleeping (I guess it is the same as being idle.)
- Runnable (strange word, I guess it means that "it has been executed successfully.")
- Suspended (I am not sure it means "failed" or "waiting")
Here are my questions:
How do I resolve the "Timeout" problem?
What is the definition of "Suspended"?
Thanks.
Seaport
April 16, 2008 at 6:16 am
Suspended means waiting. Something interrupted the process and it's in a wait state. I'm not that familiar with Access, but from what you're describing, Access is in some manner holding open the connection, and the query, until you process all the data on the client side, scrolling to the end of the record set. It must have some kind of "live" connection state or something that is holding open that connection. I'd focus on Access as the source of the problem, but what exactly it's doing, I couldn't say.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2008 at 6:53 am
MS Access is a bit of a terrible front-end for a SQL Database. It is not really designed for a multi-user environment. To add to that, you have added the ODBC layer to it through linked tables and that creates a bit of a mess.
If you can, change your Access database to an Access Data Project (.adp file). This will allow you to have your same forms but connect directly to the SQL Server via a native OLEDB driver and eliminate the overhead and pile of problems you will get with ODBC.
If you cannot do this, check all of the ODBC settings and linked server settings in your Access database. You need to look for the lock type properties. You may be able to adjust the configuration to allow for an optomistic concurrency model that will reduce the number of resource locks you have.
April 17, 2008 at 8:00 am
Hi,
Rather than looking at Access as the problem perhaps someone should question the implementation.
Why do you need to be able to look at 13,000 records at one time?
An assumption here but i'm betting your users do a search on the records to a retrieve a specific
customer from which you are directed to further data about that customer
i.e double clicking on a record takes you to some transactional data sales data for example.
The logical answer here is to change the implentation.
Use the single form view and populate the recordset with the string of a search query.
i.e
Private Sub Form_Load()
Me.RecordSource = ""
End Sub
Private Sub TXTCustAccountSearch_AfterUpdate()
Dim StrCustAccount as String
Dim StrSQL as String
StrCustAccount = nz(trim(Me.TXTCustAccountSearch),"0")
If StrCustAccount = "0" Then
MsgBox "Please enter a valid Account Number", vbInformation, "Account Search"
Else
StrSQL = "Select Customer.CustAccount, Customer.CustFName, Customer.CustSName from Customer where (((Customer.CustID) ='" & StrCustAccount & "'))
Me.Recordsource = StrSQL
Me.Requery
End If
End Sub
This will retrieve one customer based on the criteria entered into the text box, no match no customer.
The obvious benefit is you are only retrieving the data you need.
K.
April 17, 2008 at 8:14 am
13,000 or 30, if Access is going to require him to scroll through every select list to the bottom or it will retain a lock on that table... You have a bigger problem. That said, the argument I hate having, that I have over and over again, is reminding the business that "having" everything and "seeing" everything are two different things. So, as you say, supplying a limited list of values rather than 13,000 is the preferred method. I just think that's a secondary fix, in this case, because the problem he's experiencing isn't related to the size of the data set.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 18, 2008 at 6:51 pm
Hi, thanks for all your replies.
Michael,
ADP is definitely an option to me. But converting the whole system is a pretty major undertake and my client is hesitating.
Karma,
Showing just a record makes sense to a programmer, but it takes me time to explain to the client, because they want the ability to scroll up and down freely. When the system gets to the point that it just cannot output the full list, I may still need to show part of the "List." For example, if the user searches for "American Shipping", the system will show all customers with name starting "A".
Grant,
I agree with you that the size of the dataset might not be the issue. I am not sure whether the client (Access) or the server caused the "suspended" process. Anyway, I figured out a way to eliminate the "Suspended" process. Access 2003 allows a form's recordset assigned at runtime. So I created a ADO recordset object, which retrieves the data from the sql server, and assigned the continuous form's recordset to the newly created one. In way, I guess, I forced Access to retrieve all the data when the line of code is executed.
Still, I do not understand why the continuous form caused "suspended" process. Maybe it is related to the local network connection. When I tested the system on my laptop, where both SQL Server (dev ed.) and Access resided, there was no "suspended" process.
Again, thanks to your valuable inputs.
Seaport
April 20, 2008 at 1:19 am
A suspended SPID is waiting for a resource (CPU, data from cache) to become available.
For an excellent discussion of this topic I would recommend:
SQL Server 2005 Performance Tuning using Waits and Queues:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 21, 2008 at 3:55 am
April 29, 2008 at 6:57 pm
Marios and Livius,
Thanks a lot. Both papers are great, especially Livius', which is right on my problem.
Seaport
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply