Search code examples
sqlsql-serversqlperformance

Improve execution time of select distinct query

enter image description hereI'm having issues getting a full result set back for the query below. When i select distinct top 10000 i get results in seconds. When i take this out it keeps running for over 1 hour. I checked and tried to index the fields such as company reg num + country inc but makes no difference. Toad suggested adding the following index: 

USE [BW_DCF];
    GO
CREATE NONCLUSTERED INDEX [idx_Nonclustered_WeccoParty_OverallStatus]
ON [CORE].[WeccoParty]
([OverallStatus])
INCLUDE (
[GtId], [CrmPartyId], [FirstName], [LastName], [LegalName], [CountryInc], 
[BusinessClass], [RmFullName], [PbeFullName], [OverallClientStatus], 
[OverallRpStatus], [CompanyRegNum] 
)
WITH
(
PAD_INDEX = OFF,
FILLFACTOR = 100,
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = NONE
)
ON [BW_DCF_Group1];
GO



   select distinct wp1.GtId, 
  wp1.CrmPartyId, 
  wp1.LegalName, 
  wp1.BusinessClass, 
  wp1.RmFullName, 
  wp1.PbeFullName, 
  wp1.OverallClientStatus, 
  wp1.OverallRpStatus, 
  wp1.FirstName,
  wp1.LastName,  
  wp1.LegalName,
  wp1.CountryInc,
  wp1.CompanyRegNum,
  wp2.GtId, 
  wp2.CrmPartyId, 
  wp2.LegalName, 
  wp2.BusinessClass, 
  wp2.RmFullName, 
  wp2.PbeFullName, 
  wp2.OverallClientStatus, 
  wp2.OverallRpStatus,  
  wp2.FirstName,
  wp2.LastName,
  wp2.LegalName,
  wp2.CountryInc,
  wp2.CompanyRegNum
     from CORE.WeccoParty wp1
      join CORE.WeccoParty wp2 on   wp1.CompanyRegNum = wp2.CompanyRegNum
                    and wp1.CountryInc = wp2.CountryInc     
                    and wp1.GtId <> wp2.GtId 
                and wp1.OverallStatus = 'Onboarded'
            and wp2.OverallStatus = 'Onboarded'
Solution

  • Long query times often lie on overflowing RAM space or a database which has insufficient RAM space given. This query is completely normal and there is not really any performance boost possible.

    As a solution I would give the database more ram to work with.

    In my experience I saw that sql-server has huge issues when it lacks on RAM space. PostgreSQL seems to deal with it a lot quicker.