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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian J. Parker - 16 Feb 2004 18:23 GMT

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 System

Reply to this Message

Baisong Wei[MSFT] - 17 Feb 2004 11:32 GMT

Hi Brian,

Thank you for using the newsgroup and it is my pleasure to help you with
you issue.

As my understanding of your question, you have a ADP and there is one combo
box, the row source is select ... from view_name, and this run fine in
Query Analyzer. However, when open the form with the combo box in the ADP,
it hangs and the CPU utilization is high, right?

Please take this steps:
1) New a ADP and connect to the SQL Server and the Database that your
connect to in the ADP which met problem.
2) New a query, the SQL of it would be 'select ... from view_name' (same as
the one you met problem), how about the performace?
3) Create a new form with only one combo box. the row source will be ther
query you have just created. Then open it again, anything abnormal?

If the above steps will cause nothing abnormal, could you import all the
objects in the old ADP to the new one. Then try to open it. If every thing
is normal in the new one, you could replace the old one with the new one.

Hope this helps. If you still have any question, please feel free to post
message here and I am ready to help. Thanks

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Reply to this Message

Brian J. Parker - 17 Feb 2004 13:55 GMT

> As my understanding of your question, you have a ADP and there is one combo
> box, the row source is select ... from view_name, and this run fine in
> Query Analyzer. However, when open the form with the combo box in the ADP,
> it hangs and the CPU utilization is high, right?

Yes. The CPU utilization is high on the SQL Server.

> Please take this steps:
> 1) New a ADP and connect to the SQL Server and the Database that your
[quoted text clipped - 3 lines]
> 3) Create a new form with only one combo box. the row source will be ther
> query you have just created. Then open it again, anything abnormal?

I am not sure I understand (2).

I made a new ADP and connected to the SQL Server and database, as you
suggest in (1). Then I tried to create a REPORT based on the query; the
report opens with no problem. Then I create a new form with one combo box,
as you suggest in (3). I make the row source the same query; it hangs.

I can also do this connecting the ADP to our development server, which has
very similar hardware and uses a backup of our production database.

I do see (with a trace in Profiler) that the execution plan generated by the
REPORT is different from the execution plan generated by the COMBO BOX.
That seems odd.

> Hope this helps. If you still have any question, please feel free to post
> message here and I am ready to help. Thanks

Do you have any other suggestions? Do I have no recourse but a paid support
call? I can post SQL code, trace results, execution plans, etc., if it
helps.

Regards,
Brian

Reply to this Message

Baisong Wei[MSFT] - 18 Feb 2004 06:30 GMT

Hi Brian,

Thank you for your update.

Actually, there is a certain time delay in the newsgroup and if you are
urgent and the problem is critical, it is not suitable to resolve the issue
through the newsgroup. Also, if some issue is very hard and also not
suitable to solve through the newsgroup, we will also recommend recommend
opening a Support incident with Microsoft Support Services, so that a
dedicated Support Professional can assist with your advisory case. Please
be advised that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.

We just want to solve our customer's problem by the most efficient way and
your understanding is very much appreciated.

If you'd still like to continue working via the newsgroup, I'd like to set
your expectations that it may take a while for us to solve your problem or
help you narrow down the problem and we may eventually redirect you to PSS
to continue working with a dedicated Support Professional. For now, please
try the following and let me know how it goes:

Based on your information, you view covers different tables in one
database, when execute in the Query Analyzer on the SQL Server machine
'select ... from the view_name1 where...' it is fast. When in the ADP, a by
using the Report wizard to create a report on the 'select ... from the
view_name1 where...', it is fast, right?

In my last reply, the second step is means:
Since the row source of the combo box is 'select ... from the view_name1
where...', in your ADP, open view_name1, will it hang? Then could you
create a new view, named 'view_name2'. The definition of it would be:
'select ... from view_name1 where ...', same as the row source of the combo
box. Then, save and open the 'view_name2', how long it will take for
returnning the result set that you want to show in the combo box? Will it
hang?

If it will not hang, could you repeat the step 3: New a form by 'Design
View', press 'OK'. Then put one combo box on the form, when the 'Combo Box
Wizard' appear, choose 'I want the combo box to look up the values in a
table or query', press 'Next'; Then, check the 'Queris', from the queries,
choose the 'View_name2', press 'Next'; Then select all available fields,
press 'Next'; Then press 'Next' until press 'Finish'. Then save it as
'Form1'. Then open 'Form1' will it hang?

I tested that on my side, and use the profiler to catch the T-SQL:
BatchCompleted, the T-SQL I caught is just the report and the combo box
based on. You could only see the execution plan in the Query Analyzer by
press Ctrl+L after you run the query in the Query Analyzer. No matter what
the view I use, since the data accessible through a view is not stored in
the database as a distinct object. What is stored in the database is a
SELECT statement. If the query for the report and the combo box is the
same, t hen the execution is the same because the run the same select
statements. In my testing it is the same. So, could you double-check it?

Looking forward to your reply. Thanks.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Reply to this Message

Brian J. Parker - 18 Feb 2004 19:31 GMT

> Actually, there is a certain time delay in the newsgroup and if you are
> urgent and the problem is critical, it is not suitable to resolve the issue
> through the newsgroup.

Of course. This was not a mission-critical piece of functionality, and we
could have provided a workaround, so it wasn't too urgent.

And as I said in my original message, opening a support call would be very
difficult for me. So I want to avoid it if possible!

> Based on your information, you view covers different tables in one
> database, when execute in the Query Analyzer on the SQL Server machine
> 'select ... from the view_name1 where...' it is fast. When in the ADP, a by
> using the Report wizard to create a report on the 'select ... from the
> view_name1 where...', it is fast, right?

Yes, that is correct.

> In my last reply, the second step is means:
> Since the row source of the combo box is 'select ... from the view_name1
[quoted text clipped - 4 lines]
> returnning the result set that you want to show in the combo box? Will it
> hang?

The original view ("view_name1") will hang if I open it directly in the ADP.

I created a new "wrapper" view ("view_name2"), which is the same as the row
source of the combo box. It behaves just like "view_name1": It runs very
fast in Query Analyzer or as the data sourcew of a report. If I try to open
"view_name2" directly, or use it as the row source of a combo box, it hangs.

> I tested that on my side, and use the profiler to catch the T-SQL:
> BatchCompleted, the T-SQL I caught is just the report and the combo box
> based on. You could only see the execution plan in the Query Analyzer by
> press Ctrl+L after you run the query in the Query Analyzer.

Ctrl+L will just give you an estimated execution plan. Try using Ctrl+K (or
Query --> Show Execution Plan) in Query Analyzer, then running it, to get
the "real" execution plan, which is sometimes different.

> No matter what
> the view I use, since the data accessible through a view is not stored in
> the database as a distinct object. What is stored in the database is a
> SELECT statement. If the query for the report and the combo box is the
> same, t hen the execution is the same because the run the same select
> statements. In my testing it is the same. So, could you double-check it?

I know this should be the case. But I had Profiler display execution plans
(in the "Events" tab, add Performance --> Execution Plan) and the Execution
Tree shown differs. I don't know why the combo box triggers a different
execution plan, I can hardly believe my eyes, it doesn't make any sense to
me. But here is the Execution Tree after running the query in Query
Analyzer (I know it will not mean much, I just want to show they are
different):

