I’m pretty excited about some huge (~90%) performance gains I’ve been able to make in our custom UI over the past few days.
Originally, our software had the typical default grey forms with drop-down menus, which was pretty much the standard in the 80s and 90s. Shortly before I started here, they migrated to our “Facelift” UI, which is really cool, uses a themed ribbon menu, shiny buttons, mouseover effects, rounded textboxes, etc.
We’re still using VFP, so we don’t have the advantage of the new .NET UI features, so we are doing all this “the hard way” by custom drawing our controls, every time the form is drawn. This slowed down the rendering of our forms by quite a lot.
So, the other day I took it upon myself to dig into that drawing code, and see if I could make some optimizations. I implemented a caching system, so that we save off each control as a PNG after we draw it the first time, and then the next time, grab it from cache. I also implemented an “ImageServer” that pre-loads all cached images on system startup.
This drastically improved performance. Forms are now back to being instantaneous, even with our UI goodies. Obviously, the downside is we now eat up a bunch more disk space for the cache, but these days disk space is cheap and plentiful. Just in case, I implemented a global “GDI Optimization” setting so the feature can be turned on or off.
I recorded a sample form opening with and without optimization and sent it to my manager and coworkers and got a few “wows”. It’s a hit!
A caching mechanism is something to strongly consider if you are doing some custom relatively static drawing of this type.
(If any VFP developers using GDIPlusX want more specific details, let me know. It takes relatively few lines of code to make this change.)
I just ran across a rather complicated instance of this, and thought I’d post about it. It’s the kind of thing that is obvious after the fact, but easy for newbies to screw up and cause confusion, especially with monolithic queries with many joins and clauses, that get edited and extended over time.
SELECT *
FROM a LEFT JOIN b ON a.pk = b.fk AND b.somefield = <value>vs.
SELECT *
FROM a LEFT JOIN b ON a.pk = b.fk
WHERE b.somefield = <value>The difference here is that the first query will ALWAYS return all the rows from table a, along with the matching data from b where the foreign key links and the field equals the value. This is probably what you mean to do in most cases, or you wouldn’t have the OUTER JOIN in there to begin with. The second query will only return the rows from a that link to b AND that have the field equal to the value. This is because b.somefield will be NULL when there is no match in b, and NULL doesn’t equal anything, even NULL itself. If this is really what you want to do, you may as well make it an INNER JOIN.
That is pretty obvious here, but if you have 10 joins and 15 WHERE conditions, and 3 years down the line, you want to add a new query parameter, it can be pretty easy to add to the WHERE clause instead of the JOIN, and not notice you introduced a bug.
I’ve been working on a project to barcode-enable our casino system for inventory, issues/returns and point-of-sale. One of the convenience features is to automatically pop up the Map/Add form if we scan a barcode that hasn’t already been mapped to an item in the database.
Our existing entry screen was set up as non-modal (WindowType=0), but that wouldn’t do in this case, as my calling form needs to wait for this form to complete (i.e. add the item and return the new PK) before it can proceed. Also, I obviously didn’t want to change the form to ALWAYS be modal, but only act that way in this one instance.
This took me a bit longer to remember/figure out than something this simple should, so I figured I’d write about it in case anyone else runs into this situation. If you have a class-based (VCX) form, you can simply CREATEOBJECT() and call o.Show(1). However, in my case, our preexisting form was an SCX form, so using that object-based approach is a little trickier. Here’s how I got it to work:
* Pass in .T. to tell form to work in "immediate" mode
* (has to be implemented in the form that is being called)
DO FORM <SCX form> WITH .t. NAME loMyForm NOSHOW
* NOSHOW should have made this unnecessary, but it didn't work for me
loMyForm.Visible=.f.
loMyForm.Show(1) &&Now show it as a modal form
* Get new item key that was just added
lnNewID = loMyForm.<SomeReturnValueProperty>
loMyForm.Release()
loMyForm=.null.Post with 1 note
One thing I’ve been thinking about lately is the maintainability of SQL Stored Procedures. We have a couple of accounting systems which do some pretty heavy data manipulation for reporting. In one application alone, we have something like ~500 built-in reports. Each of those reports has a great number of parameters and options. A single report may execute one of several SELECT statements, depending on the options selected.
As an example we have an Over/Short report that can be run as a Detail or Summary version, and with 2 different categories of Employees, or both (a certain class of employees has an entirely different transaction table).
So, for this report, we have 2 stored procs, each with 3 slight variations of basically the same query, with slightly different JOINs/GROUPing. Now, a few months ago, I had to introduce another JOIN to be able to add a new column to the report, so I had 6 times the work to do, which is almost begging for a bug.
In client code, you can refactor your code for reuse and ease of maintenance, but that isn’t so easy with SQL. You could use Dynamic SQL to dynamically build the huge SQL query, but that gets pretty hard to visually parse, has performance implications, and opens you up to SQL injection concerns. You can try to do some SQL gymnastics to try to slim down to a single SQL query, but that gets very hard to maintain, and it becomes very easy to introduce a bug that might not be obvious, and hard to debug.
I’ve thought about refactoring the common query components into a separate SP, and then join/filter the results of that, but I’m not sure of the performance impact of that (something to try, at least). Anyone out there have a best practice/suggestion for this kind of situation, or is it just “pick your poison”?
Post with 2 notes
Is anyone out there a member of ACM? Lately, somehow, I must have been brought to their attention as I’ve gotten a couple letters and emails. Is there much of a benefit to being a member?
Post with 4 notes
When I started my last job, we had to go through extensive training for several months as most of the new hires were kids fresh out of college, and they used rather obscure and outdated programming languages. One of the classes was on customer service, and our CEO was the one who came in to give the lecture.
She asked for a show of hands on how many people thought “the customer is always right.” I think I even raised my hand disingenuously because I thought it was the answer she wanted to hear, even though it is clearly not true (e.g. if a parent orders vodka for their 7 year old). It’s the kind of mantra that most service-oriented companies live by, after all. But, she pleasantly surprised me when she said the answer is “no”.
There are laws and regulations that you have to abide, security concerns, as well as common sense and best practices for which you may want to fight. As a straightforward example, consider an architect with a client whose plans are structurally unsound, or not up to code.
You have a responsibility to protect the customer, even from themselves! For liability purposes, if not ethical reasons, you should stick to your guns when you feel your customer is making bad or even irresponsible or illegal choices.
I needed to do a FULL OUTER JOIN yesterday, which is the first time I can clearly remember needing one in a long time, if not since college. In my experience, full outer joins and cross joins are relatively rare in most database applications. Therefore, it might be confusing as to which one you need for a given situation.
A full outer join takes all records from either table, and joins them on the given criteria, if there is a match. An example might be if you had a table of languages, and a table of countries (and a table for official languages of countries), and you wanted a list of all countries with their official language(s), if they have any, and all languages that are not the official language of any country. You want output like this:
A cross join (Cartesian product) is for when you need every possible combination of records from two tables (possibly also filtered on some conditions). An example might be if a tennis coach is trying to come up with his best doubles teams. Maybe he has a magic algorithm for determining the best partners based on individual abilities and personalities, so even though Tom is the best singles payer, he is a lousy doubles player, and the team of John and Peter is the best possible one.
Microsoft seems to have a knack for making things more difficult than they should be.
A while ago, one of our techs in the field was trying to stage some data for a demonstration/screen shots for a new potential customer. He screwed something up, so he asked if I could send him a quick UPDATE command to fix it. Specifically, he wanted to change a date stamp and a primary key.
Now, admittedly, this stems from this application using the primary key for a “meaningful” purpose (i.e. presented to the user as a Transaction Number), which is a no-no. I didn’t write it, and wouldn’t have done that, but it’s now my job to support it.
This isn’t something I’ve done often (possibly ever) in SQL Server, so I thought SET IDENTITY_INSERT would enable you to turn off identity field protection so you could make the change. Nope! This is explicitly for INSERTing custom values. There is no SET IDENTITY_UPDATE in SQL Server, although there is in Sybase. Microsoft took the purist approach that there should never be a need to change a primary key.
So, your only alternatives, to my knowledge, are:
?
I had a series of unfortunate events with SQL Server this week. It started off with a seemingly innocent addition of a stored procedure call in an existing stored procedure. Basically, every time we do thing A, we now want to go off and make sure thing B gets done. So, I made that change, tested a call to stored procedure A, and it worked. Beautiful!
The first problem was that I didn’t write stored procedure B, nor was I immediately aware that stored procedure A can itself be called by another procedure. Stored Procedure B uses an INSERT/EXEC call to fill a temporary table. The procedure (let’s call it C) that calls A also fills a temporary table with the results from stored procedure A, using INSERT/EXEC. As you may know (though I didn’t), SQL Server doesn’t let you have nested INSERT/EXEC statements. So, now things were broken when you called procedure C.
Post with 1 note
I was a CSci student in the mid-late 90s, when software development was just growing out of its adolescence. Java was brand new. Design Patterns and Object Orientation were new, at least to the mainstream. The internet had just started to catch on. One thing I’ve found myself curious about is how education has changed since then. All sciences change over time, but I can’t think of any field that has evolved as quickly as Computer Science.
When I was in high school and college, most programming classes were either fundamental (algorithms and data structures), or niche (databases, graphics, UI, AI, OS, etc.). Every project was either built totally from scratch, or from scratch with some canned code you were provided. In the majority of real-world circumstances, you will instead be doing a fair portion of code maintenance, enhancements, bug fixes, etc. So, in retrospect, I think this is an area which was under-emphasized in my education (another is gathering requirements, which is done for you in school). I don’t know if that is still the case, but I suspect it is.
Page 1 of 3
