Paginating Records in CF with One SQL Server 2005 call
ColdFusionIn response to Ben Nadel's (http://www.bennadel.com/index.cfm?dax=blog:990.view) interesting post about pagination via one SQL Server call, I've outlined below a strategy I've worked up over the last couple of days that I prefer.
Features of what I'll call the "CF/SQL Server 2005 Sproc Pagination" solution are:
- It's a general solution (one sproc supports nearly any pagination you might want to do)
- supports most select statements, including INNER and OUTER joins and sub-selects, though not UNIONs; note this hasn't been thoroughly tested
- supports most basic WHERE clauses
- Stored Procedures nearly always run faster on SQL Server 2005 than the equivalent cfquery SQL
- I'm sure it's not perfect, as I've only recently completed this initial pass; it needs much more testing. I'm all ears to any suggestions/bugs that people see.
The sproc (updated on 11/5/2007:
And with simply passing the Page (pg) in the URL, you can derive the Start and End rows with something like the following in your Gateway CFC:
And in your DAO CFC method:
Thoughts?
Ben Nadel said:
Aaron,
That looks good. I like the fact that in SQL Server 2005, it makes getting row numbers easier. Unfortunately, I am still stuck on 2000 :( Some clients of ours do have 2005 and overall, it seems to be much faster.
That being said, my main concern, while shallow, is that it loses the look and feel of a CFQuery. Granted CF was developed originally to be "fast enough", but one of the things that's so nice about CFQuery is that it makes query readability a huge benefit.
Of course, among the OOP world, this seems to be of no concern at all. So, again, this might just be a reflection of my lack of experience in that area of work.
My only other concern is that it seems you are pulling all column data before you even worry about the pagination (vs. pulling full data for only the returned records). Of course, this could probably be easily accomodated by adding another Proc Param that defines the identity column(s) for the first part of the query, and then the column names for a latter part. Although, since you are pulling from a temp table, this might be possible.
Of course, this may not even be an issue since when you paginate, you generally don't need to pull so much data anyway.
Anyway, good stuff. Certainly stored procedures are consistently something that I don't know enough about.
That looks good. I like the fact that in SQL Server 2005, it makes getting row numbers easier. Unfortunately, I am still stuck on 2000 :( Some clients of ours do have 2005 and overall, it seems to be much faster.
That being said, my main concern, while shallow, is that it loses the look and feel of a CFQuery. Granted CF was developed originally to be "fast enough", but one of the things that's so nice about CFQuery is that it makes query readability a huge benefit.
Of course, among the OOP world, this seems to be of no concern at all. So, again, this might just be a reflection of my lack of experience in that area of work.
My only other concern is that it seems you are pulling all column data before you even worry about the pagination (vs. pulling full data for only the returned records). Of course, this could probably be easily accomodated by adding another Proc Param that defines the identity column(s) for the first part of the query, and then the column names for a latter part. Although, since you are pulling from a temp table, this might be possible.
Of course, this may not even be an issue since when you paginate, you generally don't need to pull so much data anyway.
Anyway, good stuff. Certainly stored procedures are consistently something that I don't know enough about.
Aaron Longnion said:
@Ben -
Thanks for your feedback. For S.S. 2000, you could do something very similar to mine via Rob's script: http://www.robgonda.com/blog/files/robGonda/UserFi..., and get nearly the same performance benefit.
While agree that the look of the SQL takes a little bit to get used to, it doesn't take long in the grand scheme of things.
As far as pulling *all* the data before paginating, that's what this technique intended to *avoid*. 2005 has this "Common Table Expressions" feature, which I'm just learning. See http://www.simple-talk.com/sql/sql-server-2005/sql...
If I understand CTEs correctly, the way I'm using it is *not* pulling data unnecessarily. Maybe I'll investigate more later, but for now the performance is SOOOOO incredible on this sproc, that I don't have incentive to change it. Any time I want only 50 records or less on a paginated page, the sproc has been consistently coming back in 0 ms (100 of the 7 million-row table took on average ~90 ms, and I wouldn't ever want more than 100 records per page). I don't know if it's worth trying to improve those numbers. ;)
Thanks for your feedback. For S.S. 2000, you could do something very similar to mine via Rob's script: http://www.robgonda.com/blog/files/robGonda/UserFi..., and get nearly the same performance benefit.
While agree that the look of the SQL takes a little bit to get used to, it doesn't take long in the grand scheme of things.
As far as pulling *all* the data before paginating, that's what this technique intended to *avoid*. 2005 has this "Common Table Expressions" feature, which I'm just learning. See http://www.simple-talk.com/sql/sql-server-2005/sql...
If I understand CTEs correctly, the way I'm using it is *not* pulling data unnecessarily. Maybe I'll investigate more later, but for now the performance is SOOOOO incredible on this sproc, that I don't have incentive to change it. Any time I want only 50 records or less on a paginated page, the sproc has been consistently coming back in 0 ms (100 of the 7 million-row table took on average ~90 ms, and I wouldn't ever want more than 100 records per page). I don't know if it's worth trying to improve those numbers. ;)
Rob Gonda said:
You can find the blog post about the script Ben shared at http://www.robgonda.com/blog/index.cfm/2006/4/25/M...
For sql 2005, I personally opted your first option, but without the SP ... Simply add the surround your regular query with the With Statement and filter the row numbers
Cheers
For sql 2005, I personally opted your first option, but without the SP ... Simply add the surround your regular query with the With Statement and filter the row numbers
Cheers
Ben Nadel said:
Aaron, I have changed my opinion a bit. After posted a comment here, I then saw that you had posted on my site as well (I got to this from CFBloggers, not my site). Anyway, I realize that when we are going for *optimization*, readability is a secondary thing. Paginating over a huge record set is something that just needs to be done often and it needs to be fast! So, I am all for doing faster when necessary.
Aaron Longnion said:
@Ben,
I'm not in the "premature optimization is the root of all programming evil" camp, and people that work with me know that one of the first things I think about when I look at a web architecture/coding issue is, "how's that going to perform under load?".
By far the best programmer/architect I ever worked with believed in doing everything the right way, every time, no matter what the constraints/business requirements. He would insist on doing all of the following (sometimes he would have others help on some of the steps):
1) insist on fully fleshed out specs before he even started; he'd draw it out of them if they didn't give him everything he asked for - he'd make sure someone put together proper wire-frames if much UI was required
2) he'd make technical requirements doc, UML diagrams, domain models, use cases, etc. himself
3) then, he'd write all the unit tests to correspond to the requirements
4) and after all that, he'd start actual coding
And I learned that he believed in coding so that all errors/exceptions should be handled gracefully on the first version of the software, that performance should be optimized so that it will scale out to many times it's original size (he thought in terms of years, not months), and that it should have enough documentation and unit tests that it could be handed off to a different developer at any point during development or after release.
I'm not nearly at that level yet, but seeing how good his code and architectures were gave me a whole new perspective, and I strive to be at least half that good. ;}
I'm not in the "premature optimization is the root of all programming evil" camp, and people that work with me know that one of the first things I think about when I look at a web architecture/coding issue is, "how's that going to perform under load?".
By far the best programmer/architect I ever worked with believed in doing everything the right way, every time, no matter what the constraints/business requirements. He would insist on doing all of the following (sometimes he would have others help on some of the steps):
1) insist on fully fleshed out specs before he even started; he'd draw it out of them if they didn't give him everything he asked for - he'd make sure someone put together proper wire-frames if much UI was required
2) he'd make technical requirements doc, UML diagrams, domain models, use cases, etc. himself
3) then, he'd write all the unit tests to correspond to the requirements
4) and after all that, he'd start actual coding
And I learned that he believed in coding so that all errors/exceptions should be handled gracefully on the first version of the software, that performance should be optimized so that it will scale out to many times it's original size (he thought in terms of years, not months), and that it should have enough documentation and unit tests that it could be handed off to a different developer at any point during development or after release.
I'm not nearly at that level yet, but seeing how good his code and architectures were gave me a whole new perspective, and I strive to be at least half that good. ;}
Ben Nadel said:
Yeah, agreed. I am not against early optimization. I don't see why it is premature? I think sometimes confuse "best practices" with "premature optimization". If you *know* something is better, it's not premature to use it.
Sebastiaan said:
Hi,
you wouldn't happen to have a MySQL 5 version of this code, would you? I'm writing a webapp that needs to run on both MySQL and MSSQL you see!
Thanx up front ;-)
you wouldn't happen to have a MySQL 5 version of this code, would you? I'm writing a webapp that needs to run on both MySQL and MSSQL you see!
Thanx up front ;-)
Aaron Longnion said:
@Sebastiaan,
I don't have similar code for mySQL, but hopefully the following article by Pete Freitag will help:
http://www.petefreitag.com/item/451.cfm
I don't have similar code for mySQL, but hopefully the following article by Pete Freitag will help:
http://www.petefreitag.com/item/451.cfm
James Allen said:
Hi Aaron,
I think I'm missing something with this code. Just trying to implement now but I can't get it to work. I notice that the dbvarname's in the do not fully match with the parameters in the stored procedure. You have:
But the stored proc expects:
@SqlColumns VARCHAR(MAX),
@SqlFriendlyColumns VARCHAR(MAX),
@SqlTableClause VARCHAR(MAX),
@StartRow INT,
@EndRow INT,
@SqlWhere VARCHAR(MAX),
@SqlRowNumOrderBy VARCHAR(MAX),
@SqlOuterOrderBy VARCHAR(MAX)
When I try to plug in your code I am getting errors converting varchar to int which I think is due to the incorrect params, although I could be getting this completely mixed up as I have never used storedproc's before.
Also, is there any way of debugging storedproc's as the errors from CF are useless.
Thanks,
James.
I think I'm missing something with this code. Just trying to implement now but I can't get it to work. I notice that the dbvarname's in the
But the stored proc expects:
@SqlColumns VARCHAR(MAX),
@SqlFriendlyColumns VARCHAR(MAX),
@SqlTableClause VARCHAR(MAX),
@StartRow INT,
@EndRow INT,
@SqlWhere VARCHAR(MAX),
@SqlRowNumOrderBy VARCHAR(MAX),
@SqlOuterOrderBy VARCHAR(MAX)
When I try to plug in your code I am getting errors converting varchar to int which I think is due to the incorrect params, although I could be getting this completely mixed up as I have never used storedproc's before.
Also, is there any way of debugging storedproc's as the errors from CF are useless.
Thanks,
James.
cfZen said:
@James,
Yes, you're right. I updated the stored procedure SQL at one point, but not the CF code that calls it. As you said, simply replace with the correct param names, and i think it should work. If not, let me know.
Also, I'm no sproc guru myself, so I'm not sure the ideal way to debug one like this, but you can write little tests against it using "EXECUTE [sproc_name_here] '[param 1]' '[param 2]', etc." (note the order of the params matters).
And to get better errors to output to CF when they occur in sprocs, I think there's proper ways to use @@error, but haven't tried using that in years, so I won't be much help.
Good luck!
Yes, you're right. I updated the stored procedure SQL at one point, but not the CF code that calls it. As you said, simply replace with the correct param names, and i think it should work. If not, let me know.
Also, I'm no sproc guru myself, so I'm not sure the ideal way to debug one like this, but you can write little tests against it using "EXECUTE [sproc_name_here] '[param 1]' '[param 2]', etc." (note the order of the params matters).
And to get better errors to output to CF when they occur in sprocs, I think there's proper ways to use @@error, but haven't tried using that in years, so I won't be much help.
Good luck!
.: HIDE REPLIES :.
James Allen said:
Hi Aaron,
No problem, I specified the correct params and just passed in the sort and where clauses to the extra params as required.
I now have this built into my system and working nicely. I created a DataTools CFC which I inject into any Gateway that needs to use pagination.
I also created a view and controller method for generating a navtrail bar in Model-Glue so I can easily add pagination to any part of my application with a few lines of XML.
Thanks for posting this! I am working on a site that has the pontential to have many thousands of questions posted and I needed an efficient way or paging through the results without creating nasty bottlenecks.
This looks like it will work a treat.
No problem, I specified the correct params and just passed in the sort and where clauses to the extra params as required.
I now have this built into my system and working nicely. I created a DataTools CFC which I inject into any Gateway that needs to use pagination.
I also created a view and controller method for generating a navtrail bar in Model-Glue so I can easily add pagination to any part of my application with a few lines of XML.
Thanks for posting this! I am working on a site that has the pontential to have many thousands of questions posted and I needed an efficient way or paging through the results without creating nasty bottlenecks.
This looks like it will work a treat.
James Allen said:
Hi Aaron,
I hope you can help me. I've been using your SP extensively around my new site and it's working great. However, I am now tring to get it to work with a table join and I just can't get it to work with a table or alias prefix (in the columns parameter).
E.G:
Columns = Threads.ID,Threads.DateSent
Table Clause = tblMessageThreads Threads
I haven't even added the JOIN in to the table clause yet but keep getting this error:
"The multi-part identifier "Threads.ID" could not be bound."
Any idea how to get the SP to allow me to use the alas or table prefix without generating this error? Without it I can't use the join as I have no way to specify the data in each table.
Many thanks in advance - this is really frustrating me.. :(
I hope you can help me. I've been using your SP extensively around my new site and it's working great. However, I am now tring to get it to work with a table join and I just can't get it to work with a table or alias prefix (in the columns parameter).
E.G:
Columns = Threads.ID,Threads.DateSent
Table Clause = tblMessageThreads Threads
I haven't even added the JOIN in to the table clause yet but keep getting this error:
"The multi-part identifier "Threads.ID" could not be bound."
Any idea how to get the SP to allow me to use the alas or table prefix without generating this error? Without it I can't use the join as I have no way to specify the data in each table.
Many thanks in advance - this is really frustrating me.. :(
cfZen said:
Let me know if this clears up the issue, or if you need more details. ;D
http://cfzen.instantspot.com/blog/2008/06/09/Pagin...
http://cfzen.instantspot.com/blog/2008/06/09/Pagin...
Loading....