Cymen Vig

Writing Custom Rules for Web Deployment Tool

This is some very useful information. It’s somewhat frustrating that msdeploy is similar to rsync but seems much less functional from the stand point of what is baked in for rules. And this approach is unofficial and all that so…

The best SQL formatter I’ve found so far is: sqlformat.appspot.com It works nicely with subqueries which is becoming increasingly important for my use of it.

Sometimes one needs to replicate one gem environment to another host. Maybe there is a ruby way to do this but here is a quick script that runs “gem list” and then makes a list of “gem install PACKAGE -v VERSION” commands. It’s quick and dirty but maybe it’ll be useful for you too:

One of the tricky things with SQL Server Express is that tools like the index tuning wizard and similar are not supported or available. A work around for the lack of the Microsoft profiler is the AnjLab Sql Profiler which does work with the 2005 and 2008 SQL Server Express products.

Recently, I was faced with a database that needed a number of indexes to improve performance. The database was running on SQL Server Express 2008 R2 so I couldn’t be lazy and use the index tuning wizard. Some research revealed these resources:

• Uncover Hidden Data to Optimize Application Performance

• Find Most Expensive Queries Using DMV

Quickly finding likely columns to index

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

This is as simple as running the query above as found in the first referenced article. As one adds indexes, simply refresh the query and you’ll continue to see more items. Note that in some cases if views are in use the suggested indexes may be present but unused by the view. In that case, one should take a closer look at how views work and potentially alter the views to support indexing of the view columns.

Finding likely columns to index via heavy queries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

The other way to approach index creation is to look at the heavier queries and see what they are doing. The query above comes from the second referenced article. In some cases, it is obvious that indexes will help. Looking at the actual queries being run is a lot of help because sometimes it is clear that the problem is with query itself. In that case, either the query needs to be modified or the approach to getting that information needs to be rethought (an example of that would be moving to a nightly batch process to collate expensive-to-query data).

In both cases one has to be critical of the work being done. Index creation is a bit of an art because simply indexing everything is not an option. Thinking about joins and indexes when designing the database is definitely helpful but going back to add them can be done and it doesn’t have to be painful even when using SQL Server Express!

I need to be able to register a route via an instance of RouteCollection to an area. I couldn’t put it in the normal area route registration as it had to be registered last. It took a while to figure out that this works:

1
2
3
4
5
6
7
8
9
10
11
public static void RegisterAreaRoute(RouteCollection routes) {
    routes.MapRoute(
        "MyRouteName",
        "{*path}",
        new
        {
            // options
        },
        new[] { "_Namespace_.Areas._Area_Name_.Controllers" }
    ).DataTokens.Add("area", _Area_Name_);
}

Routes are rewrite rules with the “object defaults” part of the routes being similar to the variables parsed out of the URL via mod_rewrite (but they the “object defaults” can store values from other sources so the routes are somewhat like a data store).

ViewData[] is similar to a global variable in PHP and it is populated by the Controllers and seen by the Models.

Lots of differences of course but just a quick thought…

One of the annoyances with moving from URLs based on actual files on a file system to database-driven websites is ugly URLs. There are a lot of articles on working around the problem. Typically, the solution is having a numeric identifier along with a more verbose string that identifies the article. The verbose string is then ignored by the application.

This approach makes a lot of sense when listing categories of things that include many items. Common examples would be movies, books, etc. If a numeric identifier was not used then the database lookup would need to be based on the verbose string. There are two issues here: database lookups based on strings are inefficient compared to numeric values and the longer the URL the more likely it will be accidentally corrupted when used outside of the application (say in an email).

However, I argue that always using the numeric identifier is not necessary. A first class URL (one without a numeric identifier) should be used when possible. Two methods of doing this come to mind: registering all the URLs that are first class with the application so that the URL to numeric identifier is known by the application without a database lookup or by doing a database lookup based on the verbose string. I believe the first method is most performant so that is the route I am going to investigate first. I also believe the second method can be made to perform well particularly when there are a limited number of first class URLs (I am not arguing that one should use first class URLs for categories with large numbers of entries) however there are issues besides performance (see the case where the URL includes a “/” within the entry name but “/” is also used to separate the entries – I suspect this is easier to handle with the first method).

My next article will focus on an implementation of this in ASP.NET MVC although this post has nothing to do with any particular platform so the same methods should work with any platform. One critical requirement is that URLs can be registered/deregistered during runtime in the event of adding/removing content.

I am going to blog about each tutorial mentioning just the issues encountered with VWD 2010 Beta 2 and ASP.NET MVC 2 RC installed.

1. The Model generated using the Microsoft Entity Framework named “MoviesDBModel.edmx” did not have MovieSet as in the article but instead Movies1. There is an option in MoviesDBModel.edmx on the properties of Movie that has “Entity Set Name” defaulting to Movies1 – that should be changed to MovieSet.

Download the ISO instead of doing the installs over the net as it is highly likely that more than one installation attempt will be needed to get everything installed.

1. Install Visual Web Developer 2010 Express Beta 2 from ISO (if virtually mounting the disk, make the mount persistent as there is a reboot halfway through the install and if the disk is not present, the install bails).

2. Uninstall “Microsoft ASP.NET MVC 2” but not “Microsoft ASP.NET MVC 2 - VWD Express 2010 Tools”. The VWD 2010 Express Beta 2 comes with a version of ASP.NET MVC that is pre-RC.

3. Install AspNetMVC2_RC2_VS2008.exe (yes, that is the correct version for ASP.NET MVC 2 RC even though it has 2008 in the name).

4. If “Microsoft Visual Studio 2010 ADO.NET Entity Framework Tools Beta 2” does not show up in Add/Remove Programs then force it to install by opening a command prompt and going to the \VWDExpress\wcu\EFTools directory of the CD and running ADONETEntityFrameworkTools_enu.msi USING_EXUIH=1 /log “dd_ADONETEntityFrameworkTools_enu_MSI.txt”

I’ve released mediawiki-gsa-interwiki which is based on mediawiki-gsa-engine but adds support for results from multiple local wikis by hooking into the interwiki part of the Mediawiki search classes. This is useful for those that have multiple Mediawiki installations indexed by a Google Search Appliance (GSA) and want the search results for the current wiki to be results from the current wiki but also have a sidebar with results from other local wikis. Not a huge market there but useful all the same for those that need it. There are some other subtle changes documented at the project site.

I’ve switched from a Virtual Private Server hosted at Gandi.net in France to one hosted by Prgmr.com in California. I switched because the prices increased at Gandi.net after the beta and the latency was beginning to get annoying (from Wisconsin, USA). Prgmr.com is attractive because the prices are very reasonable and the RAM allocation for the price generous. I’m paying about 1/2 - 2/3 of the Gandi price but getting twice the RAM and disk space. The one thing that Gandi did have going for it is that the bandwidth was unmetered (but capped). However, for my purposes, the Prgmr model of X GB per month is not an issue.

The setup process was quick and the VPS is performing great with my minimal usage. I’m happy with the switch and would definitely recommend considering Prgmr as a VPS provider. I have no need for a high end VPS at this time but when I do, the options at Prgmr will definitely be the the first I’ll consider.

On a final note, at first I attributed the slow console feel of the VPS at Gandi to latency but after comparing the feel between Gandi and Prgrmr and the latency figures, I’d put some of the Gandi slow down to low CPU allocation for the single slice VPS offering. I suspect Prgmr is more generous with not only the RAM but the CPU slices which is excellent for those looking for a well performing but small VPS.

The biggest issue is that Windows 7 has very strong requirements on driver signing. In theory I think this is an excellent idea and may indeed keep the operating system installs of many people more stable than if this requirement is not present. However, in some cases it is an impediment: We need a libusb-based driver that will work with ArgyllCMS and the only option that I’m aware of is an unsigned driver. The good news is that Driver Signature Enforcement Overrider program exists for Windows 7 and it can put it into “Test Mode” which will allow you to load an unsigned driver.

Files (google for them):

• dseo13b.exe

• SIXAXIS_libusb-win64.zip

• Argyll_V1.0.4_win32_exe.zip

• dispcalGUI-0.2.6b3-Setup.exe

Steps:

1. Extract SIXAXIS_libusb-win64.zip

2. Run bin\inf-wizard.exe from the above extraction and choose your Spyder2 device to create the libusb-based driver files for it

3. Run dseo13b and follow directions to put the system into test mode

4. Reboot and run dseo13b again to sign all of the spyder* files in the above directory (I had to sign the .inf files and I signed the .sys and .dll too). Do this for each spyder* file in the SIXAXIS bin directory (the files created by inf-wizard.exe).

5. Start Device Manager and update the drive for the Spyder2 device. Choose “Have Disk” or similar and navigate to the SIXAXIS bin directory. Choose the unsigned (no certificate) driver.

6. I had problems with the above. I had to repeat it a couple times before it finally worked.

7. Extract Argyll_V1.0.4_win32_exe.zip to C:\ (the files inside are all in a Argyl_v1.0.4 directory

8. Go to the Windows Advanced System settings and add C:\Argyll_V1.0.4\bin to the end of the PATH environmental variable. You may be able to skip this step if you use the displayCal GUI instead of Argyll directly. I didn’t skip it.

9. Install dispcalGUI-0.2.6b3-Setup.exe.

10. Reboot again.

11. Start a command prompt and go to c:\argyll_v1.0.4\bin and run “spyd2en.exe” to extract some of the drive firmware payload (or similar) from the standard Spyer2 driver. (This maybe the equivalent of the “use Spyder2 device” (don’t have exact wording) option in the displaycalGUI interface).

12. In displaycalGUI, choose the menu option mentioned above.

13. You may need to restart displaycalGUI

14. Now displaycalGUI should see the Spyder2 device.

When I tried to use displayCal with the Spyder2 device, I kept getting “instrument access failed” messages kicked back from one of the ArgylCMS executables. I don’t know what I did to solve it. It might have been rebooting. I googled a while on it and finally it just started working. Not exactly a stellar guide here but there is little else out there.

The rest of it is still uncharted territory. I did notice gamma defaulted to 2.4 so I bumped that down to 2.2. I set my profile to Photo and am running a calibration right now. It looks like quite a lengthy process to do calibrations/profiles. Or the progress is slightly unclear. Either way, I’ll be ecstatic if this lets me use the full potential of the Spyder2 device without having to buy the Spyder2 Pro license/upgrade (at that point, I’d opt towards buying a new device).

The last thing missing is the LUT/profile loader. I am hoping the Spyder2 profile loader will work with the files generated by ArgylCMS.