Advertisement
hk1

EF Problem, SQL Query Runs Slowly

hk1
Dec 3rd, 2015
110
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 2.76 KB | None | 0 0
  1. Declare @param1 varchar(max);
  2. Declare @param2 varchar(max);
  3. Declare @param4 varchar(max);
  4. Declare @param5 varchar(max);
  5. Set @param1 = '%village%';
  6. Set @param2 = '%village%';
  7. Set @param4 = '%media%';
  8. Set @param5 = '%media%';
  9.  
  10. SELECT p.*, c.Name AS CompanyName, mp.Info AS MobilePhone, bp.Info AS BusinessPhone, a.Address1, a.City, a.[State], a.PostalCode
  11.     FROM ContactPersons p
  12.         LEFT JOIN Locations l ON l.Id = p.LocationId
  13.         OUTER APPLY (
  14.             SELECT TOP 1 m.Info, m.ContactPersonId, m.Label, m.[Type]
  15.             FROM ContactMethods m
  16.             WHERE Label = 'Mobile' AND [Type] = 'Phone' AND p.Id = ContactPersonId
  17.         ) AS mp
  18.         OUTER APPLY (
  19.             SELECT TOP 1 m.Info, m.ContactPersonId, m.Label, m.[Type]
  20.             FROM ContactMethods m
  21.             WHERE Label = 'Business' AND [Type] = 'Phone' AND p.Id = ContactPersonId
  22.         ) AS bp
  23.         OUTER APPLY (
  24.             SELECT TOP 1 a.Address1, a.City, a.[State], a.PostalCode, a.LocationId
  25.                 FROM Addresses a
  26.                 WHERE a.IsBilling = 1 AND a.LocationId = l.Id
  27.         ) AS a
  28.         LEFT JOIN Companies c ON c.Id = p.CompanyId WHERE ((
  29.             (FirstName LIKE @param2 OR LastName LIKE @param2 OR p.Notes LIKE @param2 OR dbo.ufnStripName(c.Name) LIKE @param1 OR c.Notes LIKE @param2 OR c.OtherNames LIKE @param2)
  30.             OR p.Id IN (SELECT ContactPersonId FROM ContactMethods WHERE Info LIKE @param2)
  31.             OR c.Id IN (SELECT c2.ID FROM Companies c2
  32.                         INNER JOIN Company_Class cc on c2.Id = cc.CompanyId
  33.                         INNER JOIN Classes cl ON cl.Id = cc.ClassId
  34.                          WHERE cl.Tag LIKE @param2)
  35.             OR
  36.                 (p.LocationId IS NOT NULL AND p.LocationId IN (
  37.                     SELECT l.Id FROM Locations l WHERE Name LIKE @param2 OR l.Id IN (
  38.                         SELECT Id FROM Addresses WHERE
  39.                             Address1 LIKE @param2 OR
  40.                             Address2 LIKE @param2 OR
  41.                             City LIKE @param2 OR
  42.                             [State] LIKE @param2 OR
  43.                             PostalCode LIKE @param2 OR
  44.                             Country LIKE @param2
  45.                         )
  46.                     )
  47.                 )
  48.             ) AND (
  49.             (FirstName LIKE @param5 OR LastName LIKE @param5 OR p.Notes LIKE @param5 OR dbo.ufnStripName(c.Name) LIKE @param4 OR c.Notes LIKE @param5 OR c.OtherNames LIKE @param5)
  50.             OR p.Id IN (SELECT ContactPersonId FROM ContactMethods WHERE Info LIKE @param5)
  51.             OR c.Id IN (SELECT c2.ID FROM Companies c2
  52.                         INNER JOIN Company_Class cc on c2.Id = cc.CompanyId
  53.                         INNER JOIN Classes cl ON cl.Id = cc.ClassId
  54.                          WHERE cl.Tag LIKE @param5)
  55.             OR
  56.                 (p.LocationId IS NOT NULL AND p.LocationId IN (
  57.                     SELECT l.Id FROM Locations l WHERE Name LIKE @param5 OR l.Id IN (
  58.                         SELECT Id FROM Addresses WHERE
  59.                             Address1 LIKE @param5 OR
  60.                             Address2 LIKE @param5 OR
  61.                             City LIKE @param5 OR
  62.                             [State] LIKE @param5 OR
  63.                             PostalCode LIKE @param5 OR
  64.                             Country LIKE @param5
  65.                         )
  66.                     )
  67.                 )
  68.             )) ORDER BY c.Name ASC, p.FirstName, p.LastName OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement