August 7, 2008 at 2:05 pm
Hi to all
I am developping Multiple web sites in same DB
ther will be user input to which web sites to post thier content (something like Ads example: I want to post in 100 web sites...)
for now 31 web sites
one row AdID, text, time, .... websiteIDs
for now I have 2 ways to go
1. have second table and for each site add row and find yes there is for this web site post...
2.
http://msdn.microsoft.com/en-us/library/ms174965.aspx
have column websiteIDs and do & (Bitwise AND) with FLAG for this web site and ask
SELECT * from ads where ((websiteIDs & @myFlag) > 0)
Which will be fast if many posts ????
My expiriance filter by second column which is not PK in 22 000 000 row is slow
Thanks
valentin
August 7, 2008 at 2:28 pm
I'm not positive that I understand what you are asking, but I would do a design with 3 tables Ads, WebSites, and Ad_WebSites like this:
[font="Courier New"]CREATE TABLE Ads
(
AdId INT PRIMARY KEY,
OtherColumns
)
CREATE TABLE WebSites
(
WebSiteId INT PRIMARY KEY,
OtherColumns
)
CREATE TABLE Ad_WebSites
(
AdId INT REFERENCES Ads(AsId),
WebSiteId INT REFERENCES WebSites(WebSiteId)
PRIMARY KEY(AdId, WebSiteId)
)
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 2:42 pm
This my first choice and have JOINs
the second choice Go binary
define my logic...
website1 = 2
website2 = 4
have one column websiteIDs
if I update websiteIDs = 2
and do
select * from Ads where (websiteIDs & @websiteCONST) > 0
In this case I will have only 1 row per post and in one INT or BIGINT if is need in future handle the logic
I realy had that bad expiriance when I was join table with 22 000 000 rows slow
the up example
if websiteIDs = 6
and I do binary AND with 4 = website2 is there....
when I update this column I do binary OR (+)
thanks for responds
val
August 7, 2008 at 2:50 pm
The Joins should work fine as long as you have proper indexes on them and in the design I have suggested they are all part of a clustered index. THe problem with the binary approach is maintenance. What if the programmer that comes after you doesn't understand it? Joins are simple and should be fast, if the indexes are correct.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 2:54 pm
No I am doing this in my private business
I do the programming maintenance is not a problem...
but I have never expiriance with binary in SQL never do this logic...
this will eliminate one table = number of posts * websites = rows
Which will be faster????
v
August 8, 2008 at 11:32 am
Hi again
I did a test
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[waID]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[waID](
[aID] [int] NOT NULL,
[wID] [int] NOT NULL,
CONSTRAINT [PK_waID] PRIMARY KEY CLUSTERED
(
[aID] ASC,
[wID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[a](
[aID] [int] IDENTITY(1,1) NOT NULL,
[t] [varchar](50) NOT NULL,
[int] NOT NULL,
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[aID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
where I insert 100 000 rows in table a and for column i I insert 2147483646 which are SUM of all 2 power 2 ( website1 = 2 + website2 = 4....) this for 30 web sites
for the column t = 'text' same...
into the second table waID I have now 3 000 000 rows
TEST 1.
SELECT a.*
FROM a INNER JOIN
waID ON a.aID = waID.aID
WHERE (waID.wID = 4)
COST 2 seconds !!!!!!!!!!!!!!!!!
---------------------
TEST 2.
DECLARE @flag INT
SET @FLAG =4;
SELECT * from a where (a.i & @FLAG) > 0
COST 1 second !!!!!!!!!!!!!!!!!! :w00t:
--------------------------------------------------------------------------------
here is the insert code
DECLARE @counter INT
set @counter = 1
DECLARE @ID INT
DECLARE @wsCNT INT
DECLARE @wsFlag INT
while @counter < 100000
begin
INSERT INTO a (t, i) VALUES ('text',2147483646)
SET @ID =Scope_identity()
set @wsCNT = 1
SET @wsFlag = 1
while @wsCNT < 31
begin
set @wsFlag = @wsFlag * 2
INSERT INTO waID (aID, wID) VALUES (@ID,@wsFlag)
set @wsCNT = @wsCNT + 1
end
set @counter = @counter + 1
end
----------------------------
still hards to take desition :w00t: where to go
any suggestions thanks in advance
v
August 8, 2008 at 12:38 pm
I'd go with the 3-table suggestion. It's more standard, and thus will be easier to deal with. Save yourself the agony of having to debug the binary two years from now.
- 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
August 8, 2008 at 12:42 pm
My thoughts exactly.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply