Sql-server – Efficient way to check for number of records more than a limit from a table in SQL Server

countperformancequery-performancesql server

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.

Best Answer

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

Related Solutions

MySQL optimization – year column grouping – using temporary table, filesort

I don't see a lot of opportunity for improvement.

The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data.

But since you're using functions to construct the financial_year value for the group by, both the "using filesort" and "using temporary" can't be avoided. But, those aren't the real problem. The real problem is that you're evaluating MONTH(tran_date) 346,485 times and YEAR(tran_date) at least that many times... ~700,000 function calls in one second doesn't seem too bad.

Plan B: I am definitely not a fan of storing redundant data, and I'm dead-set against making the application responsible for maintaining it... but one option I might be tempted to try would be to create a dashboard_stats_by_financial_year table, and use after-insert/update/delete triggers on the transactions1 table to manage keeping those stats current.

That option has a cost, of course -- adding to the amount of time it takes to update/insert/delete a transaction... but, waiting > 1200 milliseconds for stats for your dashboard is a cost, too. So it may come down to whether you want to pay for it now or pay for it later.

Ms-access – “No Current Record” when using COUNT() in Access 2010

Okay, well, since nobody else chimed in and I needed to get it done, I took one last shot at it. Not quite sure on the why of it all, but I used basically the entire query as a subquery in the FROM clause, and then ran the report against that, and that seemed to do it...

SELECT
  [VendorCusts].[VendorPaid],
  [VendorCusts].[DE?],
  [VendorCusts].[IC?],
  [VendorCusts].[AG?],
  [VendorCusts].[GB?],
  COUNT(VendorCustID) AS [CustCount]
FROM
  (
    SELECT DISTINCT
      [Plan Revenue Expense].[Check To] AS [VendorPaid],
      [Support Provider].[DE?],
      [Support Provider].[IC?],
      [Support Provider].[Agency?] AS [AG?],
      [Support Provider].[GeneralBus?] AS [GB?],
      Customer.CustID AS [VendorCustID],
      Customer.LName AS [VendorCustLName],
      Customer.FName AS [VendorCustFName]
    FROM
      (((Customer
      INNER JOIN Plan ON Customer.CustID=Plan.CustID)
      INNER JOIN [Plan Revenue] ON [Plan].[Plan ID]=[Plan Revenue].[PlanID])
      INNER JOIN [Plan Revenue Expense] ON [Plan Revenue].[Rev ID]=[Plan Revenue Expense].[RevID])
      LEFT JOIN [Support Provider] ON [Plan Revenue Expense].[SP]=[Support Provider].[ID]
    WHERE
      (
        (
          ([Plan Revenue Expense].[First Day]>=[Expense Start "MM/DD/YYYY"])
          AND
          ([Plan Revenue Expense].[First Day]<=[Expense End "MM/DD/YYYY"])
        )
        OR
        (
          ([Plan Revenue Expense].[Last Day]>=[Expense Start "MM/DD/YYYY"])
          AND
          ([Plan Revenue Expense].[Last Day]<=[Expense End "MM/DD/YYYY"])
        )
      )
      AND NOT
      (
        [Plan Revenue].[Service]='111' OR
        [Plan Revenue].[Service]='222' OR
        [Plan Revenue].[Service] LIKE '333*'
      )
      AND NOT Customer.[Inactive?]=TRUE
    ORDER BY
      [Plan Revenue Expense].[Check To],
      Customer.LName,
      Customer.FName
  ) AS [VendorCusts]
GROUP BY
  [VendorCusts].[VendorPaid],
  [VendorCusts].[DE?],
  [VendorCusts].[IC?],
  [VendorCusts].[AG?],
  [VendorCusts].[GB?]
HAVING
  NOT ([VendorCusts].[DE?]=TRUE OR [VendorCusts].[IC?]=TRUE)
ORDER BY
  [VendorCusts].[DE?] ASC,
  [VendorCusts].[IC?] ASC,
  [VendorCusts].[AG?] ASC,
  [VendorCusts].[GB?] ASC,
  [VendorCusts].[VendorPaid] ASC;

This is more or less what I finally came up with and it appears to get the job done. Checked a few data points and they seemed to agree with the slightly more verbose version w/o a Count() function. So, I think the results are good.

Then I abstracted out the start and end of the date range so I could run a few different versions. And added a few other parameters I needed...

Seems like it's working as intended, if a bit kludge-y. Sometimes it's better to be 'right' than 'pretty.' ;)

Best Answer

Related Solutions

MySQL optimization – year column grouping – using temporary table, filesort

Ms-access – “No Current Record” when using COUNT() in Access 2010

Related Question