Compute Scalar(DEFINE:([Expr1026]=If ([pimParticipant].[CaseNum]<>NULL) then
[pimParticipant].[CaseNum] else ''+If ('
'+[pimParticipant].[GeneticCode]<>NULL) then ('
'+[pimParticipant].[GeneticCode]) else ''))
|--Hash Match(Right Outer Join,
HASH:([demAnswerTypeOptions].[AnswerTypeID],
[demOptions].[OptionValue])=([demForm].[FormInstance_AnswerTypeID],
[demInterviewForm].[FormInstanceID]),
RESIDUAL:([demForm].[FormInstance_AnswerTypeID]=[demAnswerTypeOptions].[Answ
erTypeID] AND
[demInterviewForm].[FormInstanceID]=[demOptions].[OptionValue]))
|--Hash Match(Inner Join,
HASH:([demOptions].[OptionId])=([demAnswerTypeOptions].[OptionID]))
| |--Clustered Index
Scan(OBJECT:([PPDM].[dbo].[demOptions].[PK__demOptions__09A971A2]))
| |--Clustered Index
Scan(OBJECT:([PPDM].[dbo].[demAnswerTypeOptions].[PK_demAnswerTypeOptions]))
|--Merge Join(Inner Join, MANY-TO-MANY
MERGE:([schEntry].[SchedID])=([demInterviewForm].[SchedID]),
RESIDUAL:([demInterviewForm].[SchedID]=[schEntry].[SchedID]))
|--Sort(ORDER BY:([schEntry].[SchedID] ASC))
| |--Compute
Scalar(DEFINE:([schEntry].[SchedID]='@SiteID='+ltrim(str(Convert(dbo.schEntr
y.[SiteID]), NULL,
NULL))+',@ProbandId='+ltrim(str(Convert(dbo.schEntry.[ProbandId]), NULL,
NULL))+',@StudyID='+ltrim(str(Convert(dbo.schEntry.[StudyID]), NULL,
NULL))+',@PId='+ltrim(str(Convert(dbo.schEntry.[PId]), NULL,
NULL))+',@ProtId='+ltrim(str(Convert(dbo.schEntry.[ProtId]), NULL,
NULL))+',@ProcID='+ltrim(str(Convert(dbo.schEntry.[ProcID]), NULL,
NULL))+',@InterviewId='+ltrim(str(Convert(dbo.schEntry.[InterviewId]), NULL,
NULL))))
| |--Index
Scan(OBJECT:([PPDM].[dbo].[schEntry].[XIFschEntry_PIDStudySiteProc]))
|--Sort(ORDER BY:([demInterviewForm].[SchedID] ASC))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[PId]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[TargetPId]) WITH PREFETCH)
| |--Sort(ORDER BY:([demInterviewForm].[TargetPId]
ASC))
| | |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[ProbandId]) WITH PREFETCH)
| | |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[ProcID]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[ProtId]))
| | | | |--Hash Match(Inner Join,
HASH:([admStudy].[StudyID])=([demInterviewForm].[StudyID]))
| | | | | |--Clustered Index
Scan(OBJECT:([PPDM].[dbo].[admStudy].[PK__admStudy__53640638]))
| | | | | |--Merge Join(Inner
Join, MERGE:([admSite].[SiteId])=([demInterviewForm].[SiteId]),
RESIDUAL:([demInterviewForm].[SiteId]=[admSite].[SiteId]))
| | | | | |--Nested
Loops(Inner Join)
| | | | | | |--Clustered
Index Seek(OBJECT:([PPDM].[dbo].[demForm].[PK_demForm]),
SEEK:([demForm].[FormID]=Convert([@1])) ORDERED FORWARD)
| | | | | | |--Clustered
Index Scan(OBJECT:([PPDM].[dbo].[admSite].[PK__admSite__526FE1FF]), ORDERED
FORWARD)
| | | | | |--Compute
Scalar(DEFINE:([demInterviewForm].[SchedID]='@SiteID='+ltrim(str(Convert([de
mInterviewForm].[SiteId]), NULL,
NULL))+',@ProbandId='+ltrim(str(Convert([demInterviewForm].[ProbandId]),
NULL, NULL))+',@StudyID='+ltrim(str(Convert([demInterviewForm].[StudyID]),
NULL, NULL))+',@PId='+ltrim(str(Convert([demInterviewForm].[PId]), NULL,
NULL))+',@ProtId='+ltrim(str(Convert([demInterviewForm].[ProtId]), NULL,
NULL))+',@ProcID='+ltrim(str(Convert([demInterviewForm].[ProcID]), NULL,
NULL))+',@InterviewId='+ltrim(str(Convert(dbo.demInterviewForm.[InterviewId]
), NULL, NULL))))
| | | | | |--Clustered
Index
Scan(OBJECT:([PPDM].[dbo].[demInterviewForm].[PK__demInterviewForm__4CA06362
]), WHERE:([demInterviewForm].[FormID]=Convert([@1]) AND
[demInterviewForm].[StatusFlag]='N') ORDERED FORWARD)
| | | | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[admProtocol].[PK__admProtocol__517BBDC6]),
SEEK:([admProtocol].[ProtId]=[demInterviewForm].[ProtId]) ORDERED FORWARD)
| | | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[admProcedure].[PK__admProcedure__5087998D]),
SEEK:([admProcedure].[ProcID]=[demInterviewForm].[ProcID]) ORDERED FORWARD)
| | |--Index
Seek(OBJECT:([PPDM].[dbo].[pimParticipant].[XIFpimParticipant_PID_PPDMPsychE
valDate]), SEEK:([pimParticipant].[PId]=[demInterviewForm].[ProbandId])
ORDERED FORWARD)
| |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[pimParticipant].[PK_pimParticipant]),
SEEK:([pimParticipant].[PId]=[demInterviewForm].[TargetPId]) ORDERED
FORWARD)
|--Index
Seek(OBJECT:([PPDM].[dbo].[pimParticipant].[XIFpimParticipant_PID_PPDMPsychE
valDate]), SEEK:([pimParticipant].[PId]=[demInterviewForm].[PId]) ORDERED
FORWARD)

And here is the Execution Tree for the *EXACT SAME QUERY*, but run as the
row source of a combo box:

