Improving performance: 42 seconds down to under 5

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

SQL Server Development (2000)

Improving performance: 42 seconds down to under 5

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-07-29 : 11:17:23

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

22859 Posts

Posted - 2005-07-29 : 15:19:51

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

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-08-01 : 03:29:51

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.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-08-01 : 06:01:06

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources