opening ADP combo box causes high CPU utilization on SQL Server; query never completes

Hello all,
I'm using an Access 2000 (9.0.6926 SP-3) ADP as a front-end to a SQL Server
2000 (8.00.760 SP-3) database. SQL Server is running on a dedicated server.
There is a certain query, based on a view, that completes in about two
seconds via Query Analyzer. (It's a straightforward SELECT ... FROM
view_name WHERE ...; I'll spare you the code since the view does end up
hitting many tables, and I'm mostly curious whether this is a well-known bug
I wasn't able to find.) The query returns less than 300 rows.
We're using that query as the Row Source for a combo box in the ADP
front-end. When attempting to expand the combo box, the ADP seems to
freeze. CPU utilization for sqlservr.exe jumps to around 99. Firing up
Profiler beforehand and doing a trace on SQL Server shows a
SQL:BatchStarting and SQL:StmtStarting, with the TextData being the SELECT,
as I'd expect.
Eventually, if I kill the ADP (via task manager), the trace shows a
SQL:BatchCompleted with high numbers for CPU and Reads (unscientifically, it
seems that the longer I let the ADP run, the higher the numbers). There is,
of course, no SQL:StmtCompleted. I'm inclined to believe that, somehow, the
way that Access submits the query is different from Query Analyzer and is
causing SQL Server to go into an infinite loop of some kind.
I'm hoping this is a straightforward, known issue. I'd rather not open a
call with Microsoft; the beauracracy here with providing the up-front fee
(even if it is refunded) would make doing so very painful and difficult. I
can provide more back-end code, the execution plans, trace information,
etc., if anyone takes an interest. Thanks in advance for any help!
Regards,
Brian J. Parker
Systems Coordinator, Childhood Depression Research
Western Psychiatric Institute and Clinic, UPMC Health SystemA little follow-up on this problem:
The view on which the hanging SELECT is based includes a join between two
large tables; they were being joined on a shared derived (text) column
because the view writer didn't want to type out all the underlying integer
columns instead.
I re-wrote the view to use "real" columns and the problem vanishes (which
takes away some of the urgency). My original issue is, I think, still
valid-- a query that takes six seconds in Query Analyzer shouldn't run
indefinitely as the row source of a combo box-- but this may be a clue.
Regards,
Brian|||This would depend on how the data is retrieved for the combo box. Is it a b
ound control? That could be adding some overhead on the query being sent to
SQL Server.|||"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:B676064D-81AB-43FB-AE1C-23EC93FCC069@.microsoft.com...
> This would depend on how the data is retrieved for the combo box. Is it a
bound control? That could be adding some overhead on the query being sent
to SQL Server.
No, it's not a bound control, but thanks for the suggestion.
If you're curious: the control provides a list of research study interviews
meeting certain criteria. Based on the user's selection, we launch a report
that provides some information related to that interview.
Regards,
Brian