Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

odd SQL query performance is causing issues

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

0
wattkey
Asked:
wattkey
  • 4
  • 4
  • 4
  • +1
1 Solution
 
JuanCarnigliaCommented:
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.
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
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
0
 
wattkeyAuthor 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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
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.
0
 
JuanCarnigliaCommented:
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.
0
 
wattkeyAuthor 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.
0
 
Anthony PerkinsCommented:
>>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.
0
 
wattkeyAuthor 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

0
 
Anthony PerkinsCommented:
Any additional indexes on these tables?
0
 
JuanCarnigliaCommented:
Exactly. You are not using the indexes defined here, when running the query.

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

tblInboundPaper.ProductID - no index
tblInboundPaperRolls.BookingID  - no index
tblInboundPaper.CustomerID - no index
tblInboundPaper.CustomerID - no index

Maybe that's the problem.

0
 
wattkeyAuthor Commented:
I am going to add the extra indexes, these should be non-clustered indexes?
0
 
JuanCarnigliaCommented:
0
 
Anthony PerkinsCommented:
>>these should be non-clustered indexes? <<
They have to be non-clustered.  You already have a clustered index on each table.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now