Query Performance [Resolved] - 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)

Query Performance [Resolved]

Author

Topic

Maldred
Starting Member

28 Posts

Posted - 2010-09-20 : 14:06:07

I need some help on the performance of one of my queries. I'm not sure why it's running so slow. The query below is taking about 10 seconds to run.


SELECT TCA.CBillClass_PK,
			BCC.FK_ReferenceInfo,
			TBC.BreakdownCode,
			TBC.BillCode_PK,
			TBC.AccountCode, 
			BCC.FK_ServiceCode,
			SC.Code AS ServiceCode,
			/*If there is no Group Code in the Reference Calling record then
			use the Group Code that was setup with the Bill Code within the Reference.*/
			CASE 
				WHEN ISNULL(FCR.FK_GroupCode,-1) = -1 THEN BCC.FK_GroupCode
				WHEN ISNULL(FCR.FK_GroupCode,-1) <> -1 THEN FCR.FK_GroupCode
			END AS FK_GroupCode,
			CASE 
				WHEN FCR.OriginCode = @ORIGINCODE_AIRCRAFT THEN '9000' + FCR.SequenceNumber + BCC.SequenceNumber
				WHEN FCR.OriginCode <> @ORIGINCODE_AIRCRAFT THEN FCR.SequenceNumber + '000' + BCC.SequenceNumber
			END AS SequenceNumber,
			BCC.PriceDescription,
			BCC.SalvageFlag,
			BCC.FK_CalcCode,
			BCC.Ratio,
			BCC.ClassCombo,
			BCC.AddCode,
			BCC.AddQuantity,
			BCC.Pack,
			BCC.Minimum,
			BCC.Maximum,
			BCC.BoardChart,
			CASE 
				WHEN FCR.CountFlag = '' THEN BCC.CountFlag
				WHEN FCR.CountFlag <> '' THEN FCR.CountFlag
			END AS CountFlag,
			BCC.SpecialMeal, 
			BCC.UseFlag,
			BCC.Flag1,
			BCC.Flag2,
			CASE 
				WHEN FCR.PricePercent = '' THEN BCC.PricePercent
				WHEN FCR.PricePercent <> '' THEN FCR.PricePercent
			END AS PricePercent
		FROM @TableClassAdjustments TCA
		INNER JOIN tblFinalCountReferences FCR ON TCA.CBillClass_PK = FCR.FK_CBillClass 
		INNER JOIN tblBillCodeCalculation BCC ON FCR.FK_ReferenceInfo = BCC.FK_ReferenceInfo
		INNER JOIN @TableBillCodes TBC ON BCC.BillCode = TBC.BillCode
		AND BCC.PriceType = TBC.PriceType
		LEFT OUTER JOIN tblServiceCode SC ON BCC.FK_ServiceCode = SC.ServiceCode_PK
		WHERE BCC.BillCodeEffectiveDate <= @FlightDate 
		AND (BCC.BillCodeThruDate >= @FlightDate
		OR BCC.BillCodeThruDate IS NULL)
		AND FCR.AdjustmentCode <> 'D'
		AND ISNULL(BCC.FK_CalcCode, -1) <> @N_CalcCode_PK
		AND ISNULL(BCC.FK_CalcCode, -1) <> @Memo_CalcCode_PK
		/*Put an order by on the bill codes to be by the class. This will help
		avoid a lot of looping within the application when finding the current
		class the Bill Codes are on.*/
		ORDER BY TCA.CBillClass_PK

Originally when I had this query setup without the table tblServiceCodes in the joins it would take less than 1 second to run. The minute I added the table it started taking 10 seconds to run. The table itself has only 165 records and the join is being done on a primary key to foreign key relationship.

Also if I remove 'SC.Code AS ServiceCode' from the select list but leave the join it runs under 1 second. Only when a field from the table tblServiceCode is include in the select list does the query run slow.

This doesn't happen only with the table tblServiceCode. It occurs with any table I add to the query. So it's not directly related to tblServiceCode.

If anyone can help me I would greatly appreciate it.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 14:39:05

you will need to post the ddl of the tables

Along with indexes and keys

But besides the other things I can comment on..this

quote:

AND ISNULL(BCC.FK_CalcCode, -1) <> @N_CalcCode_PK
AND ISNULL(BCC.FK_CalcCode, -1) <> @Memo_CalcCode_PK

Is non sargable and will cause a scan

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

Maldred
Starting Member

28 Posts

Posted - 2010-09-20 : 14:57:24

I understand on what you mean by non sargable but not always does it actually cause a table scan and in this instance it isn't doing that. It using an index scan. So not worried about that.

Thanks for taking a look at it Brett!!

Maldred
Starting Member

28 Posts

Posted - 2010-09-20 : 15:21:57

Sorry actually I realized I removed the field from the select and that's why it was working fine. So false alarm my query is still running slow.

Below is the DDL of each table involved in the query.

The table variable @TableClassAdjustments is loaded using an XML string.

The table variable @TableBillCodes is loaded from the table tblBillCodes.


DECLARE @TableClassAdjustments TABLE(CBillClass_PK BIGINT,
		FK_Class INT,
		FK_FlightClass BIGINT,
		FK_EquipmentType BIGINT,
		Cycle VARCHAR(2),
		FlightNumber VARCHAR(8),
		FlightSeq VARCHAR(1),
		Segment VARCHAR(2),
		DayCode VARCHAR(1),
		ACDayCode VARCHAR(1))


DECLARE @TableBillCodes TABLE(BillCode_PK BIGINT,
		BillCode VARCHAR(15),
		PriceType VARCHAR(1),
		BreakdownCode VARCHAR(1),
		AccountCode VARCHAR(2))




USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblFinalCountReferences]    Script Date: 09/20/2010 14:20:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblFinalCountReferences](
	[FinalCountReferences_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_CBillClass] [bigint] NOT NULL,
	[FK_ReferenceInfo] [bigint] NOT NULL,
	[FK_GroupCode] [int] NULL,
	[AdjustmentCode] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SequenceNumber] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SalvageFlag] [bit] NOT NULL,
	[Cycle] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CountFlag] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PricePercent] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[OriginCode] [tinyint] NULL,
	[CreateDate] [datetime] NOT NULL,
	[ModifyDate] [datetime] NOT NULL,
	[Username] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_TblFinalCountReferences] PRIMARY KEY CLUSTERED 
(
	[FinalCountReferences_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].[tblFinalCountReferences]  WITH CHECK ADD  CONSTRAINT [FK_TblFinalCountReferences_tblCBillClass] FOREIGN KEY([FK_CBillClass])
REFERENCES [dbo].[tblCBillClass] ([CBillClass_PK])
GO
ALTER TABLE [dbo].[tblFinalCountReferences] CHECK CONSTRAINT [FK_TblFinalCountReferences_tblCBillClass]
GO
ALTER TABLE [dbo].[tblFinalCountReferences]  WITH CHECK ADD  CONSTRAINT [FK_TblFinalCountReferences_tblGroupCode] FOREIGN KEY([FK_GroupCode])
REFERENCES [dbo].[tblGroupCode] ([GroupCode_PK])
GO
ALTER TABLE [dbo].[tblFinalCountReferences] CHECK CONSTRAINT [FK_TblFinalCountReferences_tblGroupCode]
GO
ALTER TABLE [dbo].[tblFinalCountReferences]  WITH CHECK ADD  CONSTRAINT [FK_TblFinalCountReferences_tblReferenceInfo] FOREIGN KEY([FK_ReferenceInfo])
REFERENCES [dbo].[tblReferenceInfo] ([ReferenceInfo_PK])
GO
ALTER TABLE [dbo].[tblFinalCountReferences] CHECK CONSTRAINT [FK_TblFinalCountReferences_tblReferenceInfo]




USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblBillCodeCalculation]    Script Date: 09/20/2010 14:20:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblBillCodeCalculation](
	[BillCodeCalculation_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_GroupCode] [int] NULL,
	[FK_ReferenceInfo] [bigint] NULL,
	[FK_CalcCode] [smallint] NULL,
	[FK_ServiceCode] [int] NULL,
	[SequenceNumber] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tblBillCodeCalculation_SequenceNumber]  DEFAULT ((0)),
	[BillCodeEffectiveDate] [datetime] NOT NULL,
	[BillCodeThruDate] [datetime] NULL CONSTRAINT [DF_tblBillCodeCalculation_BillCodeThruDate]  DEFAULT (NULL),
	[BillCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PriceType] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PriceDescription] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SalvageFlag] [bit] NOT NULL,
	[Cycle] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PaxRatio] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Ratio] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ClassCombo] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AddCode] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AddQuantity] [int] NOT NULL,
	[Pack] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Minimum] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Maximum] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[BoardChart] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CountFlag] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SpecialMeal] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[UseFlag] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Flag1] [smallint] NOT NULL,
	[Flag2] [smallint] NOT NULL,
	[isCopy] [bit] NOT NULL CONSTRAINT [DF_tblBillCodeCalculation_IsCopy]  DEFAULT ((0)),
	[isCopyInProgress] [bit] NULL,
	[PricePercent] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_tblBillCodeCalculation_UpdateDate]  DEFAULT (getdate()),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_tblBillCodeCalculation_CreateDate]  DEFAULT (getdate()),
	[UserName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_tblBillCodeCalculation] PRIMARY KEY NONCLUSTERED 
(
	[BillCodeCalculation_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].[tblBillCodeCalculation]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodeCalculation_tblBillCodeCalculation] FOREIGN KEY([FK_ServiceCode])
REFERENCES [dbo].[tblServiceCode] ([ServiceCode_PK])
GO
ALTER TABLE [dbo].[tblBillCodeCalculation] CHECK CONSTRAINT [FK_tblBillCodeCalculation_tblBillCodeCalculation]
GO
ALTER TABLE [dbo].[tblBillCodeCalculation]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodeCalculation_tblCalcCode] FOREIGN KEY([FK_CalcCode])
REFERENCES [dbo].[tblCalcCode] ([CalcCode_PK])
GO
ALTER TABLE [dbo].[tblBillCodeCalculation] CHECK CONSTRAINT [FK_tblBillCodeCalculation_tblCalcCode]
GO
ALTER TABLE [dbo].[tblBillCodeCalculation]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodeCalculation_tblReferenceInfo] FOREIGN KEY([FK_ReferenceInfo])
REFERENCES [dbo].[tblReferenceInfo] ([ReferenceInfo_PK])
GO
ALTER TABLE [dbo].[tblBillCodeCalculation] CHECK CONSTRAINT [FK_tblBillCodeCalculation_tblReferenceInfo]
GO
ALTER TABLE [dbo].[tblBillCodeCalculation]  WITH CHECK ADD  CONSTRAINT [ReftblGroupCode1371] FOREIGN KEY([FK_GroupCode])
REFERENCES [dbo].[tblGroupCode] ([GroupCode_PK])
GO
ALTER TABLE [dbo].[tblBillCodeCalculation] CHECK CONSTRAINT [ReftblGroupCode1371]


USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblServiceCode]    Script Date: 09/20/2010 14:21:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblServiceCode](
	[ServiceCode_PK] [int] IDENTITY(1,1) NOT NULL,
	[FK_KitchenID] [smallint] NULL,
	[FK_CustomerID] [smallint] NULL,
	[Code] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_tblServiceCode] PRIMARY KEY NONCLUSTERED 
(
	[ServiceCode_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].[tblServiceCode]  WITH CHECK ADD  CONSTRAINT [FK_tblServiceCode_tblCompanyID] FOREIGN KEY([FK_KitchenID])
REFERENCES [dbo].[tblCompanyID] ([KitchenID_PK])
GO
ALTER TABLE [dbo].[tblServiceCode] CHECK CONSTRAINT [FK_tblServiceCode_tblCompanyID]
GO
ALTER TABLE [dbo].[tblServiceCode]  WITH CHECK ADD  CONSTRAINT [FK_tblServiceCode_tblCustomerID] FOREIGN KEY([FK_CustomerID])
REFERENCES [dbo].[tblCustomerID] ([CustomerID_PK])
GO
ALTER TABLE [dbo].[tblServiceCode] CHECK CONSTRAINT [FK_tblServiceCode_tblCustomerID]

Maldred
Starting Member

28 Posts

Posted - 2010-09-20 : 15:27:53

Just too add a little more detail to my issue. I have a similar query that uses the same tables except instead of tblFinalCountReferences to join to tblBillCodeCalculation it uses tblFlightClass_X_BillCodeCalc (Green Text). This query runs perfectly fine. Below is the query and also the DDL to the table tblFlightClass_X_BillCodeCalc.


SELECT TCA.CBillClass_PK,
			TBC.BillCode_PK,
			TBC.BreakdownCode,
			TBC.AccountCode,
			BCC.FK_ServiceCode,
			SC.Code AS ServiceCode,
			BCC.FK_GroupCode,
			BCC.SequenceNumber + '000000' AS SequenceNumber,
			BCC.PriceDescription,
			BCC.SalvageFlag,
			BCC.FK_CalcCode,
			BCC.Ratio,
			BCC.ClassCombo,
			BCC.AddCode,
			BCC.AddQuantity,
			BCC.Pack,
			BCC.Minimum,
			BCC.Maximum,
			BCC.BoardChart,	
			BCC.CountFlag,
			BCC.SpecialMeal, 
			BCC.UseFlag,
			BCC.Flag1,
			BCC.Flag2,
			BCC.PricePercent
		FROM @TableClassAdjustments TCA
		INNER JOIN tblFlightClass_X_BillCodeCalc FCxBC ON TCA.FK_FlightClass = FCxBC.FK_FlightClass
		AND FCxBC.BCFrequency LIKE '%' + TCA.DayCode + '%'
		INNER JOIN tblBillCodeCalculation BCC ON FCxBC.FK_BillCodeCalculation = BCC.BillCodeCalculation_PK
		AND (BCC.Cycle = TCA.Cycle
		OR BCC.Cycle = '')
		INNER JOIN @TableBillCodes TBC ON BCC.BillCode = TBC.BillCode
		AND BCC.PriceType = TBC.PriceType
		LEFT OUTER JOIN tblServiceCode SC ON BCC.FK_ServiceCode = SC.ServiceCode_PK
		WHERE BCC.BillCodeEffectiveDate <= @FlightDate 
		AND (BCC.BillCodeThruDate >= @FlightDate
		OR BCC.BillCodeThruDate IS NULL)
		AND ISNULL(BCC.FK_CalcCode, -1) <> @N_CalcCode_PK
		AND ISNULL(BCC.FK_CalcCode, -1) <> @Memo_CalcCode_PK
		/*Put an order by on the bill codes to be by the class. This will help
		avoid a lot of looping within the application when finding the current
		class the Bill Codes are on.*/
		ORDER BY TCA.CBillClass_PK






USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblFlightClass_X_BillCodeCalc]    Script Date: 09/20/2010 14:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblFlightClass_X_BillCodeCalc](
	[FlightClass_X_BillCodeCalc_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_FlightClass] [bigint] NOT NULL,
	[FK_BillCodeCalculation] [bigint] NOT NULL,
	[BCFrequency] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_tblFlightClass_X] PRIMARY KEY NONCLUSTERED 
(
	[FlightClass_X_BillCodeCalc_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].[tblFlightClass_X_BillCodeCalc]  WITH CHECK ADD  CONSTRAINT [FK_tblFClass_X_BillCodeCalc_tblBillCodeCalculation] FOREIGN KEY([FK_BillCodeCalculation])
REFERENCES [dbo].[tblBillCodeCalculation] ([BillCodeCalculation_PK])
GO
ALTER TABLE [dbo].[tblFlightClass_X_BillCodeCalc] CHECK CONSTRAINT [FK_tblFClass_X_BillCodeCalc_tblBillCodeCalculation]
GO
ALTER TABLE [dbo].[tblFlightClass_X_BillCodeCalc]  WITH CHECK ADD  CONSTRAINT [FK_tblFlightClass_X_tblFlightClass] FOREIGN KEY([FK_FlightClass])
REFERENCES [dbo].[tblFlightClass] ([FlightClass_PK])
GO
ALTER TABLE [dbo].[tblFlightClass_X_BillCodeCalc] CHECK CONSTRAINT [FK_tblFlightClass_X_tblFlightClass]

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 15:51:56

quote:
Originally posted by Maldred

I understand on what you mean by non sargable but not always does it actually cause a table scan and in this instance it isn't doing that. It using an index scan. So not worried about that.

Did I say table scan? And you're not worried about that?

quote:

I did find my problem though. My join between tblBillCodeCalculation and tblFinalCountReferences was a foreign key to foreign key join. Both foreign keys point to the same primary key, but it wasn't using an index to do the search. I put the table with the Primary Key inbetween the join to the two tables and now it's working.

Well at least you got that part straightened out

Just remove the ISNULL (should be COALESCE) since the absence of anything is going to be outside your result set (i.e. FALSE) I don't know why you would need to encourage a scan for no reason

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

Maldred
Starting Member

28 Posts

Posted - 2010-09-20 : 16:40:50

quote:
Originally posted by X002548

quote:
Originally posted by Maldred

I understand on what you mean by non sargable but not always does it actually cause a table scan and in this instance it isn't doing that. It using an index scan. So not worried about that.

Did I say table scan? And you're not worried about that?

Sorry, it's using an index scan. So not worried about it.

quote:
Originally posted by X002548

quote:
Originally posted by Maldred

[quote]
I did find my problem though. My join between tblBillCodeCalculation and tblFinalCountReferences was a foreign key to foreign key join. Both foreign keys point to the same primary key, but it wasn't using an index to do the search. I put the table with the Primary Key inbetween the join to the two tables and now it's working.

Well at least you got that part straightened out

Just remove the ISNULL (should be COALESCE) since the absence of anything is going to be outside your result set (i.e. FALSE) I don't know why you would need to encourage a scan for no reason

If you mean to remove the ISNULL on the BCC.FK_CalcCode. I can't. I need it to grab records that have a NULL in that field.

And sorry no it was a false alarm. My issue is still not fixed. The query still runs slow.

Maldred
Starting Member

28 Posts

Posted - 2010-09-20 : 17:38:00

Ok I resolved it. I put an index on the foreign key FK_ReferenceInfo.

For some reason I thought that since the primary key was indexed it would work without a Table Scan. I guess that's where I was wrong. Shows my lack of knowledge in SQL.

Thanks for the help Brett.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-20 : 18:05:53

Why aren't you concerned about an index scan?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 18:55:50

I'd have to log on...but are you saying

that if you say

Col <> 'x'

That you won't get 'a', 'b', 'c' and null?

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

Maldred
Starting Member

28 Posts

Posted - 2010-09-21 : 09:40:37

quote:
Originally posted by X002548

I'd have to log on...but are you saying

that if you say

Col <> 'x'

That you won't get 'a', 'b', 'c' and null?

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

That is correct. Records with a NULL will not be selected. A NULL value is compared differently. Same reason you can't put Col = NULL, you have to put Col IS NULL.

Maldred
Starting Member

28 Posts

Posted - 2010-09-21 : 09:54:14

quote:
Originally posted by tkizer

Why aren't you concerned about an index scan?

Tara Kizer

Well not concerned because the scan is on a small number of records. For the most part under 100 because the joins themselves limit the number of records being brought in. To avoid an index scan I would have to put an index on all the fields I use in my WHERE clause. The query already performs under a second, no reason to put indexes on it to have it perform better. This would just cause any INSERT on the table to take a hit because of all the indexes. A lot of the tables see a lot of transactions within my application so I'm careful on when and where to add an index.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 10:37:06

ok...good understanding..but I'm not so sure you couldn't modify an existing index

If you post your DDL with contraints and indexes it would help us

HOWEVER...I don't think anyoe mentioned adding an index

And if you do this


CREATE TABLE myTable99(Col1 int, Col2 char(1))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1,'x' UNION ALL
SELECT 2,'y' UNION ALL
SELECT 3,'z' UNION ALL
SELECT 4,null

SELECT * FROM myTable99
WHERE Col2 <> 'y' OR Col2 IS NULL
GO

DROP TABLE myTable99
GO

You would elimiate the scan

And you should always worry about scans..NEVER go on an interview and say what you said

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Maldred
Starting Member

28 Posts

Posted - 2010-09-21 : 11:30:11

quote:
Originally posted by X002548

ok...good understanding..but I'm not so sure you couldn't modify an existing index
If you post your DDL with contraints and indexes it would help us

Below is the table tblBillCodeCalculation where the nullable foreign key FK_CalcCode exists.

Then the table tblCalcCode where the Primary key exists.


USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblBillCodeCalculation]    Script Date: 09/21/2010 10:28:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblBillCodeCalculation](
	[BillCodeCalculation_PK] [bigint] IDENTITY(1,1) NOT NULL,
	[FK_GroupCode] [int] NULL,
	[FK_ReferenceInfo] [bigint] NULL,
	[FK_CalcCode] [smallint] NULL,
	[FK_ServiceCode] [int] NULL,
	[SequenceNumber] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tblBillCodeCalculation_SequenceNumber]  DEFAULT ((0)),
	[BillCodeEffectiveDate] [datetime] NOT NULL,
	[BillCodeThruDate] [datetime] NULL CONSTRAINT [DF_tblBillCodeCalculation_BillCodeThruDate]  DEFAULT (NULL),
	[BillCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PriceType] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PriceDescription] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SalvageFlag] [bit] NOT NULL,
	[Cycle] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PaxRatio] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Ratio] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ClassCombo] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AddCode] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AddQuantity] [int] NOT NULL,
	[Pack] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Minimum] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Maximum] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[BoardChart] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CountFlag] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SpecialMeal] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[UseFlag] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Flag1] [smallint] NOT NULL,
	[Flag2] [smallint] NOT NULL,
	[isCopy] [bit] NOT NULL CONSTRAINT [DF_tblBillCodeCalculation_IsCopy]  DEFAULT ((0)),
	[isCopyInProgress] [bit] NULL,
	[PricePercent] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_tblBillCodeCalculation_UpdateDate]  DEFAULT (getdate()),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_tblBillCodeCalculation_CreateDate]  DEFAULT (getdate()),
	[UserName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_tblBillCodeCalculation] PRIMARY KEY NONCLUSTERED 
(
	[BillCodeCalculation_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].[tblBillCodeCalculation]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodeCalculation_tblBillCodeCalculation] FOREIGN KEY([FK_ServiceCode])
REFERENCES [dbo].[tblServiceCode] ([ServiceCode_PK])
GO
ALTER TABLE [dbo].[tblBillCodeCalculation] CHECK CONSTRAINT [FK_tblBillCodeCalculation_tblBillCodeCalculation]
GO
ALTER TABLE [dbo].[tblBillCodeCalculation]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodeCalculation_tblCalcCode] FOREIGN KEY([FK_CalcCode])
REFERENCES [dbo].[tblCalcCode] ([CalcCode_PK])
GO
ALTER TABLE [dbo].[tblBillCodeCalculation] CHECK CONSTRAINT [FK_tblBillCodeCalculation_tblCalcCode]
GO
ALTER TABLE [dbo].[tblBillCodeCalculation]  WITH CHECK ADD  CONSTRAINT [FK_tblBillCodeCalculation_tblReferenceInfo] FOREIGN KEY([FK_ReferenceInfo])
REFERENCES [dbo].[tblReferenceInfo] ([ReferenceInfo_PK])
GO
ALTER TABLE [dbo].[tblBillCodeCalculation] CHECK CONSTRAINT [FK_tblBillCodeCalculation_tblReferenceInfo]
GO
ALTER TABLE [dbo].[tblBillCodeCalculation]  WITH CHECK ADD  CONSTRAINT [ReftblGroupCode1371] FOREIGN KEY([FK_GroupCode])
REFERENCES [dbo].[tblGroupCode] ([GroupCode_PK])
GO
ALTER TABLE [dbo].[tblBillCodeCalculation] CHECK CONSTRAINT [ReftblGroupCode1371]




USE [FAIRSTEST]
GO
/****** Object:  Table [dbo].[tblCalcCode]    Script Date: 09/21/2010 10:30:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCalcCode](
	[CalcCode_PK] [smallint] IDENTITY(1,1) NOT NULL,
	[Code] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_tblCalcCode] PRIMARY KEY NONCLUSTERED 
(
	[CalcCode_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

quote:

You would elimiate the scan

Wouldn't that only eliminate the scan if there was an index for SQL to use in the first place?

Also since I fixed my issue with the query running slow, I now changed the query a little and included tblCalcCode in my joins with a LEFT OUTER JOIN. So now I'm using Code instead of FK_CalcCode in my WHERE clause. It still makes use of the ISNULL though. So if you can provide an alternative solution to that I would appreciate it. I know you posted an alternative already above, but as mentioned it would still not eliminate the scan as there is no Index on Code. So it would still do a scan.


SELECT TCA.CBillClass_PK,
			BCC.FK_ReferenceInfo,
			TBC.BreakdownCode,
			TBC.BillCode_PK,
			TBC.AccountCode, 
			BCC.FK_ServiceCode,
			SC.Code AS ServiceCode,
			/*If there is no Group Code in the Reference Calling record then
			use the Group Code that was setup with the Bill Code within the Reference.*/
			CASE 
				WHEN ISNULL(FCR.FK_GroupCode,-1) = -1 THEN BCC.FK_GroupCode
				WHEN ISNULL(FCR.FK_GroupCode,-1) <> -1 THEN FCR.FK_GroupCode
			END AS FK_GroupCode,
			CASE 
				WHEN FCR.OriginCode = @ORIGINCODE_AIRCRAFT THEN '9000' + FCR.SequenceNumber + BCC.SequenceNumber
				WHEN FCR.OriginCode <> @ORIGINCODE_AIRCRAFT THEN FCR.SequenceNumber + '000' + BCC.SequenceNumber
			END AS SequenceNumber,
			BCC.PriceDescription,
			BCC.SalvageFlag,
			CC.Code AS CalcCode,
			BCC.Ratio,
			BCC.ClassCombo,
			BCC.AddCode,
			BCC.AddQuantity,
			BCC.Pack,
			BCC.Minimum,
			BCC.Maximum,
			BCC.BoardChart,
			CASE 
				WHEN FCR.CountFlag = '' THEN BCC.CountFlag
				WHEN FCR.CountFlag <> '' THEN FCR.CountFlag
			END AS CountFlag,
			BCC.SpecialMeal, 
			BCC.UseFlag,
			BCC.Flag1,
			BCC.Flag2,
			CASE 
				WHEN FCR.PricePercent = '' THEN BCC.PricePercent
				WHEN FCR.PricePercent <> '' THEN FCR.PricePercent
			END AS PricePercent
		FROM @TableClassAdjustments TCA
		INNER JOIN tblFinalCountReferences FCR ON TCA.CBillClass_PK = FCR.FK_CBillClass 
		INNER JOIN tblBillCodeCalculation BCC ON FCR.FK_ReferenceInfo = BCC.FK_ReferenceInfo
		INNER JOIN @TableBillCodes TBC ON BCC.BillCode = TBC.BillCode
		AND BCC.PriceType = TBC.PriceType
		LEFT OUTER JOIN tblServiceCode SC ON BCC.FK_ServiceCode = SC.ServiceCode_PK
		LEFT OUTER JOIN tblCalcCode CC ON BCC.FK_CalcCode = CC.CalcCode_PK
		WHERE BCC.BillCodeEffectiveDate <= @FlightDate 
		AND (BCC.BillCodeThruDate >= @FlightDate
		OR BCC.BillCodeThruDate IS NULL)
		AND FCR.AdjustmentCode <> 'D'
		AND ISNULL(CC.Code, '') <> 'N'
		AND ISNULL(CC.Code, '') <> '*'
		/*Put an order by on the bill codes to be by the class. This will help
		avoid a lot of looping within the application when finding the current
		class the Bill Codes are on.*/
		ORDER BY TCA.CBillClass_PK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources