SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.5.355 STD] Illegal JOIN Hint for table variables

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.5.355 STD] Illegal JOIN Hint for table variables
Author Message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post [11.5.355 STD] Illegal JOIN Hint for table variables Reply with quote
SQL Assistant wrongly adds a table hint when joining to table variables.

For example picking the variable "@myClientsTableVariable" in this image:



results in a JOIN with the hint "WITH(NOLOCK)" which is illegal for table variables in MS-SQL.




Here is the code before:
Code:
DECLARE @myClientsTableVariable AS TABLE (clientIncId INT)

SELECT
   Clients.clientName
FROM dbo.Clients WITH(NOLOCK)
   JOIN
WHERE Clients.isDeleted = 0x0
   AND Clients.city = 'Auckland'

and after selecting the table variable:
Code:
DECLARE @myClientsTableVariable AS TABLE (clientIncId INT)

SELECT
   Clients.clientName
FROM dbo.Clients WITH(NOLOCK)
   JOIN @myClientsTableVariable AS mctv WITH(NOLOCK)
WHERE Clients.isDeleted = 0x0
   AND Clients.city = 'Auckland'


The joining hint WITH(NOLOCK) causes a crash on running this code in MS-SQL.
Tue Mar 02, 2021 3:36 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
The text added after table names is coming from your current settings, please see DB Options -> SQL Assistance -> SQL Server -> Table Hint.

That "Table Hint" is a free text option. You can enter anything you want as its value, for example, "/* I like this table name */, which is what's going to be added to the end of each table name. SQL Assistant doesn't attempt to interpret the meaning of that text value.
Tue Mar 02, 2021 5:18 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
Yes, I know that the text is coming from the setting I created under "Table Hint" and it's a great feature for us. Not having to constantly type our standard joining hint is a real time-saver and we love it!

BUT getting an error on every join to a table variable is painfully annoying.

background information: https://www.sql-server-performance.com/forum/threads/with-nolock-and-table-vars-15855/

So I understand you don't want to do any sort of evaluation of what we put in the setting, just apply it dumbly regardless of content. That's disappointing though and would be great if you could step up to the plate and make Sql Assistant more intelligent.
Tue Mar 02, 2021 6:40 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Of course I understand that NOLOCK is incompatible with table variables. I'm saying that the feature being used is not designed to interpret the text appended. That feature is designed to append user provided text.

A simple workaround for that could be creating a code snippet with a hot key assigned to it, for example Alt+0, and using it instead of automatically inserted text. The snippet can be intelligent and evaluate the name entered before cursor, and if it's not beginning with @ symbol, add whatever text is coded in the snippet. But considering that the hint is inserted after pressing a hot key, after Alt+0 is pressed by a human, checking for @ isn't really needed in this scenario, the snippet could be as simple as

Code:
WITH (NOLOCK)



Tue Mar 02, 2021 11:33 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
I never thought I'd actually meet someone using that feature. Why would anyone want add a table hint(s) to each and every table? Especially one as dangerous as NOLOCK is.
Tue Mar 02, 2021 1:25 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
@gemisigo
Your comments are off-topic.
Regardless of whether NOLOCK is recommend or not, I am asking for a more aware application of the table-hint setting in SQL Assistant.
Tue Mar 02, 2021 5:01 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
@SysOp
thanks for your suggestion with the code snippet but actually we really like the table hint feature and it is very convenient
DB Options -> SQL Assistance -> SQL Server -> Table Hint

Since it is clearly called "Table Hint" I would have expected the SQL Assistant to understand what it is doing and not simply treat the information as "free text".

It is a shame you won't improve this so we will just file it under "minor daily annoyances" and live with it.
Tue Mar 02, 2021 5:10 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I would like to acknowledge that in the use case scenario described in this message thread the expectation is that SQL Assistant makes sense of the text added from the Table Hint option, which is not what it supports now. It doesn't go that far.


I thought you might want to know some additional details. I've looked up history of the Table Hint feature and here is what I've dug up.

The feature was introduced in version 6.2 and the original enhancement request was for automating addition of hints (auto-text) to SELECT SQL statement in Oracle in which the actual hint is added in the beginning of SQL SELECT statement, for example "SELECT /*+ FIRST_ROWS */ ... FROM ...". Currently in addition to Oracle automatic hints are supported for SQL Server and Sybase Adaptive Server Anywhere databases (now SAP), for example "...FROM some_table AS t WITH (REPEATABLEREAD)..."

