Access SQL query incompatability (nolock)

[Robert]

Greetings

I am assisting a developer with an Access application performance problem and an ODBC timeout. In a nutshell they have a combo box with a drop down that queries a lookup table on a SQL Server database over ODBC and returns 30,000+ records each time it is accessed. By using a stored procedure to view the locks on the tables in the DB we suspect that a locking issue or a dead lock occurs which is causing the timeout. Once the user receives an ODBC time out they have to exit the app to free up the locks. We are trying to develop a method where a limited result set is returned there by reducing the amount locking that takes place. For the time being we are trying to modify the query to add in the 'with (nolock)':

SELECT SSN ,Name ,BEMS FROM tblClients with (nolock) ORDER BY Name

However, Access does not support 'with (nolock)' in the SQL statement. Is there a way to get around this?

[Salad]

Robert wrote:

Greetings

I am assisting a developer with an Access application performance
problem and an ODBC timeout. In a nutshell they have a combo box with a
drop down that queries a lookup table on a SQL Server database over ODBC
and returns 30,000+ records each time it is accessed. By using a stored
procedure to view the locks on the tables in the DB we suspect that a
locking issue or a dead lock occurs which is causing the timeout. Once
the user receives an ODBC time out they have to exit the app to free up
the locks. We are trying to develop a method where a limited result set
is returned there by reducing the amount locking that takes place. For
the time being we are trying to modify the query to add in the 'with
(nolock)':
SELECT SSN ,Name ,BEMS FROM tblClients with (nolock) ORDER BY Name

However, Access does not support 'with (nolock)' in the SQL statement.
Is there a way to get around this?

Don't know. I'd increase the timeout value.

At the same time....30K records for a combo? The word "Ridiculous"
comes to mind.

Can you run the query from the database window? If so, I'd use a form
to search for the records. I created a generic form for my app where I
can search on any column on any table and filter based on the open
arguments the way I want.

I suppose you can use a 30K row combo (it will grow most likely), but if
I were a user I wouldn't want to.

My $.02

[Robert]

"Salad" <oi*@vinegar.com> wrote in message
news:F0******************@newsread1.news.pas.earth link.net...

Robert wrote:

Greetings

I am assisting a developer with an Access application performance
problem and an ODBC timeout. In a nutshell they have a combo box with a
drop down that queries a lookup table on a SQL Server database over ODBC
and returns 30,000+ records each time it is accessed. By using a stored
procedure to view the locks on the tables in the DB we suspect that a
locking issue or a dead lock occurs which is causing the timeout. Once
the user receives an ODBC time out they have to exit the app to free up
the locks. We are trying to develop a method where a limited result set
is returned there by reducing the amount locking that takes place. For
the time being we are trying to modify the query to add in the 'with
(nolock)':
SELECT SSN ,Name ,BEMS FROM tblClients with (nolock) ORDER BY Name

However, Access does not support 'with (nolock)' in the SQL statement.
Is there a way to get around this?

Don't know. I'd increase the timeout value.

At the same time....30K records for a combo? The word "Ridiculous"
comes to mind.

Can you run the query from the database window? If so, I'd use a form
to search for the records. I created a generic form for my app where I
can search on any column on any table and filter based on the open
arguments the way I want.

I suppose you can use a 30K row combo (it will grow most likely), but if
I were a user I wouldn't want to.

My $.02

We don't want to increase the time out. We want to reduce the result set
with filters as we are running into dead lock issues. However, until we can
rewrite the query associated with the combo box and test it we would like to
prevent locking while the pull down is bringing data over the wire. I think
we will have to use a pass through query to resolve the SQL compatability
problem as Access doesn't support 'with (nolock)'. Thanx for your
suggestions just the same.

[DCM Fan]

You have a couple choices, but the quickest is to make sure that the query is
using the DISTINCT keyword in its SELECT statment:

SELECT DISTINCT blah...

Access insists on getting locks on tables that it requests w/o DISTINCT.

The other option is to bind the combo box to a pass-through query that calls a
stored procedure. This is the best-performing alternative.

Then again, it's a REALLY BAD programming practice to bind a 30,000 record set
to a combon box...you're much better off with some othere search mechansim
instead, using a 2-step process.

[John Winterbottom]

"Robert" <st*******@boeing.com> wrote in message
news:Hw*******@news.boeing.com...

Greetings

I am assisting a developer with an Access application performance
problem and an ODBC timeout. In a nutshell they have a combo box with a
drop down that queries a lookup table on a SQL Server database over ODBC
and returns 30,000+ records each time it is accessed. By using a stored
procedure to view the locks on the tables in the DB we suspect that a
locking issue or a dead lock occurs which is causing the timeout. Once
the user receives an ODBC time out they have to exit the app to free up
the locks. We are trying to develop a method where a limited result set
is returned there by reducing the amount locking that takes place. For
the time being we are trying to modify the query to add in the 'with
(nolock)':
SELECT SSN ,Name ,BEMS FROM tblClients with (nolock) ORDER BY Name

However, Access does not support 'with (nolock)' in the SQL statement.
Is there a way to get around this?

You could create a stored procedure on the server that uses the no lock
hint, (alternatively use SET TRANSACION ISOLATION LEVEL READ UNCOMMITTED.
Then call this procedure using a passthrough query, or else fill the combo
by hand with a callback function.

Tere's another method that is quite a bit of work, but may be worth it.
Create a temporary, local jet database, open a forward-only, read-only
recordset and dump the data into the local database. Then you can use this
for doing all your filtering, sorting etc. This works very well for
read-only data that you need to access frequently, such as are found in
combo and list boxes.