Compute Scalar(DEFINE:([Expr1026]=If ([pimParticipant].[CaseNum]<>NULL) then
[pimParticipant].[CaseNum] else ''+If ('
'+[pimParticipant].[GeneticCode]<>NULL) then ('
'+[pimParticipant].[GeneticCode]) else ''))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES:([demInterviewForm].[FormInstanceID],
[demForm].[FormInstance_AnswerTypeID]))
|--Nested Loops(Inner Join,
WHERE:([demInterviewForm].[SchedID]=[schEntry].[SchedID]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[ProbandId]) WITH PREFETCH)
| | |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[PId]) WITH PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[TargetPId]) WITH PREFETCH)
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[ProcID]))
| | | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[ProtId]))
| | | | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[StudyID]))
| | | | | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([demInterviewForm].[SiteId]))
| | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[demForm].[PK_demForm]),
SEEK:([demForm].[FormID]=Convert([@1])) ORDERED FORWARD)
| | | | | | | | |

|--Filter(WHERE:([demInterviewForm].[StatusFlag]='N'))

| | | | | | | | | |--Compute
Scalar(DEFINE:([demInterviewForm].[SchedID]='@SiteID='+ltrim(str(Convert([de
mInterviewForm].[SiteId]), NULL,
NULL))+',@ProbandId='+ltrim(str(Convert([demInterviewForm].[ProbandId]),
NULL, NULL))+',@StudyID='+ltrim(str(Convert([demInterviewForm].[StudyID]),
NULL, NULL))+',@PId='+ltrim(str(Convert([demInterviewForm].[PId]), NULL,
NULL))+',@ProtId='+ltrim(str(Convert([demInterviewForm].[ProtId]), NULL,
NULL))+',@ProcID='+ltrim(str(Convert([demInterviewForm].[ProcID]), NULL,
NULL))+',@InterviewId='+ltrim(str(Convert(dbo.demInterviewForm.[InterviewId]
), NULL, NULL))))
| | | | | | | | | |--Bookmark
Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PPDM].[dbo].[demInterviewForm]))
| | | | | | | | | |--Index
Seek(OBJECT:([PPDM].[dbo].[demInterviewForm].[XIFdemInterviewForm_FormID_For
mInstanceID]), SEEK:([demInterviewForm].[FormID]=Convert([@1])) ORDERED
FORWARD)
| | | | | | | | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[admSite].[PK__admSite__526FE1FF]),
SEEK:([admSite].[SiteId]=[demInterviewForm].[SiteId]) ORDERED FORWARD)
| | | | | | | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[admStudy].[PK__admStudy__53640638]),
SEEK:([admStudy].[StudyID]=[demInterviewForm].[StudyID]) ORDERED FORWARD)
| | | | | | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[admProtocol].[PK__admProtocol__517BBDC6]),
SEEK:([admProtocol].[ProtId]=[demInterviewForm].[ProtId]) ORDERED FORWARD)
| | | | | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[admProcedure].[PK__admProcedure__5087998D]),
SEEK:([admProcedure].[ProcID]=[demInterviewForm].[ProcID]) ORDERED FORWARD)
| | | | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[pimParticipant].[PK_pimParticipant]),
SEEK:([pimParticipant].[PId]=[demInterviewForm].[TargetPId]) ORDERED
FORWARD)
| | | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[pimParticipant].[PK_pimParticipant]),
SEEK:([pimParticipant].[PId]=[demInterviewForm].[PId]) ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[pimParticipant].[PK_pimParticipant]),
SEEK:([pimParticipant].[PId]=[demInterviewForm].[ProbandId]) ORDERED
FORWARD)
| |--Compute
Scalar(DEFINE:([schEntry].[SchedID]='@SiteID='+ltrim(str(Convert(dbo.schEntr
y.[SiteID]), NULL,
NULL))+',@ProbandId='+ltrim(str(Convert(dbo.schEntry.[ProbandId]), NULL,
NULL))+',@StudyID='+ltrim(str(Convert(dbo.schEntry.[StudyID]), NULL,
NULL))+',@PId='+ltrim(str(Convert(dbo.schEntry.[PId]), NULL,
NULL))+',@ProtId='+ltrim(str(Convert(dbo.schEntry.[ProtId]), NULL,
NULL))+',@ProcID='+ltrim(str(Convert(dbo.schEntry.[ProcID]), NULL,
NULL))+',@InterviewId='+ltrim(str(Convert(dbo.schEntry.[InterviewId]), NULL,
NULL))))
| |--Index
Scan(OBJECT:([PPDM].[dbo].[schEntry].[XIFschEntry_PIDStudySiteProc]))
|--Row Count Spool
|--Nested Loops(Inner Join, OUTER
REFERENCES:([demAnswerTypeOptions].[OptionID]))
|--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[demAnswerTypeOptions].[PK_demAnswerTypeOptions]),
SEEK:([demAnswerTypeOptions].[AnswerTypeID]=[demForm].[FormInstance_AnswerTy
peID]) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([PPDM].[dbo].[demOptions].[PK__demOptions__09A971A2]),
SEEK:([demOptions].[OptionId]=[demAnswerTypeOptions].[OptionID]),
WHERE:([demInterviewForm].[FormInstanceID]=[demOptions].[OptionValue])
ORDERED FORWARD)

