rewriting NOT in subquery with left outer

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 2008 Forums

Transact-SQL (2008)

rewriting NOT in subquery with left outer

Author

Topic

woodsy1978
Starting Member

8 Posts

Posted - 2011-07-21 : 04:34:21

ok so the java guys are claiming this query from the jsp page used to work fine and gave me a horrible looking beast, i've trimmed it to this :-

select left(unique_id,16) AS casino_id , right(unique_id,24) AS game_id
from ( select
distinct o.casino_id + g.game_id AS unique_id
from
game g,
bet b,
casinouser u,
onewalletcasino o
where
game_start between dateadd(mi, -180, getdate())
and dateadd(mi, -5, getdate())
and g.game_id = b.game_id
and b.user_id = u.user_id
and u.casino_id = o.casino_id
and b.status <> 'P') t
where
unique_id NOT IN
(select casino_id + game_id AS casino_id
from thirdpartysettlecalled
where
status = 'Y')
order by casino_id ;

I have added a index to half the query time, but I want to rewrite the subquery at the bottom with a left join.... i'd like to also remove the concatenation of the fields altogether but that is a 2nd point.

Sachin.Nand

2937 Posts

Posted - 2011-07-21 : 04:55:01

Not sure why you need a left join since you have a in clause.You will need a inner join to modify your existing subquery.



...and u.casino_id = o.casino_id 
and b.status <> 'P') t 
inner join thirdpartysettlecalled tp on t.unique_id=tp.unique_id where t.status='Y'

PBUH

woodsy1978
Starting Member

8 Posts

Posted - 2011-07-21 : 04:55:57

sorry forgot to put the NOT IN back in !! I was using the IN claus to confirm the NOT IN was causing performance issues

woodsy1978
Starting Member

8 Posts

Posted - 2011-07-21 : 05:38:04

rewritten old code to place the joins in the correct place

SELECT
left(unique_id,16) AS casino_id ,
right(unique_id,24) AS game_id
FROM (
SELECT
distinct o.casino_id + g.game_id AS unique_id
FROM
game g INNER JOIN Bet b
ON g.game_id = b.game_id
INNER JOIN CasinoUser u
ON b.user_id = u.user_id
INNER JOIN onewalletcasino o
ON u.casino_id = o.casino_id
WHERE
game_start between dateadd(mi, -180, getdate())
and dateadd(mi, -5, getdate())

and b.[status] <> 'P') t
WHERE
unique_id NOT IN
(SELECT casino_id + game_id AS casino_id
FROM
thirdpartysettlecalled
WHERE
[status] = 'Y')
ORDER BY casino_id

Sachin.Nand

2937 Posts

Posted - 2011-07-21 : 06:04:56

Are you sure it is the NOT IN which is causing the performance issue ?

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources