ASE 15.7 Derived Tables and Index Interaction

[former_member1259891]

Hey There!!

I'm attempting to locate some documentation on Derived Tables and how indexes work with them, any caveats, anything special that needs to be done. I am finding nothing on the web nor in the ASE manuals nor ASE Books covering the developer, admin, performance & tuning realms.

The issue I'm having is with code that is using Derived Table joins and I can't seem to locate why it's not choosing the correct index. I've added a column to 1 of the nonclustered indexes. I'm assuming the optimizer would choose the underlying table's index but that doesn't seem to be the case here.

Any help would be most appreciated!!

Thank you and Best Regards,

Rob

[Mark_A_Parsons]

Hard to say without a **lot** more info, eg, the complete query, query plan, sp_help output for all tables referenced by the query, @variable definitions (if there are any @variables used in where/join clauses), and possibly a) sp_cacheconfig and b) optdiag output for each of the tables; if you can run the query then the output from having set statistics io turned on may also be of interest

[former_member1259891]

Hi Mark!!

It's nice to see a familiar name :-). Would it be possible to send you the info you referred to?

Can I assume that the optimizer would choose the correct index whether or not the table is derived ie is there anything funky that goes on with derived tables?

Best,
Rob

[Mark_A_Parsons]

While the requested data would definitely take up a lot of space in a comment/answer, dumping all the requested data into a text file and attaching said file to a comment/answer would be easier/cleaner.

This (attach file to comment) is the method I'd recommend since a) I can't guarantee if/when I'd respond to an email and b) gives you the option of receiving input from a gaggle of folks with ASE query tuning experience.

'course, if your organization has an ongoing need for P&T work (or upgrades, or specialized projects, or you have nothin' better to do with yer $$$) ... I'd be open to discussing a contract opportunity! 🙂

-------------------

At a very basic level a derived table is 'just' a sub-query; how the optimizer chooses to process this structure will be based on all of the normal considerations (eg, index availability, table/column/index stats, join criteria, etc).

The optimizer could choose to populate a worktable with the contents of the derived table and then join this worktable with the rest of the query; or perhaps the optimizer will flatten the query (at which point the table-access/join orders could be anyone's guess).

[MattthiasWild]

Hello Robert,

Please be aware that SAP ASE 15.7 is running out of support end of 2020. Please consider to upgrade to 16.0.03 and to move the 'problem' to a modern ASE version.

Stay safe

[former_member1259891]

Hi Matthias!!

Thanks for your reply. Yes i'm aware that 15.7 will be EOL. I can't wait to fix the issue until after a upgrade.

Best,
Rob