The feature is called Table Hint, which is what makes it confusing here. Indeed it's just an auto-text added to the statement/table scope as is, without interpretation. Speaking of which there might be some edge case usage scenarios when it's useful, e.g. when a database is designed for specific purpose or optimized /not-optimized for a specific workload type, in which case adding /*+ RULE */ or /*+ FIRST_ROWS */ hints might be useful. In a SQL Server database containing memory optimized tables only, use of hints like WITH (SNAPSHOT) is quite permissible.

I have to agree with Brendon that in case of SQL Server table scope hints are not supported with table variables. Since that text is added automatically to tables, it should pay attention to table type, and in case of table variables don't add it automatically. I submitted today new enhancement request to check table type and don't add hints to table variables. Enhancement number #SA0040284.
Wed Mar 03, 2021 3:24 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
BrendonOSullivan wrote:
@gemisigo
Your comments are off-topic.


Since my question was related to the use cases of the feature you currently want to be improved I'm pretty sure they weren't. Also, judging by the tone of your replies I guess you took my question and sincere curiosity as if there was offense meant. Please, feel free not to give an answer. You've got every right to it. But there's no need to be arrogant.

As for the feature, I think it's rather limited and SA offers much better alternatives, eg. using the Alt+n hotkeys or even snippets. Using this feature leaves with one (or one set of) table hint(s), while the snippet is much more flexible. Switching between the hints (or hint sets), or even disabling it requires changing SA options or constantly deleting/rewriting the inserted hints and that's much slower than hitting another key or two.

I've first seen this feature requested almost a decade ago (here) and not many times has it been discussed since then, that's why I thought it to be a "dead branch".
Wed Mar 03, 2021 7:29 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
gemisigo wrote:
BrendonOSullivan wrote:
@gemisigo
Your comments are off-topic.


Since my question was related to the use cases of the feature you currently want to be improved I'm pretty sure they weren't. Also, judging by the tone of your replies I guess you took my question and sincere curiosity as if there was offense meant. Please, feel free not to give an answer. You've got every right to it. But there's no need to be arrogant.


Thanks for your interest gemisigo.

I did feel threatened by your question because a long exchange about the merits and dangers of NOLOCK might have distracted the thread from my problem which is about how the table-hint is applied, not whether or not it is good practise to do so.

For your information we have a corporate policy to use this in all statements that are SELECT only. That covers perhaps 95% or more of the base code and an even higher proportion of what we write on a daily basis. Perhaps that's just us but actually I think in most projects the INSERT or UPDATE statements are much rarer than the routine SELECTs. Also the INSERT stuff in our case is mainly handled by an entity-like framework while our day-to-day coding is mainly done for exports and reports using the base data generated by an app through the framework.

So NOLOCK gives better performance in the routine workflow (+95% SELECT) and avoids bottlenecks if an instance crashes. We used to have poor performance and frequent crashes so 10 years ago it was pretty important. Maybe the root causes were our own crappy code that has since improved so possibly the NOLOCK is a hangover from earlier times and not really necessary any more.

I am not enough of an expert to judge if it is a good policy in our company but I also can't challenge it so I just need the best possible tool to make it easy to do in our daily work. I started this topic to get help with what we perceive as a bug in SQL Assistant, not to get into a theoretical debate about whether or not we should actually be using NOLOCK so frequently.
Thu Mar 04, 2021 7:04 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
The reason I asked is I'd like to help but helping for the wrong reason can cause more harm than good.

Regarding the hazard of NOLOCK, BTDT, and I think this topic, "abusing the with(NOLOCK)" is actually a very good one for a debate. But since you expressed that you're not wanting one, I'll just assume you know what you're doing. I don't want to hijack your topic, I only want to shed some light on alternatives.

Back to the feature (sorry, I had to).
The fastest alternative that is also flexible is the one SysOp already said, assign the WITH(NOLOCK) to a hotkey and apply it generously, er, I mean when needed. This also might need a great deal of discipline, so it may not appeal to everyone.

The other alternative is even faster but it is rather, well, stiff. But if you definitely want to end up having each and every table stuck with NOLOCK, then you could skip that annoying little vermin and go straight for the transaction isolation level (or TIL for short) of READ UNCOMMITTED.

Switching to that TIL has several advantages:
- all of your queries in this session will behave as if all of the tables had been smacked with NOLOCK
- you can switch to it and back to READ COMMITTED at will, that is, turn the NOLOCK on or off, even in a stored procedure
- won't fail when using a table variable
- if you're mainly in SQL Server Management Studio then you won't even have to rely on SA, it has an option for making this sort of permanent until changed


(BTW, @SysOp, it would be nice to have something similar in SE, too, the ability to change the defaults for those settings was so convenient).

Of course, there are some disadvantages as well:
- you either fold or are all in. I mean, this really behaves as if adding NOLOCK all of your tables (RUC) or none of them (RC), there's no mixing and no exceptions (at least not ones you could CATCH)
- there's no simple indicator of which TIL you're using currently, if you want to know you have to ask (query) or set explicitly
- and yes, all the side effects of the NOLOCK or the inconvenience of readers and writers blocking each other.

Its greatest drawback is that you cannot make exceptions, all the tables will behave exactly the same (lock-wise). While this might not be the perfect tool for your needs, it's good to have in your tool belt, and you might consider using it until ST improves/implements some intelligence into the Table Hint feature.


Also, assigning this short Execute and Display Output Results type snippet to an Alt+num hotkey (if you have any unused) or a snippet keyword can switch between READ COMMITTED and READ UNCOMMITTED in a nano:
Code:

$$
DECLARE @til int ;

SELECT @til = des1.[transaction_isolation_level]
FROM sys.dm_exec_sessions AS des1
    WHERE session_id = @@SPID;
   
IF @til NOT IN (1, 2)
    SELECT 'Neither ReadUncommitted, nor ReadCommitted, leaving it alone';
ELSE IF @til = 1
    BEGIN
        SELECT 'Switching: ReadUncommitted => ReadCommitted';
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    END;
ELSE IF @til = 2
    BEGIN
        SELECT 'Switching: ReadUncommitted => ReadCommitted';
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    END;
;
$$



Last edited by gemisigo on Mon Mar 08, 2021 10:20 am; edited 1 time in total
Thu Mar 04, 2021 7:25 pm View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
@gemisigo
wow, thanks for taking the time to explain that.
Setting the isolation level of our transactions certainly seems like a more professional and explicit way to achieve what we want to do.

I'll share it with the team.

Regards, Brendon.
Fri Mar 05, 2021 2:49 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Quote:
(BTW, @SysOp, it would be nice to have something similar in SE, too, the ability to change the defaults for those settings was so convenient).


I hear you, I will check if we already have something like that in our to-do queue.

While there is no SSMS like support for remembered session settings, which may or may not be a bad thing, your mileage may vary, it's possible to add commonly used presets to the menu.where they are easily accessible. The trick there is using code snippets with executable $$ ... $$ macros, and assigning Execute Snippet command type to the menu Action. For example, your nice snippet above can be used for flipping TIL state, no need to remember snippet's name and not limited to 10 Alt+number hotkeys, also supports using hierarchical menus for better organization. If you're interested in continuing this discussion, we should move it to a different topic, unrelated to the issue with JOINs and their collision with table hints.

Here is my screenshot demonstrating how to add custom menus executing custom code snippets to set TIL settings in the current session. For demonstration purpose I used the first two things that came to mind.



Fri Mar 05, 2021 4:08 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
SysOp wrote:
If you're interested in continuing this discussion, we should move it to a different topic


I am, and we should definitely move it to a new topic. I also happen to have an idea.

Brendon, thank you for describing your use case. The systems I work on have a relatively high write-to-read ratio. I think many (most?) of the OLTP systems do. In addition, the business rules we are compelled to follow do not allow any data loss for most of the processes, that's why I usually frown upon the NOLOCK.

As your enhancement request already has a ticket number, I guess it may be implemented sooner or later. Please note, that I suggested the TIL method as a workaround until it is done and not as a final/ultimate solution. Though you might turn it into one if you want. It's kinda like the dark side. It's not stronger but is quicker and easier (perhaps even more seductive :) The most obvious issue with it is this one: which TIL is currently active? Can you execute your query without the side-effects of the NOLOCK? Or lack of thereof, in your case? But for that, I have an idea that I want to share with SysOp that might help both dba and developer people.
Mon Mar 08, 2021 11:30 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.