Tough one! - SQL Server Forums

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)

Tough one!

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 07:53:15

I am currently developing an application for the Swedish Military. It is classified so I will use the concept of an dating service instead when explaining here.

It is still the same I want, but more elegant. The queries I have today are ugly hacks. In real application I search for conflict of interests (COI), but here I will call it a match instead.

To this date, I have managed to narrow down the three queries to
1. Insert into table variable @Coi
2. Update the table variable @Coi
3. Select from table variable @Coi

I believe this can be done more effeciently and more elegant. Is anyone up for the challenge? khtan, Madhivanan, RyanRandall, Michael Valentine Jones, Jen, Kristen, nr?

The task is to present all matches (COIs) against all members in the table. All members belong to a specific level, depending on ranks. For dating service this could be Beginner, Novice, Intermediate and so on. In my application it is Lieutenant, Captain, Major, Colonel and so on. The anwers given are not mandatory. Every member can choose any number of answers within category, if at all.

The task is to create a query that select all matches (COIs) between the member that has at least one match within any category. The minimum number of categories is determined by table MemberLevels. If a member belongs to memberlevel 7, the two minimum categories match could be any two categories - Looks and Wealth, or Interests and Marriage, or Children and Living, that the two matching members has given the same answer. Minimum number of categories is as specified in MemberLevels table.

Good luck!

This is an extract of the DDL for relevant tables,


CREATE TABLE [dbo].[Alternatives] (
	[AlternativeID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryID] [int] NOT NULL ,
	[AlternativeText] [varchar] (50) COLLATE Finnish_Swedish_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Answers] (
	[AnswerID] [int] IDENTITY (1, 1) NOT NULL ,
	[MemberID] [int] NOT NULL ,
	[AlternativeID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] [varchar] (50) COLLATE Finnish_Swedish_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MemberLevels] (
	[Level] [int] IDENTITY (1, 1) NOT NULL ,
	[Categories] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Members] (
	[MemberID] [int] IDENTITY (1, 1) NOT NULL ,
	[MemberName] [varchar] (50) COLLATE Finnish_Swedish_CI_AS NOT NULL ,
	[MemberLevel] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Alternatives] WITH NOCHECK ADD 
	CONSTRAINT [PK_Alternative] PRIMARY KEY  CLUSTERED 
	(
		[AlternativeID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Categories] WITH NOCHECK ADD 
	CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED 
	(
		[CategoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[MemberLevels] WITH NOCHECK ADD 
	CONSTRAINT [PK_MemberLevels] PRIMARY KEY  CLUSTERED 
	(
		[Level]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Members] WITH NOCHECK ADD 
	CONSTRAINT [PK_Members] PRIMARY KEY  CLUSTERED 
	(
		[MemberID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Alternatives] ADD 
	CONSTRAINT [IX_Alternatives] UNIQUE  NONCLUSTERED 
	(
		[AlternativeID],
		[CategoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Alternatives] ADD 
	CONSTRAINT [FK_Alternatives_Categories] FOREIGN KEY 
	(
		[CategoryID]
	) REFERENCES [dbo].[Categories] (
		[CategoryID]
	)
GO

ALTER TABLE [dbo].[Answers] ADD 
	CONSTRAINT [FK_Answers_Alternatives] FOREIGN KEY 
	(
		[AlternativeID]
	) REFERENCES [dbo].[Alternatives] (
		[AlternativeID]
	),
	CONSTRAINT [FK_Answers_Members] FOREIGN KEY 
	(
		[MemberID]
	) REFERENCES [dbo].[Members] (
		[MemberID]
	)
GO

ALTER TABLE [dbo].[Members] ADD 
	CONSTRAINT [FK_Members_MemberLevels] FOREIGN KEY 
	(
		[MemberLevel]
	) REFERENCES [dbo].[MemberLevels] (
		[Level]
	)
GO

but for the ease of the challenge I have converted the sample data to table variables instead.

declare @answers table (AnswerID int, MemberID int, AlternativeID int)

insert @answers
select 1, 1000, 29 union all
select 2, 1000, 5 union all
select 3, 1000, 4 union all
select 4, 1000, 7 union all
select 5, 1000, 13 union all
select 6, 1000, 14 union all
select 7, 1000, 20 union all
select 8, 1000, 22 union all
select 9, 1000, 27 union all
select 10, 2000, 1 union all
select 11, 2000, 10 union all
select 12, 2000, 4 union all
select 13, 2000, 7 union all
select 14, 2000, 13 union all
select 15, 2000, 14 union all
select 16, 2000, 18 union all
select 17, 2000, 23 union all
select 18, 2000, 27 union all
select 19, 3000, 9 union all
select 20, 3000, 2 union all
select 21, 3000, 3 union all
select 22, 3000, 11 union all
select 23, 3000, 6 union all
select 24, 3000, 16 union all
select 25, 3000, 17 union all
select 26, 3000, 22 union all
select 27, 3000, 25 union all
select 28, 4000, 29 union all
select 29, 4000, 1 union all
select 30, 4000, 4 union all
select 31, 4000, 8 union all
select 32, 4000, 15 union all
select 33, 4000, 19 union all
select 34, 4000, 21 union all
select 35, 4000, 28 union all
select 36, 5000, 2 union all
select 37, 5000, 4 union all
select 38, 5000, 8 union all
select 39, 5000, 13 union all
select 40, 5000, 14 union all
select 41, 5000, 15 union all
select 42, 5000, 20 union all
select 43, 5000, 24 union all
select 44, 5000, 26 union all
select 45, 6000, 29 union all
select 46, 6000, 5 union all
select 47, 6000, 7 union all
select 48, 5000, 7 union all
select 49, 6000, 16 union all
select 50, 6000, 17 union all
select 51, 6000, 19 union all
select 52, 6000, 21 union all
select 53, 6000, 23 union all
select 54, 7000, 1 union all
select 55, 7000, 9 union all
select 56, 7000, 12 union all
select 57, 7000, 15 union all
select 58, 7000, 22 union all
select 59, 7000, 27

declare @members table (MemberID int, MemberName varchar(50), MemberLevel int)

insert @members
select 1000, 'Peter', 4 union all
select 2000, 'Jennie', 4 union all
select 3000, 'Jane', 7 union all
select 4000, 'John', 1 union all
select 5000, 'Henrik', 1 union all
select 6000, 'Sara', 2 union all
select 7000, 'Anette', 7

declare @memberlevels table (Level int, Categories int)

insert @memberlevels
select 1, 2 union all
select 2, 1 union all
select 3, 1 union all
select 4, 7 union all
select 5, 1 union all
select 6, 1 union all
select 7, 2

declare @categories table (CategoryID int, CategoryName varchar(50))

insert @categories
select 1, 'Looks' union all
select 2, 'Wealth' union all
select 3, 'Health' union all
select 4, 'Interests' union all
select 5, 'Children' union all
select 6, 'Marriage' union all
select 7, 'Living'

declare @alternatives table (AlternativeID int, CategoryID int, AlternativeText varchar(50))

insert @alternatives
select 1, 1, 'Thin' union all
select 2, 1, 'Thick' union all
select 3, 2, 'Poor' union all
select 4, 2, 'Rich' union all
select 5, 2, 'Independent' union all
select 6, 3, 'Astma' union all
select 7, 3, 'No known illnesses' union all
select 8, 3, 'Beer belly' union all
select 9, 1, 'Tall' union all
select 10, 1, 'Short' union all
select 11, 2, 'Welfare' union all
select 12, 3, 'Smoker' union all
select 13, 4, 'Film' union all
select 14, 4, 'Movies' union all
select 15, 4, 'Walk in the park' union all
select 16, 4, 'Gym' union all
select 17, 5, 'Not in my lifetime' union all
select 18, 5, 'None. But want to have' union all
select 19, 5, 'Have. But no more' union all
select 20, 5, 'Have. Want more' union all
select 21, 6, 'If you want to...' union all
select 22, 6, 'Already is' union all
select 23, 6, 'Within next three years' union all
select 24, 6, 'Nothing for me' union all
select 25, 7, 'Rented apartment' union all
select 26, 7, 'Rented house' union all
select 27, 7, 'Bought apartment' union all
select 28, 7, 'Bought house' union all
select 29, 1, 'Medium built'

Output from query should be something like

MemID   Name   MemID  Name    CatID  CatName    AnsID  AnsText
------  -----  -----  ------  -----  ---------  -----  ------------------
  1000  Peter   2000  Jennie      1  Looks          4  Rich
  1000  Peter   2000  Jennie      7  Living         7  No known illnesses
  1000  Peter   2000  Jennie      4  Interests     13  Film
  1000  Peter   2000  Jennie      4  Interests     14  Movies

produced by all matches/conflicts present. Even if minimum level is 2, all matches/COIs should be output, if there are more.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-07-03 : 09:32:47

"I am currently developing an application for the Swedish Military. It is classified so I will use the concept of an dating service instead when explaining here."

I have built a dating site database ... but its proprietary so I'll have to charge you to convert it for use by the Swedish Military

We use two tables - Self and Wants.

You describe what you have (Self) and what you want in your partner (wants). You can be vague, or broad. Your answers are stored as bit patterns - so if you are 30 (one bit for, say, 28-32) and you are looking for someone 25-27, 28-32 or 33-35 then that sets 3 bits. Just OR'ing them together tells you if you have a match, or not.

We have about 10 columns for the bit patterns, each column represents one question and its answers - so columns for Age, Profession type, Education level, Weight and so on.

The search is not very efficient! but it does the job, and people do not change their characteristics very often - other than new registrations, so we do cache some data to ease the processing burden.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 09:42:18

Thanks for the swift reply, Kristen.

But this doesn't do the job for me. The application has to comply for BELBIN project model ([url]http://www.belbin.com/downloads/Belbin_Team_Role_Summary_Descriptions.pdf[/url]). It's purpose is to set together certain teams. It could be foreign service task force, or in-house project. Assessments of each member are changed at at least 4 times per year, based on interviews with subordinates, higher officers and training.

Today, my three queries runs for 15 seconds for 31,000 members and approx 2.4 million answers. And I think it the ugly hacks will be hard to maintain in the future. So I need a more elegant solution. In the queries below there is also a version component, since we must maintain all assessments quarterly. The reason I choose version, is that the version is not always quarterly. Sometimes it can be monthly, and sometimes only twice a year. But there is a need to compare every persons assessments, for every period, in order to see if a person advances in his/hers people skills, technical performance and so on.

These are the three queries I have today

DECLARE 	@Coi TABLE
			(
				RuleID INT,
				RuleSet INT,
				TargetModelID INT,
				TargetMemberCode VARCHAR(30),
				TargetMemberName VARCHAR(120),
				TargetDimensionID INT,
				TargetDimensionName VARCHAR(32),
				SourceModelID INT,
				SourceMemberCode VARCHAR(30),
				SourceMemberName VARCHAR(120),
				SourceDimensionID INT,
				SourceDimensionName VARCHAR(32),
				Cloned TINYINT
			)

INSERT INTO	@Coi
		(
			RuleID,
			RuleSet,
			TargetModelID,
			TargetMemberCode,
			TargetMemberName,
			TargetDimensionID,
			TargetDimensionName,
			SourceModelID,
			SourceMemberCode,
			SourceMemberName,
			SourceDimensionID,
			SourceDimensionName,
			Cloned
		)
SELECT		Rules.RuleID,
		Rules.RuleSet,
		Rules.ModelID,
		TargetModels.MemberCode,
		TargetModels.MemberName,
		TargetDimensions.DimensionID,
		TargetDimensions.DimensionName,
		Translations.ModelID,
		SourceModels.MemberCode,
		SourceModels.MemberName,
		SourceDimensions.DimensionID,
		SourceDimensions.DimensionName,
		0
FROM		Rules
INNER JOIN	Models TargetModels ON TargetModels.ModelID = Rules.ModelID
INNER JOIN	Dimensions TargetDimensions ON TargetDimensions.DimensionID = TargetModels.DimensionID
INNER JOIN	Translations ON Translations.RuleID = Rules.RuleID
INNER JOIN	Models SourceModels ON SourceModels.ModelID = Translations.ModelID
INNER JOIN	Dimensions SourceDimensions ON SourceDimensions.DimensionID = SourceModels.DimensionID
INNER JOIN	Prioritygroups ON Prioritygroups.PrioritygroupID = Rules.PrioritygroupID
WHERE		Prioritygroups.VersionID = @VersionID
		AND Prioritygroups.PrioritygroupLevel = @PrioritygroupLevel

UPDATE	r1
SET	r1.Cloned = 1
FROM	@Coi r1,
	@Coi r2
WHERE	r1.SourceModelID = r2.SourceModelID
	AND r1.RuleID <> r2.RuleID

SELECT DISTINCT	coi.RuleID,
		r.RuleID,
		coi.SourceDimensionID
FROM		@Coi coi
CROSS JOIN	(
			SELECT	RuleID,
				SourceDimensionID
			FROM	@coi
		) R
WHERE		coi.RuleID < R.RuleID
		AND coi.SourceDimensionID = R.SourceDimensionID
		AND coi.Cloned = 1
ORDER BY	coi.RuleID,
		R.RuleID,
		coi.SourceDimensionID

Peter Larsson
Helsingborg, Sweden

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-07-04 : 05:00:28

Man, I want to be able to look at this and find a solution, but I have to go home... maybe another day.

Rob Farley
http://robfarley.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-04 : 05:44:50

I have managed to slim the queries. Now I have got rid of the update part.
So now there is only to queries
1. Insert @Coi
2. Select @Coi

I hope this can be slimmed further into only one SELECT.
This is what I got this far

declare @Coi table (AltID INT, CatID INT, FromID INT, Cats INT, ToID INT)

insert		@Coi
select		ans1.alternativeid altid,
		alt1.categoryid catid,
		mem1.memberid fromid,
		lev1.categories cats,
		mem2.memberid toid
from		@answers ans1
inner join	@answers ans2 on ans2.alternativeid = ans1.alternativeid and ans2.memberid <> ans1.memberid
inner join	@alternatives alt1 on alt1.alternativeid = ans1.alternativeid
inner join	@members mem1 on mem1.memberid= ans1.memberid
inner join	@members mem2 on mem2.memberid= ans2.memberid
inner join	@memberlevels lev1 on lev1.level = mem1.memberlevel
inner join	@memberlevels lev2 on lev2.level = mem2.memberlevel
where		lev1.categories <= lev2.categories

select		c.fromid,
		mem1.membername,
		c.toid,
		mem2.membername,
		c.catid,
		cat.categoryname,
		c.altid,
		alt.alternativetext alttext
from		@coi c
inner join	@members mem1 on mem1.memberid = c.fromid
inner join	@members mem2 on mem2.memberid = c.toid
inner join	@categories cat on cat.categoryid = c.catid
inner join	@alternatives alt on alt.alternativeid = c.altid
inner join	(
			select		fromid,
					toid
			from		@coi
			group by	fromid,
					toid
			having		count(distinct catid) >= min(cats)
		) z on z.fromid = c.fromid and z.toid = c.toid
order by	c.fromid,
		c.toid

Peter Larsson
Helsingborg, Sweden

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-07-05 : 11:33:04

You could probably do this is one query, but it isn't worth the effort. It would probably run slower anyway.

this is my shot:


Declare @Prep Table (MemIdA int, MemNameA varchar(20), MemIdB int, MemNameB varchar(20), CatId int, CatName varchar(20), AnsId int, AnsText varchar(50), matches int)

Insert Into @Prep
Select 
	MemIdA = A.MemberId,
	MemNameA = A.MemberName,
	MemIdB = B.MemberId,
	MemNameB = B.MemberName,
	CatId = C.CategoryId,
	CatName = D.CategoryName,
	AnsId = C.AlternativeId,
	AnsText = C.AlternativeText,
	MinMatches = (Select min(categories) From @memberlevels Where Level in (A.memberLevel,B.memberLevel))
From @members A
Inner Join @answers A1
On A.memberId = A1.memberId
Inner Join @answers B1
On A1.AlternativeId = B1.AlternativeId
and A1.answerId <> B1.answerId
Inner Join @members B
On B1.memberId = B.memberId
and A.memberId < B.memberId
Inner Join @alternatives C
On A1.AlternativeId = C.AlternativeId
Inner Join @categories D
On C.CategoryId = D.CategoryId

Select * From @Prep Where MemIdA = 1000 and MemIdB = 2000

Select * 
From @Prep Z
Inner Join
	(
	Select MemIdA, MemIdB
	From @Prep A
	Group By MemIdA, MemIdB
	Having count(CatId) >= min(matches)
	) Y
On Z.MemIdA = Y.MemIdA
and Z.MemIdB = Y.MemIdB

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources