How to prevent code duplication at this sql query

Question

Here the sql query

The first query is my main select query. It will select millions of records

However at the later queries i have to use first selected UserIds. right now my only solution is duplicating this query

However i wonder would there be any more proper solution?

SQL server 2014 Sp1

    SELECT
  UserId
FROM tblUsersProfile
WHERE (
TotalBattleCount < 20
AND Money < 200000
AND LastMoveTime < DATEADD(DAY, -30, SYSUTCDATETIME())
AND UserId IN (SELECT
  UserId
FROM tblUsersPokemons
GROUP BY UserId
HAVING COUNT(Id) < 20)
AND UserId NOT IN (SELECT
  UserId
FROM tblUsersPokemons
WHERE PokemonLevel > 90
GROUP BY UserId)
AND UserId > 3
)
OR UserId IN (SELECT
  UserId
FROM tblBannedUsers)
ORDER BY UserId ASC



DELETE FROM tblDailyRewardsParticipants
WHERE EventUserId IN (SELECT
    UserId
  FROM tblUsersProfile
  WHERE (
  TotalBattleCount < 20
  AND Money < 200000
  AND LastMoveTime < DATEADD(DAY, -30, SYSUTCDATETIME())
  AND UserId IN (SELECT
    UserId
  FROM tblUsersPokemons
  GROUP BY UserId
  HAVING COUNT(Id) < 20)
  AND UserId NOT IN (SELECT
    UserId
  FROM tblUsersPokemons
  WHERE PokemonLevel > 90
  GROUP BY UserId)
  AND UserId > 3
  )
  OR UserId IN (SELECT
    UserId
  FROM tblBannedUsers)
  ORDER BY UserId ASC)


DELETE FROM tblDailyRewardsUserParticipateCounts
WHERE UserId IN (SELECT
    UserId
  FROM tblUsersProfile
  WHERE (
  TotalBattleCount < 20
  AND Money < 200000
  AND LastMoveTime < DATEADD(DAY, -30, SYSUTCDATETIME())
  AND UserId IN (SELECT
    UserId
  FROM tblUsersPokemons
  GROUP BY UserId
  HAVING COUNT(Id) < 20)
  AND UserId NOT IN (SELECT
    UserId
  FROM tblUsersPokemons
  WHERE PokemonLevel > 90
  GROUP BY UserId)
  AND UserId > 3
  )
  OR UserId IN (SELECT
    UserId
  FROM tblBannedUsers)
  ORDER BY UserId ASC)

Not clear what you are trying to achieve here, what is the question? — Bulat, Aug 06 '15 at 13:49
@Bulat questin is preventing code duplication. The first query selects list of UserIds. At the later queries i have to use those lists — Furkan Gözükara, Aug 06 '15 at 13:50
@GordonLinoff something like that would work great. can you show example ty — Furkan Gözükara, Aug 06 '15 at 13:50
Aside: `ORDER BY` in `DELETE`s (without `TOP`) is always interesting. — HABO, Aug 06 '15 at 14:05

score 2 · Answer 1 · answered Aug 06 '15 at 14:11

2

Not sure exactly what you are trying to do here, but you could create a temp table to store your first query data and then use that do your rest of the deletes easily like this:

Temp Table Insert:

SELECT UserId
INTO #TempUsersProfile
FROM tblUsersProfile tu
WHERE (
        tu.TotalBattleCount < 20
        AND tu.[MONEY] < 200000
        AND tu.LastMoveTime < DATEADD(DAY, - 30, SYSUTCDATETIME())
        AND tu.UserId IN (
            SELECT UserId
            FROM tblUsersPokemons
            GROUP BY UserId
            HAVING COUNT(Id) < 20
            )
        AND NOT EXISTS (
            SELECT UserId
            FROM tblUsersPokemons tp
            WHERE tu.UserID = tp.UserId
                AND tp.PokemonLevel > 90
            GROUP BY UserId
            )
        AND UserId > 3
        )
    OR UserId IN (
        SELECT UserId
        FROM tblBannedUsers
        )
ORDER BY UserId ASC;

Delete Queries:

DELETE
FROM tblDailyRewardsParticipants
WHERE EventUserId IN (SELECT UserID FROM #TempUsersProfile);

DELETE
FROM tblDailyRewardsUserParticipateCounts
WHERE UserId IN (SELECT UserID FROM #TempUsersProfile);

Also, on a side note, I changed your NOT IN clause to use NOT EXISTS.

answered Aug 06 '15 at 14:11

FutbolFan

13,235
3
23
35

ty for answer what difference do they have in this scenario? i mean not in vs not exists ? – Furkan Gözükara Aug 06 '15 at 14:30
Sure, here is a good explanation on that: http://stackoverflow.com/questions/173041/not-in-vs-not-exists – FutbolFan Aug 06 '15 at 14:31
1

`The most important thing to note about NOT EXISTS and NOT IN is that, unlike EXISTS and IN, they are not equivalent in all cases. Specifically, when NULLs are involved they will return different results. To be totally specific, when the subquery returns even one null, NOT IN will not match any rows.` – FutbolFan Aug 06 '15 at 14:32
ty for answer. not in comes to me really easy to understand semantically though :D however i should get used to not exists i suppose – Furkan Gözükara Aug 06 '15 at 14:37
@MonsterMMORPG Absolutely agree with you, it makes more sense semantically when you think about using `NOT IN` as compared to `NOT EXISTS`. But overtime, I have gotten used to using `NOT EXISTS` and it seems to save me a lot of headaches. :) – FutbolFan Aug 06 '15 at 14:39

score 1 · Answer 2 · answered Aug 06 '15 at 14:13

1

Alternatively you can create a view for your select statement and call that in your delete statements something like.....

CREATE VIEW vw_UserIds_To_Delete
AS
SELECT UserId
FROM tblUsersProfile
WHERE (
    TotalBattleCount < 20
AND Money < 200000
AND LastMoveTime < DATEADD(DAY, -30, SYSUTCDATETIME())
.................. and so on.....

Then delete statements would look something like ....

DELETE FROM tblDailyRewardsParticipants
WHERE EXISTS (SELECT 1 
              FROM vw_UserIds_To_Delete 
              WHERE tblDailyRewardsParticipants.EventUserId = vw_UserIds_To_Delete.UserId)

etc etc

answered Aug 06 '15 at 14:13

M.Ali

67,945
13
101
127

view or temp table which one faster better ? what would be the difference in this particular scenario ? – Furkan Gözükara Aug 06 '15 at 14:30
temp table you execute the query once and store data in a temp table, now if the data has changed since you last stored the data into a temp table you may miss out some rows when actually deleting rows from that stored data set, view executes the whole query each time it is called, more data processing since it is running the query each time you call it but you will not accidentally miss out any rows. – M.Ali Aug 06 '15 at 14:33
Also I would replace all the `NOT IN` operators to `NOT EXISTS` in your select query, cuz of two reasons , 1) Nullable columns can confuse the `IN` operator 2) Many times I have experienced that `EXISTS` operator performs better than `IN` operator. – M.Ali Aug 06 '15 at 14:38
1

ty for answer i see. in my case i think temp table better. – Furkan Gözükara Aug 06 '15 at 14:38

score 0 · Answer 3 · edited Aug 06 '15 at 15:07

0

I think that insert can be trimmed down

SELECT UserId
INTO #TempUsersProfile
FROM tblUsersProfile tu
WHERE (
        tu.TotalBattleCount < 20
        AND tu.[MONEY] < 200000
        AND tu.LastMoveTime < DATEADD(DAY, - 30, SYSUTCDATETIME())
        AND tu.UserId IN ( 
            SELECT UserId
            FROM tblUsersPokemons
            WHERE tp.PokemonLevel <= 90
            AND UserId > 3
            GROUP BY UserId
            HAVING COUNT(Id) < 20
            )
      )
   OR UserId IN (
      SELECT UserId
      FROM tblBannedUsers
      )
ORDER BY UserId ASC;

edited Aug 06 '15 at 15:07

FutbolFan

13,235
3
23
35

answered Aug 06 '15 at 14:59

paparazzo

44,497
23
105
176

I agree! I thought about that as well. – FutbolFan Aug 06 '15 at 15:08

How to prevent code duplication at this sql query

3 Answers3