You can see why I am surprised!

Regards,
Brian

Reply to this Message

Vadim Rapp - 18 Feb 2004 21:33 GMT

BJP> I don't know why the combo box triggers a different execution plan, I
BJP> can hardly believe my eyes, it doesn't make any sense to me.

Access might have executed some code (usually in the form of s.p.'s) that
might change the options of sql server. For accuracy, you have to profile
not only the opening of the combobox, but everything beginning from starting
Access. You'll see quite a number of undocumented s.p.'s. If you now run
them all from qa, most likely the final result will be the same execution
plan as from Access itself. It would be then interesting to narrow down the
one that produced the effect.

Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com

Reply to this Message

Brian J. Parker - 19 Feb 2004 20:15 GMT

Vadim,

Thanks! That's a great idea, I can't believe I didn't think of it myself.
When I have a little time I'll give that a try.

Regards,
Brian

> BJP> I don't know why the combo box triggers a different execution plan, I
> BJP> can hardly believe my eyes, it doesn't make any sense to me.
[quoted text clipped - 6 lines]
> plan as from Access itself. It would be then interesting to narrow down the
> one that produced the effect.

Reply to this Message

Baisong Wei[MSFT] - 24 Feb 2004 01:16 GMT

Hi Hi Brian,,

Thanks for using the newsgroup and I am reviewing you issue. Since we have
not heard from you for some days in the newsgroup, I wonder how about the
result when you take the community member's suggestion. Here I just want to
add some value on that.

From my experience, the application performance problem is always complex
and there are so many factors that you should take into consideration.
First, I should say that, execution plans could be different due to
statistics, parameters, system resources,etc. Second, it is important that
you should make sure the comparasion should based on the same T-SQL that is
executed on the SQL Server side. Even if you run only one stored procedure
from the Query Analyzer and the VB application, you should know what is
actually executed on SQL Server from the start to the end. To run T-SQL
from the application may cause some other T-SQL or some system run on the
SQL Server first, then the environment may be changed compared with just
run a single statement or stored procedure in the Query Analyzer. The
followings are some links I think will be helpful for your reference:

HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587

How to Collect and Analyze Performance Data in Microsoft SQL Server
http://support.microsoft.com/?id=324692

INFO: Choosing an rdoResultset Cursortype
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:
80/support/kb/articles/q149/0/54.asp&NoWebContent=1

Thanks.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Reply to this Message

Allen - 19 Feb 2004 15:14 GMT

I agree that if the SQL statement that will run on the
server side is same, the execution plan have to be same.
So, you have to be sure that the sql statement run on the
server side is the same for the report or the view. if
they are exactly the same, the comparasion of the
execution plan will make a sense.

i have an suggestion on that. Create a new ADP and create
a view exactly the same as the troublesome one, you could
delete the old one on the SQL Server first. Then open the
view. just forget the old ADP. if there is no problem in
the new one ,you'd better import the other objects in the
old ADP to the new one. then use the new one instead of
the old.

it is quite odd that when report based on the view will
work like a charm but when directly open the view,it hangs.

Reply to this Message

Brian J. Parker - 17 Feb 2004 21:58 GMT

A 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

Reply to this Message

- 19 Feb 2004 00:48 GMT

Hi Brian,
Just a couple of thoughts;

Do you have an index set up on the column you were using
for the join in SQL Server?

Could you wrap the functionality of your view in a stored
proc instead?

HTH
Chris

>-----Original Message-----
>A little follow-up on this problem:
[quoted text clipped - 13 lines]
>
>.

Reply to this Message

Brian J. Parker - 19 Feb 2004 20:18 GMT

Chris,

Thanks for the suggestions. No, the joining column was a derived,
non-indexed column. I "fixed" the problem by re-writing the view to use the
underlying "real" columns instead. (Which also increased the performance of
the view in Query Analyzer, unsurprisingly!)

Regards,
Brian

> Hi Brian,
> Just a couple of thoughts;
[quoted text clipped - 32 lines]
> >
> >.

Reply to this Message