I've heard that NOT EXISTS works better with a larger set of records.. If it's only a few I heard that NOT IN would be better to use.
Of course this is just what I heard from our Oracle DBA a few years ago. So YMMV... best choice is to run a test.. load 50K rows and check. On Tue, Jan 13, 2009 at 8:14 AM, Adrian Lynch <[email protected]> wrote: > Cheers Dom. I read that NOT EXISTS did little or nothing for performance in > this case. Of course I can't point you to where I read that! > > Anyone know for sure? > > Adrian > >> -----Original Message----- >> From: Dominic Watson [mailto:[email protected]] >> Sent: 13 January 2009 13:48 >> To: cf-talk >> Subject: Re: need some database advice >> >> >SELECT TOP 1 itemID >> >FROM item >> >WHERE itemID NOT IN ( >> > SELECT itemID >> > FROM vote >> > WHERE userID = theUserVoting >> >) >> >> That doesn't work? Should work fine, as should NOT EXISTS: >> >> SELECT TOP 1 i.itemID >> FROM item i >> WHERE NOT EXISTS ( >> SELECT v.itemID >> FROM vote v >> WHERE v.itemId = i.itemId >> AND v.userID = theUserVoting >> ) >> >> Dom >> >> >> >> 2009/1/13 Adrian Lynch <[email protected]>: >> > I'm doing the same but with voting. People get to vote once per item >> and I >> > store this so they can't vote again. >> > >> > I'm doing something like: >> > >> > SELECT vote >> > FROM vote >> > WHERE userID = theUserVoting >> > AND itemID = theItemTheyreAttemptingToVoteFor >> > >> > This runs a lot and so far so good. >> > >> > What I AM having problems with is getting a random item that they've >> yet to >> > vote for. >> > >> > I have this: >> > >> > SELECT TOP 1 itemID >> > FROM item >> > WHERE itemID NOT IN ( >> > SELECT itemID >> > FROM vote >> > WHERE userID = theUserVoting >> > ) >> > >> > I've read around a bit and EXISTS might be the way forward but the >> examples >> > I've read use IN and not NOT IN. >> > >> > Any ideas? >> > >> > Sorry for the half-a-thread hijack! >> > >> > Adrian >> > >> >> -----Original Message----- >> >> From: Mike Soultanian [mailto:[email protected]] >> >> Sent: 13 January 2009 07:37 >> >> To: cf-talk >> >> Subject: need some database advice >> >> >> >> Hey Everyone, >> >> I have a project and I'm trying to figure out the best way to go >> about >> >> it. What I want to do is keep track of what songs a user has >> listened >> >> to and what songs they haven't. The first thing that comes to mind >> is >> >> a >> >> table with song IDs and a table with user IDs and a join table >> between >> >> the two that keeps track of what song a user has listened to. With >> >> 50,000 songs, that could be a lot of records in the join table. Is >> >> there a more efficient way to tackle this kind of problem? I don't >> >> think I'll have that many users, but even if I had ten users, that >> >> table >> >> could be pretty big. >> >> >> >> I'm trying to think if there are any tricks such as whether to store >> if >> >> a user has listened to a track or store if they haven't. I plan to >> >> have >> >> a button called "mark all as listened", which could empty the join >> >> table >> >> of any records pertaining to that user if I was storing the tracks >> they >> >> didn't listen to. So the join table would initially start out very >> >> large for a user and then drop down... that's just one thought I >> had. >> >> >> >> If anyone has any tricks, I'd appreciate your advise! >> >> >> >> Thanks, >> >> Mike > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317852 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
