How to reduce sql server query time

Question

I'm running a query using inner join. The table that I use to do the aggregation (SUM) have indexes created on them with column name dbo.tb_Sub_Contract.ContractGeneratedNo.

The maximum row count that any of the tables have is about 250 records. The query takes a long time to execute, how can I reduce the execution time???

SELECT 
   dbo.tb_Sub_Contract.ContractGeneratedNo, 
   SUM(dbo.tb_Sub_Contract.GrossAmount) AS GrossTotalAmount, 
   SUM(dbo.tb_Sub_Contract.WithheldAmount) AS WithheldTotalAmount, 
   dbo.tb_General_Contract.FilingMonth, 
   dbo.tb_General_Contract.FromSequenceNo, 
   dbo.tb_General_Contract.BoxNo, 
   CAST(dbo.tb_General_Contract.CompanyTRN AS Varchar(20)) + '-' + CAST(ISNULL(dbo.tb_General_Contract.CompanyBranch, '') AS Varchar(2)) AS Company_TRN_Branch, 
   COUNT(dbo.tb_Sub_Contract.SubContractId) AS NumberofContractors, 
   dbo.Get_Company_Name(CAST(dbo.tb_General_Contract.CompanyTRN AS Varchar(20)) + '-' + CAST(ISNULL(dbo.tb_General_Contract.CompanyBranch, '') AS Varchar(2))) AS Taxpayer_Name, 
   dbo.tb_General_Contract.PostedDate, 
   dbo.tb_Station.Collectorate, 
   dbo.tb_Station.StationCode
FROM         
   dbo.tb_General_Contract 
INNER JOIN
   dbo.tb_Sub_Contract ON dbo.tb_General_Contract.ContractGeneratedNo = dbo.tb_Sub_Contract.ContractGeneratedNo 
INNER JOIN
   dbo.tb_Station ON dbo.tb_General_Contract.StationId = dbo.tb_Station.StationCode
WHERE     
   (dbo.tb_Sub_Contract.IsActive = 1) AND (dbo.tb_General_Contract.IsActive = 1)
GROUP BY 
   dbo.tb_Sub_Contract.ContractGeneratedNo, dbo.tb_General_Contract.FilingMonth, 
   dbo.tb_General_Contract.FromSequenceNo, dbo.tb_General_Contract.BoxNo, 
   dbo.tb_General_Contract.CompanyTRN, dbo.tb_General_Contract.CompanyBranch, 
   dbo.tb_General_Contract.PostedDate, dbo.tb_Station.Collectorate, 
   dbo.tb_Station.StationCode

See below both table structures of tables use in aggregation

