March 25, 2008 at 6:50 am
I tested this over the weekend. Here's what I came up with:
create table Contacts (
ID int identity primary key,
Name varchar(100))
go
insert into dbo.contacts (name)
select top 1000000 clean
from dbo.numbercleanb
go
create table ContactsRelations (
ID1 int not null references contacts(id),
ID2 int not null references contacts(id),
constraint PK_Relations primary key (id1, id2))
go
create view RelationsBackwards
with schemabinding
as
select id2, id1
from dbo.contactsrelations
go
create unique clustered index UCI_RelationsBackwards
on dbo.relationsbackwards (id2, id1)
go
insert into dbo.contactsrelations(id1, id2)
select t1.id, t2.id
from dbo.contacts t1
inner join dbo.contacts t2
on t1.id between t2.id - 5 and t2.id + 5
and t1.id != t2.id
This created a relations table with 9,999,970 rows in it.
I then tested it with this:
select *
from dbo.contactsrelations
where id1 = 10000
union all
select *
from dbo.relationsbackwards
where id2 = 10000
Results:
===============================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 5 ms.
(20 row(s) affected)
Table 'RelationsBackwards'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactsRelations'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
===============================
That was in SQL 2005 Dev Edition (I don't have SQL 2000 available for this kind of testing). Computer is Pentium D (dual-core) 3 Ghz, 2 Gig of RAM, SATA 3Gbps HDD, Windows XP Pro. So any decent server should be able to get pretty good performance out of a set-up like this.
I changed the query to (to eliminate duplicates):
select distinct *
from
(select *
from dbo.contactsrelations
where id1 = 10001
union all
select *
from dbo.relationsbackwards
where id2 = 10001) Sub
Results:
===============================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(10 row(s) affected)
Table 'RelationsBackwards'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactsRelations'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
===============================
Of course, you'll need to change the query to something you can join on and get data from both tables and all that. But basically it should do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2008 at 5:06 pm
Wow GSquared, thank you so much for taking your time and putting this together, i do appreciate it very much 🙂 so basically all i need is to create a view to combine the "contactsrelations" table with "relationsbackwards" view and it should be it. i really want to use only one view for my lookups...
thanks again!!!
March 27, 2008 at 8:21 am
You're welcome.
I'd go with a proc for the final select, instead of a view. Then you can use input parameters. But basically, yeah, that should do it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply