too many hits to the database

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

Answer 1

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

Answer 2

User-661350001 posted

check this link

http://sqlserverplanet.com/sql-server/best-sql-server-pagination-method/

Answer 3

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