SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Improving performance: 42 seconds down to under 5
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 07/29/2005 :  11:17:23  Show Profile  Reply with Quote
--I have a query. It currently takes 42 seconds. I need it to take 5 or less.

--1) DDL

CREATE TABLE [Companies] (
[COM_CompanyID] uniqueidentifier NOT NULL,
[COM_Name] varchar (50) NOT NULL
)

CREATE TABLE [CompanyDepartments] (
[DEP_DepartmentID] uniqueidentifier NOT NULL,
[DEP_CompanyID] uniqueidentifier NOT NULL,
[DEP_DepName] varchar (50) NOT NULL
)

CREATE TABLE [CompanyContacts] (
[CON_ContactID] uniqueidentifier NOT NULL,
[CON_DepartmentID] uniqueidentifier NOT NULL,
[CON_PersonID] uniqueidentifier NOT NULL,
[CON_Type] tinyint NOT NULL
)

CREATE TABLE [People] (
[PEO_PersonID] uniqueidentifier NOT NULL,
[PEO_Name] varchar (50) NOT NULL
)

CREATE TABLE [Products] (
[PRO_ProductID] uniqueidentifier NOT NULL,
[PRO_OwnerID] uniqueidentifier NOT NULL,
[PRO_BaseProductID] uniqueidentifier NOT NULL
)

CREATE TABLE [BaseProducts] (
[BAS_ProductID] uniqueidentifier NOT NULL
)

ALTER TABLE dbo.Companies ADD CONSTRAINT
DF_Companies_COM_CompanyID DEFAULT (newid()) FOR COM_CompanyID
GO
ALTER TABLE dbo.Companies ADD CONSTRAINT
DF_Companies_COM_Name DEFAULT ('') FOR COM_Name
GO

CREATE CLUSTERED INDEX IX_CompaniesName ON dbo.Companies
(
COM_Name
) ON [PRIMARY]
GO
ALTER TABLE dbo.Companies ADD CONSTRAINT
PK_Companies PRIMARY KEY NONCLUSTERED
(
COM_CompanyID
) ON [PRIMARY]

GO

ALTER TABLE dbo.CompanyDepartments ADD CONSTRAINT
DF_CompanyDepartments_DEP_DepartmentID DEFAULT (newid()) FOR DEP_DepartmentID
GO
ALTER TABLE dbo.CompanyDepartments ADD CONSTRAINT
DF_CompanyDepartments_DEP_DepName DEFAULT ('') FOR DEP_DepName
GO

CREATE CLUSTERED INDEX IX_CompanyDepartmentsCompanyID ON dbo.CompanyDepartments
(
DEP_CompanyID
) ON [PRIMARY]
GO
ALTER TABLE dbo.CompanyDepartments ADD CONSTRAINT
PK_CompanyDepartments PRIMARY KEY NONCLUSTERED
(
DEP_DepartmentID
) ON [PRIMARY]

GO

ALTER TABLE dbo.CompanyContacts ADD CONSTRAINT
DF_CompanyContacts_CON_ContactID DEFAULT (newid()) FOR CON_ContactID
GO
ALTER TABLE dbo.CompanyContacts ADD CONSTRAINT
DF_CompanyContacts_CON_ContactTypeEnum DEFAULT (254) FOR CON_Type
GO


CREATE CLUSTERED INDEX IX_CompanyContactsPersonID ON dbo.CompanyContacts
(
CON_PersonID
) ON [PRIMARY]
GO
ALTER TABLE dbo.CompanyContacts ADD CONSTRAINT
PK_CompanyContacts PRIMARY KEY NONCLUSTERED
(
CON_ContactID
) ON [PRIMARY]

GO

ALTER TABLE dbo.People ADD CONSTRAINT
DF_People_PEO_PersonID DEFAULT (newid()) FOR PEO_PersonID
GO
ALTER TABLE dbo.People ADD CONSTRAINT
DF_People_PEO_Name DEFAULT ('') FOR PEO_Name
GO

CREATE CLUSTERED INDEX IX_PeopleName ON dbo.People
(
PEO_Name
) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE dbo.People ADD CONSTRAINT
PK_People PRIMARY KEY NONCLUSTERED
(
PEO_PersonID
) ON [PRIMARY]

GO

ALTER TABLE dbo.Products ADD CONSTRAINT
DF_Products_PRO_ProductID DEFAULT (newid()) FOR PRO_ProductID
GO

CREATE CLUSTERED INDEX IX_ProductsBaseProductID ON dbo.Products
(
PRO_BaseProductID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Products ADD CONSTRAINT
PK_Products PRIMARY KEY NONCLUSTERED
(
PRO_ProductID
) ON [PRIMARY]

GO

--2) Sample Data DML
INSERT INTO Companies (COM_Name) VALUES ('MyCompany')

INSERT INTO CompanyDepartments (DEP_DepName, DEP_CompanyID)
SELECT 'MyDep', (SELECT COM_CompanyID FROM Companies)

INSERT INTO People (PEO_Name) VALUES ('MyPerson')

INSERT INTO CompanyContacts (CON_DepartmentID, CON_PersonID)
SELECT (SELECT DEP_DepartmentID FROM CompanyDepartments), (SELECT PEO_PersonID FROM People)

INSERT INTO BaseProducts (BAS_ProductID) VALUES (NEWID())

INSERT INTO Products (PRO_OwnerID, PRO_BaseProductID)
SELECT (SELECT CON_ContactID FROM CompanyContacts), (SELECT BAS_ProductID FROM BaseProducts)

INSERT INTO Products (PRO_OwnerID, PRO_BaseProductID)
SELECT (SELECT TOP 1 COM_CompanyID FROM Companies), (SELECT TOP 1 BAS_ProductID FROM BaseProducts)

/*
In the live DB, these are the real record counts:
Companies 62K
CompanyDepartments 104K
People 590K
CompanyContacts 285K
BaseProducts 600K
Products 300K
*/

--3) Attempt DML

--populate test parameter
DECLARE @CompanyID UNIQUEIDENTIFIER
SET @CompanyID = (SELECT COM_CompanyID FROM Companies)

SELECT
PRO_ProductID,
BAS_ProductID


FROM CompanyDepartments
INNER JOIN CompanyContacts ON CompanyDepartments.DEP_DepartmentID = CompanyContacts.CON_DepartmentID
INNER JOIN Products ON CompanyContacts.CON_ContactID = Products.PRO_OwnerID
INNER JOIN BaseProducts ON Products.PRO_BaseProductID = BaseProducts .BAS_ProductID

WHERE (DEP_CompanyID = @CompanyID)

UNION

SELECT
PRO_ProductID,
BAS_ProductID

FROM Companies
INNER JOIN Products ON Companies.COM_CompanyID = Products.PRO_OwnerID
INNER JOIN BaseProducts ON Products.PRO_BaseProductID = BaseProducts .BAS_ProductID
WHERE (COM_CompanyID = @CompanyID)

--4)
/*
Basically Companies or Contacts can be the owners of Products. Sometimes I want to bring back all the Products that a company owns - including that of all it's contacts.
This DML attempt runs quick, but I don't think it scales up to the amount of records I mention above.
*/

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/29/2005 :  15:19:51  Show Profile  Reply with Quote
Might benefit from indexes on

CON_DepartmentID
PRO_OwnerID
BAS_ProductID

so that all sides of the JOINS are indexes.

I've never believed in this, but its what the books say!

Do you need a UNION or could you use a UNION ALL - that would save some CPU cycles

Kristen
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 08/01/2005 :  03:29:51  Show Profile  Reply with Quote
I tried indexes on CON_DepartmentID and BAS_ProductID and it actually slowed it down - probably because the optimizer had to choose between indexes, not sure.
More than 99% of the records have PRO_OwnerID as the same value. So I'm going to try a partition on the Products table and put that PRO_OwnerID in as a constraint on one of the two tables and then use a view. We'll see if that fixes it.
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 08/01/2005 :  06:01:06  Show Profile  Reply with Quote
That worked :-)

If you pass an invalid ID in for @CompanyID, would that result in the query running considerably slower because of joining on nulls or something?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000