Jiwa

I like to think of it as:

Table variable/Temp table/CTE (or sub-query versions of them) are really all just hints to SQL's query optimizer.

And we need to figure out the best hint for the most common use-case for the query in question. And that hint can change over time, too. Your database may work best with one option today, but in a year, when your database has gone from 2GB to 10GB, a different option may be needed to get better results.

pricerc wrote:that hint can change over time, too. Your database may work best with one option today, but in a year, when your database has gone from 2GB to 10GB, a different option may be needed to get better results.

Exactly.

The challenge is when you have hundreds of customers each with varying sized databases, all growing at different rates, and one suffers from poor performance for a particular query because we're using one particular strategy and then the nephew of their CTO working as an intern points out that we must be idiots over here at Jiwa because we're using X when clearly we should be using Y because changing it to use Y saw a 2000% performance increase.

Your test would be interesting when running the Debtors Balance As At report (or more specifically the underlying stored procs) and testing the differences between CTE vs table variables vs temp tables.
Try on the biggest database you've got.

Then you'd also need to purge the cache to get the same starting point.

And (a new thread should be started for this as it's really a separate topic) did you know that the usage of the scalar function in the stored proc usp_JIWA_Debtors_DebtorBalanceAsAt

Code: Select all

dbo.ufn_JIWA_Debtors_TransactionPeriodNo(TranDate,DebtorID,@SP_Date)

stops the stored proc from any advantages of parallelism?

I can tell you from personal experience with that report in 6.5.13 with a given client who has thousands of customers the report with table variables was taking over half an hour to produce a result and changing it to temporary tables brought the running time down to under three minutes.

The problem with this is that depending on the industry the customer is in you can either be dealing with a large volume of transactions in the case of say where a customer is selling seeds i.e. a low ticket item as they need the volumes to make a buck compared to a customer who sells say motor cars where obviously the volumes can be a lot less.

Perhaps the answer lies in having the wizard that creates a blank database have the ability to create a high volume database or a low volume database and the store procedures could be different based upon the choice at setup, it would certainly keep the complexity out of the procedures.

DannyC wrote:Your test would be interesting when running the Debtors Balance As At report (or more specifically the underlying stored procs) and testing the differences between CTE vs table variables vs temp tables.
Try on the biggest database you've got.

Then you'd also need to purge the cache to get the same starting point.

And (a new thread should be started for this as it's really a separate topic) did you know that the usage of the scalar function in the stored proc usp_JIWA_Debtors_DebtorBalanceAsAt

Code: Select all

dbo.ufn_JIWA_Debtors_TransactionPeriodNo(TranDate,DebtorID,@SP_Date)

stops the stored proc from any advantages of parallelism?

I'm sure I've done a re-write of that into a table-valued function, but I don't remember where. I did however find my most recent related adventure with CreditorBalanceAsAt.

I've shared it in the SQL forum. https://forums.jiwa.com.au/viewtopic.php?f=25&t=1238

When I've tracked down the Debtors version, I'll add that to my post (I think it was for a customer I haven't seen in a couple of years).