July 30, 2014 at 5:26 pm
Okay, yes I am a SQL newbie 😉 and I am seriously hoping someone can assist with an issue I have been presented with.
Issue in a nut shell, I have been tasked with comparing two tables "GLAMF" and "GLPOST" both tables have a common column "ACCTID". GLAMF contains account information where GLPOST contains financial data linking back to the GLAMF table.
I was asked to identify accounts within the GLAMF table that where/are inactive (never used), this was simple enough, to do this I used the following query
SELECT ACCTID from GLAMF
EXCEPT
SELECT ACCTID from GLPOST
This presented me with the unused accounts, now the headache started. I need to somehow, using the output from the previous query, modify/update a column within the GLAMF table to indicate the inactive accounts, the "Activesw", this dictates whether the account is active or inactive (0=inactive, 1=active), if this was just a few records I would have done this manually but I am sitting with just under a million records that need to be amended.
Has anyone got any ideas or able to point me in a direction?
July 30, 2014 at 5:55 pm
UPDATE GLAMF
SET Activesw = 0
WHERE ACCTID NOT IN
(SELECT ACCTID from GLPOST)
July 30, 2014 at 5:58 pm
cwarden 74876 (7/30/2014)
Okay, yes I am a SQL newbie 😉 and I am seriously hoping someone can assist with an issue I have been presented with.Issue in a nut shell, I have been tasked with comparing two tables "GLAMF" and "GLPOST" both tables have a common column "ACCTID". GLAMF contains account information where GLPOST contains financial data linking back to the GLAMF table.
I was asked to identify accounts within the GLAMF table that where/are inactive (never used), this was simple enough, to do this I used the following query
SELECT ACCTID from GLAMF
EXCEPT
SELECT ACCTID from GLPOST
This presented me with the unused accounts, now the headache started. I need to somehow, using the output from the previous query, modify/update a column within the GLAMF table to indicate the inactive accounts, the "Activesw", this dictates whether the account is active or inactive (0=inactive, 1=active), if this was just a few records I would have done this manually but I am sitting with just under a million records that need to be amended.
Has anyone got any ideas or able to point me in a direction?
Here is an UPDATE statement. You may want to write a SELECT version and see if it pulls the rows that need to be updated.
UPDATE dbo.GLAMF SET
Activesw = 0
WHERE
ACCTID in (
SELECT ACCTID from GLAMF
EXCEPT
SELECT ACCTID from GLPOST
)
July 30, 2014 at 11:55 pm
autoexcrement (7/30/2014)
UPDATE GLAMFSET Activesw = 0
WHERE ACCTID NOT IN
(SELECT ACCTID from GLPOST)
:exclamation: This query will work fine is GLPOST is not empty. But if it is empty, the condition will become :
WHERE ACCTID NOT IN (null)
which will never be true neither false !). So no line will be updated, instead of all lines :hehe:
Always be careful with NOT IN. Prefer Lynn Pettis solution : IN
July 31, 2014 at 8:05 am
Thank you, I was wondering why Lynn wrote it that way! Makes sense.
July 31, 2014 at 8:18 am
Roland C (7/30/2014)
autoexcrement (7/30/2014)
UPDATE GLAMFSET Activesw = 0
WHERE ACCTID NOT IN
(SELECT ACCTID from GLPOST)
:exclamation: This query will work fine is GLPOST is not empty. But if it is empty, the condition will become :
WHERE ACCTID NOT IN (null)which will never be true neither false !). So no line will be updated, instead of all lines :hehe:
Not quite.
NOT IN has the unexpected behaviour you describe when any of the rows in the subquery have the value NULL for the column, not when it's an empty result set. When it's an empty result set, the NOT IN returns all rows, exactly as expected.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2014 at 12:05 am
Thank you Gila Monster.
Clearly I made a mistake, sorry :blush:
I think I remembered it this way because in my mind I saw the logic behind. Now I don't quite understand the logic of the real behaviour. Could you please explain us ?
Roland
August 1, 2014 at 1:11 am
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2014 at 2:19 am
Very clear, thank you !
August 1, 2014 at 4:38 pm
Hi
Although the above mentioned queries will work I have learned not to do Inner select as this creates over head on the tempdb on the server.
An inner select is sql of this form
-- Select
select a.*
from (
--Inner Selects
Select a.<some column> ,b.<some column>
from <Table A> a
join <Table B> b on a.1=b.1
where <some clause>
)a
I suggest something like this.
-- Update records that cant be found in GLPOST
update a
set Activesw = 0
from GLAMF a
left join GLPOST b with (nolock)
on a.ACCTID = b.ACCTID
where b.ACCTID is null
Hope this add a bit more knowledge.
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
August 1, 2014 at 4:50 pm
Daniel Matthee (8/1/2014)
HiAlthough the above mentioned queries will work I have learned not to do Inner select as this creates over head on the tempdb on the server.
An inner select is sql of this form
-- Select
select a.*
from (
--Inner Selects
Select a.<some column> ,b.<some column>
from <Table A> a
join <Table B> b on a.1=b.1
where <some clause>
)a
I suggest something like this.
-- Update records that cant be found in GLPOST
update a
set Activesw = 0
from GLAMF a
left join GLPOST b with (nolock)
on a.ACCTID = b.ACCTID
where b.ACCTID is null
Hope this add a bit more knowledge.
I'd drop the NOLOCK hint as you really don't want a dirty read at this point.
August 2, 2014 at 10:29 am
Daniel Matthee (8/1/2014)
I have learned not to do Inner select as this creates over head on the tempdb on the server.
You have, of course, a reference or an example showing this? Showing that a derived table (what you call an inner select) has overhead on TempDB?
p.s. why nolock? You do know what that does, don't you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2014 at 10:58 am
Hi
Please help me if I am wrong. But usually when i do an inner select and look at sql monitor the physical reads, or even sp_who2 active the amount of threads that are open (but suspended) are why more than when you do the way i suggest.
By all means if you disagree, can you explain why you are saying what you are saying? That why we all are learning.
Kind Regards
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
August 2, 2014 at 11:15 am
I'm saying that's a pretty extreme statement to make, claiming that all subqueries and derived tables cause TempDB overhead (which, btw, would not show as physical reads, they'd show as logical reads from a worktable, unless there's so little memory available that SQL's having to force TempDB pages out of cache). Extraordinary claims, such as that, require either a large number of reproducible examples or a reputable reference, or they're like someone claiming that the sky is purple. Especially when it's trivial to show that the example you gave does not have any TempDB overhead and, in fact, has an identical execution plan as an equivalent query without the derived table, and that NOT IN with subqueries has a slightly lower cost and the same reads as the equivalent query with LEFT OUTER JOIN ... IS NULL (see the link I posted earlier)
Are the suspended sessions you mentioned related in any way to the running query? Are you seeing additional IO on the TempDB files? Additional allocations in TempDB? Waits indicative of IO with resources related to TempDB? What table are the physical reads being done against? Are you clearing the cache between executions (which would cause reads to be physical)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2014 at 11:59 am
GilaMonster (8/2/2014)
I'm saying that's a pretty extreme statement to make, claiming that all subqueries and derived tables cause TempDB overhead (which, btw, would not show as physical reads, they'd show as logical reads from a worktable, unless there's so little memory available that SQL's having to force TempDB pages out of cache). Extraordinary claims, such as that, require either a large number of reproducible examples or a reputable reference, or they're like someone claiming that the sky is purple. Especially when it's trivial to show that the example you gave does not have any TempDB overhead and, in fact, has an identical execution plan as an equivalent query without the derived table, and that NOT IN with subqueries has a slightly lower cost and the same reads as the equivalent query with LEFT OUTER JOIN ... IS NULL (see the link I posted earlier)Are the suspended sessions you mentioned related in any way to the running query? Are you seeing additional IO on the TempDB files? Additional allocations in TempDB? Waits indicative of IO with resources related to TempDB? What table are the physical reads being done against? Are you clearing the cache between executions (which would cause reads to be physical)?
Coolio, I will then hereby take back that statement. Sorry for that.
Can you please explain then when using an inner select statement (to minimize the sample )on huge table. (+- 20 columns, 400mil rows) the query runs WAY longer than actually putting the minimized sampel into and temp table and then reading from it.
I am still new to SQL so by all means I am not an expert and only saying what I experience.
Kind Regards
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply