I have the following query that creates a temporary table with one indexed column, populates it and then selects from other tables to create an Employee cross reference from 2 merged companies on Employee IDs. It works fine and quickly until I add the last join which is to get only those employees that have OneNote licenses (at the most 2500). I tried a RIGHT join and then an INNER join because I only want those from the list that have this program. But the query just sits there. There are NULL Employees in the OneNote table, so I excluded them in the final WHERE clause:select #t.P_User ,H.EMPLID,[SoftwareReporting].[dbo].[FormatName] ((CASE WHEN charindex(',', e.[NAME]) > 0 THEN replace(e.[NAME], ',', ', ') ELSE e.[NAME] END),'F') FirstName,[SoftwareReporting].[dbo].[FormatName] ((CASE WHEN charindex(',', e.[NAME]) > 0 THEN replace(e.[NAME], ',', ', ') ELSE e.[NAME] END),'L') LastNamefrom #tleft join ERS_ePeople e on E.EMPLOYEE_ID = #t.p_userinner join HR_NSV_PM H ON [SoftwareReporting].[dbo].[FormatName] ((CASE WHEN charindex(',', e.[NAME]) > 0 THEN replace(e.[NAME], ',', ', ') ELSE e.[NAME] END),'F') = H.FIRST_NAMEAND [SoftwareReporting].[dbo].[FormatName] ((CASE WHEN charindex(',', e.[NAME]) > 0 THEN replace(e.[NAME], ',', ', ') ELSE e.[NAME] END),'L') = H.LAST_NAMEInner join OneNoteUsersPM O on O.EMPLID = H.EMPLIDwhere e.NAME is not null and O.EMPLID is not nullorder by P_UserI know the FormatName function looks weird, but it works, and fast, too. The only problem is adding that last join. I added indexes to all the appropriate columns, too, thinking that was the problem, but it is still just churning along with no results. There are less than 100,000 rows in any of the tables, and as I said, runs quickly without the last join.Duane
Query running too long with extra JOIN
Posted - 2010-05-20 : 11:02:22
