[RTFACT-20776] MsSQL indexes need to be inline with the other databases Created: 04/Dec/19  Updated: 01/Jul/20  Resolved: 29/Mar/20

Status: Resolved
Project: Artifactory Binary Repository
Component/s: Database
Affects Version/s: None
Fix Version/s: 7.4.0

Type: Improvement Priority: High
Reporter: Kfir Avraham Assignee: Barak Hacham
Resolution: Fixed Votes: 3
Labels: None

Attachments: PNG File image-2020-03-29-18-18-37-915.png     PNG File image-2020-03-29-18-39-44-304.png    
Regression:
Yes

 Description   

Currently "node_props" table indexes in MsSQL DB are not equivalent to other database causing degraded performance and longer response time for queries (e.g NPM search).

 

To observe this behavior and improvement following the indexes change follow the below steps:

  1. Setup Artifactory with MsSQL DB
  2. Deploy tens of thousand NPM Packages (~80,000)
  3. Issue 50 NPM search requests concurrently
    $ curl -Lvv -uadmin:password http://localhost:8081/artifactory/api/npm/npm/-/v1/search\?text\=<value>
  1. Tail Artifactory's requets.log and notice the the request time.
  2. Change the indexes in the database
  3. Issue the same commands from #3
  4. Tail Artifactory's requets.log and notice the the request time reduced by ~50%.

Generated at Tue Sep 29 10:07:38 UTC 2020 using Jira 8.5.3#805003-sha1:b4933e02eaff29a49114274fe59e1f99d9d963d7.