Help on Query Speed - 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)

Help on Query Speed

Author

Topic

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 11:07:52

I have a query that is running slower than I think it should. It is taking 52 seconds to run, which I think is too long.


SELECT BC.FFGBillCode, 
		BC.PriceType, 
		MIN(BC.EffectiveDate), 
		MAX(BC.EffectiveDate),
		BC.BreakDownCode, 
		SUM(CBCO.ActualBillQty), 
		BC.Price, 
		BC.Cost,
		BCMM.RevenueManMinutes,
		CBCO.Cancel,
		CBCO.PricePercent,
		BCMM.FK_Department
	FROM tblCBillFlight CBF
	INNER JOIN tblCBillFlightInfo CBFI ON CBF.CBillFlight_PK = CBFI.FK_CBillFlight
	INNER JOIN tblCBillClass CBC ON CBFI.CBillFlightInfo_PK = CBC.FK_CBillFlightInfo
	INNER JOIN tblCBillCodes CBCO ON CBC.CBillClass_PK = CBCO.FK_CBillClass
	INNER JOIN tblBillCodes BC ON CBCO.FK_BillCode = BC.BillCode_PK
	INNER JOIN tblBillCodeManMinutes BCMM ON BC.BillCode_PK = BCMM.FK_BillCode
	WHERE BC.FK_KitchenID =  @FK_KitchenID
	AND BC.FK_CustomerID = @FK_CustomerID
	AND CBFI.FlightDate BETWEEN @BeginDate AND @EndDate
	GROUP BY BC.FFGBillCode, BC.PriceType, BC.BreakDownCode, BC.Price, BC.Cost,
	BCMM.RevenueManMinutes, CBCO.Cancel, CBCO.PricePercent, BCMM.FK_Department

I have a non-clustered-index on all the FK and PK fields in the joins.
I tried putting non-clustered-indexs on FK_KitchenID, FK_CustomerID and FlightDate which are used in the WHERE clause but that didn't help.

The thing I find confusing is if I remove the WHERE clause completely or only leave FK_KitchenID in it the query takes only 4 seconds to run.

Does anyone have any suggestions?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-14 : 11:11:55

Look at the execution plan to see where you're losing performance. Also set statistics io on and look at that.

A quick guess is that you have out of date statistics on your date fields.

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 11:31:51

The table that is causing the biggest hit on performance is tblCBillCodes. I see that it is doing a Table Scan on it, which I know is bad but I don't see what is causing it to do a table scan instead of an index seek. What is really throwing me off is that even if I modify the query to remove the WHERE clause it still does a Table Scan on that table but it returns my results in 4 seconds. What is causing it to perform so much better without the WHERE clause?

I check the statistics on FlightDate and they are up to date. Last update was done last night and I have not done any inserts or deletes on any of the tables.

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 11:33:03

quote:
Originally posted by russell

Also set statistics io on and look at that.

Ok I set the statistics io on. What does that do exactly?

Kristen
Test

22859 Posts

Posted - 2010-12-14 : 11:52:16

You have an index on FK_CBillClass in tblCBillCodes? Double check?

Does

sp_helpindex 'tblCBillCodes'

list an index on FK_CBillClass ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-12-14 : 11:53:42

if the 2 columns in the statement
"INNER JOIN tblCBillCodes CBCO ON CBC.CBillClass_PK = CBCO.FK_CBillClass
do not match in datatypes, then a tables scan will result.

can you post (all) the tables definitions and the execution plan?

Kristen
Test

22859 Posts

Posted - 2010-12-14 : 11:58:56

Good point Andrew

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-14 : 11:59:20

quote:
Originally posted by Maldred

quote:
Originally posted by russell

Also set statistics io on and look at that.

Ok I set the statistics io on. What does that do exactly?

shows the number of reads against each object in the query.

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 12:18:25

quote:
Originally posted by Kristen

You have an index on FK_CBillClass in tblCBillCodes? Double check?

Does

sp_helpindex 'tblCBillCodes'

list an index on FK_CBillClass ?

Yea I double checked and it is listed.

quote:
Originally posted by AndrewMurphy

if the 2 columns in the statement
"INNER JOIN tblCBillCodes CBCO ON CBC.CBillClass_PK = CBCO.FK_CBillClass
do not match in datatypes, then a tables scan will result.

Both fields are setup as BigInt.

Here is the table definitions for both tblCBillClass and tblCBillCodes.


USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblCBillClass]    Script Date: 12/14/2010 11:12:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCBillClass](
	[CBillClass_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_CBillFlightInfo] [bigint] NOT NULL,
	[FK_InvoiceHeader] [bigint] NULL,
	[FK_Class] [int] NOT NULL,
	[FK_RecapType] [bigint] NOT NULL,
	[FK_EquipmentType] [bigint] NULL,
	[Cycle] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PaxCount] [smallint] NOT NULL,
	[Count2] [smallint] NOT NULL,
	[Count3] [smallint] NOT NULL,
	[Count4] [smallint] NOT NULL,
	[Count5] [smallint] NOT NULL,
	[Count6] [smallint] NOT NULL,
	[MealCodeCA] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MealCodeCU] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CateredFlight] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CateredFlightSeq] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CateredDayCode] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CateredSegment] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OperationCode] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Tax] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Discount] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PortFee] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Surcharge] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CXDFD] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CXDSVC] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CXDBND] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[isAdjustments] [bit] NOT NULL,
	[isCalculated] [bit] NOT NULL CONSTRAINT [DF_tblCBillClass_isCalculated]  DEFAULT ((0)),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_tblCBillClass_UpdateDate]  DEFAULT (getdate()),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_tblCBillClass_CreateDate]  DEFAULT (getdate()),
	[UserName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_tblCBillClass] PRIMARY KEY NONCLUSTERED 
(
	[CBillClass_PK] 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
ALTER TABLE [dbo].[tblCBillClass]  WITH CHECK ADD  CONSTRAINT [FK_tblCBillClass_tblCBillFlightInfo] FOREIGN KEY([FK_CBillFlightInfo])
REFERENCES [dbo].[tblCBillFlightInfo] ([CBillFlightInfo_PK])
GO
ALTER TABLE [dbo].[tblCBillClass] CHECK CONSTRAINT [FK_tblCBillClass_tblCBillFlightInfo]
GO
ALTER TABLE [dbo].[tblCBillClass]  WITH CHECK ADD  CONSTRAINT [FK_tblCBillClass_tblInvoiceHeader] FOREIGN KEY([FK_InvoiceHeader])
REFERENCES [dbo].[tblInvoiceHeader] ([InvoiceHeader_PK])
GO
ALTER TABLE [dbo].[tblCBillClass] CHECK CONSTRAINT [FK_tblCBillClass_tblInvoiceHeader]
GO
ALTER TABLE [dbo].[tblCBillClass]  WITH CHECK ADD  CONSTRAINT [ReftblClass1391] FOREIGN KEY([FK_Class])
REFERENCES [dbo].[tblClass] ([Class_PK])
GO
ALTER TABLE [dbo].[tblCBillClass] CHECK CONSTRAINT [ReftblClass1391]
GO
ALTER TABLE [dbo].[tblCBillClass]  WITH CHECK ADD  CONSTRAINT [ReftblEquipmentTypes1411] FOREIGN KEY([FK_EquipmentType])
REFERENCES [dbo].[tblEquipmentType] ([EquipmentType_PK])
GO
ALTER TABLE [dbo].[tblCBillClass] CHECK CONSTRAINT [ReftblEquipmentTypes1411]
GO
ALTER TABLE [dbo].[tblCBillClass]  WITH CHECK ADD  CONSTRAINT [ReftblRecapTypes1401] FOREIGN KEY([FK_RecapType])
REFERENCES [dbo].[tblRecapType] ([RecapType_PK])
GO
ALTER TABLE [dbo].[tblCBillClass] CHECK CONSTRAINT [ReftblRecapTypes1401]



USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblCBillCodes]    Script Date: 12/14/2010 11:12:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCBillCodes](
	[CBillCodes_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_ReferenceInfo] [bigint] NULL,
	[FK_BillCode] [bigint] NOT NULL,
	[FK_CBillClass] [bigint] NOT NULL,
	[FK_GroupCode] [int] NULL,
	[FK_ServiceCode] [int] NULL,
	[SequenceNumber] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PriceDescription] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Cancel] [decimal](18, 3) NULL,
	[ActualBillQty] [int] NOT NULL,
	[ScheduledBillQty] [int] NOT NULL,
	[UseFlag] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AccountCode1] [int] NOT NULL,
	[AccountCode2] [int] NULL,
	[AccountCode3] [int] NULL,
	[AccountCode4] [int] NULL,
	[AccountCode5] [int] NULL,
	[SalvageFlag] [bit] NOT NULL,
	[Flag1] [smallint] NOT NULL,
	[Flag2] [smallint] NOT NULL,
	[PricePercent] [decimal](18, 3) NULL,
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_tblCBillCodes_UpdateDate]  DEFAULT (getdate()),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_tblCBillCodes_CreateDate]  DEFAULT (getdate()),
	[UserName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_tblCBillCodes] PRIMARY KEY NONCLUSTERED 
