Bad performance in VB6 to open a hierarchical recordset based on stored procedures

Question

Bad performance in VB6 to open a hierarchical recordset based on stored procedures

incercare0

SSC Enthusiast

Points: 173
More actions
November 28, 2009 at 10:32 am

#214410

Hi,
I have a VB6 application which uses DataReport for reporting. The DataReport uses DataEnvironment to create hierarchical recordsets.
The hierarchical recordset has 5 levels. On each level a stored procedure with parameters is used to retrieve data.
Suddenly the performance of opening the hierarchical recordset decreased very bad (the application runned fine for 6 months).
Now a stored procedure runs in about 50 seconds when the recordset is opening (from the VB6 (using DataEnvironment and MSDataShape provider)).
When I run the same procedure with the same parameters from Management Studio it runs very fine in less than one second.
The stored procedure returns only one row with one column (it makes a relatvely complex select statement joining more tables and a table valued function)
The strange thing is that when I tried to open an ADO recordset from VB6 (using an ADOCommand) i got the same bad performance (50 seconds).
After a few tries I observed that the performance returned to normal after I inserted a "SET NOCOUNT ON" into the stored procedure.
Unfortunately the "SET NOCOUNT ON" does not solve the bad performance when opening the hierarchical recordset through DataEnvironment.
Does anyone have an idea what could cause this problem?
Thank you,
Dan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I am not familiar with DataEnvironment, but I would suggest that you use SQL Server profiling to see what is really happening on your SQL Server. My offhand guess would be that it might be calling the stored procedure multiple times.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

incercare0 SSC Enthusiast Points: 173 More actions · Answer 2

In SQL Server Profiler it is only one call for each level of the hierarchical recordset. Each call has a Duration of about 50 seconds. So there are no multiple calls of the same stored procedure.

The strange thing is that when called from Management Studio the same procedure call (copied from Profiler) takes about 700 miliseconds.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

First have the profiler break it down to the statement level, and then compare the times, statement by statement between the two. Find which statement(s) have the most different time, then post them here. Secondly, use the profiler to get the execution plans for the sproc from VB6 and compare it to the plan for the same sproc from SSMS.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

incercare0 (11/30/2009)
In SQL Server Profiler it is only one call for each level of the hierarchical recordset. Each call has a Duration of about 50 seconds. So there are no multiple calls of the same stored procedure.
The strange thing is that when called from Management Studio the same procedure call (copied from Profiler) takes about 700 miliseconds.

It sounds more like a network or web server "latency" problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 5

Try running profiler while VB6 is running script but catch the query execution plans. Then run the query in SSMS and pull query plans. It could be an occurrance of the optimizer in the database engine creating an inefficient execution plan for the stored procedure when it got compiled. You will not typically see this in SSMS because it is being compiled during runtime in that occurance using the parameters you use in that instance, which could lead to a more efficient query. It could be that you might need to tune the sp or update stats.

Joie Andrew
"Since 1982"

JacekO SSCertifiable Points: 6610 More actions · Answer 6

Many years ago I had similar problem with SQL Server 7. After installing SP3 the application using the server basically stopped to work. The response times were so slow the end users could not use the app. It turned out (if I remember correctly) Microsoft changed the network packet size with SP3. There was no way to make the app to be compatible with the SP3 packet sizes so I had to undo the SP3.

Did you make sure nothing really changed in your environment?

Can you run the app on the same box with SQL Server to eliminate the network as a potential cause?

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

incercare0 SSC Enthusiast Points: 173 More actions · Answer 7

Because of time pressure (poor performance on the production server) I had to find a solution quickly and I had no time for further investigation. I have created another stored procedure which inserts the intermediate results in a table and rewritten the stored procedure that is called from the hierarchical recordset to select from that table.

That resolved the speed problem.

I wish I had time to investigate and find the reason for that behavior but unfortunately I didn't.

Thank you all for your replies.