none
SRS Report with Store proc takes forever, does not pass dates correctly RRS feed

  • Question

  • When I use a stored procedure for my SSRS report it takes forever. 
    I have heard of parameter sniffing but it did not make a difference or I did not do it right.

    Also the is issues with dates being passed from SSRS into SQL server.
    .


    USE [usgeCCA]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_pleCall]    Script Date: 04/08/2011 09:40:56 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO



    ALTER PROC [dbo].[usp_MultipleCall]
    (
          @MyDate DateTime, 
          @MyDate2 DateTime, 
          @SearchMode varchar(MAX), 
          @SearchPhone varchar(10),
          @MyId Int

    )
    AS
          BEGIN
          

    DECLARE @EndDate int 
    DECLARE @StartDate int 
    DECLARE @SearchDepartment AS VARCHAR(1000) 

    SET @MyDate =  dateadd(dd,0, datediff(dd,0,@MyDate))
    SET @MyDate2 =  dateadd(hh,24, datediff(dd,0,@MyDate2))

    -- Set Dates and convert them to Unix TimeStamp
    SET @StartDate =  CONVERT(int,DATEDIFF(s, '19700101', @MyDate))
    SET @EndDate = CONVERT(int,DATEDIFF(s, '19700101', @MyDate2))
    SET @SearchMode = ',' + @SearchMode + ','

    -- SET the search departments options
    IF PATINDEX(',All Departments,',@SearchMode) > 0
          SET  @SearchDepartment = ',Customer Service,Sales,QA,Compliance,Marketing,Sales,Sales Operations,'
    ELSE IF PATINDEX(',Sales Group,',@SearchMode) > 0 
          SET  @SearchDepartment = ',PMA Sales,NMB Sales,Retention,Sales,'
    ELSE
          SET  @SearchDepartment = @SearchMode


    SELECT  i.MyId, i.DestinationPhone AS Phone_or_ContactId,  u.MyUserId, i.MyOriginator,             
                (i.duration/60) AS duration, -- 1. Diplay Call Duration in Minutes
                (u.firstname + ' ' + u.lastname) AS SalesPerson,
                dbo.fn_MyFunction(i.MyStartDate) AS Date,  -- 2. Convert from unix timestamp to EST TimeZone
                
                -- 3. Show "No Recording" when no mp3 available
                l.name, ISNULL(('\\MYshare\recodring\' + q.filename),'No Recording') as Recording, 
                
                -- 4. Show "Untraced Call" when Customer Name Not Available
                COALESCE(nullif(rtrim(p.FirstName +  ' ' + p.LASTNAME), '') + ',' ,'Untraced Call')  AS [Customer Name],
                
                -- 5. Show Counts on DestinationPhone            
                (count(DestinationPhone) over (partition by DestinationPhone))AS CNT
                FROM (
                SELECT distinct(MyId), DestinationPhone, MyOriginator, MyStartDate, duration
                FROM CallLog 
                WHERE MyStartDate BETWEEN
                @MyStartDate AND
                @EndDate AND
                -- AND MyOriginator > ''       -- 6. This is optional      and will only hide blank MyOriginators  = 
                (@SearchPhone = '' OR DestinationPhone = @SearchPhone)
                ) i
                
                -- 7. Retrieve Sales Person's name
                JOIN Myuser u ON      (u.MyUserId =  i.MyOriginator) 
                
                -- 8. Retrieve Department Name
                JOIN departmentTable l ON u.departmentid = l.departmentid 
                
                -- 9. Retrieve call recording
                LEFT JOIN CallRecordingTable AS q ON i.MyId = q.MyId 
                
                -- 10. Retrieve Customer Name from PredectiveContacts
                LEFT JOIN (select MyPhone As Phone, MAX(field3) AS FirstName, MAX(field4) AS LASTNAME from MyContacts
                GROUP BY MyPhone) p ON      i.DestinationPhone = p.MyPhone
                            
                -- 11. Search Multiple Departments
                WHERE
                (charindex(',' + l.name + ',', @SearchDepartment) > 0) AND

                -- 12. Departments not allowed in any select
                (l.name <> 'Dep A' AND
                l.name <> 'Dep B' AND
                l.name <> 'Dep C' AND
                l.name <> 'Dep D' AND
                l.name <> 'Dep E' AND
                l.name <> 'Dep F')
                
                -- 13. Sort by Phone and DateTime
                ORDER BY i.DestinationPhone, i.MyStartDate


    END
    Friday, April 8, 2011 4:59 PM