Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Hints not working inside blocks #441

Closed
SiPurdy opened this issue Mar 18, 2024 · 3 comments · Fixed by #435
Closed

Hints not working inside blocks #441

SiPurdy opened this issue Mar 18, 2024 · 3 comments · Fixed by #435

Comments

@SiPurdy
Copy link

SiPurdy commented Mar 18, 2024

Strange one.

Trying to activate/deactivate workflows through a script (trying to align ownership)

When I have a query like the following:

update workflow set
    statecode=1,
    statuscode=-1
from workflow
inner join solutioncomponent on workflow.workflowid = solutioncomponent.objectid
inner join solution on solutioncomponent.solutionid = solution.solutionid
where
    workflow.category in (0,3,5) and -- 0=Workflow, 3=Action, 5=Flow
    workflow.type = 1 and -- Definition
    workflow.statecode != 1 and
    solution.uniquename = '<UNIQUENAME>'
OPTION (MAXDOP 1, USE HINT('BATCH_SIZE_1' , 'USE_LEGACY_UPDATE_MESSAGES'))

The hints all take affect, queries are run in series 1 at a time using SetState requests :).

If I have a similar query but inside a condition the hints aren't followed, for example

if (@workflowOwnerId is not null) begin
    update workflow set
        statecode=0,
        statuscode=-1
    from workflow
    inner join solutioncomponent on workflow.workflowid = solutioncomponent.objectid
    inner join solution on solutioncomponent.solutionid = solution.solutionid
    where
        workflow.category in (0,3) and -- 0=Workflow, 3=Action
        workflow.type = 1 and -- Definition
        workflow.statecode != 0 and
        solution.uniquename = '<UNIQUENAME>' and
        workflow.ownerid != @workflowOwnerId
    OPTION (MAXDOP 1, USE HINT('BATCH_SIZE_1' , 'USE_LEGACY_UPDATE_MESSAGES'))   
end

What's happening is that the MAXDOP and the USE_LEGACY_UPDATE_MESSAGES hints aren't being followed (it does seem to follow the batch size) and I'm getting errors around concurrent WorkflowSetState actions.

Am I hinting right?

@MarkMpn
Copy link
Owner

MarkMpn commented Mar 18, 2024

Thanks - yes, it looks like the IF condition is overwriting the hints applied to the inner statements. I’ll take a look at this for the next release.

@SiPurdy
Copy link
Author

SiPurdy commented Mar 19, 2024

Thanks
I think I owe you beer, coffee or both 😀.
Any estimate on v.next release date? It does seem a massive release with a few bits I'm interested in.

MarkMpn added a commit that referenced this issue Mar 19, 2024
@MarkMpn
Copy link
Owner

MarkMpn commented Mar 19, 2024

I'm hoping to release it sometime in April - just doing some more testing on the conversions using the latest FetchXML features and I want to get the equivalent FetchXML -> SQL conversions in place as well.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants