Query Efficiency

Tbl_Contact
Contact_ID
FirstName
LastName
Tbl_Contact_Detail
Contact_ID
Question_ID
The combination of Contact_ID and Question_ID is unique in
Tbl_Contact_Detail. There are millions of records in this table. Basically
I need to write an optimized query for returning the Contact_ID, FirstName
and LastName of all contacts that have records in Tbl_Contact_Detail where
Question_ID = 44 and a separate record where Question_ID = 45. What's the
fastest approach?
Thanks a tonThe best approach depends on the right indexing on Tbl_Contact_Detail.
It also probably depends on how what percentage of the contacts have
those questions, and how many questions there are per contact.
Here are some things you could try.
If there are no indexes on Tbl_Contact_Detail this might still perform
reasonably. If there is a clustered index on Question_ID it might
also perform well.
SELECT *
FROM Tbl_Contact as A
JOIN (SELECT Contact_ID
FROM Tbl_Contact_Detail
WHERE Question_ID IN (44, 45)
GROUP BY Contact_ID
HAVING COUNT(distinct Question_ID) = 2) as B
ON A.Contact_ID = B.Contact_ID
This query would not work well unless there is an index on
(Contact_ID, Question_ID), or the reverse. If there are very few
questions per Contact_ID it might not perform too badly if there is
just an index on Contact_ID. In this case non-clustered indexes might
work better than clustered, particularly if Tbl_Contact_Detail has
more columns than were shown.
SELECT *
FROM Tbl_Contact as A
WHERE EXISTS
(SELECT *
FROM Tbl_Contact_Detail as B
WHERE A.Contact_ID = B.Contact_ID
AND B.Question_ID = 44)
AND EXISTS
(SELECT *
FROM Tbl_Contact_Detail as B
WHERE A.Contact_ID = B.Contact_ID
AND B.Question_ID = 45)
Of course the thing to do is run some tests and see how they go.
Roy Harvey
Beacon Falls, CT
On Tue, 25 Sep 2007 16:40:30 -0400, "James" <neg@.tory.com> wrote:
>Tbl_Contact
>Contact_ID
>FirstName
>LastName
>Tbl_Contact_Detail
>Contact_ID
>Question_ID
>The combination of Contact_ID and Question_ID is unique in
>Tbl_Contact_Detail. There are millions of records in this table. Basically
>I need to write an optimized query for returning the Contact_ID, FirstName
>and LastName of all contacts that have records in Tbl_Contact_Detail where
>Question_ID = 44 and a separate record where Question_ID = 45. What's the
>fastest approach?
>Thanks a ton
>|||Hi James
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data. You would need to try each different solution to see if it is
using your indexes and of the performance is ok
try:
SELECT C.Contact_ID, C.FirstName, C.LastName
FROM dbo.tbl_contract C
JOIN dbo.tbl_Contact_Detail D1 ON C.Contact_ID = D1.Contact_ID AND
D1.Question_ID = 44
JOIN dbo.tbl_Contact_Detail D2 ON C.Contact_ID = D2.Contact_ID AND
D2.Question_ID = 45
or
SELECT C.Contact_ID, C.FirstName, C.LastName
FROM dbo.tbl_contract C
JOIN ( SELECT Contact_ID
FROM dbo.tbl_Contact_Detail
WHERE Question_ID = 44
OR Question_ID = 45
GROUP BY Contact_ID
HAVING COUNT(*) = 2 ) D ON C.Contact_ID = D.Contact_ID
John
"James" wrote:
> Tbl_Contact
> Contact_ID
> FirstName
> LastName
> Tbl_Contact_Detail
> Contact_ID
> Question_ID
> The combination of Contact_ID and Question_ID is unique in
> Tbl_Contact_Detail. There are millions of records in this table. Basically
> I need to write an optimized query for returning the Contact_ID, FirstName
> and LastName of all contacts that have records in Tbl_Contact_Detail where
> Question_ID = 44 and a separate record where Question_ID = 45. What's the
> fastest approach?
> Thanks a ton
>
>

Query blues... Help!

I have a security app that is like this: a Facility has many Apps and those
Apps may have any combination Modules, Roles, or Privileges.
Problem Query: Put together a query(s) that will delete all records related
to a single Facility.
CREATE TABLE Applications
(Appl_ID int IDENTITY,
FacilityID char(4) NOT NULL,
ApplName char(200) NOT NULL,
CONSTRAINT PK_Applications PRIMARY KEY CLUSTERED (Appl_ID))
go
CREATE TABLE ModulesRolesPrivileges
(MRP_ID int IDENTITY,
MRP_Name char(150) NOT NULL,
MRP_Type int NOT NULL, -- 1=Role 2=Module 3=Privilege
CONSTRAINT PK_ModulesRolesPrivileges PRIMARY KEY CLUSTERED (MRP_ID))
go
CREATE TABLE ApplicationsModulesRolesPrivileges -- Intersection table
between Applications and ModulesRolesPrivileges
(AMRP_ID int IDENTITY,
Appl_ID int NOT NULL,
MRP_ID int NOT NULL,
CONSTRAINT PK_ApplicationsModulesRolesPrivileges PRIMARY KEY CLUSTERED
(AMRP_ID),
CONSTRAINT R_1 FOREIGN KEY (Appl_ID) REFERENCES Applications (Appl_ID),
CONSTRAINT R_2 FOREIGN KEY (MRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))
go
CREATE TABLE ModulesRolesPrivilegesHierarchy
(MRPH_ID int IDENTITY,
Parent_AMRP_ID int NOT NULL,
Child_AMRP_ID int NOT NULL,
CONSTRAINT PK_ModulesRolesPrivilegesHierarchy PRIMARY KEY CLUSTERED (MRPH_ID),
CONSTRAINT R_3 FOREIGN KEY (Parent_AMRP_ID) REFERENCES
ModulesRolesPrivileges (MRP_ID),
CONSTRAINT R_4 FOREIGN KEY (Child_AMRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Host
Financials') -- 1
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'PeopleSoft')
-- 2
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Acct Recv')
-- 3
INSERT Applications (FacilityID, ApplName) VALUES ('0914', 'Acct Recv')
-- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Budget', 2)
-- 1
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('GL', 2)
-- 2
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 3
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Admin', 1)
-- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Query', 3)
-- 5
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Update', 3)
-- 6
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 7
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 8
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 9
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 1) -- 1
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 2) -- 2
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (2, 3) -- 3
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (4, 8) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (1, 3) -- 1
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 5) -- 2
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 6) -- 3
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 7) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 4) -- 5
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (4, 5) -- 6
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 3) -- 7
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (8, 9) -- 8
Is Stored Procedure out of the question? If not, you can do the following.
1) SP will have one argument for FacilityID.
2)
Delete *
From ModulesRolesPrivilegesHierarchy
Where Parent_AMRP_ID IN (SELECT AMRP_ID
FROM ApplicationsModulesRolesPrivileges
WHERE Appl_ID IN (SELECT Appl_ID
FROM Applications
WHERE FacilityID = @.FacilityID))
3)
Delete *
From ApplicationsModulesRolesPrivileges
WHERE Appl_ID IN (SELECT Appl_ID
FROM Applications
WHERE FacilityID = @.FacilityID)
4)
Delete *
From Applications
WHERE FacilityID = @.FacilityID
That should do the trick.
Hope that helps.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||Please ignore my answer, I misread table statements. So my answer is
incorrect.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Mohit K. Gupta" wrote:

> Is Stored Procedure out of the question? If not, you can do the following.
> 1) SP will have one argument for FacilityID.
> 2)
> Delete *
> From ModulesRolesPrivilegesHierarchy
> Where Parent_AMRP_ID IN (SELECT AMRP_ID
> FROM ApplicationsModulesRolesPrivileges
> WHERE Appl_ID IN (SELECT Appl_ID
> FROM Applications
> WHERE FacilityID = @.FacilityID))
> 3)
> Delete *
> From ApplicationsModulesRolesPrivileges
> WHERE Appl_ID IN (SELECT Appl_ID
> FROM Applications
> WHERE FacilityID = @.FacilityID)
> 4)
> Delete *
> From Applications
> WHERE FacilityID = @.FacilityID
> That should do the trick.
> Hope that helps.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005