(
	[CBillCodes_PK] 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

How can I post the execution plan? Do you just want to see the text layout of it?

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 12:38:42

Sorry here is the definitions of the other tables.


tblCBillFlight
USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblCBillFlight]    Script Date: 12/14/2010 11:37:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCBillFlight](
	[CBillFlight_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_KitchenID] [smallint] NOT NULL,
	[FK_CustomerID] [smallint] NOT NULL,
	[FlightNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[FlightSeq] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Segment] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_tblCBillFlight_CreateDate1]  DEFAULT (getdate()),
 CONSTRAINT [PK_tblCBillFlight] PRIMARY KEY NONCLUSTERED 
(
	[CBillFlight_PK] 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
ALTER TABLE [dbo].[tblCBillFlight]  WITH CHECK ADD  CONSTRAINT [FK_tblCBillFlight_tblCompanyID] FOREIGN KEY([FK_KitchenID])
REFERENCES [dbo].[tblCompanyID] ([KitchenID_PK])
GO
ALTER TABLE [dbo].[tblCBillFlight] CHECK CONSTRAINT [FK_tblCBillFlight_tblCompanyID]
GO
ALTER TABLE [dbo].[tblCBillFlight]  WITH CHECK ADD  CONSTRAINT [FK_tblCBillFlight_tblCustomerID] FOREIGN KEY([FK_CustomerID])
REFERENCES [dbo].[tblCustomerID] ([CustomerID_PK])
GO
ALTER TABLE [dbo].[tblCBillFlight] CHECK CONSTRAINT [FK_tblCBillFlight_tblCustomerID]


tblCBillFlightInfo
USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblCBillFlightInfo]    Script Date: 12/14/2010 11:44:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCBillFlightInfo](
	[CBillFlightInfo_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_RecapType] [bigint] NOT NULL,
	[FK_CBillFlight] [bigint] NOT NULL,
	[FK_SalesType] [smallint] NOT NULL,
	[FK_AircraftInfo] [bigint] NULL,
	[FK_EquipmentType] [bigint] NULL,
	[FlightDate] [datetime] NOT NULL,
	[NumberSegments] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ReferenceFlightNum] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ReferenceFlightSeq] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Location] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Destination] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[FlightCountFlag] [smallint] NOT NULL,
	[ActivityCode] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Reason] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CateredDayCodeAC] [bit] NOT NULL,
	[ShipNumber] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CateredFlight] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CateredFlightSeq] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CateredDayCode] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CateredSegment] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OperationCode] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CXDFD] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CXDSVC] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CXDBND] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[isInsertFlight] [bit] NOT NULL CONSTRAINT [DF_tblCBillFlightInfo_isInsertFlight]  DEFAULT ((0)),
	[ETD] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AdjustmentTime] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AdjustmentDay] [smallint] NOT NULL CONSTRAINT [DF_tblCBillFlightInfo_AdjustmentDay]  DEFAULT ((0)),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_tblCBillFlightInfo_UpdateDate]  DEFAULT (getdate()),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_tblCBillFlightInfo_CreateDate]  DEFAULT (getdate()),
	[UserName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_CBillFlightInfo] PRIMARY KEY NONCLUSTERED 
