This is simply my needle in an evergrowing haystack called the Internet. My chance to document whatever is going on in my head.
Over the years I've been presented with too many scenarios which involved me searching the Internet for some obscure piece of information. This is my chance to document such a finding in order to save some time for the next poor soul who encounters a similar problem.
Tuesday, January 17, 2006
Paging in Microsoft SQL Server
Every SQL Server and/or VB developer will come across the need to return data from a database. There will come a time when that database grows to a size when returning the entire list of records is not feasible.
I initially encountered the problem when widespread use of the Internet involved using a 28.8k or 56k modem to connect to the Internet. Most users were not specifying a filter and therefore returning the entire list of records. I had to think of a fix.
Next course of action, search the Internet. Plenty of articles about paging using the recordset's AbsolutePage property. After all the coding and testing internally it seemed to have worked. Now was the time to test it over the modem. No luck. It seems that paging only works when building recordset on a single table. My application was using a stored procedure to join about 8 tables. It took me a while to find that out with some write-and-test code sessions.
Then I got an idea. I could manually have the database do the work, but only request the specified pages I needed. It was fairly simple too. I simply allowed the user to submit their query, and instead of returning the records I would return the recordcount.
The first stored procedure would create a temporary table with an IDENTITY column, and a key column which linked the data. Then, if I requested page #1 (and if i specified each page was 50 records) i would simply write a query which filtered IDENTITY >=1 and IDENTITY <=50.
This would then be joined to the original query, but would only return the 50 records requested. The datagrid displaying the rows would get populated with the first page. In code if I detected the user going past that first page, the application would fetch the second page of data and fill the grid. Eventually, the user would scroll down to the bottom - at which point the entire grid was populated and no need for trips to the database.
Although this solution has its drawbacks. You have to create a global temporary table that each user accesses, and they won't be able to open multiple versions of the application (different filter, same table, screwy results). Then you have to worry about deleting the tables when they log out.
I only recently saw a second solution that was implemented by a colleage of mine in a financial application. It was a very complicated version of what I am going to show you now.
select * from (
select top @records_per_page * from (
select top @records_per_page * @page_number * from vwRECORDS
order by date_posted desc) as foo
order by date_posted asc) as bar
order by date_posted desc
If you follow the example on a dataset of 10 records and a pagesize=2 you can very easily follow what it is doing.
Ingenious isn't it? I'm sure this can be adopted to any product that has a similar convention to the "top" keyword as Microsoft SQL Server has.
I initially encountered the problem when widespread use of the Internet involved using a 28.8k or 56k modem to connect to the Internet. Most users were not specifying a filter and therefore returning the entire list of records. I had to think of a fix.
Next course of action, search the Internet. Plenty of articles about paging using the recordset's AbsolutePage property. After all the coding and testing internally it seemed to have worked. Now was the time to test it over the modem. No luck. It seems that paging only works when building recordset on a single table. My application was using a stored procedure to join about 8 tables. It took me a while to find that out with some write-and-test code sessions.
Then I got an idea. I could manually have the database do the work, but only request the specified pages I needed. It was fairly simple too. I simply allowed the user to submit their query, and instead of returning the records I would return the recordcount.
The first stored procedure would create a temporary table with an IDENTITY column, and a key column which linked the data. Then, if I requested page #1 (and if i specified each page was 50 records) i would simply write a query which filtered IDENTITY >=1 and IDENTITY <=50.
This would then be joined to the original query, but would only return the 50 records requested. The datagrid displaying the rows would get populated with the first page. In code if I detected the user going past that first page, the application would fetch the second page of data and fill the grid. Eventually, the user would scroll down to the bottom - at which point the entire grid was populated and no need for trips to the database.
Although this solution has its drawbacks. You have to create a global temporary table that each user accesses, and they won't be able to open multiple versions of the application (different filter, same table, screwy results). Then you have to worry about deleting the tables when they log out.
I only recently saw a second solution that was implemented by a colleage of mine in a financial application. It was a very complicated version of what I am going to show you now.
select * from (
select top @records_per_page * from (
select top @records_per_page * @page_number * from vwRECORDS
order by date_posted desc) as foo
order by date_posted asc) as bar
order by date_posted desc
If you follow the example on a dataset of 10 records and a pagesize=2 you can very easily follow what it is doing.
Ingenious isn't it? I'm sure this can be adopted to any product that has a similar convention to the "top" keyword as Microsoft SQL Server has.
Long input into a TEXTAREA control hangs a form submit
Here's a very simple technique when using forms to submit data to another page. Well, it's not really a technique because it's really the proper way of doing it.
If you've ever created a web form that submits large amounts of data to another page, you may click until you're blue in the face and the submit button will not do anything. Well, there's a reason for that:
In the form tag specify method="post"
That's it!
If you've ever created a web form that submits large amounts of data to another page, you may click until you're blue in the face and the submit button will not do anything. Well, there's a reason for that:
In the form tag specify method="post"
That's it!
I hate global warming
It's nearing the end of January and being one of the coldest months in Ontario (the other being February, not too far off) you would never know it if you stepped outside. We had a couple of freak snowstorms in December which put a nice ring on the Christmas holidays, but January has been dismal. I can probably count the number of days it's snowed on my left hand.
Granted, most of you are probably not complaining as I write this. I grew up waiting for winter and then summer. I've never liked spring or fall. Too wet and too blah. Rollerblading in the summer and skiing in the winter, that was the life for me.
Often I came across the dumbfounded look on people's faces when I told them I was looking forward to winter. My screensaver is a snow-covered forest along the slopes of the Rocky Mountains, while the rest of the office has white sand beaches and clear blue warm oceans.
Granted I wasn't selfish, I was happy with the three months of pure excitement that mother nature bestowes upon us ... January, February, and March. Three out of the twelve months isn't asking for much is it? Besides, if you wanted sun all year round then Florida is where you should be. And I wasn't afraid to tell somebody that.
But what's happening lately? Western Canada is experiencing some unusually high temperatures for this time of year. In fact the Grouse Mountain ski resort located just outside of Vancouver, British Columbia opened their ski and snowboard operations early. The 2005/2006 ski season looked promising. Many of the Rocky Mountain resorts were getting record snowfalls in October.
But it would not last long because Mother Nature playing a cruel joke on us and tempting us with just enough snowfall to force us to wax our skis and snowboards early in anticipation for the upcoming season. Then in the words of Emeril, BANG! she melted our hopes away.
Grouse Mountain had to close because the weather was too warm even with a snowmaking operation. Plenty of Canadian resorts went on a semi-alert status with the warmer weather because of avalanche concerns.
At Ski Fernie in British Columbia, on an actual groomed run, a slab of snow (ok, not quite an avalanche movie-style) broke off and partially buried nine skiers (including a ski patroller).
CTV News - Avalanche almost buries skiiers in Rockies
This ski season is unbelievable! A record number of Colorado ski resorts were opening early, in fact before Halloween! Most have had record snowfalls within the last 2 months.
Here in Ontario, we've got a lot of rain and warm temperatures in the low-digits. As I look outside I see not a piece of snow in sight.
Global warming is all good when we get warmer summers, but when it impacts my snow season ... well, that's just not right. I used to ask for at least 3 good months of snow to ski and snowboard. Looks like we'll barely get 1-2 months this year.
I just hope all those ski resorts also operate golf courses so they can capitalize on the loss of their winter bread-and-butter. You suppose they can build a hole on every run? I wonder if uphill or downhill golfing would be easier?
Granted, most of you are probably not complaining as I write this. I grew up waiting for winter and then summer. I've never liked spring or fall. Too wet and too blah. Rollerblading in the summer and skiing in the winter, that was the life for me.
Often I came across the dumbfounded look on people's faces when I told them I was looking forward to winter. My screensaver is a snow-covered forest along the slopes of the Rocky Mountains, while the rest of the office has white sand beaches and clear blue warm oceans.
Granted I wasn't selfish, I was happy with the three months of pure excitement that mother nature bestowes upon us ... January, February, and March. Three out of the twelve months isn't asking for much is it? Besides, if you wanted sun all year round then Florida is where you should be. And I wasn't afraid to tell somebody that.
But what's happening lately? Western Canada is experiencing some unusually high temperatures for this time of year. In fact the Grouse Mountain ski resort located just outside of Vancouver, British Columbia opened their ski and snowboard operations early. The 2005/2006 ski season looked promising. Many of the Rocky Mountain resorts were getting record snowfalls in October.
But it would not last long because Mother Nature playing a cruel joke on us and tempting us with just enough snowfall to force us to wax our skis and snowboards early in anticipation for the upcoming season. Then in the words of Emeril, BANG! she melted our hopes away.
Grouse Mountain had to close because the weather was too warm even with a snowmaking operation. Plenty of Canadian resorts went on a semi-alert status with the warmer weather because of avalanche concerns.
At Ski Fernie in British Columbia, on an actual groomed run, a slab of snow (ok, not quite an avalanche movie-style) broke off and partially buried nine skiers (including a ski patroller).
CTV News - Avalanche almost buries skiiers in Rockies
This ski season is unbelievable! A record number of Colorado ski resorts were opening early, in fact before Halloween! Most have had record snowfalls within the last 2 months.
Here in Ontario, we've got a lot of rain and warm temperatures in the low-digits. As I look outside I see not a piece of snow in sight.
Global warming is all good when we get warmer summers, but when it impacts my snow season ... well, that's just not right. I used to ask for at least 3 good months of snow to ski and snowboard. Looks like we'll barely get 1-2 months this year.
I just hope all those ski resorts also operate golf courses so they can capitalize on the loss of their winter bread-and-butter. You suppose they can build a hole on every run? I wonder if uphill or downhill golfing would be easier?
Subscribe to:
Posts (Atom)