The Valley of Data Access - Part 5
A two-letter word has had a profound impact on Charlie’s database access code. Before I learned of this word, I did not know how to tell SQL Server to test for one of a range of values. So, I took the brute-force approach of simply issuing the same query over and over, passing in new parameter values each time.
Here is the text of the command that I needed to execute:
String query =
@"SELECT
r.Role_Id,
Role_Name,
DomainEntityRole_CanCreate,
DomainEntityRole_CanRetrieve,
DomainEntityRole_CanUpdate,
DomainEntityRole_CanDelete
FROM
Charlie_DomainEntityRole der
INNER JOIN
Charlie_Role r
ON
r.Role_Id = der.Role_Id
WHERE
EntityType_Id = @entitytypeID
AND
Entity_Id = @entityID
AND
Domain_Id = @domainID";Then, in order to test different parameter values, I issued three separate requests for a DataReader:
command.Parameters.Clear();
command.Parameters.AddWithValue("@entitytypeID", entityTypeId);
command.Parameters.AddWithValue("@domainID", domainId);
command.Parameters.AddWithValue("@entityID", entityId);
reader = command.ExecuteReader();
triplet.CollectionByEntityId = FillRoleCrudCollection(reader);
reader.Close();
command.Parameters.Clear();
command.Parameters.AddWithValue("@entitytypeID", entityTypeId);
command.Parameters.AddWithValue("@domainID", domainId);
command.Parameters.AddWithValue("@entityID", -1);
reader = command.ExecuteReader();
triplet.CollectionByDomainId = FillRoleCrudCollection(reader);
reader.Close();
command.Parameters.Clear();
command.Parameters.AddWithValue("@entitytypeID", entityTypeId);
command.Parameters.AddWithValue("@domainID", -1);
command.Parameters.AddWithValue("@entityID", -1);
reader = command.ExecuteReader();
triplet.CollectionByEntityTypeId = FillRoleCrudCollection(reader);
reader.Close();If this were a rare requirement, then this brute-force approach might be acceptable. However, this was the code that implemented the cascading logic needed for each entity to receive its security roles. So each time an entity was requested from the database, this silly code issued a further three queries.
Fortunately my Google searching turned up a little gem of a tutorial: Introduction to Structured Query Language by James Hoffman. Included in the tutorial is a brief example of the IN keyword, and with that example I was able to undo the silliness above. Here is the updated command text:
String query =
@"SELECT
r.Role_Id,
Role_Name,
DomainEntityRole_CanCreate,
DomainEntityRole_CanRetrieve,
DomainEntityRole_CanUpdate,
DomainEntityRole_CanDelete,
Domain_Id,
Entity_Id
FROM
Charlie_DomainEntityRole der
INNER JOIN
Charlie_Role r
ON
r.Role_Id = der.Role_Id
WHERE
EntityType_Id = @entitytypeid
AND
Entity_Id IN (@entityID, -1)
AND
Domain_Id IN (@domainID, -1)";With that change it now takes only one database hit to retrieve the roles for an entity. Fortunately, it did not take too long for me to realise that this exact same query could be used to retrieve the roles not just for a single entity, but also for a collection of entities. Rather than passing in a single @entityID parameter, I would pass in the ID values of all the entities in the collection:
String IDmarker = "[[@entityIDs]]";
String query =
@"SELECT
r.Role_Id,
Role_Name,
DomainEntityRole_CanCreate,
DomainEntityRole_CanRetrieve,
DomainEntityRole_CanUpdate,
DomainEntityRole_CanDelete,
Domain_Id,
Entity_Id
FROM
Charlie_DomainEntityRole der
INNER JOIN
Charlie_Role r
ON
r.Role_Id = der.Role_Id
WHERE
EntityType_Id = @entitytypeid
AND
Entity_Id IN (" + IDmarker + @")
AND
Domain_Id IN (@domainid, -1)";
StringBuilder builder = new StringBuilder();
for (Int32 i = 0; i < criteria.EntityIDs.Count; i++)
{
String param = String.Format("@entityid{0}", i.ToString());
builder.AppendFormat("{0},", param);
command.Parameters.AddWithValue(param, (Int32)criteria.EntityIDs[i]);
}
builder.Append("-1");
query = query.Replace(IDmarker, builder.ToString());
command.CommandText = query;
command.Parameters.AddWithValue("@entitytypeid", criteria.EntityTypeId);
command.Parameters.AddWithValue("@domainid", criteria.DomainId);Previously, a collection of ten entities would require 30 database hits in order to retrieve all the roles for that collection. Now, no matter how many entities are within the collection, only a single database visit is required to retrieve the security roles.
That’s a great improvement, but I am still a little disturbed that one database visit is required to retrieve an entity or an entity collection, and then a separate database visit is required to retrieve its roles. To state the bleeding obvious, this approach is doubing the number of hits to the database.
I have been in two minds about whether this is a problem that needs to be solved. While an extra database hit is of course undesirable, performance is not everything. The current design cleanly separates entity content from entity security.
What I have resolved to do is to have a bash at combining the two queries into one. In most circumstances I would say to myself, “Until it actually becomes a problem, it is not a problem to be solved.” However, I need to get a better understanding of SQL, so I consider this to be an exercise that may also provide a performance boost for Charlie. I’ll give it a go, but I won’t be too concerned if I cannot get it to work.
by Alister Jones | Next up: The Valley of Data Access - Part 6 →
----
Post a Comment