(
	[CBillFlightInfo_PK] 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
ALTER TABLE [dbo].[tblCBillFlightInfo]  WITH CHECK ADD  CONSTRAINT [FK_tblCBillFlightInfo_tblCBillFlight] FOREIGN KEY([FK_CBillFlight])
REFERENCES [dbo].[tblCBillFlight] ([CBillFlight_PK])
GO
ALTER TABLE [dbo].[tblCBillFlightInfo] CHECK CONSTRAINT [FK_tblCBillFlightInfo_tblCBillFlight]
GO
ALTER TABLE [dbo].[tblCBillFlightInfo]  WITH CHECK ADD  CONSTRAINT [ReftblAircraftInfo1471] FOREIGN KEY([FK_AircraftInfo])
REFERENCES [dbo].[tblAircraftInfo] ([AircraftInfo_PK])
GO
ALTER TABLE [dbo].[tblCBillFlightInfo] CHECK CONSTRAINT [ReftblAircraftInfo1471]
GO
ALTER TABLE [dbo].[tblCBillFlightInfo]  WITH CHECK ADD  CONSTRAINT [ReftblEquipmentTypes1481] FOREIGN KEY([FK_EquipmentType])
REFERENCES [dbo].[tblEquipmentType] ([EquipmentType_PK])
GO
ALTER TABLE [dbo].[tblCBillFlightInfo] CHECK CONSTRAINT [ReftblEquipmentTypes1481]
GO
ALTER TABLE [dbo].[tblCBillFlightInfo]  WITH CHECK ADD  CONSTRAINT [ReftblRecapTypes1461] FOREIGN KEY([FK_RecapType])
REFERENCES [dbo].[tblRecapType] ([RecapType_PK])
GO
ALTER TABLE [dbo].[tblCBillFlightInfo] CHECK CONSTRAINT [ReftblRecapTypes1461]
GO
ALTER TABLE [dbo].[tblCBillFlightInfo]  WITH CHECK ADD  CONSTRAINT [ReftblSalesType1451] FOREIGN KEY([FK_SalesType])
REFERENCES [dbo].[tblSalesType] ([SalesType_PK])
GO
ALTER TABLE [dbo].[tblCBillFlightInfo] CHECK CONSTRAINT [ReftblSalesType1451]

tblBillCodes
USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblBillCodes]    Script Date: 12/14/2010 11:37:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblBillCodes](
	[BillCode_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_KitchenID] [smallint] NOT NULL,
	[FK_CustomerID] [smallint] NOT NULL,
	[FK_MealServiceCodes] [bigint] NOT NULL,
	[FK_ProductType1] [bigint] NULL,
	[FK_ProductType2] [bigint] NULL,
	[isDefault] [bit] NOT NULL,
	[MPLREVNUM] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[EffectiveDate] [datetime] NOT NULL,
	[ThruDate] [datetime] NULL,
	[CustomerBillCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[FFGBillCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PriceType] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Price] [decimal](18, 3) NOT NULL CONSTRAINT [DF_tblBillCodes_Price]  DEFAULT ((0)),
	[Cost] [decimal](18, 3) NOT NULL CONSTRAINT [DF_tblBillCodes_Cost]  DEFAULT ((0)),
	[Unit] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[BBDescription] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SequenceNumber] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[BreakdownCode] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AccountCode] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SalvageFlag] [bit] NOT NULL,
	[TDPSCode] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[EstimatePrice] [bit] NOT NULL,
	[DietCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[TotalRS] [decimal](18, 3) NOT NULL CONSTRAINT [DF_tblBillCodes_TotalRS]  DEFAULT ((0)),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_tblBillCodes_CreateDate]  DEFAULT (getdate()),
	[ModifyDate] [datetime] NOT NULL CONSTRAINT [DF_tblBillCodes_ModifyDate]  DEFAULT (getdate()),
	[UserName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_tblBillCodes] PRIMARY KEY NONCLUSTERED 
(
	[BillCode_PK] 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
ALTER TABLE [dbo].[tblBillCodes]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodes_tblCompanyID] FOREIGN KEY([FK_KitchenID])
REFERENCES [dbo].[tblCompanyID] ([KitchenID_PK])
GO
ALTER TABLE [dbo].[tblBillCodes] CHECK CONSTRAINT [FK_tblBillCodes_tblCompanyID]
GO
ALTER TABLE [dbo].[tblBillCodes]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodes_tblCustomerID] FOREIGN KEY([FK_CustomerID])
REFERENCES [dbo].[tblCustomerID] ([CustomerID_PK])
GO
ALTER TABLE [dbo].[tblBillCodes] CHECK CONSTRAINT [FK_tblBillCodes_tblCustomerID]
GO
ALTER TABLE [dbo].[tblBillCodes]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodes_tblMealServiceCodes] FOREIGN KEY([FK_MealServiceCodes])
REFERENCES [dbo].[tblMealServiceCodes] ([MealServiceCodes_PK])
GO
ALTER TABLE [dbo].[tblBillCodes] CHECK CONSTRAINT [FK_tblBillCodes_tblMealServiceCodes]
GO
ALTER TABLE [dbo].[tblBillCodes]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodes_tblProductType] FOREIGN KEY([FK_ProductType1])
REFERENCES [dbo].[tblProductType] ([ProductType_PK])
GO
ALTER TABLE [dbo].[tblBillCodes] CHECK CONSTRAINT [FK_tblBillCodes_tblProductType]
GO
ALTER TABLE [dbo].[tblBillCodes]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodes_tblProductType1] FOREIGN KEY([FK_ProductType2])
REFERENCES [dbo].[tblProductType] ([ProductType_PK])
GO
ALTER TABLE [dbo].[tblBillCodes] CHECK CONSTRAINT [FK_tblBillCodes_tblProductType1]

tblBillCodeManMinutes
USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblBillCodeManMinutes]    Script Date: 12/14/2010 11:38:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblBillCodeManMinutes](
	[BillCodeManMinutes_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_BillCode] [bigint] NOT NULL,
	[FK_Department] [int] NOT NULL,
	[RevenueManMinutes] [decimal](18, 3) NOT NULL,
	[CostManMinutes] [decimal](18, 3) NOT NULL,
	[IndexManMinutes] [decimal](18, 3) NOT NULL,
 CONSTRAINT [PK_tbl_BillCodeManMinutes] PRIMARY KEY NONCLUSTERED 
(
	[BillCodeManMinutes_PK] 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
ALTER TABLE [dbo].[tblBillCodeManMinutes]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodeManMinutes_tblBillCodes] FOREIGN KEY([FK_BillCode])
REFERENCES [dbo].[tblBillCodes] ([BillCode_PK])
GO
ALTER TABLE [dbo].[tblBillCodeManMinutes] CHECK CONSTRAINT [FK_tblBillCodeManMinutes_tblBillCodes]
GO
ALTER TABLE [dbo].[tblBillCodeManMinutes]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodeManMinutes_tblDepartment] FOREIGN KEY([FK_Department])
REFERENCES [dbo].[tblDepartment] ([Department_PK])
GO
ALTER TABLE [dbo].[tblBillCodeManMinutes] CHECK CONSTRAINT [FK_tblBillCodeManMinutes_tblDepartment]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-14 : 12:52:23

EffectiveDate is indexed? FFGBillCode is indexed?

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 13:00:50

quote:
Originally posted by russell

EffectiveDate is indexed?

Not normally but just tried putting an index on it it only reduced the query to 51 seconds.

quote:
Originally posted by russell

FFGBillCode is indexed?

Yes

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-12-14 : 13:34:10

Run this query:

SELECT
object_name(object_ID),
STATS_DATE (object_id, index_id) AS last_update,
*
FROM
sys.indexes
order by
1

what kind of dates do you get? This Query gives you information as to whether or not your stats are up to date.

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 14:03:00

quote:
Originally posted by denis_the_thief

Run this query:

SELECT
object_name(object_ID),
STATS_DATE (object_id, index_id) AS last_update,
*
FROM
sys.indexes
order by
1

what kind of dates do you get? This Query gives you information as to whether or not your stats are up to date.

All statistics that are coming from the tables in the joins have a 12/14/2010 date. I have a process that runs every morning updating my statistics on that database.

On some of the system objects it has a date of 2005. Would that affect my database in anyway? Listed below are some of the system objects that have an old date.

sysallocunits
sysbinobjs
sysdbfiles
sysdiagrams
syshobtcolumns

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-12-14 : 14:08:08

quote:

On some of the system objects it has a date of 2005. Would that affect my database in anyway? Listed below are some of the system objects that have an old date.

sysallocunits
sysbinobjs
sysdbfiles
sysdiagrams
syshobtcolumns

I think those sys ones are fine.

What kind of table counts for the tables involved are we looking at. You could run this:

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY 1

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 15:04:37

quote:
Originally posted by denis_the_thief

What kind of table counts for the tables involved are we looking at. You could run this:

tblCBillFlight = 1081
tblCBillFlightInfo = 113,930
tblCBillClass = 122,377
tblCBillCodes = 1,114,024
tblBillCodes = 49,726
tblBillCodeManMinutes = 348,082

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-12-14 : 15:57:18

quote:
Originally posted by Maldred

quote:
Originally posted by denis_the_thief

What kind of table counts for the tables involved are we looking at. You could run this:

tblCBillFlight = 1081
tblCBillFlightInfo = 113,930
tblCBillClass = 122,377
tblCBillCodes = 1,114,024
tblBillCodes = 49,726
tblBillCodeManMinutes = 348,082

- if you are using SSMS 2008, when you create execution plan, it should suggest an index?
- the table with the scan is rather large, try removing tables from your query to see when the table scan goes away
- you could post your indexes
- you could run this query repeatedly while your query is executing, it shows your buffer cache, if your numbers go up and down while the query is executing, could be your cache is too small:

SELECT count(*)AS cached_pages_count 
    ,name ,index_id 
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id 
ORDER BY cached_pages_count DESC

if you are not on a production server, run this first to clear your buffer cache:

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS
GO

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 17:40:57

quote:
- if you are using SSMS 2008, when you create execution plan, it should suggest an index?

I'm using 2005.

quote:
- the table with the scan is rather large, try removing tables from your query to see when the table scan goes away

I removed tblBillCodes and tblBillCodeManMinutes from the query after that it became an Index Seek but then it also had to do an RID LOOKUP to get the values for PricePercent, Cancel and ActualBillQty. Only if I create a cover index to include those fields does it become a complete index seek.

quote:
- you could post your indexes

tblCBillFlight
IDX_CBILLFLIGHT_FK_CUSTOMERID nonclustered located on PRIMARY FK_CustomerID
idx_tblCBillFlight_FK_KitchenID nonclustered located on PRIMARY FK_KitchenID
PK_tblCBillFlight nonclustered, unique, primary key located on PRIMARY CBillFlight_PK

tblCBillFlightInfo
IDX_tblCBillFlightInfo_FK_CBillFlight nonclustered located on PRIMARY FK_CBillFlight
PK_CBillFlightInfo nonclustered, unique, primary key located on PRIMARY CBillFlightInfo_PK

tblCBillClass
IDX_tblCBillClass_FK_CBillFlightInfo nonclustered located on PRIMARY FK_CBillFlightInfo
PK_tblCBillClass nonclustered, unique, primary key located on PRIMARY CBillClass_PK

tblCBillCodes
IDX_CBILLCODES_FK_BILLCODE nonclustered located on PRIMARY FK_BillCode
IDX_CBILLCODES_FK_CBILLCLASS nonclustered located on PRIMARY FK_CBillClass
PK_tblCBillCodes nonclustered, unique, primary key located on PRIMARY CBillCodes_PK

tblBillCodes
IDX_BILLCODES_FFGBILLCODE nonclustered located on PRIMARY FFGBillCode
PK_tblBillCodes nonclustered, unique, primary key located on PRIMARY BillCode_PK

tblBillCodeManMinutes
IDX_BILLCODEMANMINUTES_FK_BILLCODE nonclustered located on PRIMARY FK_BillCode
PK_tbl_BillCodeManMinutes nonclustered, unique, primary key located on PRIMARY BillCodeManMinutes_PK

quote:
- you could run this query repeatedly while your query is executing, it shows your buffer cache, if your numbers go up and down while the query is executing, could be your cache is too small:

No the numbers do not jump around.

Maldred
Starting Member

28 Posts

Posted - 2010-12-14 : 18:49:17

Sorry the below is not a good solution because it causes other problems in my Stored Procedure. Just leaving the post here in case it gives anyone any new idea of what might be wrong.

If I put the Cancel and PricePercent fields that are coming from tblCBillCodes in a CASE statement it reduces the query to 15 seconds. I still think that is running too slow. If anyone has any other suggests I would greatly appreciate it. Below is the updated query.


SELECT 
	CASE 
		WHEN CBCO.Cancel <> 1 THEN 0
		WHEN CBCO.PricePercent <> 1 THEN 0
		ELSE 1
	END isFullPrice, 
	BC.FFGBillCode, 
	BC.PriceType, 
	MIN(BC.EffectiveDate), 
	MAX(BC.EffectiveDate),
	BC.BreakDownCode, 
	SUM(CBCO.ActualBillQty), 
	BC.Price, 
	BC.Cost,
	BCMM.RevenueManMinutes,
	BCMM.FK_Department
FROM tblCBillFlight CBF
	INNER JOIN tblCBillFlightInfo CBFI ON CBF.CBillFlight_PK = CBFI.FK_CBillFlight
	INNER JOIN tblCBillClass CBC ON CBFI.CBillFlightInfo_PK = CBC.FK_CBillFlightInfo
	INNER JOIN tblCBillCodes CBCO ON CBC.CBillClass_PK = CBCO.FK_CBillClass
	INNER JOIN tblBillCodes BC ON CBCO.FK_BillCode = BC.BillCode_PK
	INNER JOIN tblBillCodeManMinutes BCMM ON BC.BillCode_PK = BCMM.FK_BillCode
	WHERE BC.FK_KitchenID =  @FK_KitchenID
	AND BC.FK_CustomerID = @FK_CustomerID
	AND CBFI.FlightDate BETWEEN @BeginDate AND @EndDate
	GROUP BY 
	BC.FFGBillCode, 
	BC.PriceType, 
	BC.BreakDownCode, 
	BC.Price, 
	BC.Cost,
	BCMM.RevenueManMinutes, 
	BCMM.FK_Department, 
	CASE 
		WHEN CBCO.Cancel <> 1 THEN 0
		WHEN CBCO.PricePercent <> 1 THEN 0
		ELSE 1
	END

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-12-15 : 03:37:01

I'd be looking to get some/all of these into some covering indexes...i.e add a new index/expand an existing one to include these columns. these conditions would be forcing "scans" of your tables.

BC.FK_KitchenID
BC.FK_CustomerID
CBFI.FlightDate

Maldred
Starting Member

28 Posts

Posted - 2010-12-15 : 10:18:35

quote:
Originally posted by AndrewMurphy

I'd be looking to get some/all of these into some covering indexes...i.e add a new index/expand an existing one to include these columns. these conditions would be forcing "scans" of your tables.

BC.FK_KitchenID
BC.FK_CustomerID
CBFI.FlightDate

Yea I tried putting them into covering indexes and it did not help. It actually caused it to increase in duration. :(

The only thing I have not tried yet is including Price, Cost, BreakdownCode, FFGBillCode, PriceType and EffectiveDate into a covering index. I will try that next and see what I get. Will post my results.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources