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

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

Type: Improvement Priority: High
Reporter: Kfir Avraham Assignee: Unassigned
Resolution: Unresolved Votes: 3
Labels: None

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 Feb 18 07:54:22 UTC 2020 using JIRA 7.6.16#76018-sha1:9ed376192612a49536ac834c64177a0fed6290f5.