none
SSRS Report no longer loading after database reindexing RRS feed

  • Question

  • We recently had utility maintenace done on our database where our tables were reindexed. After this one of my datasets with a lot of joins stopped working. The query runs when in the query designer but when I go to preview the report it'll run until I get the message "Report Cancelled by User". I didn't changeanything between before the indexing and after. The other items/reports work fine except this one. The query is eating up a lot of memory. Also the report has 3 subreports on it if that matters.

    Using SSRS 2008 r2

    SELECT
    ip.fopmemo,
    im.fprice,
    sr.funetprice,
    sr.fdelivery,
    id.fonhand as IDfonhand,
    jd.flead_tim,
    jd.fnsimulops, 
    i.fsub_from,
    i.createddate,
    sm.forderdate,
    ji.fdescmemo,
    i.fjobno as Job,
    i.ftype as [Type],
    i.fpartno as [Part No],
    i.fpartrev as [Part Rev],
    si.fcustpart as [Cust Part],
    i.fac [Fac],
    si.manualplan [Manual Plan],
    im.fgroup [fGroup],
    im.fprodcl [Prodcl],
    im.fbuyer  [fnbuyer],
    im.fnusrqty1 [fnurst],
    im.fonhand [fonhand],
    im.fbook [fbook],
    im.fonhand-im.fbook as OverShort,
    im.fproqty   as IMfproqty,
    im.fonorder,
    ji.fdesc [fdesc],
    jd.foperno [foperno],
    jp.flastoper [flastoper],
    i.fstatus [status],
    i.fcus_id [fcus_id],
    sm.fcontact [fcontact] ,
    sm.fsono [SMfsono],
    sm.fcustpono [fcustpono],
    iw.fdept [fdept],
    jd.fpro_id [fpro_id],
    jd.fdescnum [fdescnum],
    jd.fccharcode [fccharcode],
    i.fsono [Ifsono],
    ji.finumber, 
    jb.fltooling,
    jb.fbompart,
    MIN(i.ftduedate) over (Partition by jb.FBOMPART) as EarliestDate,
    i.fnusrcur1,
    i.fdusrdate1,
    i.flisapl,
    i.ftduedate ,
    i.frel_dt,
    i.fddue_date,
    sr.fduedate,
    i.fquantity,
    jd.foperqty,
    jd.fnqty_comp,
    jd.fnqty_move,
    jd.fnqty_togo,
    jd.fcomp_date,
    jd.fuprodtime,
    jd.flschedule, 
    ji.fshipqty,
    sr.fshipmake,
    i.fstrt_date,
    im.fbin1,
    im.flocate1,
    im.fcusrchr1,
    im.fcusrchr2,
    im.fcusrchr3,
    im.fnusrqty1,
    ix.flocate1 as IXflocate,
    ix.fbin1 as IXfbin1,
    ix.fcusrchr1 as IXfcusrchr1,
    ix.fcusrchr2 as IXfcusrchr2,
    ix.fcusrchr3 as IXfcusrchr3,
    ix.fdusrdate1 as IXfdusrdate1,
    row_number() over (Partition by jb.FBOMPART  Order by i.ftduedate, jb.FBOMPART, i.FPARTNO, i.FDDUE_DATE) as tempGroup
    FROM jomast i
    LEFT JOIN joitem ji ON i.fjobno=ji.fjobno 
    LEFT JOIN jopest jp ON i.fjobno=jp.fjobno 
    LEFT JOIN jodrtg jd ON i.fjobno=jd.fjobno
    left join inopds ip on jd.fdescnum=ip.fdescnum  
    LEFT JOIN jodbom jb ON i.fjobno = jb.fjobno and 1=jb.fltooling  
    LEFT JOIN inmastx ix ON jb.fbompart=ix.fpartno 
    LEFT JOIN somast sm ON i.fsono =sm.fsono 
    LEFT JOIN soitem si ON ji.fsono+ji.finumber=si.fsono+si.finumber 
    LEFT JOIN sorels sr ON ji.fsono+ji.finumber+ji.fkey=sr.fsono+sr.finumber+sr.frelease 
    LEFT JOIN inmast im ON i.fpartno+i.fpartrev=im.fpartno+im.frev 
    LEFT JOIN inwork iw ON jd.fpro_id=iw.fcpro_id
    LEFT JOIN inonhd id ON im.fpartno = id.fpartno and 'Z3' = id.flocation
    WHERE i.flisapl=1 and
    LEFT(i.fstatus,1)<>'C' and iw.fdept='02' AND jd.flschedule=1
    ORDER BY [fdescnum], i.frel_dt, i.fddue_date, Job
    OPTION (MERGE JOIN, HASH JOIN)


    • Edited by David9501 Monday, January 14, 2019 9:32 PM
    Monday, January 14, 2019 5:08 PM

All replies

  • Hi David9501

    According to your description , you could check in the following aspects.

    When you first run the report , it would load the data and generated the cache first ,that would take up a lot of time. You could try to run the report again (second time), and check if it works fine or not?

    Also check your DataSource , whether the index is exist or not , check if any index lost after you re-index ? (Also should check the index in the ssrs server database)

    select i.[name] as index_name,
        substring(column_names, 1, len(column_names)-1) as [columns],
        case when i.[type] = 1 then 'Clustered index'
            when i.[type] = 2 then 'Nonclustered unique index'
            when i.[type] = 3 then 'XML index'
            when i.[type] = 4 then 'Spatial index'
            when i.[type] = 5 then 'Clustered columnstore index'
            when i.[type] = 6 then 'Nonclustered columnstore index'
            when i.[type] = 7 then 'Nonclustered hash index'
            end as index_type,
        case when i.is_unique = 1 then 'Unique'
            else 'Not unique' end as [unique],
        schema_name(t.schema_id) + '.' + t.[name] as table_view, 
        case when t.[type] = 'U' then 'Table'
            when t.[type] = 'V' then 'View'
            end as [object_type]
    from sys.objects t
        inner join sys.indexes i
            on t.object_id = i.object_id
        cross apply (select col.[name] + ', '
                        from sys.index_columns ic
                            inner join sys.columns col
                                on ic.object_id = col.object_id
                                and ic.column_id = col.column_id
                        where ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    where t.is_ms_shipped <> 1
    and index_id > 0
    order by i.[name]

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.



    • Edited by Mitarai Queen Tuesday, January 15, 2019 2:36 AM
    • Proposed as answer by Mitarai Queen Thursday, January 17, 2019 1:31 AM
    Tuesday, January 15, 2019 2:25 AM