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

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

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!

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