|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
 Posted - 07/29/2005 : 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.
*/ |
|
Improving performance: 42 seconds down to under 5
