Fog Creek Software
Discussion Board




Database Programming

An employee of ours recently made a log viewer that reads from a table in our SQL Server database.  I told him to put some parameter controls at the top of the form and a grid at the bottom to view the returned recordset.

The parameter controls are there to allow you to choose the sub-set of data to view.  You can pick a date, type in a computer name, etc.

Well in the TextBox_Change() event he put a call to the procedure that queries the database.  This means every time somebody types a single letter into the text box it sends a query to the database.

When I saw this I said to him "You can't be serious about this piece of code.  You want to query the database every time somebody types one letter into this text box?".  He said that he thought it was cool that the grid was being filled as you type.  I told him that he'd better stop thinking that way because that's not the way we do things. 

Then he got all upset and left because he said that I was picking on him (right before that I told him to declare Constants and Enums at the top of a module instead of below the private variable declarations). 

I've told him a number of times since he's started here (3 months ago) that you have to treat the database as a scarce resource but he fails to see the big picture.  Before this he was doing C++ programming mostly and he's just learning Database stuff.  Am I being too hard on him?

Wayne
Thursday, August 14, 2003

Maybe if you explained the why then he would be more open.  Explain that it is cool that it updates all the time, but you have to balance performance and usability.  Also, is your database connection and server that fast that it can reliably show results in the time it takes to type?  Even if someone is typing at 50wpm?  That could be very annoying.

Another thing you can do is show him graphically how bad that is for performance.  Choose a time that the database is particularly loaded and search for something very common.  If you have to load the database with a script of your own before you go to show him this I understand :)

Anyway, after all that rambling, the point is show him the why as well as the how.  And take on a teaching standpoint with him, not a "my way or the highway" standpoint.

Andrew Hurst
Thursday, August 14, 2003

--
When I saw this I said to him "You can't be serious about this piece of code.  You want to query the database every time somebody types one letter into this text box?".
--

By the way that type of comment immediately puts people on guard, and essentially ends all productive discussion without it even getting a chance to start.

It might have been better to come at it from his angle, and let him find the answer that it was too db intensive.  i.e.:

"Hey, thats neat that it updates as you type, how'd you do that?"
"I did it by tying the update to the onChange event"
"Could that have performance problems? That seems like a lot of queries for a long search string..."
etc. etc.

Its all in the wording.

Andrew Hurst
Thursday, August 14, 2003

He must use iTunes with it's awesome, fast live search.

Gorkon
Thursday, August 14, 2003

Yeah, but what if this is happening after having discussed it the "nice way"?  Like I said, we've already talked about the importance of this staying away from over-using calls to the DB.

Don't you ever get upset Andrew?

Wayne
Thursday, August 14, 2003

Oh all the time, but you have to take a deep breath and deal with it.  If, say after 5 times, they still don't listen, still don't try to do things the right way, and generally ignore you, then let them know in not-uncertain terms that they're doing it wrong.  If they are making a concerted effort but still don't make any changes, then maybe throw them a few programming books to read to get an idea of how things should work.  Pragmatic Programmer, database design books, etc.

If they are not responsive at all, then you have a lame duck.  Act accordingly.

The point of my posts above was not that you should never get angry, or tell them the right way to do it, but that you should do it in a non-blaming way. 

For a non-programming example, take a friend of mine.  I'm getting married soon, and this friend of mine always wants to help, and give suggestions.  The problem is whenever she gives suggestions she phrases them like:
"You should do X"
instead of:
"Have you thought about doing X?" or "What about X?"

That little language change turns my fiancee off to every suggestion she makes.  Even though I know my friend doesn't even realize that she is phrasing things like that, and in her mind she is asking it the second way.

Giving people suggestions with programming, or teaching in general is the same way.  Let them think they came up with the solution.

Andrew Hurst
Thursday, August 14, 2003

Find-as-you-type is significantly more usable than find-when-you-press-return.  The question you should be asking is not "why is this programmer so dumb?"  It's "how can we make this programmer's brilliant UI have acceptable performance?"  It might not be hard to prevent the UI from swamping the database.

Rob Mayoff
Thursday, August 14, 2003

Yah.  Your approach is too combative.

For me, the magic words are: "I noticed you did x, but did you consider y?"

For example: "I noticed you do a query on TextBox_Change.  Did you consider what would happen if lots of people were using this page on a real busy database?"

This gives him the opportunity to come up with his own solution instead of you forcing one on him.  "Oh yeah, I should cache that data locally" or "oh yeah, hrmm, well, maybe that feature isn't really necessary".  Or even, "yeah, but I did a back-of-the-envelope calculation and if we had a 1000 people doing this it would only strain the database by 5%".

And in formulating this question, you can weed out the superficial problems from the real blockbusters.  For example, finish this sentence:  "I noticed you defined your constants above your variables, did you consider ..."

I can't do it without making some real tortured appeal to ease of maintence ... which leads me to believe there's some micromanaging going on.

Alyosha`
Thursday, August 14, 2003

I see what you guys mean by wording.  I know I have problems with this, people skills are not my strong suit.  I will work on the wording, thank you for your help.

Re the "Find as you type" suggestion Rob, I respectfully disagree.  This is a huge table with many records, the only way to make it responsive would be to download the whole table when the form opens or in the background and filter it as you type. 

In this case, making it more useable would make it less functional.  I would have to requery on a huge recordset to keep it up to date.

Wayne
Thursday, August 14, 2003

speaking of downloading the whole table...

That reminds me of a co-worker that I was having similar problems with.  He cached all 200+ results of the query in javascript arrays, and passed them between pages in the POST request rather than making a single query to the database.  He said it was because the database was slow to interact with.

Later, to fix an unrelated problem with his code when he wasn't around we were going through his code.  The database was slow because he was doing a 4 table join with 3 subselects in the SELECT part of the query to get more results.

Thats one person me trying to be nice to and let him come upon the solution himself didn't make a difference.

Andrew Hurst
Thursday, August 14, 2003

Am I being too hard on him?

No.

Yes, you should probably be more diplomatic, however, imo all bets are off if the application he is building somehow affects you professionally.

What is your relationship with this co-worker?  Are you in charge of babysitting him (his mentor)?  Are you his boss?  Are you both working on the same project?

What/who is this application for?

Sounds to me like this "database programming newbie" needs some serious supervision such as having his source code inspected before it is released into a production environment or to the general public.

One Programmer's Opinion
Thursday, August 14, 2003

find as you type _is_ cool.

Actually, depending on how much ram you can assume is available you dont need to requery it every time.

Just once after they type their first letter, after that you have a record set that contains every record you will need until they delete & retype that first letter.

Id think hard about making that work, it is a _good_ UI feature to have where it is doable.

FullNameRequired
Thursday, August 14, 2003

"The database was slow because he was doing a 4 table join with 3 subselects in the SELECT part of the query to get more results."

There's gotta be more to it than that - that's not a complex query at all. Was there a cartesian join involved? Or maybe some fields should be indexed?

Philo <- is amused when people quote the number of joins as "this query is too hard"

Philo
Thursday, August 14, 2003

Find-as-you-type is significantly more usable than find-when-you-press-return.  The question you should be asking is not "why is this programmer so dumb?"  It's "how can we make this programmer's brilliant UI have acceptable performance?"  It might not be hard to prevent the UI from swamping the database.  - Rob Mayoff

Great post. Before I read it, I was thinking in terms of how to fix the programmer. Now I'm thinking you could reach some sort of middle ground.

Like ... after the first character is typed, grab all the data that matches it. Subsequent characters are going to narrow the dataset already retrieved, so just use them to do some front-end filtering of that dataset.

If one character does not produce a sufficiently small result set, maybe the second or third character can trigger the search.

Bonus: Going to him and saying, "hey, I've been thinking about your solution and it may be that we can implement it or something like it without the performance hit I was concerned about." Building on his solution may get the relationship on the right track.

Zahid
Thursday, August 14, 2003

Philo, you're being too generous.  Not every RDBMS is MS SQL or Oracle.  Some of them *do* choke on joins and subselects *cough*MySQL*cough*.

Of course, if he's using subselects, then it's probably not MySQL.

Richard Ponton
Thursday, August 14, 2003

---
"The database was slow because he was doing a 4 table join with 3 subselects in the SELECT part of the query to get more results."

There's gotta be more to it than that - that's not a complex query at all. Was there a cartesian join involved? Or maybe some fields should be indexed?
---

A quick look over some of the source, gives the following:
the where clause was of the form:
where lower( field1 ) like '%search_term%' OR
.. ( 7 fields tested this way )
...
OR record_id in ( select record_id from table1 where base_id = N )
OR record_id in ( select record_id from table2 where base_id = N )
OR record_id in ( select record_id from table3 where base_id = N )

From what I can tell without inserting debug statements (the query is crafted over about 100 lines of code) there are just way too many full table scans and like clauses.  This implements a generic search utility for a database.

This runs in about half a second on a 500KB database on a quad xeon server with 1GB of ram.  ( and yes its been tuned correctly.  Other databases on the same machine run just fine and they're much larger).

And thanks for calling me on how the query was not super complex as described.  Its a holdover from my mysql days a couple years back when I shyed away from joins ( and we didn't have subselects :)

Andrew Hurst
Thursday, August 14, 2003

See, it's not the joins or subselects that are evil - it's the like and the OR clauses that are killing you. If they were *AND* clauses you'd be restricting the resultset and returning *fewer* records - a *good* thing, asssuming base_id is indexed.

*Philo* <-hopped up on emphasizing *words*

Philo
Thursday, August 14, 2003

OR clauses are awful for performance, because they're inclusive rather than exclusive. If you're using MSSQL, you should look at UNIONs rather than ORs. I've seen many a 60-second OR that ran in sub-1-second as a UNION.

Troy King
Thursday, August 14, 2003

Hey now, its not my code :)

I don't get to change it, and if I did I would re-write it all in perl in about 3 days.  Its 5 web pages as a search interface to a database.  As of right now its 8000 lines of java and html to generate these 5 ( simple! ) pages that took 3 months and 3 re-writes to develop by one person.  Don't get me started :)

I did what I could to influence the design decisions but was ignored.  Now its just an example I use in software engineering discussions :)

Andrew Hurst
Thursday, August 14, 2003

Oh yeah, and its running on Oracle.

Andrew Hurst
Thursday, August 14, 2003

Is he using recordset.filter?

http://www.aspalliance.com/aspxtreme/ado/lettinguserssearchforinformation.aspx?pageno=3

I'm preety sure that ADO avoids requerying the database when applying the filter property.

Ged Byrne
Thursday, August 14, 2003

Not entirely on topic, but there are many joins that optimizers don't do right; For a good introduction, look at [ http://www.cs.nyu.edu/cs/faculty/shasha/papers/aqueryseminar.ppt ] (It's not working now, I suppose due to the NYC power problems).

I'd be really surprised if a 5 way join such as those described in this presentation are properly optimized by either SQL Server or Oracle. Any of you guys have different experience than Shasha & Lerner?

Ori Berger
Thursday, August 14, 2003

Rob is right, wayne is wrong. the original programmer's UI is better. why are you treating the database as a "scarce resource?" most databases these days can handle multiple requests per second. 

on tone, being a dick is usually better than being diplomatic. but make sure when you are being a dick that you are either right, or prepared to be flamed yourself when you are wrong.

.
Thursday, August 14, 2003

How many people making comments have ever actually TRIED this technique with large volume, multi-user databases?

Yes, it may be an interesting technique for LOCAL, single-user databases. But move your database to another box, share it between 20 or more users, and populate it with several hundred records. Now try your interface design. Is a 1-2 second delay between keystrokes appropriate?

And how will you support MULTIPLE query parameters if required at a later date? This is a common post-release-1 request in database applications.

And how will you support mid-string wild-cards, such as 'SMI%SON'?  Another common post-release request.

Sorry, but you're just not thinking clearly about large volume database systems.

HeWhoMustBeConfused
Thursday, August 14, 2003

Query as you type is cool. 

However for now it's just a matter of getting it done today.  I've since come to the conclusion this is not the fault of said employee though. 

Really it's my fault.  On top of confusing him with some un-answered design questions, I was expecting him to do stuff that I still have to stop and figure out.

For instance, I wanted him to use ado async calls to connect and execute on the server so the ui remained responsive. 

Also, I am a big meanie.

Wayne
Thursday, August 14, 2003

HeWhoMustBeConfused is right too.  If you think about it logs are usually quite wide, implicitly requiring the existance of ways to slice and dice the data by means of multiple parameters, which this log viewer has.

I've since re-designed it to have parameters on one tab and the grid/detail viewer on another tab.  When you update a parameter(s) and switch tabs it detects the change and does that async connection/query. 

Wayne
Thursday, August 14, 2003

As a notorious peckerhead myself, I highly recommend "How to Win Friends and Influence People." It's pushed for marketing people and consultants, as it should be. But it's got a lot of good stuff that would help programmers too.

Basically I've got the ultimate test environment here. I have a hostile thirteen year old stepson, who's prone to explode if I speak to him at all. I have daily opportunities to try out the ideas recommended in the book. In short, it works like a charm. If you can apply the principles in the book, you can tell somebody to go to blazes and they'll come away with a smile on their face.

I wish I would have read this book before my I took my first programming job.  Could have saved myself a lot of burnout and frustration.

Clay Dowling
Friday, August 15, 2003

Developers when new to something need mentoring and a key aspect of mentoring is feedback without time lag.  The developer in question would have had less personal investment in his code if you had discussed this at design time rather than after it was built.  A developer who is new or is under suspicion of dodgy practices should be asked to stick to procedures and produce good levels of documentation at every phase.  Then you should review the design documentation with them before they proceed to build.

Nicolas

Nicolas Woollaston
Friday, August 29, 2003

*  Recent Topics

*  Fog Creek Home