1. Panos Platanas
  2. PowerBuilder
  3. Saturday, 22 October 2022 10:30 AM UTC

Hi all,

we were checking our databases using the various sp_Blitz procedures (First Responder Kit) and we discovered something we had hints before:

SELECT statements produced by our PowerBuilder (2017 using SNC) application's datawindows create lots of multiple plans for the same query inside SQL Server (various SQL Server versions tested both with old and new Cardinality Estimator).

And when I say multiple, I mean we can have thousands of multiple plans for the same SELECT statement:

the same SELECT with the same WHERE clause BUT different value each time: eg 

  • SELECT id,id_name from XXX WHERE id = 1000
  • SELECT id,id_name from XXX WHERE id = 1001

This forces new compilations on each execution, driving CPU to high usage etc.

Have you come across this issue?

If so, how do cope with that?

Thank you

Markus Eckert Accepted Answer Pending Moderation
  1. Monday, 24 October 2022 07:46 AM UTC
  2. PowerBuilder
  3. # 1

Hi Panos

If you go the "DisableBind" Route, you have to know that you'll always get parametrized queries, meaning you'll always get one execution plan for the DW, and that can lead to Parameter Sniffing problems. If you have Querys which sometimes affect a few rows and sometimes affect tons of them (depending on the parameter), you'll get a plan optimized for one group but suboptimal for the other.
It's even worse for queries which have "optional" where clauses (when you write "AND ( customer.number = :cust_no OR :cust_no = 0)"). If the actual parameter value is included in the query text, you'll get an index scan on the customer number when :cust_no <> 0 or a full table scan when :cust_no = 0. With a parametrized query, you'll always get a full table scan, because the plan must work for both options.

 

In our system we kept the DisableBind=1 because switching it off would have resulted in many queries growing slower. But we started using SQL Stored Procedures for queries that are always supposed to use the same execution plan.

It's a pretty straightforward conversion too, you can just create a new DW using the Stored Procedure as datasource and then replace the "retrieve=" section in the original DW with the "procedure=" section in the new DW.

Regards,
Markus

Comment
There are no comments made yet.
Arnd Schmidt Accepted Answer Pending Moderation
  1. Saturday, 22 October 2022 20:58 PM UTC
  2. PowerBuilder
  3. # 2

Hi Panos,

do you use DisableBind for the DB Connection - if so... try to avoid!

https://docs.appeon.com/pb2022/connection_reference/DisableBind.html

hth

Arnd

Comment
  1. Panos Platanas
  2. Sunday, 23 October 2022 08:19 AM UTC
Hi Arnd. I never came across this. Thank you.

So, in our Connection String we do not specify the DisableBind nowhere. In the link you provided, I can see than SNC defaults to DisableBind=0 so if I understand your post correctly, are we (luckily) on the correct path?

Thank you
  1. Helpful
  1. Panos Platanas
  2. Sunday, 23 October 2022 08:27 AM UTC
Sorry. It seems that SNC defaults to DisableBind=1 (Sunday morning here, not fully waken up :) ), so I guess we need to add DisableBind=0 to our connection string.

Thanx again! I will indeed try that (along with BindSPInput=1 maybe)

  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Saturday, 22 October 2022 20:06 PM UTC
  2. PowerBuilder
  3. # 3

Hi, Panos -

Chris's suggestion is excellent, so I hope it helps.

If it does not, I suggest you examine the steps described in the execution plan(s) to see if you can discern any pattern to their differences. For example, the plan may specify a table scan (which is a slow operation) because one or more indexes are either missing or not helpful/correct based on the query(-ies).

The app I help support uses SQL Server as the DBMS. We recommend to our customers that their DBA's schedule/execute a nightly job to run sp_updatestats after hours.

Good luck!

Comment
  1. Panos Platanas
  2. Sunday, 23 October 2022 08:13 AM UTC
Thank you for your reply John. I tend to believe that we are OK with Indexes. We do monitor them in our big customers and we update the modified Statistics regularly.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Saturday, 22 October 2022 14:42 PM UTC
  2. PowerBuilder
  3. # 4

Hi Panos;

  Have you tried having your DBA team clear the "DB Statistics"? Quite often if this work area gets cluttered, the SQL optimizer starts to "over think" itself.

Regards ... Chris 

Comment
  1. Panos Platanas
  2. Saturday, 22 October 2022 16:11 PM UTC
Thank you Chris.

I will try a DBCC FREEPROCCACHE and update the post

Have a nice weekend!
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.