0

I came across queries that can most efficiently get the total sum of the records in the table. However, i want to group the records in a table by a column and check if the count of the records is higher than 1000, if not so, i need to return the count and if so, i can return -1.

TableDesign

CREATE TABLE [dbo].[Accounts](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](255) NOT NULL,
    [CreatedById] [bigint] NOT NULL,
    [CreatedDate] [datetimeoffset](7) NOT NULL,
    [ModifiedById] [bigint] NOT NULL,
    [ModifiedDate] [datetimeoffset](7) NOT NULL,
    [IsActive] [bit] NOT NULL,
    [StagingStatusId] [bigint] NULL,
    [IsApproved] [bit] NOT NULL,
    [IsActive] [bit] NOT NULL
)

Currently, I have the following query that groups the data by the status and then returns the count.

select StagingStatusId, count(1) AS Total
from I8Core.AccountCode
where IsActive = 1 and IsApproved = 1
group by StagingStatusId

However, as the number of records in the database table is higher (> 100k), the query gets slower and delayed.

The aim of this query is to know what statusids are higher than 999 and what are lesser than 999.

For the records that are higher than 999 (Ex. if statusid = 1 & count = 1244, we need as statusid=1,count=999+), else, we need to return statusid=1, count=998 (for any value less than 999).

I found that the below query can return the total row count per table, but this cannot be applied to this scenario.

Kindly suggest the best practice to achieve this.

  • 1
    Most important, if StagingStatusId is not indexed, add an index on the field. – Laughing Vergil Nov 1 '18 at 17:32
  • Sure, I have an index on the field. – Saran Nov 2 '18 at 5:07
0
CREATE NONCLUSTERED INDEX IX_StagingStatusId ON Accounts(StagingStatusId)INCLUDE(IsActive, IsApproved);

SELECT T.StagingStatusId
     ,'StatCounts'= CASE WHEN T.Total>999 THEN '>999'
                         WHEN T.Total<999 THEN '<999'
                       END 

 FROM (
select StagingStatusId, count(*) AS Total
from Accounts
where IsActive = 1 and IsApproved = 1
group by StagingStatusId
) AS T
  • Thank you. I will try this and share the update. However, will there be a constant index update as and when I push the record in the table or update the data as we are dealing with the count query. Kindly let me know the impact on the day-to-day transactions. – Saran Nov 4 '18 at 7:12
  • @Saran whenever you insert new row to the table or update value of the columns in index, then index of table will get updated.This index will help you reduce the burden on table by avoiding complete table scan or key lookup for other columns as you have already included the columns you require in Included columns in index. – Rsola Nov 5 '18 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.