Product Ideas

Share your suggestions and ideas. Vote on ideas you find valuable.

Do you want to play a crucial role in shaping Jet Global products?

Add WITH (NOLOCK) - SQL Query

Adding the command WITH (NOLOCK) in the SQL query could avoid blocking tables and sometimes increase the performance. At least would be great give the option to the user to use this command or not in his own reports.

  • Guest
  • Oct 4 2018
  • Released
Designing & Running Reports
  • May 2, 2019

    Admin response

    Jet 2019 R2 (20.0.19116.1) - released April 30 2019

    When using a SQL data source, you have the ability to configure your data sources settings so that Jet functions will use the SQL NOLOCK hint. With heavy-traffic data sources, this can potentially offer significant performance improvements by not requiring queries to wait for SQL to release locks on a table.

    See this knowledgebase article for more information.

    nolock1.png
    Attachments Open full size
  • Attach files
  • Kim Duey commented
    July 10, 2019 21:13

    I believe the NOLOCK hint is primarily about the query reading a locked table, not disabling table locking. However, I'm surprised that a query locks tables in the first place. I can certainly see a long-running report having an affect on resources. It certainly would be easy to give it a try though.

    Attachments Open full size
  • Guest commented
    July 04, 2019 05:16

    We have some challenges in our business with large Jet Reports causing table locking on our NAV database.

    Wondering if enabling the NO LOCK hint on the data source setting (as per the knowledge base article above) could potentially give us a reduction in the amount of table locking caused by the Jet Reports running?

    Attachments Open full size

Related ideas