Stop the Madness!! ( Sorting )

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stop the Madness!! ( Sorting )

Author

Topic

Toekiller
Starting Member

5 Posts

Posted - 2005-11-03 : 05:34:03

Ello.

Starting to optimize some of my queries which I use to feed pivot tables. I'm getting rid of all the nasty table scans but using Query Analyzer I'm noticing that it wants to sort my data when I'm grouping up data to a higher level. I don't need it to sort and from the analysis it's a massive overhead I don't want.

For Example...

Select

PeriodID,
StreamID,
ProdID,
CntryID,
Achtyp,
Sum ( CldAvgBal ) as CldAvgBal,
Sum ( CldSpotBal ) as CldSpotBal,
Sum ( LedAvgBal ) as LedAvgBal,
Sum ( NII ) as NII

From T_Stlg_Final

Group By

PeriodID,
StreamID,
ProdID,
CntryID,
Achtyp

I'm running SQL 2000 in normal mode.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-03 : 08:47:25

SQL Server is sorting the data, because it has chosen that as the fastest way to do the GROUP BY.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-03 : 09:05:13

You're getting rid of table scans?

With that Query?

I don't think so.

Post the table DDL for T_Stlg_Final

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Toekiller
Starting Member

5 Posts

Posted - 2005-11-03 : 12:17:04

CREATE TABLE [dbo].[T_Stlg_Final] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[PeriodID] [smallint] NOT NULL ,
[StreamID] [int] NULL ,
[ProdID] [smallint] NULL ,
[CntryID] [smallint] NULL ,
[Achtyp] [int] NULL ,
[SatInd] [smallint] NULL ,
[AcNo] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AcName] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcNameType] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Performing] [smallint] NULL ,
[CldAvgBal] [money] NULL ,
[LedAvgBal] [money] NULL ,
[LedSpotBal] [money] NULL ,
[CldSpotBal] [money] NULL ,
[NII] [money] NULL ,
[Lim] [money] NULL ,
[Wrate] [real] NULL ,
[Source] [smallint] NULL ,
[Exclusion] [tinyint] NOT NULL ,
[TierRef] [tinyint] NULL ,
[Tier1ID] [smallint] NULL ,
[Tier2ID] [smallint] NULL ,
[Tier3ID] [smallint] NULL ,
[Tier4ID] [smallint] NULL ,
[Tier5ID] [smallint] NULL ,
[Exposure] [money] NULL ,
[ConNo] [int] NULL ,
[MatDate] [datetime] NULL ,
[CreditTurnOver] [money] NULL ,
[OpenDate] [smalldatetime] NULL ,
[InitialLoanBal] [money] NULL ,
[RepaymentType] [smallint] NULL ,
[DebitIntType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Renewaldate] [smalldatetime] NULL ,
[DebitTurnOver] [money] NULL ,
[CrEntries] [int] NULL ,
[DrEntries] [int] NULL ,
[IND] [int] NULL ,
[BIC] [int] NULL ,
[STO] [int] NULL ,
[BUS] [int] NULL ,
[CrIntRate] [real] NULL ,
[DrIntRate] [real] NULL ,
[SAPAvgBal] [money] NULL ,
[SAPNII] [money] NULL ,
[SAPPC] [int] NULL ,
[SAPCC] [int] NULL ,
[SAPProd] [int] NULL ,
[FlagBal] [smallint] NULL ,
[FlagInActive] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FlagOcar] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FlagLimit] [smallint] NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_Stlg_Final] ON [dbo].[T_Stlg_Final]([PeriodID], [Exclusion]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[T_Stlg_Final] WITH NOCHECK ADD
CONSTRAINT [DF_T_Stlg_Final_CntryID] DEFAULT (0) FOR [CntryID],
CONSTRAINT [DF_T_Stlg_Final_FlagInActive] DEFAULT ('N') FOR [FlagInActive],
CONSTRAINT [DF_T_Stlg_Final_FlagLimit] DEFAULT (0) FOR [FlagLimit],
CONSTRAINT [PK_T_Stlg_Final] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_Stlg_Final_ProdID] ON [dbo].[T_Stlg_Final]([ProdID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_Stlg_Final_StreamID] ON [dbo].[T_Stlg_Final]([StreamID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_T_Stlg_Final_AcNo] ON [dbo].[T_Stlg_Final]([AcNo]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_T_Stlg_Final_Tier1ID] ON [dbo].[T_Stlg_Final]([Tier1ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_T_Stlg_Final_Tier2ID] ON [dbo].[T_Stlg_Final]([Tier2ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_T_Stlg_Final_TierRef] ON [dbo].[T_Stlg_Final]([TierRef]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [Ix_Period,Stream] ON [dbo].[T_Stlg_Final]([PeriodID], [StreamID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER T_Stlg_Final_Trigger_Delete ON dbo.T_Stlg_Final
FOR DELETE
AS

-- Print 'No!!! Argh! why are you trying this???'
-- Rollback Transaction

Return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources