[RTFACT-15615] Property search on MSSQL bad performance Created: 08/Jan/18  Updated: 06/Mar/19  Resolved: 09/Dec/18

Status: Resolved
Project: Artifactory Binary Repository
Component/s: None
Affects Version/s: 4.9.0, 5.8.2
Fix Version/s: 6.6.0

Type: Improvement Priority: Normal
Reporter: Ariel Kabov Assignee: Lior Gur (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Attachments: HTML File test200k    
Assigned QA: Maria Pogozelits
Sprint: Leap 35

 Description   

When running a Property Search using either REST API or AQL, when connected to MSSQL, a query similar to the following is issued:

2018-01-08 14:29:42,388 [http-nio-8081-exec-3] [DEBUG] (o.a.s.d.u.JdbcHelper:255) - Executing SQL: 'Select bb.* from (Select aa.*  ,ROW_NUMBER() OVER ( order by (SELECT 1)) AS RN  from ( select distinct  n.repo as itemRepo,n.node_path as itemPath,n.node_name as itemName,n.created as itemCreated,n.modified as itemModified,n.updated as itemUpdated,n.created_by as itemCreatedBy,n.modified_by as itemModifiedBy,n.node_type as itemType,n.bin_length as itemSize,n.node_id as itemId,n.depth as itemDepth,n.sha1_actual as itemActualSha1,n.sha1_original as itemOriginalSha1,n.md5_actual as itemActualMd5,n.md5_original as itemOriginalMd5 from  nodes n left outer join node_props np100 on np100.node_id = n.node_id where (((( np100.prop_key = 'liza' and np100.prop_value = 'ariel')))) and(n.repo != 'auto-trashcan' or n.repo is null)  ) aa ) bb Where bb.RN >= 1 AND bb.RN < 9223372036854775807 '.

The following part:

Select bb.* from (Select aa.*  ,ROW_NUMBER() OVER ( order by (SELECT 1)) AS RN  from (

is redundant, as something like this should be added only if the query is limited.

The issue, is that currently this runs with a limit of 'long.MAX_VALUE', which does nothing and only harm in performance.



 Comments   
Comment by Gal Ben Ami [ 04/Dec/18 ]

See comparison here:
https://docs.google.com/spreadsheets/d/1KHfA1jCsHQ2Px3wNXTk3wLe9xfYIO1bNyj7_Tfsm0_8/edit?usp=sharing

Generally we have problems with the following:
curl -uadmin:password 'localhost:8081/artifactory/api/search/prop?a=1&b=763736&repos=example-repo-local'
curl -uadmin:password 'localhost:8081/artifactory/api/search/aql' --data 'items.find({"@a" : {"$eq" : "1"}}, {"@b" : {"$eq" : "763736"}}).include("path", "name").limit(100000)' -H"Content-Type:text/plain"

Comment by Lior Gur (Inactive) [ 06/Dec/18 ]

I add a flag in ConstantValues : sqlServerQueryBuilderForceAddOffsetToQuery.

when the flag sets to true the generated query will be as before the fix (with offset)

Comment by Andrei Komarov [ 06/Mar/19 ]

One report suggested that 'update statistics' might help with faster query execution times.

Generated at Sun Nov 17 20:17:46 UTC 2019 using JIRA 7.6.16#76018-sha1:9ed376192612a49536ac834c64177a0fed6290f5.