SQL Server Performance Forum – Threads Archive

Eliminate INActive Child ID’s within N tables

Here is the scenario
I have two or more tables : Person and subscription.
For 1 personid from person table there can be multiple subscriptionid’s in the subscription table.
After joining the tables using common column I need to select only
those personid corresponding to subscriptionID whose status is inactive or expired(4,5,6,7,8,9,10,13).hence eliminate the id’s (1,2,3,11,12) from subscription table
I am using the following query but somehow the active subscriptions are
populated .I tried to filter out the active subscriptions in so many ways but its just not working out.Please help me solve this issue its a deadline for me.
Thanks in Advance
Here is the query
SELECT distinct
isnull(P.id,”) PersonID
, FirstName
, isnull(replace(Ltrim(Rtrim(P.lastname)),’,’,”),”) LastName
, State = isnull(replace(Ltrim(Rtrim(PA.State)),’,’,”),”)
, b.code
, p.company
, rc.RevenueCategoryid
FROM vwpersons p with (nolock)
join vwsubscriptions s on p.id = s.recipientid
join vwcompanies c WITH (NOLOCK) on c.id = p.companyid
join vwbusinesscodes b WITH (NOLOCK) on b.id = c.primarybusinesscode
join vwRevenueCategorys rc WITH (NOLOCK)on rc.id = c.RevenueCategoryid
join vwPersonPrefAddr pa WITH (NOLOCK) on p.id = pa.id
where NOT EXISTS (SELECT s1.recipientid FROM vwsubscriptions S1 WHERE S1.SHIPTOID =S.SHIPTOID AND S.PRODUCTID = S1.PRODUCTID AND S1.STATUSID IN (1,2,3,11,12) –Active Members
AND S1.STATUS NOT like ‘active’)
and s.statusid in (4,5,6,7,8,9,10,13) –Inactive Members
group by p.id , p.firstname,p.lastname,
p.company,pa.state, b.code
p.company,rc.RevenueCategoryid
)

---

You don’t give a simple description for what exactly you need to do, so I suggest you start by describing in everyday words what it is … (1) delete all inactive subscriptions? (2) delete persons with no active subscriptions? (3) delete inactive subscriptions for persons only if they still have active subscriptions? (4) ………………….. ?

---

Hi Adriaan
I am just doing a select statement from multiple tables with different search criterion so i cannot delete any rows from tables.I am focusing here on 2 tables person and subscription table.For 1 personid in person table there are 2 or more subscription id’s in subscription table whose status is either active statusid = (1,2,3,11,12) or expired,cancelled,denied status id =(4,5,6,7,8,9,10,13)).Hence i am trying here to join person and subscription table using the
columns p.id = s.recipientid in person and subsciption table respectively.The column statusid in subscription table decides if it is in a active status or expired status.
I have to pick up subscriptionid’s from subscription table whose statusID is inactive.
hope that is clear

---

Let me get this straight …
* subscriptionid is a unique identifier for the Subscription table
* there’s a statusid column in the Subscription table as well
* you want to filter on statusid 4, 5, 6, 7, 8, 9, 10, 13
* you need only the subscriptionid’s SELECT S.subscriptionid
FROM Subscription S
WHERE S.statusid IN (4, 5, 6, 7, 8, 9, 10, 13) Now what you’re not telling us – I’m mostly guessing here – is that you want to filter for subscriptionid’s where there is no active subscription with the same recipientid + shiptoid + productid. If that is the object of the query, then I would use a single NOT EXISTS correlated subquery to check for active cases with the same recipientid + shiptoid + productid, and filter the main query for inactive statusid’s. SELECT <column list>
FROM Subscription I
<joins to other tables/views>
WHERE I.statusid IN (4, 5, 6, 7, 8, 9, 10, 13)
AND NOT EXISTS
(SELECT A.recipientid FROM Subscription A
WHERE A.statusid IN (1, 2, 3, 11, 12)
AND A.recipientid = i.recipientid
AND I.shipto = A.shipto
AND I.productid = A.productid)

---

Hi Adriaan
Thanks for your query which i have implemented.
Here is my query which is taking atleast 4 hours to run.I have run on different servers but its taking the same amount of time.
Thanks for your quick response
SELECT <column list>
FROM vwpersons p with (nolock)
join vwsubscriptions s on p.id = s.recipientid
join vwcompanies c WITH (NOLOCK) on c.id = p.companyid
join vwbusinesscodes b WITH (NOLOCK) on b.id = c.primarybusinesscode
join vwRevenueCategorys rc WITH (NOLOCK)on rc.id = c.RevenueCategoryid
join vwPersonPrefAddr pa WITH (NOLOCK) on p.id = pa.id
WHERE NOT EXISTS (SELECT s1.recipientid FROM vwsubscriptions S1 WHERE S1.SHIPTOID =S.SHIPTOID
AND S.PRODUCTID = S1.PRODUCTID AND S1.STATUSID IN (1,2,3,11,12) )
and p.id in (select recordid from vwTopicCodeLinks tcl
Where tcl.entityid = 457
And tcl.topiccodeid = 332
And tcl.value = ‘Yes’)
and ((p.PreferredAddress = ‘Business Address’ and p.BadBusinessAddress = 0)
or (p.PreferredAddress = ‘PO Box Address’ and p.BadPOBoxAddress = 0)
or (p.PreferredAddress = ‘Home Address’ and p.BadHomeAddress = 0))
and b.code in ( ‘M2′,’V2′,’V5′,’W1′,’W2′,’W3′,’W4’
,’W5′,’W6′,’W7′,’W8′,’W9′,’WZ’,’X2′,’X3′,’X4′)
and p.status <> 5
and s.statusid in (4,5,6,7,8,9,10,13)
and s.productid in (23)
group by b.code ,p.id , p.firstname,p.lastname,p.MiddleName,p.suffix,
p.company,pa.addressline1,pa.addressline2,pa.city,pa.state,pa.zipcode

