wattkey
asked on
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 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
Please try this,
It seems tblProducts.ProductDesc, has very large data.
SELECT distinct
tblInboundPaper.BookingID,
FROM tblInboundPaper
inner join tblProducts
ON tblProducts.ProductID = tblInboundPaper.ProductID
INNER JOIN tblInboundPaperRolls
ON tblInboundPaper.BookingID = tblInboundPaperRolls.Booki
INNER JOIN tblOutboundPaperRolls
ON tblInboundPaperRolls.Inbou
WHERE tblInboundPaper.CustomerID
ORDER BY tblInboundPaper.BookingNum
It seems tblProducts.ProductDesc, has very large data.
SELECT distinct
tblInboundPaper.BookingID,
FROM tblInboundPaper
inner join tblProducts
ON tblProducts.ProductID = tblInboundPaper.ProductID
INNER JOIN tblInboundPaperRolls
ON tblInboundPaper.BookingID = tblInboundPaperRolls.Booki
INNER JOIN tblOutboundPaperRolls
ON tblInboundPaperRolls.Inbou
WHERE tblInboundPaper.CustomerID
ORDER BY tblInboundPaper.BookingNum
ASKER
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
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
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.
I know it's kind of radical, and you are only selecting, but... who knows.
ASKER
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.
>>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.
It will. For a day or two and then you will be back to the same problem.
ASKER
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]
Any additional indexes on these tables?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am going to add the extra indexes, these should be non-clustered indexes?
Preferably non-clustered.
Check out this article:
http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-clustered-vs-nonclustered-indexes-whats-the-difference/
Bye
Check out this article:
http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-clustered-vs-nonclustered-indexes-whats-the-difference/
Bye
>>these should be non-clustered indexes? <<
They have to be non-clustered. You already have a clustered index on each table.
They have to be non-clustered. You already have a clustered index on each table.
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.