Archive for February, 2008
Friday, February 29th, 2008
I was extremely excited about GridViews and DataGrids when I first started working with .NET. The thought of having controls with built in paging and sorting just blew me away. Needless to say, this excitement was short lived. By the end of my first project in .NET, I found that this built in sorting and paging had some major performance issues. It was too much data to send back and forth between the web server and the SQL server; not to mention that .NET just isn't as optimized to sort that much data as fast as SQL Server.
The alternative was to have SQL Server sort and page the data and only send back the data that was needed to the web server. After some research and some time to combine the ideas I found into my own solution I came up with:
CREATE PROCEDURE dbo.ReturnPage
(
@SelectStmt varchar(1000), --Select Statement with Where Clause
@OrderBy varchar(1000), --Order By Clause 'ASC' is required
@StartRow int, --First Row of Page
@EndRow int, --Last Row of Page
@totRows int output --Output: Total Rows in Set
)
AS
DECLARE @ColList varchar(2000)
DECLARE @WhereCls varchar(2000)
DECLARE @i int
DECLARE @i2 int
DECLARE @tmp varchar(1000)
DECLARE @dec varchar(1000)
DECLARE @f varchar(100)
DECLARE @d varchar(100)
DECLARE @Symbol char(2)
DECLARE @SQL varchar(4000)
DECLARE @Sort varchar(1000)
DECLARE @tmpSQL NVARCHAR(4000)
--Assign the total number of rows available to @totRows for output
SET @SQL = N'SELECT @totRows = COUNT(*) FROM (' + @SelectStmt + ') tbl'
SET @tmpSQL = '' + @SQL
EXEC sp_executesql @tmpSQL , N'@totRows int OUTPUT',
@totRows = @totRows OUTPUT
SET @Sort = @OrderBy + ', '
SET @dec = ''
SET @WhereCls = ''
SET @SQL = ''
SET @i = charindex(',' , @Sort)
--Pull apart the sort by clause for declarations
WHILE (@i != 0)
BEGIN
SET @tmp = LEFT(@Sort,@i-1)
SET @i2 = charindex(' ', @tmp)
SET @f = ltrim(rtrim(LEFT(@tmp,@i2-1)))
SET @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))
SET @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
SET @i = charindex(',', @Sort)
SET @symbol = case when @d = 'ASC' then '>' else '< ' end +
case when @i=0 then '=' else '' end
SET @dec = @dec + 'declare @' + @f + ' sql_variant; '
SET @ColList = isnull(REPLACE(REPLACE(@colList,'>','='),'< ','=') +
' and ','') + @f + @Symbol + ' @' + @f
SET @WhereCls = @WhereCls + ' OR (' + @ColList + ') '
SET @SQL = @SQL + ', @' + @f + '= ' + @f
END
--Put together Transact SQL for final output
SET @SQL = @dec + ' ' +
'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
'SELECT ' + substring(@SQL,3,7000) + ' from ('
+ @SelectStmt + ') a ORDER BY ' + @OrderBy + '; ' +
'SET ROWCOUNT ' +
convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
'select * from (' + @SelectStmt + ') a WHERE ' +
substring(@WhereCls,4,7000) + ' ORDER BY ' + @OrderBy +
'; SET ROWCOUNT 0;'
EXEC(@SQL)
GO
What's it doing?
The procedure works by turning a simple query like:
SELECT col1, col2, col3, col4 FROM TABLE ORDER BY col1 ASC
and turning it into the following to add the paging.
DECLARE @col1 sql_variant
SET ROWCOUNT = @StartRow
SELECT @col1 = col1 FROM TABLE ORDER BY col1 ASC
SET ROWCOUNT = (@EndRow - @StartRow) + 1
SELECT col1, col2, col3, col4 FROM tbl WHERE col1 > @col1
ORDER BY col1 ASC
Simple enough right?
Disclaimer
While this procedure does work with more complex queries containing SUB-SELECTS, JOINS and WHERE clauses, it does have its limitations. GROUP BY clauses do not work in this version. Maybe in the future.
I wouldn't suggest calling this procedure directly as that would mean you're building your query in-code, and probably not doing necessary escaping to prevent security issues. Creating a stored procedure that builds the original query from passed in WHERE and ORDER BY Parameters and then calls the ReturnPage procedure will be much more efficient and secure.
I hope to post a complete sample that will illustrate the changes to the .NET code and the wrapping procedure soon.
Posted in Development, Programming, SQL Server | Add a Comment »
Friday, February 29th, 2008
After a late arrival to Miami last night, Josh and I are down here for Future of Web Apps today.
Rarely do I make it down to Miami, but this is the second time now within a month that I've been down here. The last time was for some personal abuse, though.
Posted in Who We Are | 2 Comments »
Tuesday, February 26th, 2008
This past Friday everyone from the CNP offices attended the local Addy Awards banquet to see what kind of awards we would walk out with this year. The Addy Awards are conducted by the American Advertising Federation and the local competition is the entry point to their national competition. Awards are given across a multitude of categories from printed newsletters to TV commercials, product packaging to web sites.
Photo by Sean
Basically, how it works is that we submit our best works from the previous year to the local competition and based on several criteria are awarded a gold award, silver award or nothing. Anything that is awarded a gold at the local level is then sent on to the state competition for the next round. I'm pleased to say that at this year's competition we had four of our submissions receive gold and they will move on to the next level. They were:
cnp_studio (one gold for the web site, one gold for our logo/stationery/business cards)
SeaWorld Journey to Atlantis
Kelly Homes
The award on the Kelly Homes site is bittersweet. We're glad the site did well, but sadly the entire site design as well as the HTML and CSS behind the site were completely copied and used by someone who thought it was OK to do such a thing. Expect to see more on this in another post here.
Sites receiving silver awards include Girl Scouts of West Central Florida, Polk Transportation Planning Organization, TrueDemand Software and Heacock Classic Car Insurance.
All of this is a result of the hard work of an all star team (Mike, Pete, Sean, Jeremy) and our great clients.
Posted in Design, Development, Who We Are | 1 Comment »
Tuesday, February 26th, 2008
This really just makes me sick to my stomach and at the same time, angry enough to rip the head off a Chia Pet. BMW Marketing Consultants ripped off our work - pretty much in its entirety.
When developing the Kelly Homes site, we worked with Bonnie Williams of BMW Marketing Consultants. The project, under careless misdirection and disregard for the cost that Kelly Homes was footing, took a 2 month timeline and turned it into more than 6 months.
Months later, we received an RFP from BMW Marketing Consultants for the Silver Palms RV Resort site. The RFP was a spiral-bound book that rivaled the thickness of the phone book for Vermont.
We sent out a quote, and never heard back from BMW Marketing Consultants. A few weeks ago, I was reminded about this when I found this "book" during our move to our new office. Curious to see what happened with the project, I decided to pull up the site.
Lo' and behold:
Funny, guess they couldn't find their "own place in the sun"
Kelly Homes
The CSS, HTML, and most of the images were ripped off. If you pay close attention, you can even see some images were not completely ripped off.
Note the beige/orange color from the Kelly Homes site on the left edge of the banner image).
BMW's response? Well, from what I hear, the response was "well all sites on the internet are templates and can be copied." One of my favorite articles about this subject is Scab at Airbag Industries regarding the Joyent / Corkd design theft. Talking about Ray from Falkner Winery, the company that ripped off the designs…
Ray continued to tell me that all designers borrow ideas from other designers and that his web site design was no different. That line of thinking is the biggest cop-out, bullshit excuse (can't think of a more appropriate term) used over and over by those who simply don't care about how this affects the person who's work has been copied.
…
A real designer does not cut-and-paste the work of others, change some colors and resell it … that would be called a hack. The web is filled with hacks because the web makes it easy to hack. Hacks have about as much talent as the guys who "create" stickers at mall kiosks typically featuring Calvin either pissing on an auto manufacturer's logo or praying before a cross.
Couldn't agree more. Granted, I did not design this site - but I feel the same way about the months of work I put into it. I know Jeremy feels the same way.
No one wants their hard worked ripped off. Least of all, by the company hack that sends you the RFP for the project.
More to come…
Update: Googling "bmw marketing consultants" wields this post as the 2nd hit... got to love the Google juice, eh Hallett?...
Posted in Rants, Who We Are | 3 Comments »
Thursday, February 14th, 2008
Sometimes you just can't find words to express yourself on Valentine's Day. Luckily these cards will do the job for you.
From BagofNothing.com
This one and a whole lot more from SomethingAwful.com - Thanks to Chip for the heads up on this.
And I can't forget The Simpsons classic...
Posted in Random Stuff | Add a Comment »
Friday, February 8th, 2008
Well. It's 8:30, and I'm sittin' here at my office instead of attending acts_as_conference. Why? 4 projects are comin' in for a landing next week, and in our business, the "billable" hour is gospel. I'm not mad or anything, you just have to prioritize this stuff and getting to do the "fun stuff" has to come after the bottom line.
I've known about acts_as_conference for months, and planned as best as I could, but I have got 2 projects (both launching next week) that walked in the door last week. What's a guy to do?
In a way, it's kind of like college. One attends college to go to class, not sit around and play Halo 3 all day.
Although, if I truly believed that, I'd be at the conference.
Posted in Rails, Rants, Who We Are | 2 Comments »
Wednesday, February 6th, 2008
It seems that we've got a recurring theme in our portfolio. That is, development on social media projects for some really big brands. They're a product of the amazing vision of our good friends at Voce Communications mixed with the talent of our developers here at cnp_studio. IMHO we've made some beautiful music together and thanks to the incredible people involved we get to share some of that with everyone. Now, where to start?
Partnership
You know what it's like when someone gets you and you get them? You get one of those "Where have you been all my life?" feelings? That's what it's been like working with Mike, Josh and the others at Voce on these projects. To say they understand the industry is an understatement which is why I'm extremely excited to announce that we're officially partnering with Voce for all things social media. Consider the gap between strategy and implementation bridged. This really is only a formality for what has already been a great relationship. We're stoked to make it public. Read more from Voce's side of things here.
Plugin Release
So for PlayStation.Blog we've done quite a bit of customization to the WordPress install on the site. We use several existing plugins on the blog including WP-Polls, WP-Cache, WordPress Download Monitor and others. A couple of needs though left us without a plugin to fill the void. So, we created our own. And it's because of the great people at Sony and Voce that we get to release these plugins back to the WordPress community. Sweet.
If you look at the homepage of the PlayStation blog you'll see a main image header that they use to promote any number of things. We had originally created a simple manager to allow them to quickly upload these images and set a URL where the image should link, but they wanted to take this one step further and release it back to the community. Who were we to argue? So with some tweaks we made it into the plugin that you can read about/download here. Install it, add the line of code where you'd like the rotation to appear and you're set (oh and don't forget to add some images).
The threaded comments plugin existed, but nothing that limited it strictly to authors replying to comments. Our fear, especially in the case of the PlayStation blog, was that the threaded comments would get out of hand. That, and it's not a discussion board, so we wanted to avoid that feel. This plugin allows authors to keep their response tied directly to the comment they're replying to without having to put the "@name" in order to reference back to previous posts. If you have a high volume of comments on your blog you'll find this one helpful. Read more about and download it here.
Posted in Development, Social Media, Who We Are | Add a Comment »
Wednesday, February 6th, 2008
So we just can't get enough of our new office. Sean got some great shots of our office a couple weeks back with his new Nikon D40 and Nikkor 17-55 f/2.8 lens. This week Josh gave us a different perspective with his Nikkor 10.5mm f/2.8 fisheye lens. Check them out.
Posted in Who We Are | Add a Comment »
Monday, February 4th, 2008
On February 1 support for Netscape 9 officially ended. From the Netscape blog...
While internal groups within AOL have invested a great deal of time and energy in attempting to revive Netscape Navigator, these efforts have not been successful in gaining market share from Microsoft's Internet Explorer. Recently, support for the Netscape browser has been limited to a handful of engineers tasked with creating a skinned version of Firefox with a few extensions.
AOL's focus on transitioning to an ad-supported web business leaves little room for the size of investment needed to get the Netscape browser to a point many of its fans expect it to be. Given AOL's current business focus and the success the Mozilla Foundation has had in developing critically-acclaimed products, we feel it's the right time to end development of Netscape branded browsers, hand the reins fully to Mozilla and encourage Netscape users to adopt Firefox.
I started using Netscape back when it was version 2.0. Even though I haven't used the browser since Communicator 4.0 it's like I've lost yet another a part of my youth. First Bob Barker retires from the Price is Right and now this. So long old friend.
Posted in Random Stuff | Add a Comment »
andrew:
hey mike -- thanks for the reply, let me clarify what i mean.... I know that PHP fu...
nick:
Hi Jeff, Thanks for the heads up on the link. It's all fixed now and you should...
Jeff:
I would love to try your plugin, but the download link appears to be dead again. Ca...
mike:
@Denise: 1. The image is selected randomly each time the code is run. So normally ...
andrew:
hey -- great plugin and would like to use on several different pages, not just the ...