---

The same query without a not exists block is executing in 5 min.
Also there are some id which still have active memberships.
Dont know what .I dont know where the leak is which is populating the active status from the subscription table.
Again here is the subscription table
CREATE TABLE [dbo].[Subscription] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SubscriberID] [int] NOT NULL , –PrimaryKey REFERENCES [dbo].[Person] ([ID])
[ShipToID] [int] NOT NULL ,
[StatusID] [int] NOT NULL ,
[Status] [nvarchar] (50) NULL ,
[ProductID] [int] NOT NULL ,
[Product] [nvarchar] (50) NULL ,
[Subscriber] [nvarchar] (100) NULL ,
[SubscriberCompany] [nvarchar] (100) NULL ,
[Recipient] [nvarchar] (100) NULL ,
[RecipientCompany] [nvarchar] (100) NULL ,
) ON [PRIMARY]
GO Here is the person table
CREATE TABLE [dbo].[Person] (
[ID] [int] IDENTITY (1, 1) NOT NULL CONSTRAINT [pkey_Person] PRIMARY KEY CLUSTERED ,
[NameWCompany] [nvarchar] (120) NULL ,
[FirstName] [nvarchar] (20) NOT NULL ,
[MiddleName] [nvarchar] (20) NULL ,
[LastName] [nvarchar] (20) NULL ,
[Suffix] [nchar] (10) NULL ,
[CompanyID] [int] NOT NULL ,
[Title] [nvarchar] (50) NULL ,
[AddressLine1] [nvarchar] (100) NOT NULL ,
[AddressLine2] [nvarchar] (100) NULL ,
[AddressLine3] [nvarchar] (100) NULL ,
[City] [nvarchar] (50) NOT NULL ,
[County] [nvarchar] (50) NULL ,
[State] [nvarchar] (25) NOT NULL ,
[Country] [nvarchar] (30) NULL ,
[ZipCode] [nvarchar] (25) NOT NULL ,
[PhoneCountryCode] [nchar] (5) NOT NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

---

To show you the result for this scenario here are the results
Like for personID = 24324
I have the results from the subscription table as
ID Status Product
1599 Expired Associate
85456 Active Builder
8530 Active Builder
8341 Expired builder
I am talking about this status for personid 24324
Also i am using views which are same as the tables

---

Feed this to the Index Tuning Wizard in Enterprise Manager. Look at the Subscription table, note that it has both a StatusId and a Status nvarchar column – sign of poor normalization. You SHOULD have a lookup table for Status, with an Active BIT column. If you can’t change that, then add a temp working table to your procedure, for just the statusid + active, and use that for the filtering. Call me ignorant, but I don’t much like views, especially not as a basis for queries. Query the tables directly (provided you have the necessary permissions).

---

Adriaan
here in the subscription table the status id stand for status
like
statuid’s (1,2,3,11,12) stand for status (Active,NewMember,Reinstated etc),
statusid’s(4,5,6,7,8,9,10,13) stand for status (InActive,Expired,Cancelled,revoked,etc)
I am able to get the id’s and the status from a single table so why shld i use another lookup table.
A lookup table would end up in another join hence more i/o.
Also i dont understand why the no exists line is causing this performance impact,if i dont have the not exists line i am getting the resultset within a minute.
This thing is getting intresting to me

---

When NOT EXISTS doesn’t work out, you can always test this syntax: LEFT JOIN outertable WHERE outertable.non_null_join_column IS NULL Building the join might take less than evaluating NOT EXISTS. SELECT <column list>
FROM Subscription I
<joins to other tables/views>
LEFT JOIN
(SELECT X.recipientid, X.shipto, X.productid FROM Subscription X
WHERE X.statusid IN (1, 2, 3, 11, 12)) A
ON I.recipientid = A.recipientid AND I.shipto = A.shipto AND I.productid = A.productid
WHERE A.recipientid IS NULL AND I.statusid IN (4, 5, 6, 7, 8, 9, 10, 13) But there are no guarantees …

---

Actually it would be probably less i/o. Let’s assume average size of status column is 8 bytes. 1. If you have 100,000 rows in subscriptions table additional space occupied using your design is 800,000 bytes while in lookup table it takes only 8 bytes in average.
2. If your query access 100 rows from your table it has to read 100X8 bytes which is 100 times more then if it is in the lookup table.
3. If you ever decide to modify status column text you have to change it for each occurence of its value in subscription column.
4. If there is no row with specific status subscriber table you don’t have information anywhere in database that status is possible. That problem more affects your company related redudant columns.
5. When you need to introduce new status you have to hard-code its handling instead of just reading from db new lookup value.
… and so on.

---

]]>