Click here to monitor SSC
  • Av rating:
  • Total votes: 8
  • Total comments: 0
Bob Cramblitt

SQL Search- The Search and the Sequel

02 February 2010

It started out as an experiment to try to explore different ways of creating a software tool that people would want. It ended up as a tool that Red Gate is giving away free to the SQL Server community in return for the contribution to the project of so many of Red Gate's friends within the community. But was it easy to do? Bob Cramblitt and Richard Collins went to find out by talking to Tanya Joseph, who managed the project that turned the concept into a product.

Back at the start of October, Neil Davidson, one of Red Gate's CEOs, posted a request on Red-Gate’s internal forum:

"We're going to rent a house (probably on the Suffolk coast, far away enough to get some distance from the office but near enough to be convenient) for a week, fill it with a project team, give them a project and see what they can achieve in a week.'"

"I'm looking for:

- 2 developers
- 1 tester
- 1 UX
- 0 project managers

If you fit the bill, have a sense of adventure, are willing to try something new and are free from Sunday November 1st to Saturday 7th November then send me an e-mail. "


 "We'll expect you to work fairly solidly for the extended week. This isn't a 9-5 thing.

You will be living in the house, and you can't bring partners. In return, we'll pay all your expenses (including a small beer allowance), you'll get to work with people you might not have worked with before, in a way you haven't worked before, and produce something cool. It'll be fun."

And so it happened. The house by the sea turned out to be a magnificent converted barn in idyllic country overlooking the north sea.  It was big enough to sleep 12, with everything you could want, including table football, wood-burning fire, a luxurious  kitchen, and comfortable solid-wood furniture.

They returned with the genesis for SQL Search, a free plug-in for SQL Server Management Studio (SSMS) that allows users to search their database schemas to instantly locate any term in stored procedures, functions, views and more.

What happened then was even more remarkable, because a clever concept was then turned into a useful product, in a second phase.  This was a process that presented several challenges, but anyone who has used SQL Search seems to agree that it was well worthwhile. Bob Cramblitt and Richard Collins asked Tanya Joseph, who managed the second phase, about what went into making SQL Search a product worthy of the Red Gate name.


How do you think users will benefit from SQL Search?

There is currently no easy way to track down objects or search all the SQL code within SSMS, unless you resort to SQL code, and I think users will find those things very useful.  I really hope users enjoy using SQL Search as much we loved working on it!

According to Red Gate standards, in what state was the software after the week of development at the house by the sea?

It was awesome considering it was the outcome of just one week’s worth of work, including design, development and some testing.  But, it was primitive and not releasable. It was buggy and had a whole load of exceptions being thrown.  

What type of feedback did you get from outside testers during the alpha release?

Feedback from the alpha release was mostly targeted towards performance, specifically slow indexing. Remarks were along the lines of “I hear my machine humming” and “I am back from my coffee break and it is still indexing.”

Outside of the indexing issue, we had a whole bunch of exceptions that were reported and quite a few feature requests.  We handled all reported exceptions and fixed the indexing concerns for the beta release.  New feature requests will be considered for future versions.

How did you figure out the indexing problem and what did you do to fix it?

We discovered that as the database got larger, users would see this issue more because the way we were storing the index was not optimal. To solve the problem, we introduced SQLLite [the public-domain transactional SQL database engine] to the process.  That solved the problem and led to some good beta feedback :).

Did you add functionality to the software?

In the process of addressing our indexing issues, we changed quite a lot of code, which resulted in certain functionalities – Exact Search, for example – not being available anymore. So we had to add Exact Search anew and do some pruning before release, but no major functionality was added.

You had less than two weeks for beta testing. Why such a short period of time?

We wanted to release SQL Search and make it available for the wider community so that they could benefit from using the tool. We didn’t need much time because we weren’t adding any new features for the V1.0 release. The aim was to ensure that the indexing fix was working and to have all our beta users give their thoughts about the product.  We fixed exceptions, but did not have any show-stoppers or critical issues that required further investigation or major fixes

What was the biggest hassle of the SQL Search project?

 It was the unavoidable release deadline compounded by a set of fixed features. It was frustrating for everyone, especially for the test engineers to have new features being shoe-horned into the product late in the day without having much in the way of specifications.

 It made a lot of sense to look at it from the business perspective, because we were aware that the majority of the customers would want features such as 'Exact search'; but from the project team end, it was the quality of the delivered product that mattered.  Product development teams, thankfully, always treat the product they are working on as their baby and it is a blow to their pride to have to  release a tool which in their terms 'could still do better' or has ‘known issues’! It's something about being a perfectionist for them: It was therefore quite a task to strike the right balance for the product, team and the company at large, given that SQL Search was a free tool.

Why do you need written requirements for a project such as this?

Ok, so you have Robbie Th’ng, amazing test engineer, essentially saying “OK so what is the right behavior here?

What are we expecting to see when, for example, I search for 'emp_as'? What should be the difference on running a normal search and exact search for that string? Should it search for text within substrings? Should we test for spaces? What do we do when we see quotes used in the search bar as SQL has this concept of quoted identifiers, we surely cannot treat it as other search engines? Etc etc etc.. We had to make decisions on the fly, without the benefit of user feedback.

But at least the short deadline meant that it wasn’t the notorious death-march project envelope. So there’s a compensation to the lack of written requirements?

I wouldn't really say that, because it does affect the team's spirit. You can Imagine it, can’t you? You release a product and then a user reports a fault or anomalous behaviour, and this happens to be from one of those features that your test engineer has already expressed concerns about. It’s a personal affront for them, for it might seem to imply that they should have shouted more to ensure this was fixed.

In this case, it rebounds on me as Project Manager, as I would have had to bring down the guillotine and decide , “No more, we’re shipping, this is fine!”

What features will come next for SQL Search

We’ve had quite a few requests in already but we’re going to wait a few weeks before we aggregate and weight the feedback, score the requests by various factors, such as the frequency and difficulty. But I strongly suspect that we will need to do more work on the ‘exact search’.

The thing about Search is that there are a whole set of norms and rules that have grown up around search behaviors. And if we don’t fall in with those rules then users find it hard to understand and get rightly aggrieved, when they shouldn’t have to be put through that kind of pain.

So what’s your preferred project structure? Long or short?

It’s not the length of the project. It’s the way that you need to phase the releases so as to include a healthy dose of real user feedback. So whether it’s a long or short project, you must have a phased release, so the engineers can regularly engage with the end users to discuss their views, that makes the entire difference between a great release and a hit or miss release.

We gain and the users gain too. The users always  change their perspective  on the relative importance of features as soon as they use the actual product. The engineers need to know how user priorities have changed since the original exploratory survey. And obviously, for the engineers, it is enormously gratifying to have real people writing to you and saying how much they love the results of the work that you’ve been tackling for months. It gives you the lift you need to reach the final summit. Up in the death zone, above 20,000 bugs, where the air is very thin!

So let me make this plain: a phased release, with regular user feedback improves the product no end, but just as importantly, it improves the project.

Is there a letdown after a product such as this one gets released? What do you do afterward? Sleep? Take a holiday? Jump right into the next project?

SQL Search has been a relatively short project, and we had the luxury of Christmas holidays amidst the project schedule. So we are jumping right into the next project.  Of course we’ll have that post release evening where we enjoy a few drinks and have a late morning to follow

Bob Cramblitt

Author profile:

Bob Cramblitt, based in Cary, N.C., writes about issues that impact the day-to-day work of IT and database professionals. He can be reached at info@cramco.com.

Search for other articles by Bob Cramblitt

Rate this article:   Avg rating: from a total of 8 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.