SQL Query Optimization - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL Query Optimization

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-18 : 00:56:55

I have a query which is taking a long time to run and I'm wondering if I can re-write it or tell sql server to run the parts of the query in a certain order.

I feel like its written as best as I can to achieve the result, but I'd be interested in any ideas....

SELECT OrgStructure.ParentNodeID, OrgStructure.NodeID, OrgStructure.UserKey, OrgStructure.UserDescription, OrgStructure.OriginalLevel,

OrgStructure.StructureType, OrgStructure.System AS Business, SUM(CCTots.SOC) AS SOC, SUM(CCTots.SOU) AS SOU

FROM OrgStructure INNER JOIN
(SELECT SUM(ReportTable.Charge) AS SOC, SUM(ReportTable.Units) AS SOU, OrgStructure_1.NodeID, OrgStructure_1.ParentNodeID,

UPPER(LTRIM(RTRIM(@SessSelNode))) + '.' + SUBSTRING(SUBSTRING(OrgStructure_1.NodeID, LEN(@SessSelNode) + 2, 1000), 1,
CHARINDEX('.', SUBSTRING(OrgStructure_1.NodeID, LEN(@SessSelNode) + 2, 1000)) - 1) AS SessSelNodeChildNodeLink

FROM OrgStructure AS OrgStructure_1 INNER JOIN

ReportTable ON UPPER(LTRIM(RTRIM(OrgStructure_1.UserKey))) = UPPER(LTRIM(RTRIM(ReportTable.[Cost Centre Number])))

WHERE (ReportTable.BillingPeriodTableID = @SessSelBillingPeriodTableID)
GROUP BY ReportTable.Units, OrgStructure_1.NodeID, OrgStructure_1.ParentNodeID, UPPER(LTRIM(RTRIM(@SessSelNode)))

+ '.' + UPPER(LTRIM(RTRIM(OrgStructure_1.UserKey)))) AS CCTots ON UPPER(LTRIM(RTRIM(OrgStructure.NodeID)))

= UPPER(LTRIM(RTRIM(CCTots.SessSelNodeChildNodeLink)))
GROUP BY OrgStructure.ParentNodeID, OrgStructure.NodeID, OrgStructure.UserKey, OrgStructure.UserDescription, OrgStructure.OriginalLevel,

OrgStructure.StructureType, OrgStructure.System

ORDER BY Business, OrgStructure.UserDescription

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 01:06:05

Before looking into details of code, i just wanted to ask what's the purpose of using UPPER() in join condition. Unless your server has a case sensitive collation, it doesnt make sense at all to use UPPER() while comparing two columns.SQL server has default behaviour of doing case insensitive comparison unless you specify a different collation.

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-18 : 01:11:45

yeah through upper() i am trying to convert column to upper case.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 02:11:13

quote:
Originally posted by rammohan

yeah through upper() i am trying to convert column to upper case.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

that i know. you missed my point. what i told was you dont require this convertion while comparison using ON as sql server by default is case insensitive and you require this only if you're using a case sensitive collation for the db.

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-18 : 02:16:54

ok thank you

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 02:19:36

quote:
Originally posted by rammohan

ok thank you

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

b/w can you explain what you're trying to achieve in code above with some sample data and output you want. then i'll try to rewrite query for you.

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-22 : 02:43:33

USE [YourDB] GO /****** Object: Table [dbo].[ReportTable] Script Date: 10/22/2008 07:11:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ReportTable]( [ReportTableID] [bigint] IDENTITY(1,1) NOT NULL, [Billing Period End Date] [varchar](30) NULL, [Service] [varchar](50) NULL, [Service Description] [nvarchar](255) NULL, [Component] [nvarchar](50) NULL, [Item] [nvarchar](50) NULL, [ChargeType] [nvarchar](50) NULL, [Charge Item Description] [nvarchar](255) NULL, [Unit charge] [decimal](12, 2) NULL, [Transaction lodgement Date] [varchar](30) NULL, [Employee Number] [int] NULL, [Units] [decimal](12, 2) NULL, [Cost Centre Number] [nvarchar](5) NULL, [Computer name - where relevant] [nvarchar](20) NULL, [Environment] [nvarchar](20) NULL, [Charge] [decimal](12, 2) NULL, [Calculated Charge] [decimal](12, 2) NULL, [Invoiced charge] [money] NULL, [Transaction UID] [uniqueidentifier] NULL, [Profile] [nvarchar](50) NULL, [Central] [nvarchar](255) NULL, [Last name] [nvarchar](255) NULL, [Initials] [nvarchar](255) NULL, [First name] [nvarchar](255) NULL, [Known as] [nvarchar](255) NULL, [BUID] [nvarchar](255) NULL, [EmpGrp] [nvarchar](255) NULL, Email [nvarchar](255) NULL, [Cost Centre name] [nvarchar](255) NULL, [Organisational unit Number] [nvarchar](255) NULL, [Organisation Unit Name] [nvarchar](255) NULL, [Position ID] [nvarchar](255) NULL, [Position name] [nvarchar](255) NULL, [Generic Job Number] [nvarchar](255) NULL, [Post code] [nvarchar](255) NULL, [State] [nvarchar](255) NULL, [Level] [nvarchar](255) NULL, [Street] [nvarchar](255) NULL, [Town] [nvarchar](255) NULL, [PO Number] [nvarchar](255) NULL, [Work phone] [nvarchar](255) NULL, [BillingPeriodTableID] [smallint] NULL, [EnvironmentID] [smallint] NULL, [ProfileID] [smallint] NULL, [SAPID] [int] NULL, [TransactionID] [int] NULL, [ChargeItemID] [int] NULL, [InvoiceTableID] [bigint] NULL, [ChargeTypeID] [smallint] NULL, [ServiceID] [int] NULL, [ComponentID] [int] NULL, [WS] [nvarchar](20) NULL, [InProfile?] [bit] NULL, CONSTRAINT [PK_ComponentID] PRIMARY KEY CLUSTERED ( [ReportTableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO

and.....

USE [WRBE]

GO

/****** Object: Table [dbo].[OrgStructure] Script Date: 10/22/2008 07:12:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[OrgStructure](

[System] [nvarchar](5) NOT NULL,

[StructureType] [nvarchar](10) NULL,

[OriginalLevel] [nvarchar](10) NULL,

[ParentNodeID] [nvarchar](255) NULL,

[NodeID] [nvarchar](255) NULL,

[UserKey] [nvarchar](255) NULL,

[UserDescription] [nvarchar](255) NULL,

[NodeSource] [nvarchar](255) NULL,
[Parent2] [nvarchar](255) NULL

) ON [PRIMARY]

This is the type of data for the Org Structure table following the order of the fields above.

ODB ARM 5 ODB.AFS.SB.00087.00283 ODB.AFS.SB.00087.00283.28307 28307 RAM SOUTH AUSTRALIA NULL NULL
ODB AGR 6 ODB.AFS.SB.00087.00483.48305 ODB.AFS.SB.00087.00483.48305.4982 A4982 Warwick Report Delivery ONLY NULL NULL
ODB DCB 6 ODB.AFS.SB.00046.00252.25201 ODB.AFS.SB.00046.00252.25201.2617 A2617 Health North NSW West BBC1 NULL NULL
ODB STT 3 ODB.AFS.SB ODB.AFS.SB.00049 00049 EDUCATION NULL NULL
ODB CAP 5 ODB.AFS.ABO.Other.00165.16515 ODB.AFS.ABO.Other.00165.16515.4012 A4012 DP Acctng State Ops QLD NULL NULL
ODB APC 5 ODB.AFS.ABO.Other.00165.16522 ODB.AFS.ABO.Other.00165.16522.3340 A3340 TP - East Melbourne NULL NULL
ODB PCO 5 ODB.AFS.ABO.00040.00169.Other ODB.AFS.ABO.00040.00169.Other.2256 A2256 NSW Credit Team NULL NULL
ODB DEP 5 ODB.AFS.ABO.00040.00170 ODB.AFS.ABO.00040.00170.17006 17006 LENDING SERVS 17006 NULL NULL

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 02:57:58

see this and understand how to post required info

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-22 : 03:50:04

You coud try to use the new windowed functions.

SUM(reportTable.Charge) OVER (PARTITION BY ...) instead of using derived table.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources