We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

odd SQL query performance is causing issues

wattkey
wattkey asked
on
Medium Priority
170 Views
Last Modified: 2012-06-21
I have one particular query that takes over 1 minute to run. But other queries almost the exact same run in less than a 1 second. Its really confusing me and not sure how to speed this up.

This is running on MS SQL 2000.

I've tried re-indexing and reseting statistics.

 
-- This query takes over a mintue to run 
SELECT 
tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID
FROM tblInboundPaper 
inner join tblProducts
ON tblProducts.ProductID = tblInboundPaper.ProductID 
INNER JOIN tblInboundPaperRolls 
ON tblInboundPaper.BookingID = tblInboundPaperRolls.BookingID  
INNER JOIN tblOutboundPaperRolls 
ON tblInboundPaperRolls.InboundBOLID = tblOutboundPaperRolls.InboundBOLID 
WHERE tblInboundPaper.CustomerID = 336
GROUP BY tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID
ORDER BY tblInboundPaper.BookingNum  

-- This query takes less than a second 
-- This customer has many more records associated with it
SELECT 
tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID
FROM tblInboundPaper 
inner join tblProducts
ON tblProducts.ProductID = tblInboundPaper.ProductID 
INNER JOIN tblInboundPaperRolls 
ON tblInboundPaper.BookingID = tblInboundPaperRolls.BookingID  
INNER JOIN tblOutboundPaperRolls 
ON tblInboundPaperRolls.InboundBOLID = tblOutboundPaperRolls.InboundBOLID 
WHERE tblInboundPaper.CustomerID = 132
GROUP BY tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID
ORDER BY tblInboundPaper.BookingNum  

-- Comment out the customer criteria and it runs in less -- than a second

SELECT 
tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID
FROM tblInboundPaper 
inner join tblProducts
ON tblProducts.ProductID = tblInboundPaper.ProductID 
INNER JOIN tblInboundPaperRolls 
ON tblInboundPaper.BookingID = tblInboundPaperRolls.BookingID  
INNER JOIN tblOutboundPaperRolls 
ON tblInboundPaperRolls.InboundBOLID = tblOutboundPaperRolls.InboundBOLID 
--WHERE tblInboundPaper.CustomerID = 336
GROUP BY tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID
ORDER BY tblInboundPaper.BookingNum  

-- comment out the group by it runs in less than a second
SELECT 
tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID
FROM tblInboundPaper 
inner join tblProducts
ON tblProducts.ProductID = tblInboundPaper.ProductID 
INNER JOIN tblInboundPaperRolls 
ON tblInboundPaper.BookingID = tblInboundPaperRolls.BookingID  
INNER JOIN tblOutboundPaperRolls 
ON tblInboundPaperRolls.InboundBOLID = tblOutboundPaperRolls.InboundBOLID 
WHERE tblInboundPaper.CustomerID = 336
--GROUP BY tblInboundPaper.BookingID, --tblInboundPaper.BookingNum, tblProducts.ProductDesc, --tblInboundPaper.CustomerID
ORDER BY tblInboundPaper.BookingNum  


-- Wow this even works in 1 second until you un-comment out the where clause then 1 minutes plus
select 
* 
from
(
	SELECT 
	tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID
	FROM tblInboundPaper 
	inner join tblProducts
	ON tblProducts.ProductID = tblInboundPaper.ProductID 
	INNER JOIN tblInboundPaperRolls 
	ON tblInboundPaper.BookingID = tblInboundPaperRolls.BookingID  
	INNER JOIN tblOutboundPaperRolls 
	ON tblInboundPaperRolls.InboundBOLID = tblOutboundPaperRolls.InboundBOLID 
	GROUP BY tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID
	 
) as t
--where CustomerID = 336
ORDER BY BookingNum

Open in new window

Comment
Watch Question

There has to be and issue with tblInboundPaper. Check that CustomerID is an INT and that the main index is set up on that field.

Also, why do you GROUP By all fields, if you don't SUM, COUNT or anything? You may want to try using DISTINCT instead.

Greetings.
Imran Javed ZiaConsultant Software Engineer - .NET Architect
CERTIFIED EXPERT

Commented:
Please try this,
It seems  tblProducts.ProductDesc, has very large data.

SELECT   distinct
tblInboundPaper.BookingID, tblInboundPaper.BookingNum, tblProducts.ProductDesc, tblInboundPaper.CustomerID  
FROM tblInboundPaper  
inner join tblProducts  
ON tblProducts.ProductID = tblInboundPaper.ProductID  
INNER JOIN tblInboundPaperRolls  
ON tblInboundPaper.BookingID = tblInboundPaperRolls.BookingID    
INNER JOIN tblOutboundPaperRolls  
ON tblInboundPaperRolls.InboundBOLID = tblOutboundPaperRolls.InboundBOLID  
WHERE tblInboundPaper.CustomerID = 336  
ORDER BY tblInboundPaper.BookingNum

Author

Commented:
customerId is an INT

The main index (primary key) on tblInboundPaper is BookingId

distinct makes no performance difference over GROUP By

productDescr is a varchar(50) and there are 600 entries in the table, i don't think that is the problem
CERTIFIED EXPERT
Top Expert 2012

Commented:
For a minute, let's forget about the comparisons, I do not believe they are germaine. Please post the schema for all the tables including the indexes on each one.
Also, check that there are no current LOCKS on the database (If possible, restart the engine).

I know it's kind of radical, and you are only selecting, but... who knows.

Author

Commented:

The performance has degrade even more in the past few days. it now takes it over 3 minutes for that query to run, the others are still fine. I am going to restart the SQL server overnight and see if that makes a difference. If not I will post the schemas acperkins has asked for.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>I am going to restart the SQL server overnight and see if that makes a difference.<<
It will.  For a day or two and then you will be back to the same problem.

Author

Commented:
Here are the create statements to make the said tables

 
CREATE TABLE [dbo].[tblInboundPaper](
	[BookingID] [int] IDENTITY(1,1) NOT NULL,
	[BookingNum] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CustomerID] [int] NULL,
	[ProductID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[BookingID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]



GO
CREATE TABLE [dbo].[tblInboundPaperRolls](
	[InboundBOLID] [int] IDENTITY(1,1) NOT NULL,
	[BookingID] [int] NULL,
	[DateReceived] [smalldatetime] NULL,
	[InboundBOLNum] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[OrderNum] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[RollPrefix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[RollNum] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[InboundLoad] [float] NULL,
	[BillableLoad] [float] NULL,
	[OutboundLoad] [float] NULL,
	[Width] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Diameter] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[BasisWeight] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[InventoryFreeDays] [smallint] NULL,
PRIMARY KEY CLUSTERED 
(
	[InboundBOLID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]


GO
CREATE TABLE [dbo].[tblProducts](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[ProductTypeID] [int] NULL,
	[ProductDesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]


GO
CREATE TABLE [dbo].[tblOutboundPaperRolls](
	[OutboundBOLItemID] [int] IDENTITY(1,1) NOT NULL,
	[OutboundBOLID] [int] NULL,
	[InboundBOLID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[OutboundBOLItemID] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

Open in new window

CERTIFIED EXPERT
Top Expert 2012

Commented:
Any additional indexes on these tables?
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I am going to add the extra indexes, these should be non-clustered indexes?
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>these should be non-clustered indexes? <<
They have to be non-clustered.  You already have a clustered index on each table.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.