Returning Top SQL HierarchyID Ancestor

In an attempt to optimize a query that is taking me 10 seconds, I was looking for any way possible to shave off time.

The result will populate into a SOLR collection, and we narrow results based upon the top category ID. For example a product in the category “/7/15/92/” would be give a category of “7” in the SOLR collection. Thus we could do a search for a specific belt inside all vacuum parts for example.

In order to achieve this, our first attempt was the following:

REPLACE( SUBSTRING( HID.ToString(), CHARINDEX( '/', HID.ToString() ), CHARINDEX( '/', HID.ToString(), 1 + CHARINDEX( '/', HID.ToString() ) ) ), '/', '' )

This worked great, but I noticed that it was a bit intensive. So I did a little more research and found a method that shaved a second off of the result time:

REPLACE( HID.GetAncestor( HID.GetLevel() - 1 ).ToString(), '/', '' )

I actually couldn’t find this example anywhere and thought it up for this use. If you have a better method, feel free to comment. Thanks!

#ancestor, #hierarchyid, #sql

Console Logging of ColdFusion ORM SQL

If you read my post on getting the ColdFusion Builder Console working and use ORM, you may have run into some further questions.

When generating ORM, it’s wise to monitor what SQL queries Hibernate is generating for you, both for performance and debugging reasons. (Wasn’t ORM supposed to make my life easier?).

To start logging ORM’s SQL set this property in your application.cfc:

<cfset this.ormsettings.logsql="true">

You’ll may notice however that the default configuration will not show DDL queries used for creating or updating tables nor will it show the parametrized values (just a ?).

To enable these things look at <cf_home>\lib\log4j.properties (in my case it’s C:\ColdFusion9\lib\log4j.properties).

To enable logging of parametrized values uncomment and change the value for log4j.logger.org.hibernate.type to look like this:

log4j.logger.org.hibernate.type=DEBUG

It seems like a little overkill on what this ends up returning because not only will it return your parametrized values but also what each column returns. I wish I could disable the latter.

To enable logging of exports and updates (DDL) uncomment and change the value for log4j.logger.org.hibernate.tool.hbm2ddl to look like this:

log4j.logger.org.hibernate.tool.hbm2ddl=DEBUG, HIBERNATECONSOLE

I placed an example snippet below. Thanks to Rupesh Kumar for providing this information.

#sql-queries

SQL to ColdFusion ORMType Reference

I have not been able to find a good reference chart out there that maps SQL Data Types to ColdFusion ORM Data Types. It’s always really been my best guess. So I’m going to start a reference chart here that as I figure it out I’ll update. If you have any input on this please comment and I will update. Thanks!

ORMType SQL MySQL
big_decimal DECIMAL, MONEY DECIMAL
binary BINARY, VARBINARY TINYBLOB
blob TINYBLOB
Boolean [SMALLINT], BIT BIT
clob LONGTEXT
date DATE DATE
double DOUBLE, MONEY, NUMERIC DOUBLE
character, char CHAR
float REAL, FLOAT FLOAT
integer, int INT INT
long BIGINT BIGINT
serializable TINYBLOB
short SMALLINT SMALLINT
string CHAR, NCHAR, VARCHAR, NVARCHAR VARCHAR
text TEXT, NTEXT LONGTEXT
timestamp DATETIME, SMALLDATETIME, TIMESTAMP DATETIME
true_false CHAR
yes_no CHAR

SSIS Import/Export Wizard: “Unexpected Unrecoverable Error”

Using Microsoft’s SQL Server Management Studio’s Import and Export Wizard, I would get “Unexpected Unrecoverable Error” message when I would click “Messages…” or view a report when an import failed. This was very annoying and I wasn’t getting any work done this way.

Thanks to the SQL Server Forums I was able to resolve this issue by installing the Extended .NET Framework 4. If you have the Client Profile version installed, you will still apparently get this error. Be sure to do a Windows Update after the install as there is a security patch for it.

BTW, this import Wizard is a big fail for Microsoft. SQL Server 2000’s version did much more – now my life is just plain more difficult thanks to their “upgrade” – just say’n.

I am running SQL Server Management Studio 2008 R2 64-Bit on Windows 7 64-Bit.

Can Not Edit Hierarchyid in MS SQL SMS Editor

I’m basing my current project around SQL 2008’s hierarchyid data type, which is completely new to me. One frustrating issue I have found is the inability to edit any hierarchyid type’s data as a string inside the Microsoft SQL Server Management Studio’s edit feature. Granted I can control the data via the New Query feature, but I tend to use the interactive editor for quick and easy access to my data during the development process.

Take the following example:

Notice the hierarchyid data is converted into a string to be shown. However if you where to try and update the current value of ‘/1/’ to ‘/1/1/’ or update the current NULL value to ‘/1/1/’ then you would receive an error stating “Invalid cast from ‘System.String’ to ‘Microsoft.SqlServer.Types.SqlHierarchyId'”.

I’m not sure what data would even be acceptable in the field. I tried entering ‘0x5AC0’ for the NULL data and still received an error.

So from what I can see is this data type is unchangeable inside the editor.

The workaround for this would be to run the following TSQL:

UPDATE Table1
SET Question_Hierarchy_ID = '/1/1/'
WHERE ID=1

SQL – Delete Orphans

In the 1930’s Miss Hannigan wanted to delete orphan Annie, but now we have child neglect laws that prohibit that. Good thing we now have SQL.

Lets say we have two SQL tables that contains names of team members. One is for softball and one is for baseball. We want to remove all players from softball that do not play baseball. You could do this in a loop or a subquery, but if you have team data from the past 100 years this may become quite costly.

Here’s a quick way to make this happen:

DELETE S
FROM Softball S
LEFT JOIN Baseball B ON (S.Name = B.Name)
WHERE S.Name IS NULL

What this is doing is matching the table Softball with Baseball. Any orphaned Softball players (no match with Baseball) end up getting deleted.

SQL VARCHAR(MAX): The New TEXT

Normally, if I’ve ever needed a large amount of text data stored in a database field, I would use a column type of text. I would usually do this when going beyond 256 characters. I think this stems back to use MS SQL 6.

Now as of SQL 7, the varchar type can hold around 8000 characters (8k) and nvarchar can hold around 4000 characters. This limitation stems from the 8KB internal page size SQL Server uses to save data to disk. But who really wants to deal with putting in client-side validation of 8k characters when you can just make it unlimited?

But the pain from using the type text comes in when trying to query against it. For example grouping by a text type is not possible.

Another downside to using text types is increased disk IO due to the fact each record now points to a blob (or file).

Most clients that I’ve worked on are still on SQL 2000, and maybe 2005 – so I haven’t really caught up to the nifty 2005 and 2008 changes yet. Reason? MS SQL Servers are freak’n expensive!

In SQL Server 2005 Microsoft added support for the varchar(max) and nvarchar(max) data types. The difference between varchar(8000) and varchar(max) is that it can now store up to 2GB of data (about 2,147,483,647 characters) instead of 8K. That seems to be plenty in my books for anything I need it for. Continue reading