It's a common problem to do pagination on the "client" (in this case CF)
instead of the database server - it's easier, but as your dataset grows it
becomes very performance intensive.
Pagination in MSSQL is diffucult because you don't have the flexible LIMIT
clause of MySql/Oracle. It's doable, just verbose. You basically do a
select top [endrow]
from (select top [startrow]
from table
order by [id] desc)
order by [id]
HTH,
Dave Ross
http://www.coldspringframework.org
> Hi all, I have a database with 170K records. What I'm doing is using
> CF to query this data with a blockfactor attribute.
>
> <cfquery name="qry_selectContacts" datasource="#getVars().dsn#"
> blockfactor="#arguments.maxRows#">
>
> I have my loop doing.
>
> <cfloop query="qry_getRecords" startrow="#url.startRow#"
> endrow="#iif(not isDefined('form.goToPage') and url.startRow eq 1 or
> (isDefined('form.goToPage') and form.goToPage eq 1), de('#variables.
> maxRows#'), de('#(url.startRow+(variables.maxRows-1))#'))#">
>
> Now my query is in an application cached component.
>
> SQL in this case is on the same machine.
>
> What seems to be happening when it doesn't crash is that the
> connection from CF to the database (Driver?) is getting clogged up. CF
> returns the list immediatelly as soon as the data is retrieved.
>
> Does anyone have any ideas on how I can speed up the process? I have a
> feeling I need to do something in SQL but I don't know what to do. How
> come with the blockfactor it still seems to be trying to retrieve all
> the data?
>
>
> ---------------------------------
> Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great
> rates starting at 1ยข/min.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245465
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
