April 3, 2009 at 7:30 am
[font="Arial Narrow"]
SELECT A.USERID,A.STATUS AS [Status],A.OPTOUT AS [Opt-Out], A.LASTNAME AS [Last Name],A.FIRSTNAME AS [First Name],A.MIDDLEINITIAL AS [Middle Initial], A.TITLE AS [Title],A.CONTACTEMAIL AS [Contact Email],A.JOBPOSITION AS [Job Position],A.COMPANYNAME AS [Company Name],A.CHANNEL AS [Channel], A.[GROUP] AS [Group],A.OFFICEPHONE AS [Office Phone],A.MOBILEPHONE AS [Mobile Phone],A.FAXNUMBER AS [Fax Number], A.COMPANYADDRESS AS [Company Address?],A.ADDRESS1 AS [Address 1],A.ADDRESS2 AS [Address 2],A.COUNTRY AS [Country],A.CITY AS [City], A.STATENAME AS [State/Province],A.POSTALCODE AS [Postal Code],A.INDUSTRY AS [Industry],A.PWCREGION AS [PwC Region],
A.PWCMARKET AS [PwC Market],A.PWCNEARESTOFFICE AS [PwC Nearest Office],A.PWCCLIENT AS [PwC Client?],'' AS [Owner(s)],
A.REFERREDBY AS [Referred by],A.HIDEEMAILPHONEINFO AS [Hide Email & Phone],A.REASONFOROPTOUT AS [Reason for Opt-Out],
A.REASONFORDEACTIVATE AS [Reason for Deactivate],A.INVITATIONREQUIRED AS [Invitation Required]
FROM
(SELECT DISTINCT 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(e.TITLE,'') AS TITLE,ISNULL(u.FIRSTNAME,'') AS FIRSTNAME,
ISNULL(u.MIDDLEINITIAL,'') AS MIDDLEINITIAL,ISNULL(u.LASTNAME,'') AS LASTNAME,ISNULL(u.EMAIL,'') AS CONTACTEMAIL,
ISNULL(e.JOBPOSITION,'') AS JOBPOSITION,(select COMPANYNAME from dbo.tblCompany where COMPANYID=e.COMPANYID) as COMPANYNAME,
ISNULL((SELECT GROUPNAME FROM TBLGROUP WHERE [GROUPID]=(select [GROUPID] from dbo.tblCompany where COMPANYID=e.COMPANYID)),'') as [GROUP],
ISNULL((SELECT CHANNELNAME FROM TBLCHANNEL WHERE [CHANNELID]=(select [CHANNELID] from dbo.tblCompany where COMPANYID=e.COMPANYID)),'') as CHANNEL,
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 (SELECT STATENAME FROM TBLSTATE WHERE TBLSTATE.STATECODE=e.STATECODE) ELSE 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((SELECT MARKETNAME FROM TBLPWCMARKET WHERE TBLPWCMARKET.MARKETID=CASE WHEN e.PWCMARKETID is not null then e.PWCMARKETID else e.MARKETID end),'') 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,dbo.tblExternalusers e ,dbo.tblUsersubscription s ,dbo.tblUsertopicinterestmap x
where
u.USERID in
(select USERID from dbo.tblUsers where isnull(FIRSTNAME,'') like '%%'
and isnull(MIDDLEINITIAL,'') like '%%' and isnull(LASTNAME,'') like '%%'
and isnull(EMAIL,'') like '%%')
and u.USERID=e.USERID
and e.SELFSUBSCRIPTION in (0,1)
and e.ISACTIVE=1
AND u.userid in ( select USERID from dbo.tblUserdesignees) AND x.SUBSCRIPTIONID = s.SUBSCRIPTIONID AND x.TOPICID IN (528) ) A[/font]
April 3, 2009 at 7:33 am
The DBA advise me to create indexes for tblUserDesignees (which has only 2 fields OwnerID and UserID), follow which I create 2 cluster indexes for each of the fields. It improve a little bit but this SQL is still running for more than 1 hour (I don't know if it ever finished it run).
Thanks for your helps, all. I really appreciate it.
April 3, 2009 at 7:42 am
Is it blocked by some other process?
Can you post table definitions, index definitions, aprox row counts and the execution plan, saved as a .sqlplan file, zipped and attached?
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 8:01 am
Let's start at the beginning. You will want to test any suggestions without affecting the data, so I've stripped out the INSERT part leaving the SELECT which feeds it. Some formatting too - you're quite new here so I've done that for you, but it would be nice of you did this in future. Here's the bare SELECT:
SELECT DISTINCT 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(e.TITLE,'') AS TITLE,
ISNULL(u.FIRSTNAME,'') AS FIRSTNAME,
ISNULL(u.MIDDLEINITIAL,'') AS MIDDLEINITIAL,
ISNULL(u.LASTNAME,'') AS LASTNAME,
ISNULL(u.EMAIL,'') AS CONTACTEMAIL,
ISNULL(e.JOBPOSITION,'') AS JOBPOSITION,
--
(select COMPANYNAME from dbo.tblCompany where COMPANYID=e.COMPANYID) as COMPANYNAME,
--
ISNULL((SELECT GROUPNAME FROM TBLGROUP
WHERE [GROUPID]=(select [GROUPID] from dbo.tblCompany where COMPANYID=e.COMPANYID)),'') as [GROUP],
--
ISNULL((SELECT CHANNELNAME FROM TBLCHANNEL
WHERE [CHANNELID]=(select [CHANNELID] from dbo.tblCompany where COMPANYID=e.COMPANYID)),'') as CHANNEL,
--
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 (SELECT STATENAME FROM TBLSTATE WHERE TBLSTATE.STATECODE=e.STATECODE) ELSE 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((SELECT MARKETNAME FROM TBLPWCMARKET WHERE TBLPWCMARKET.MARKETID=CASE WHEN e.PWCMARKETID is not null then e.PWCMARKETID else e.MARKETID end),'') 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,
dbo.tblExternalusers e ,
dbo.tblUsersubscription s ,
dbo.tblUsertopicinterestmap x
--
where u.USERID in
(select USERID
from dbo.tblUsers
where isnull(FIRSTNAME,'') like '%%'
and isnull(MIDDLEINITIAL,'') like '%%'
and isnull(LASTNAME,'') like '%%'
and isnull(EMAIL,'') like '%%')
--
and u.USERID=e.USERID
and e.SELFSUBSCRIPTION in (0,1)
and e.ISACTIVE=1
AND u.userid in ( select USERID from dbo.tblUserdesignees)
AND x.SUBSCRIPTIONID = s.SUBSCRIPTIONID
AND x.TOPICID IN (528)
Well, it's still a horrible piece of code but there's plenty of room for improvement. How about you run this and see how long it takes, as a baseline? After that, I'd suggest changing everything below the keyword FROM.
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 3, 2009 at 8:22 am
Looking at the nicely cleaned up version posted by Chris (Thanks Doc) part of your problem is fairly easily apparent.
You have a cross join in there. The users and the external users tables are joined and the usersubscriptions and topincinterests tables are joined, but there's no join between them. The first thing I would suggest is you fix that. By doing so, you can probably remove the distincts.
There's also an unneccessary subquery in the where clause. You've got a subquery on users, but the users table is part of the query already.
So, converting to ANSI joins, removing distincts and fixing 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(e.TITLE,'') AS TITLE,
ISNULL(u.FIRSTNAME,'') AS FIRSTNAME,
ISNULL(u.MIDDLEINITIAL,'') AS MIDDLEINITIAL,
ISNULL(u.LASTNAME,'') AS LASTNAME,
ISNULL(u.EMAIL,'') AS CONTACTEMAIL,
ISNULL(e.JOBPOSITION,'') AS JOBPOSITION,
---
(select COMPANYNAME from dbo.tblCompany where COMPANYID=e.COMPANYID) as COMPANYNAME,
---
ISNULL((SELECT GROUPNAME FROM TBLGROUP
WHERE [GROUPID]=(select [GROUPID] from dbo.tblCompany where COMPANYID=e.COMPANYID)),'') as [GROUP],
---
ISNULL((SELECT CHANNELNAME
FROM TBLCHANNEL Channel INNER JOIN dbo.tblCompany comp ON Channel.ChannelID = comp.ChannelID
WHERE COMPANYID=e.COMPANYID),'') as CHANNEL,
---
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 (SELECT STATENAME FROM TBLSTATE WHERE TBLSTATE.STATECODE=e.STATECODE) ELSE 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((SELECT MARKETNAME FROM TBLPWCMARKET
WHERE TBLPWCMARKET.MARKETID=CASE WHEN e.PWCMARKETID is not null then e.PWCMARKETID else e.MARKETID end),'') 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 < Join criteria for these tables needed here >
INNER JOIN dbo.tblUsertopicinterestmap x ON x.SUBSCRIPTIONID = s.SUBSCRIPTIONID
---
WHERE isnull(FIRSTNAME,'') like '% %'
AND isnull(MIDDLEINITIAL,'') like '% %'
AND isnull(LASTNAME,'') like '% %'
AND isnull(EMAIL,'') like '% %'
---
AND e.SELFSUBSCRIPTION in (0,1)
AND e.ISACTIVE=1
AND u.userid in ( select USERID from dbo.tblUserdesignees)
AND x.TOPICID = 528
What is this piece intended to do?
where isnull(FIRSTNAME,'') like '% %'
and isnull(MIDDLEINITIAL,'') like '% %'
and isnull(LASTNAME,'') like '% %'
and isnull(EMAIL,'') like '% %'
What are you trying to eliminate?
Edit: Code formatting ate my spaces....
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 8:53 am
GilaMonster (4/3/2009)
Looking at the nicely cleaned up version posted by Chris (Thanks Doc) part of your problem is fairly easily apparent.
You're welcome, poppet.
Next thing I'd do is test out some of those correlated subqueries for performance, against the JOINed equivalent, starting with the Company table:
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(e.TITLE,'') AS TITLE,
ISNULL(u.FIRSTNAME,'') AS FIRSTNAME,
ISNULL(u.MIDDLEINITIAL,'') AS MIDDLEINITIAL,
ISNULL(u.LASTNAME,'') AS LASTNAME,
ISNULL(u.EMAIL,'') AS CONTACTEMAIL,
ISNULL(e.JOBPOSITION,'') AS JOBPOSITION,
----
c.COMPANYNAME, --
----
ISNULL((SELECT GROUPNAME FROM TBLGROUP WHERE [GROUPID] = c.[GROUPID]),'') as [GROUP],
----
ISNULL((SELECT CHANNELNAME FROM TBLCHANNEL WHERE ChannelID = c.ChannelID),'') as CHANNEL,
----
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 (SELECT STATENAME FROM TBLSTATE
WHERE TBLSTATE.STATECODE=e.STATECODE) ELSE 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 = ISNULL(e.PWCREGIONID, e.REGIONID)),'') AS PWCREGION,
----
ISNULL((SELECT MARKETNAME FROM TBLPWCMARKET
WHERE TBLPWCMARKET.MARKETID = ISNULL(e.PWCMARKETID, e.MARKETID)),'') 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
INNER JOIN dbo.tblUsertopicinterestmap x ON x.SUBSCRIPTIONID = s.SUBSCRIPTIONID
INNER JOIN dbo.tblCompany c ON c.COMPANYID = e.COMPANYID
----
WHERE
/*
isnull(FIRSTNAME,'') like '%%'
AND isnull(MIDDLEINITIAL,'') like '%%'
AND isnull(LASTNAME,'') like '%%'
AND isnull(EMAIL,'') like '%%'
*/
AND e.SELFSUBSCRIPTION in (0,1)
AND e.ISACTIVE=1
AND u.userid in ( select USERID from dbo.tblUserdesignees)
AND x.TOPICID = 528
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 3, 2009 at 9:10 am
Thanks Chris.
As you suggested, I start running your script in Dev environment (where I am the only user) at 10:11am and now is 11:09am, the script is still running.
April 3, 2009 at 9:16 am
have you tried removing the correlated subqueries as Chris mentioned?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 3, 2009 at 9:17 am
Thang.Nguyen10 (4/3/2009)
Thanks Chris.As you suggested, I start running your script in Dev environment (where I am the only user) at 10:11am and now is 11:09am, the script is still running.
You're welcome. As Gail points out above (thanks poppet) there's a cross join in your table list. You need to figure out how dbo.tblUsersubscription fits in with the other tables in the list. As a matter of interest, what are the approximate rowcounts of the following tables:
dbo.tblUsers
dbo.tblExternalusers
dbo.tblUsersubscription
dbo.tblUsertopicinterestmap
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 3, 2009 at 9:27 am
I've taken out the correlated queries how does it run now?
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(e.TITLE,'') AS TITLE,
ISNULL(u.FIRSTNAME,'') AS FIRSTNAME,
ISNULL(u.MIDDLEINITIAL,'') AS MIDDLEINITIAL,
ISNULL(u.LASTNAME,'') AS LASTNAME,
ISNULL(u.EMAIL,'') AS CONTACTEMAIL,
ISNULL(e.JOBPOSITION,'') AS JOBPOSITION,
-----
c.COMPANYNAME, ---
-----
ISNULL(g.GROUPNAME,'') as [GROUP],
-----
ISNULL(ch.CHANNELNAME,'') as CHANNEL,
-----
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(co.COUNTRYNAME,'') AS COUNTRY,
-----
ISNULL(e.CITY,'') AS CITY,
-----
ISNULL((CASE WHEN e.COUNTRYID=2 THEN st.STATENAME ELSE STATECODE END),'') AS STATENAME,
-----
ISNULL(e.POSTALCODE,'') AS POSTALCODE,
-----
ISNULL(i.INDUSTRYNAME,'') AS INDUSTRY,
-----
ISNULL(pwr.REGIONNAME ,'') AS PWCREGION,
-----
ISNULL(pwm.MARKETNAME,'') AS PWCMARKET,
-----
ISNULL(pwo.PWCOFFICENAME,'') 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
INNER JOIN dbo.tblUsertopicinterestmap x ON x.SUBSCRIPTIONID = s.SUBSCRIPTIONID
INNER JOIN dbo.tblCompany c ON c.COMPANYID = e.COMPANYID
--
LEFT JOIN TBLGROUP g ON g.[GROUPID] = c.[GROUPID]
LEFT JOIN TBLCHANNEL ch ON ch.ChannelID = c.ChannelID
LEFT JOIN TBLCOUNTRY co ON co.TBLCOUNTRY.COUNTRYID = e.COUNTRYID
LEFT JOIN TBLSTATE st ON st.STATECODE=e.STATECODE
LEFT JOIN TBLINDUSTRY i ON i.INDUSTRYID=e.INDUSTRYID
LEFT JOIN TBLPWCOFFICES pwo ON pwo.PWCOFFICEID = e.NEARESTPWCOFFICEID
LEFT JOIN TBLPWCREGION pwr ON pwr.REGIONID = ISNULL(e.PWCREGIONID, e.REGIONID)
LEFT JOIN FROM TBLPWCMARKET pwm ON pwm.MARKETID = ISNULL(e.PWCMARKETID, e.MARKETID)
-----
WHERE
/*
isnull(FIRSTNAME,'') like '%%'
AND isnull(MIDDLEINITIAL,'') like '%%'
AND isnull(LASTNAME,'') like '%%'
AND isnull(EMAIL,'') like '%%'
*/
AND e.SELFSUBSCRIPTION in (0,1)
AND e.ISACTIVE=1
AND u.userid in ( select USERID from dbo.tblUserdesignees)
AND x.TOPICID = 528
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 3, 2009 at 9:37 am
Thang.Nguyen10 (4/3/2009)
Thanks Chris.As you suggested, I start running your script in Dev environment (where I am the only user) at 10:11am and now is 11:09am, the script is still running.
You really should post the execution plan as a zipped file, it will help see what is happening with your query, and give us a hint at where to look to improve the speed of it.
Since the query is really long to run, you can do just an estimated execution plan, and zip it.
Cheers,
J-F
April 3, 2009 at 11:25 am
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 '% %'
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 11:44 am
As a matter of interest why do you allow the tblUsers table to have NULL values.
Why not put a DEFAULT constraint with an empty string on them. It would save you having to use all the ISNULL functions.
The query makes it appear that presenting the NULL values as empty strings is what is required in any case.
April 3, 2009 at 12:28 pm
J-F Bergeron (4/3/2009)
You really should post the execution plan as a zipped file, it will help see what is happening with your query, and give us a hint at where to look to improve the speed of it.
Indeed, and I'd also like to see the table definitions and especially index definitions.
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 1:15 pm
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 ? I did check every single field on this select and compare them to the tables and I can warranty all fields formats matched.
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 ?
[font="Arial Narrow"]
SELECT DISTINCT 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,
dbo.tblExternalusers e ,
dbo.tblUsersubscription s ,
dbo.tblUsertopicinterestmap x,
dbo.tblPwCMarket m,
dbo.tblState st
WHERE exists (select USERID from dbo.tblUserdesignees)
and u.USERID in (select USERID from dbo.tblUsers)
and u.USERID=e.USERID
and e.SELFSUBSCRIPTION in (0,1)
and e.ISACTIVE=1
and st.statecode=e.statecode
----
and (m.MARKETID=CASE WHEN e.PWCMARKETID is not null then e.PWCMARKETID else e.MARKETID end)
----
AND x.SUBSCRIPTIONID = s.SUBSCRIPTIONID
AND x.TOPICID in (528)[/font]
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply