locked
too many hits to the database RRS feed

  • Question

  • User1803939084 posted

    Hi

    We have a web form that allows users to select from a list of employees.  The employee ids are then passed as a parameter to Crystal Report as follows:

    Report.SetParameterValue("@EmpIDs", employees)

    The employees list can be up to 600 IDs.  Crystal report then uses the @EmpID parameter to call a storedProc, passing in the employees we want to query.  We only display 4 employees at a time.  Every time the user selects another page, the same query is run again on SQL Server, grabbing all 600 employees again.  How can I tell CR to only grab the next 4 employees?

    Tks

    Friday, May 25, 2012 10:42 AM

All replies

  • User-661350001 posted

    add one more parameter will should be pageIndex so select should be 

    select top 4 from 

    (select top (pageIndex*4) * from emp order by empid ) order by emp id desc

    Friday, May 25, 2012 11:19 AM
  • Friday, May 25, 2012 11:20 AM
  • User1803939084 posted

    Thanks Mudasir

    I tried your suggestion re passing in a PageIndex and modifying the SQL statement.  It works but hasn't really improved the performance.  The report initially takes about 12 seconds to load which is a bit slow, but I can live with that.  Every time the user moves onto the next page, the data is completely re-loaded again.  Is there a way to cache the initial data and not keep going back to the server each time a new page is loaded?

    Tks again

    Friday, May 25, 2012 1:03 PM