Hi!

Optimizing again and looking at Code Analysis issues that Redgate SQL Monitor is picking up.

"Three-part and four-part column references in SELECT list are deprecated – two-part names is the standard-compliant behavior." https://www.sqlservercentral.com/forums/topic/more-than-a-two-part-column-name-depricated-features

Problem example:

UPDATE [dbo].[MyTable] SET [MyCol]=([dbo].[MyTable].[MyCol] + @p2) WHERE ( ( ( [dbo].[MyTable].[MyPKId] = @p3)))

Desired output:

UPDATE [dbo].[MyTable] SET [MyCol]=([MyCol] + @p2) WHERE ( ( ( [MyPKId] = @p3)))

Is it possible to specify or tweak this behavior?

I have removed SqlCatalog using overwrites to remove that verbosity and would like to clean up further.

We have queries running 100's of millions of times per day so reducing the size of the SQL statement sent over the wire matters.

thank you!

I have removed SqlCatalog using overwrites to remove that verbosity and would like to clean up further.

You can do the same to the schema with SchemaNameOverwrites.

Thank you! How did I miss that. 👍

That does remove the schema, but not the repetitive table names in selects and predicates.

I'm also interested in removing table names where not necessary, since I'm dealing with databases that have extremely long table names.

I've tried overriding several methods to accomplish this, but I have yet to find the right one.

Indeed overwriting the schema is not what I need (did not have a chance to check before I replied).

Removing the schema is not desired in the initial table FROM, JOIN etc but only in the WHERE as per the document:

https://documentation.red-gate.com/codeanalysis/code-analysis-for-sql-server/deprecated-syntax-rules/dep026

****If I remove the schema I will be faced with this warning:

https://documentation.red-gate.com/codeanalysis/code-analysis-for-sql-server/performance-rules/pe002

I seriously doubt it influences anything at all in the end. Did you measure the queries in bulk to see if they are actually slower? The main thing is that the sql query in text is compiled once, the db will re-use the execution plan the next time.

Removing the catalog/schema names through catalog overwrites is novel, but really, it's unnecessary. In the linked thread I see guesswork and some recommendation from the standard but that's it. Which standard they're referring to is a bit of a mystery too, as no database implements any SQL standard (from which year ) in full.

Removing very long table names can be done I think by specifying aliases but you make your own life more cumbersome by writing queries that need aliasing for every field without any noticeable result.

Target names are created in the database specific subclass of the DbSpecificCreator class in the DQE.

Otis wrote:

Removing very long table names can be done I think by specifying aliases but you make your own life more cumbersome by writing queries that need aliasing for every field without any noticeable result.

Well, dealing with tables names that are about 100 characters long with more than 160 columns per table and databases that are in the cloud, I do think it matters. Using aliases will greatly reduce the length of the SQL commands.

Otis wrote:

Target names are created in the database specific subclass of the DbSpecificCreator class in the DQE.

I already have a custom DbSpecificCreator and managed to replace the table names with an alias in the SELECT part. I now need to add the alias to the FROM clause. Any pointers?

twaindev wrote:

Otis wrote:

Removing very long table names can be done I think by specifying aliases but you make your own life more cumbersome by writing queries that need aliasing for every field without any noticeable result.

Well, dealing with tables names that are about 100 characters long with more than 160 columns per table and databases that are in the cloud, I do think it matters. Using aliases will greatly reduce the length of the SQL commands.

That ... sounds like a problem indeed.

Otis wrote:

Target names are created in the database specific subclass of the DbSpecificCreator class in the DQE.

I already have a custom DbSpecificCreator and managed to replace the table names with an alias in the SELECT part. I now need to add the alias to the FROM clause. Any pointers?

You use a <full name of target> -> alias dictionary filled in the createobject name? In the DQE there's a method called 'DetermineTargetAlias', which returns either an empty string (no alias needed) or an alias for a target. You could start there.

Though, be aware that this is likely going end in tears in a lot of situations: the best way forward is to make sure, if this is really hurting you, to limit the names to shorter names. 100 characters per table/field is a lot.

Otis wrote:

You use a <full name of target> -> alias dictionary filled in the createobject name? In the DQE there's a method called 'DetermineTargetAlias', which returns either an empty string (no alias needed) or an alias for a target. You could start there.

Thanks. Will look into that.

Otis wrote:

Though, be aware that this is likely going end in tears in a lot of situations: the best way forward is to make sure, if this is really hurting you, to limit the names to shorter names. 100 characters per table/field is a lot.

Unfortunately the design of these databases is beyond my control.

I'd suggest to follow the code from the CreateSelectDQImpl method in the SQL Server DynamicQueryEngine class. E.g. the code where the fields are added to the select list (AppendResultsetFieldsToQuery) will show you how aliases are determined based on the target. We have one mechanism where it looks a bit obscure, which is in DetermineObjectAliasToUseForSelectListField, where a RelationCollection can contain an alias which is used for the projection. This is a mechanism we had to introduce to overcome alias issues without breaking code. But the code is fairly straight forward.

We use an alias -> real alias system which is used in the DbSpecificCreator.FindRealAlias, and is filled by the RelationCollection (as otherwise we don't need aliases). It might be the easiest to use that system in your extra code as well.

That aside, I'd still measure the hit in performance compared to the overall query performance when using long names; it might be it's perhaps slower but not relevant compared to the rest (as the tables have 100+ fields, they're not the fastest either), so if it e.g. is 5% slower due to the long names I'd not go through with this.

Aliasing elements is a complex topic so I'd avoid tampering with it if you can help it.