CREATE TABLE [dbo].[tb_Sub_Contract](
    [SubContractId] [bigint] IDENTITY(1,1) NOT NULL,
    [ContractGeneratedNo] [varchar](100) NOT NULL,
    [ContractTypeId] [int] NOT NULL,
    [SubContractorName] [varchar](255) NOT NULL,
    [PeriodBegin] [datetime] NOT NULL,
    [PeriodEnd] [datetime] NOT NULL,
    [GrossAmount] [decimal](14, 2) NOT NULL,
    [WithheldAmount] [decimal](14, 2) NOT NULL,
    [WithheldAmount_Cal] [decimal](14, 2) NOT NULL,
    [trn_nbr] [bigint] NULL,
    [ActionTakenId] [int] NULL,
    [PostedBy] [bigint] NULL,
    [PostedDate] [datetime] NULL,
    [ModifiedBy] [bigint] NULL,
    [ModificationDate] [datetime] NULL,
    [IsActive] [bit] NULL,
    [trn_nbrBranch] [smallint] NULL,
 CONSTRAINT [PK_tb_Sub_Contract] PRIMARY KEY CLUSTERED 
(
    [SubContractId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tb_General_Contract](
    [ContractId] [bigint] IDENTITY(1,1) NOT NULL,
    [ContractGeneratedNo] [varchar](100) NULL,
    [CompanyTRN] [bigint] NULL,
    [CompanyBranch] [smallint] NULL,
    [FilingMonth] [datetime] NULL,
    [FromSequenceNo] [varchar](50) NULL,
    [BoxNo] [varchar](50) NULL,
    [ActionTakenId] [int] NULL,
    [PostedBy] [bigint] NULL,
    [PostedDate] [datetime] NULL,
    [IsActive] [bit] NULL,
    [ModifiedBy] [bigint] NULL,
    [ModificationDate] [datetime] NULL,
    [StationId] [char](3) NULL,
    [StationIdFrom] [char](3) NULL,
 CONSTRAINT [PK_tb_General_Contract] PRIMARY KEY CLUSTERED 
(
    [ContractId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

If you press CTRL-L (show plan), what is the largest contributor (percentage) to the query? — Nick.Mc
– Nick.Mc, Commented Feb 20, 2014 at 1:17

Hart CO · Accepted Answer · 2014-02-20 00:13:41Z

Using table aliases will make your query easier to read.

Adding indexes to fields used in joins and in filtering (WHERE clause), will greatly improve performance.

There are many resources for designing indexes, like: SQL Server Index Guide

Example syntax:

CREATE CLUSTERED INDEX idx_ContractGeneratedNo ON tb_General_Contract (ContractGeneratedNo)
CREATE NONCLUSTERED INDEX idx_station ON tb_General_Contract (StationID)
....

Here's a formatting suggestion:

SELECT  SC.ContractGeneratedNo
      , SUM(SC.GrossAmount) AS GrossTotalAmount
      , SUM(SC.WithheldAmount) AS WithheldTotalAmount
      , GC.FilingMonth
      , GC.FromSequenceNo
      , GC.BoxNo
      , CAST(GC.CompanyTRN AS VARCHAR(20)) + '-'+ CAST(ISNULL(GC.CompanyBranch, '') AS VARCHAR(2)) AS Company_TRN_Branch
      , COUNT(SC.SubContractId) AS NumberofContractors
      , Get_Company_Name(CAST(GC.CompanyTRN AS VARCHAR(20))+ '-'+ CAST(ISNULL(GC.CompanyBranch,'') AS VARCHAR(2))) AS Taxpayer_Name
      , GC.PostedDate
      , S.Collectorate
      , S.StationCode
FROM    tb_General_Contract GC
INNER JOIN tb_Sub_Contract SC
    ON GC.ContractGeneratedNo = SC.ContractGeneratedNo
INNER JOIN tb_Station S
    ON GC.StationId = S.StationCode
WHERE   ( SC.IsActive = 1 )
        AND ( GC.IsActive = 1 )
GROUP BY SC.ContractGeneratedNo
      , GC.FilingMonth
      , GC.FromSequenceNo
      , GC.BoxNo
      , GC.CompanyTRN
      , GC.CompanyBranch
      , GC.PostedDate
      , S.Collectorate
      , S.StationCode

Ron Smith · Accepted Answer · 2014-02-20 00:45:31Z

You can greatly reduce the query time by reducing the group by columns. This can be done by grouping the tb_Sub_Contract aggregates by the ContractGeneratedNo and then joining that data set to the tb_General_Contract table:

select
    sc.ContractGeneratedNo,
    sc.GrossTotalAmount,
    sc.WithheldTotalAmount,
    gc.FilingMonth,
    gc.FromSequenceNo,
    gc.BoxNo, 
    cast(gc.CompanyTRN as varchar(20)) + '-' + cast(isnull(gc.CompanyBranch, '') as varchar(2)) Company_TRN_Branch,
    sc.NumberofContractors,
    dbo.Get_Company_Name(cast(gc.CompanyTRN as varchar(20)) + '-' + cast(isnull(gc.CompanyBranch, '') as varchar(2))) as Taxpayer_Name,
    gc.PostedDate,
    s.Collectorate,
    s.StationCode
from dbo.tb_General_Contract gc
    inner join  (
                select
                    ContractGeneratedNo,
                    sum(GrossAmount) GrossTotalAmount,
                    sum(WithheldAmount) WithheldTotalAmount,
                    count(SubContractId) NumberofContractors
                from dbo.tb_Sub_Contract
                where IsActive = 1
                group by
                    ContractGeneratedNo
                ) sc
        on gc.ContractGeneratedNo = sc.ContractGeneratedNo
    inner join dbo.tb_Station
        on gc.StationId = s.StationCode
where
    gc.IsActive = 1

Collectives™ on Stack Overflow

How to reduce sql server query time

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related