Monday, September 12, 2016

Fictitious Data Generators for Web Developers

When creating and testing web pages, developers often need to fill in fields with fake data.

This can be automated with tools such as the Form Filler Extension for Google Chrome, but I prefer to key in data manually.

Why?  Two reasons: 1) the data I fill in will be closer to production data [since I know what production data is like, unlike automated tools] and 2) to become more empathetic to the end users who will be using the page.

When keying in fake data, I could always use "123 First Street" for addresses, and "Joe Smith" for names, but I think it's a good idea to mix things up.  Doing this can also reveal problems with how the page works (for example, a longer address might reveal that the the database field for storing address line 1 is too short).

To get a good variety of fictitious data, I use several sites.  This one's a staple.  It'll give you just about everything you need for a fictitious person.  The one issue I've had with the site is that the product owners will think I'm testing with production data, since the fictitious identities are so convincing.  On one occasion a product owner asked me to use an identity that's obviously fake (e.g. "Joe Sample"), because he was concerned that if a customer saw a screen capture of the page filled with with data from, they would think that we're divulging customer data!  This site is useful for generating strong passwords that are easy to convey verbally and remember.  Good when testing registration pages.  This is nice for getting filler text.  Uses some classic works of literature.  This is another site for generating filler text.  The beauty of this site is that it generates random, nonsensical academic papers.  You should read the story behind it -- it's quite amusing.  A repository of high quality photographs.  When I'm demoing or testing a page where users can upload images, I like using attractive images like these.  Yes, it's Bing.  You can make fun of me.  Doesn't change the fact that the photographs are usually amazing.  I'm a big fan of classic Simpsons.  This site lets you search for your favorite dialog from The Simpsons and download the images.  Remember, no new crap.  Seasons 2-20 only.  This is for when you want to generate a lot of data, most likely to import into your database.  Like a bulk version of, but more customizable.  This is for creating placeholder images with specific dimensions.

The final link is my favorite.  I also use it for creating filler text (yes, I'm a big advocate of quality filler text).  The link is:

Now, if you're going to bookmark it (and I think you should), don't follow the link and bookmark the page you land on.   You need to bookmark that URL, since you'll get redirected to a random Wikipedia page every time.  It makes filler text fun and educational.

Want to take things to the next level?  Here's what I did -- I created an AutohotKey script with a bunch of shortcuts to random WikiPedia articles.  So I can key in "fil2" and AutoHotkey will replace it with an entire random article.  If you have a meeting when you're demoing a page or pages where you have to fill in textareas, try this technique.  For one thing it saves time, but you might also get a laugh or two.

Right now the WikiPedia articles are hard-coded in my AHK script, but I have an idea for a project where users can generate AHK scripts with keyboard shortcuts for n random articles.  If/when I create that, I'll post the code here.

In the meantime, if you're a web developer, I hope you find something useful in my list.

Monday, September 8, 2014

The Easy Way to Optimize a Stored Procedure

I noticed a stored procedure in SQL Server (2008) -- let's call it p_TheProcedure -- taking about 10 seconds to run on production,  Since this is a procedure that generally should run in under a second, that was a problem that I needed to fix.

So I created a copy of called p_TheProcedure_test that I could use to fine-tune without affecting anything on the live site.  (Before you lecture me on testing things on production, let me just point out that this procedure doesn't update any tables other than temp tables).  So I ran p_TheProcedure_test and noticed that it ran in under 1 second.  That's funny, let me try p_TheProcedure again -- 10 seconds.  What's going on?  The code is exactly the same in both procedures.

So, I tried this:

drop procedure dbo.p_TheProcedure


create procedure dbo.p_TheProcedure

After than, running dbo.p_TheProcedure is back down to below 1 second.  Problem solved.

I'm guessing this has to do with a bad query execution plan.  I could research the exact technical reason, but I'm not a theoretician, and I have work to do.

Wednesday, July 9, 2014

Excel Needs a Custom Formula Sandbox

Today I found myself in need of an Excel function to search a string from right to left (the opposite of the built-in FIND function).  I wasn't sure if Excel had a function for that, so I Googled it to find that Excel does not have that function, but it can be done using a complicated formula.

Of course it can be also done with a macro, which is what I ended up doing.  Since I have a personal macro workbook with a lot of custom functions, so I'm not adverse to creating new ones whenever I need to.

A lot of people are adverse to this approach, as the StackOverflow thread I linked to above indicates.  And I can understand why, since macros come with a lot of overhead (compatibility, security, etc.).

That gave me an idea.  MS should create a "formula sandbox" area in Excel where users can create custom formulas.  For the language, I would suggest limited-functionality versions of C#.NET and VB.NET.  The limited functionality would be necessary to ensure that the formulas don't present any security risk.

The custom formula sandbox would be linked to the user, any any custom formulas that he/she uses in the spreadsheet would be saved with that spreadsheet, so that the spreadsheet works for any user.

I think a lot of Excel users would welcome something like that.

Thursday, July 3, 2014

Dual Monitors and Excel

Windows could really use a "super maximize" button to maximize a window across all monitors.  I know that software packages made for managing multiple monitors have this functionality, but I would like it baked into Windows.

In any case, the program where I most often find myself manually resizing the window to span my two monitors is Excel.  A while ago I got sick of doing that, so I wrote a simple macro to do it.  Here's the code:

'the control parameter can be omitted.  I included it in my macro because that signature is needed for macros
'invoked from the ribbon
Sub MaximizeAcrossTwoMonitors(control As IRibbonControl)

    Dim TargetWidth As Integer
    TargetWidth = 2880 'fine-tune as needed for your specific setup
    Dim TargetHeight As Integer
    TargetHeight = 780 'fine-tune as needed for your specific setup
   'if the window is already maximized across two monitors, we're going to assume the user wants to return to regular maximized window state
    If Application.Width = TargetWidth And Application.Height = TargetHeight Then
        Application.WindowState = xlMaximized
        With Application
            .WindowState = xlNormal
            .Left = 1
            .Top = 1
            .Width = TargetWidth
            .Height = TargetHeight
        End With
    End If

End Sub

You can put that macro in your personal macro workbook and assign it to your quick access toolbar for easy access.

Once Excel is maximized across your workspace, you may want to have two or more workbooks displayed side-by-side.  You probably know that you can do that by going to View --> Arrange All --> Vertical.

However that's three steps I found myself struggling to recall the specifics of.  So, another simple macro for your quick access toolbar would be:

Sub ArrangeWindowsVertically(control As IRibbonControl)
    Windows.Arrange ArrangeStyle:=xlVertical
End Sub

I hope someone finds this useful.

Tuesday, October 8, 2013

Dangerous SQL Aggregates

Here's one for the the "seemingly innocuous change that introduces a bug" file.  The code I'm sharing is not the actual code where the incident happened, just the same pattern.  Here's the TSQL:

declare @Dates table (aDate date)
insert into @Dates values ('1/1/2000');
insert into @Dates values ('1/1/2001');
insert into @Dates values ('1/1/2002');
insert into @Dates values ('1/1/2003');

declare @AnyDateFound bit = 0;
declare @MaxDate date;

       @AnyDateFound = 1
       aDate > '1/1/2005';

In this example, @AnyDateFound would obviously be 0.

I made an update like this:

declare @MaxDate date;

       @AnyDateFound = 1,
       @MaxDate = max(aDate)
       aDate > '1/1/2005';

Most SQL Developers would spot the error right away.  But I assumed that if no records met the conditions in the where clause, @AnyDateFound would not be updated to 1, and would remain 0.  Not the case.

The problem is that aggregates always return records.  There is a maximum, even if the maximum is null.  It makes perfect sense when you think about.

select 0 where 1 = 2; -- no records
select max(0) where 1 = 2; -- one record

Thursday, August 22, 2013

ASP.NET - Error When Switching To A Precompiled Application

When changing an ASP.NET web application from being dynamically-compiled to being precompiled, a runtime error cropped in my staging environment, which did not occur in my development environment. The error is an InvalidOperationException that reads:

The type specified in the TypeName property of ObjectDataSource 'MyDataSource' could not be found.

There are lots of suggestions for fixing this to be found on StackOverflow and elsewhere.  Some involve qualifying the type name in your ObjectDataSource declaration.  For me, it was already fully-qualified, using the name assigned by ASP.NET.

Another suggestion was to change the application from a virtual directory to an application in IIS.  In my case, it was already an application.

So, none of the suggestions I found fixed the problem.  For me, the problem fixed itself.  In about an hour, the error just went away.  I'm guessing this is because something cached on ASP.NET.  Maybe restarting the application in IIS would also fix the issue.  I don't have access to doing that in the staging environment on my PC, so I can't verify.  But if you're getting this error and don't see what the issue is, just try waiting. In one case, it took about 12 hours for the error to fix itself.

Of course, is the error is occuring on production, try restarting the application in IIS. Just keep in mind that if you're going from dynamically-compiled to precompiled, this runtime error is likely to occur if your application uses ObjectDataSource controls.

Tuesday, May 14, 2013

Arbitrary Groups in SQL Server Using Windowing

I often need to divide recordsets into groups for various reasons.  For example, if we’re testing an application, the testers might want a census of sample users they’re going to test with, and I want to assign each sample user to a test group to ensure that they’re only tested once.  This can be done using windowing in SQL Server (windowing was introduced in SQL Server 2005).

Here’s an example:

--create a sample dataset  
 create table #temp (MyRecord integer identity(1000, 7), [Name] nvarchar(50));  
 declare @x integer = 1;while @x <= 500 begin  
      insert into #temp ([Name]) values ('Sample ' + convert(nvarchar(40), getdate(), 121));  
      set @x = @x + 1;  
 -- divide into groups  
 declare @Groups decimal(20, 4) = 30;-- how many groups we want. The overflow will go into another group  
      [Testing Group] =   
                     row_number() over (order by t.MyRecord)   
                     / (count(t.MyRecord) over () / (@Groups)) -- this is going to be the size of each group  
                     + 1,  
                     0, 1 -- arguments for the round function. This will cause the decimal to be truncated                 
      #temp t;  

There are probably more elegant solutions, and the code looks a little messy, but it works.