April 3, 2009 at 1:25 pm
Thang.Nguyen10 (4/3/2009)
Sorry guys, I was in the meeting until now. Here is the implicated tables relationship diagram. Is that enough or you need more information on table definition ?
Yes. Index definitions
Below are my enhanced script that I modified and updated, it needs 14 minutes to complete, is there anything else that I can do to improve the execution time ?
You still have a cross join since there's no join condition between users and usersubscriptions and you've now added a completely unnecessary subquery in the where clause.
SELECT u.USERID,
(CASE WHEN e.ISACTIVE = 1 THEN 'Active' ELSE 'Inactive' END) AS STATUS,
(CASE WHEN e.OPTOUT = 1 THEN 'Yes' ELSE 'No' END) AS OPTOUT,
ISNULL(u.LASTNAME,'') AS LASTNAME, ISNULL(u.FIRSTNAME,'') AS FIRSTNAME, ISNULL(u.MIDDLEINITIAL,'') AS MIDDLEINITIAL,
ISNULL(e.TITLE,'') AS TITLE,
ISNULL(u.EMAIL,'') AS CONTACTEMAIL,
ISNULL(e.JOBPOSITION,'') AS JOBPOSITION,
(select COMPANYNAME from dbo.tblCompany where COMPANYID=e.COMPANYID) as COMPANYNAME,
ISNULL((SELECT CHANNELNAME FROM TBLCHANNEL WHERE [CHANNELID]=(select [CHANNELID] from dbo.tblCompany where COMPANYID=e.COMPANYID)),'') as CHANNEL,
ISNULL((SELECT GROUPNAME FROM TBLGROUP WHERE [GROUPID]=(select [GROUPID] from dbo.tblCompany where COMPANYID=e.COMPANYID)),'') as [GROUP],
ISNULL(e.OFFICEPHONE,'') AS OFFICEPHONE,
ISNULL(e.MOBILEPHONE,'') AS MOBILEPHONE,
ISNULL(e.FAXNUMBER,'') AS FAXNUMBER,
(CASE WHEN e.COMPANYCONTACTADDRESS = 1 THEN 'Yes' ELSE 'No' END) AS COMPANYADDRESS,
ISNULL(e.ADDRESS1,'') AS ADDRESS1,
ISNULL(e.ADDRESS2,'') AS ADDRESS2,
ISNULL((SELECT COUNTRYNAME FROM TBLCOUNTRY WHERE TBLCOUNTRY.COUNTRYID=e.COUNTRYID),'') AS COUNTRY,
ISNULL(e.CITY,'') AS CITY,
ISNULL((CASE WHEN e.COUNTRYID=2 THEN st.STATENAME ELSE st.STATECODE END),'') AS STATENAME,
ISNULL(e.POSTALCODE,'') AS POSTALCODE,
ISNULL((SELECT INDUSTRYNAME FROM TBLINDUSTRY WHERE TBLINDUSTRY.INDUSTRYID=e.INDUSTRYID),'') AS INDUSTRY,
ISNULL((SELECT REGIONNAME FROM TBLPWCREGION WHERE TBLPWCREGION.REGIONID=CASE WHEN e.PWCREGIONID is not null then e.PWCREGIONID else e.REGIONID end),'') AS PWCREGION,
ISNULL(m.MARKETNAME ,'') AS PWCMARKET,
ISNULL((SELECT PWCOFFICENAME FROM TBLPWCOFFICES WHERE TBLPWCOFFICES.PWCOFFICEID=e.NEARESTPWCOFFICEID),'') AS PWCNEARESTOFFICE,
(CASE WHEN e.ISPWCCLIENT=1 THEN 'Yes' ELSE 'No' END) AS PWCCLIENT,
ISNULL(e.REFERREDPERSONEMAIL,'') AS REFERREDBY,
(CASE WHEN e.HIDEEMAILPHONEINFO=1 THEN 'Yes' ELSE 'No' END) AS HIDEEMAILPHONEINFO,
ISNULL(e.REASONFOROPTOUT,'') AS REASONFOROPTOUT,
ISNULL(e.REASONFORDEACTIVATE,'') AS REASONFORDEACTIVATE,
(CASE WHEN e.ISINVITATIONREQUIRED = 1 THEN 'Yes' ELSE 'No' END) AS INVITATIONREQUIRED
FROM dbo.tblUsers u
INNER JOIN dbo.tblExternalusers e ON u.USERID=e.USERID
INNER JOIN dbo.tblUsersubscription s ON < What is the join condition here? >
INNER JOIN dbo.tblUsertopicinterestmap x ON x.SUBSCRIPTIONID = s.SUBSCRIPTIONID
INNER JOIN dbo.tblPwCMarket m ON m.MARKETID=CASE WHEN e.PWCMARKETID is not null then e.PWCMARKETID else e.MARKETID end
INNER JOIN dbo.tblState st ON st.statecode=e.statecode
WHERE exists (select USERID from dbo.tblUserdesignees)
-- and u.USERID in (select USERID from dbo.tblUsers) -- Completely unnecessary. The users table is already part of teh query, so why are you checking that rows in the users table match rows in the users table?
and e.SELFSUBSCRIPTION in (0,1)
and e.ISACTIVE=1
----
AND x.TOPICID in (528)
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
April 3, 2009 at 2:14 pm
WHERE exists (select USERID from dbo.tblUserdesignees)This will return true if there are any rows in the table dbo.tblUserdesignees.
Do you mean this?WHERE EXISTS (SELECT 1 FROM dbo.tblUserdesignees WHERE USERID = u.USERID)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 7, 2009 at 7:15 am
GilaMonster (4/3/2009)
Christopher Stobbs (4/3/2009)
WHERE
/*
isnull(FIRSTNAME,'') like '%%'
AND isnull(MIDDLEINITIAL,'') like '%%'
AND isnull(LASTNAME,'') like '%%'
AND isnull(EMAIL,'') like '%%'
*/
This can't be removed, at least until we know why it's there in the first place. The initial one I posted had the spaces eaten. It should be
isnull(FIRSTNAME,'') like '% %'AND isnull(MIDDLEINITIAL,'') like '% %'
AND isnull(LASTNAME,'') like '% %'
AND isnull(EMAIL,'') like '% %'
Gila, I took the code out since it just check for null space between names (it's irrelevant).
April 7, 2009 at 7:24 am
GilaMonster (4/3/2009)
Thang.Nguyen10 (4/3/2009)
Sorry guys, I was in the meeting until now. Here is the implicated tables relationship diagram. Is that enough or you need more information on table definition ?Yes. Index definitions
Below are my enhanced script that I modified and updated, it needs 14 minutes to complete, is there anything else that I can do to improve the execution time ?
You still have a cross join since there's no join condition between users and usersubscriptions and you've now added a completely unnecessary subquery in the where clause.
SELECT u.USERID,(CASE WHEN e.ISACTIVE = 1 THEN 'Active' ELSE 'Inactive' END) AS STATUS,
(CASE WHEN e.OPTOUT = 1 THEN 'Yes' ELSE 'No' END) AS OPTOUT,
ISNULL(u.LASTNAME,'') AS LASTNAME, ISNULL(u.FIRSTNAME,'') AS FIRSTNAME, ISNULL(u.MIDDLEINITIAL,'') AS MIDDLEINITIAL,
ISNULL(e.TITLE,'') AS TITLE,
ISNULL(u.EMAIL,'') AS CONTACTEMAIL,
ISNULL(e.JOBPOSITION,'') AS JOBPOSITION,
(select COMPANYNAME from dbo.tblCompany where COMPANYID=e.COMPANYID) as COMPANYNAME,
ISNULL((SELECT CHANNELNAME FROM TBLCHANNEL WHERE [CHANNELID]=(select [CHANNELID] from dbo.tblCompany where COMPANYID=e.COMPANYID)),'') as CHANNEL,
ISNULL((SELECT GROUPNAME FROM TBLGROUP WHERE [GROUPID]=(select [GROUPID] from dbo.tblCompany where COMPANYID=e.COMPANYID)),'') as [GROUP],
ISNULL(e.OFFICEPHONE,'') AS OFFICEPHONE,
ISNULL(e.MOBILEPHONE,'') AS MOBILEPHONE,
ISNULL(e.FAXNUMBER,'') AS FAXNUMBER,
(CASE WHEN e.COMPANYCONTACTADDRESS = 1 THEN 'Yes' ELSE 'No' END) AS COMPANYADDRESS,
ISNULL(e.ADDRESS1,'') AS ADDRESS1,
ISNULL(e.ADDRESS2,'') AS ADDRESS2,
ISNULL((SELECT COUNTRYNAME FROM TBLCOUNTRY WHERE TBLCOUNTRY.COUNTRYID=e.COUNTRYID),'') AS COUNTRY,
ISNULL(e.CITY,'') AS CITY,
ISNULL((CASE WHEN e.COUNTRYID=2 THEN st.STATENAME ELSE st.STATECODE END),'') AS STATENAME,
ISNULL(e.POSTALCODE,'') AS POSTALCODE,
ISNULL((SELECT INDUSTRYNAME FROM TBLINDUSTRY WHERE TBLINDUSTRY.INDUSTRYID=e.INDUSTRYID),'') AS INDUSTRY,
ISNULL((SELECT REGIONNAME FROM TBLPWCREGION WHERE TBLPWCREGION.REGIONID=CASE WHEN e.PWCREGIONID is not null then e.PWCREGIONID else e.REGIONID end),'') AS PWCREGION,
ISNULL(m.MARKETNAME ,'') AS PWCMARKET,
ISNULL((SELECT PWCOFFICENAME FROM TBLPWCOFFICES WHERE TBLPWCOFFICES.PWCOFFICEID=e.NEARESTPWCOFFICEID),'') AS PWCNEARESTOFFICE,
(CASE WHEN e.ISPWCCLIENT=1 THEN 'Yes' ELSE 'No' END) AS PWCCLIENT,
ISNULL(e.REFERREDPERSONEMAIL,'') AS REFERREDBY,
(CASE WHEN e.HIDEEMAILPHONEINFO=1 THEN 'Yes' ELSE 'No' END) AS HIDEEMAILPHONEINFO,
ISNULL(e.REASONFOROPTOUT,'') AS REASONFOROPTOUT,
ISNULL(e.REASONFORDEACTIVATE,'') AS REASONFORDEACTIVATE,
(CASE WHEN e.ISINVITATIONREQUIRED = 1 THEN 'Yes' ELSE 'No' END) AS INVITATIONREQUIRED
FROM dbo.tblUsers u
INNER JOIN dbo.tblExternalusers e ON u.USERID=e.USERID
INNER JOIN dbo.tblUsersubscription s ON < What is the join condition here? >
INNER JOIN dbo.tblUsertopicinterestmap x ON x.SUBSCRIPTIONID = s.SUBSCRIPTIONID
INNER JOIN dbo.tblPwCMarket m ON m.MARKETID=CASE WHEN e.PWCMARKETID is not null then e.PWCMARKETID else e.MARKETID end
INNER JOIN dbo.tblState st ON st.statecode=e.statecode
WHERE exists (select USERID from dbo.tblUserdesignees)
-- and u.USERID in (select USERID from dbo.tblUsers) -- Completely unnecessary. The users table is already part of teh query, so why are you checking that rows in the users table match rows in the users table?
and e.SELFSUBSCRIPTION in (0,1)
and e.ISACTIVE=1
----
AND x.TOPICID in (528)
Gila, thanks. I tried this script and it was running amazingly fast, however, just one small difference, I got 3736 rows. And your script gave 96 rows.
April 7, 2009 at 7:40 am
Thang.Nguyen10 (4/7/2009)
Gila, thanks. I tried this script and it was running amazingly fast, however, just one small difference, I got 3736 rows. And your script gave 96 rows.
Probably because I put an inner join in where you had a cross join. It's up to you to tell which is correct.
What you had before was a query that would return every single possible combination of users and user subscriptions. So, say you had 5 users (1 - 5) and each had 1 subscription, a proper join will get you 5 rows, as follows
[font="Courier New"]
User Subscription
1 A
2 B
3 C
4 D
5 E
[/font]
A cross join (which is what you had) would get you 25 rows, as such
[font="Courier New"]
User Subscription
1 A
1 B
1 C
1 D
1 E
2 A
2 B
2 C
2 D
2 E
3 A
3 B
3 C
3 D
3 E
4 A
4 B
4 C
4 D
4 E
5 A
5 B
5 C
5 D
5 E
[/font]
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
April 7, 2009 at 8:18 am
Gila, I think I had to explain this way so you can have a better idea about what I am trying to get.
For a certain Publication (which might have a topic)
| TopicID (528)
|
tblTopicInterestMap : we get the SubscriptionID
| SubscriptionID
|
tblUserSubscription: we get the UserID & StatusId (Active or non-Active)
| UserID |
| |
| tblUsers (which store name, email, GUID, Employee No.)
| | UserID
| |
| tblPwCBusinessUnit --> For Unit Name & Address
| tblUserDesignees --> For OwnerID (which is also UserID)
|
tblExternalUsers (For Employee Adresss, RegionID, StateCode, PostalCode....)
|
tblPwCMarket (MarketID) --> Market Name
tblCountry (CountryID) --> Country Name
tblPwCRegion (RegionID) --> Region Name
tblPwCOffices (PwCOfficeID) --> PwC Office Name & Address
tblIndustry (IndustryID) --> Country Name
tlbState (StateCode) --> State Name
tblChannel(ChannelID) --> Channel Name
tblGroup (GroupID) --> Group Name
April 7, 2009 at 8:27 am
So you do need a join between users and usersubscriptions, which you did not have in your initial query and which my rewritten one did.
Still, you know the data, you know what you want so you're the one who can tell whether that 96 rows is correct or whether that 3000+ rows is correct.
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
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply