Filtering a large amount of data?

Posted By: webmonkey | Mar 9th @ 11:38 AM
page 1 of 1
Comments: 3 | Views: 957
#Mar 9th @ 11:38 AM
webmonkey
webmonkey
How am i supposed to code with theeeeeese ?
Assume a database with up to 1 million books listed.

As a user is searching they have the ability to say "never show me this book again" or "never show me any books by this author again".

So a user may have selected 1000 books as "never show me this book again".

When they search I want to filter out those results.

I can think of the simple way to do it, but it doesn't seem like it would be an efficient way to do it.

Does anyone have any suggestions as to the best way to approach this, concepts rather than actual code.

Thanks Smiley
#Mar 9th @ 12:28 PM
SimonJ
SimonJ

Assuming you identify a book by its ISBN, I would use a NOT IN construct, passing the problem onto your database server:

SELECT Isbn, Title, Author
FROM AllBooks
WHERE Title LIKE @Title
    AND Isbn NOT IN (SELECT Isbn
                                FROM NotAgainBooks
                                WHERE UserID = @UserID)

You save the ISBN of the books the don't want along with the users ID to a separate table then filter your returned list to exclude those they don't want to see again.

Do the same with the ID of the authors they don't want to see again in a separate table. You'll need some unique ID for an author as name probably won't be unique enough.

Regards,
Simon Jones
Contributing Editor
PC Pro Magazine

#Mar 9th @ 1:19 PM
webmonkey
webmonkey
How am i supposed to code with theeeeeese ?
SimonJ wrote:


Assuming you identify a book by its ISBN, I would use a NOT IN construct, passing the problem onto your database server:

SELECT Isbn, Title, Author
FROM AllBooks
WHERE Title LIKE @Title
    AND Isbn NOT IN (SELECT Isbn
                                FROM NotAgainBooks
                                WHERE UserID = @UserID)

You save the ISBN of the books the don't want along with the users ID to a separate table then filter your returned list to exclude those they don't want to see again.

Do the same with the ID of the authors they don't want to see again in a separate table. You'll need some unique ID for an author as name probably won't be unique enough.

Regards,
Simon Jones
Contributing Editor
PC Pro Magazine



That was the easy way I could think of Wink it just struck me that there was probably a more efficient way, given the potential volume of searches and NOT's happening.

Oh well, if that's the way to go then that makes things very simple.

Thanks to both you and JChung2006 Smiley

#Mar 9th @ 5:45 PM
JChung2006
JChung2006
SimonJ wrote:


Assuming you identify a book by its ISBN, I would use a NOT IN construct, passing the problem onto your database server:

SELECT Isbn, Title, Author
FROM AllBooks
WHERE Title LIKE @Title
    AND Isbn NOT IN (SELECT Isbn
                                FROM NotAgainBooks
                                WHERE UserID = @UserID)

You save the ISBN of the books the don't want along with the users ID to a separate table then filter your returned list to exclude those they don't want to see again.

Do the same with the ID of the authors they don't want to see again in a separate table. You'll need some unique ID for an author as name probably won't be unique enough.

Regards,
Simon Jones
Contributing Editor
PC Pro Magazine

Another way to do this (SQL Server 2005 or later):

SELECT Isbn, Title, Author
FROM AllBooks
WHERE Title LIKE @Title
EXCEPT
SELECT Isbn, Title, Author
FROM NotAgainBooks
INNER JOIN AllBooks ON NotAgainbooks.Isbn = AllBooks.Isbn
WHERE UserID = @UserID
page 1 of 1
Comments: 3 | Views: 957