1
\$\begingroup\$

I'm looking for a better way to manage employee photos for employee records in our database, and a way to query for the employee photos while having them on disk I have used a FileTable to store all the EmployeePhotos.

Then in my Employee View I created a join using substring, which I am concerned about. I am wondering if there is a better way of doing this.

Here's the View:

CREATE VIEW [emp].[ViewEmployees]
    AS SELECT 
        ue.Id, 
        ue.Active,
        ue.Comments,
        ue.Created,
        ue.IsLockedOut,
        ue.LastLoggedOn,
        ue.NameIdentifier,
        e.FirstName,
        e.MiddleName,
        e.LastName,
        e.DisplayName,
        e.Duties,
        e.EmailAddress,
        e.FullPart,
        e.JobTitle,
        e.LegacyId,
        e.OtherPhones,
        e.Phone,
        e.Profile,
        e.StartDate,
        e.ShowOnDirectories,
        ep.name as PhotoName,
        ep.file_stream as PhotoBytes
        FROM [security].[UserEntity] as ue 
Full Join [emp].[Employees] as e on ue.Id = e.Id Left Outer Join emp.EmployeePhotos as ep on SUBSTRING(e.EmailAddress, 0, CHARINDEX('@', e.EmailAddress, 0)) = SUBSTRING (ep.name, 0, CHARINDEX('.', ep.name, 0)) where ue.UserEntityType = 0

When you add files to the folder, it automatically creates a record in the file table with a new guid. We want to allow administrators access to that folder as a network share so they can drop new employee photos in it, or edit them. As such I can't really think of a better way to join it on my employee view.

Or maybe I shouldn't have it in the view at all, and instead do a separate query in the application to the file table for the photo.

\$\endgroup\$
4
\$\begingroup\$

The FULL JOIN looks a bit odd for this query. An INNER JOIN would be more appropriate if all employees have an associated UserEntity row (trusted foreign key relationship) and your intent is to return only employee user entity types, which seems to be the purpose of EntityType = 0 in the WHERE clause.

It is best to avoid applying functions to columns in JOIN and WHERE clauses because that prevents indexes on those columns from being used efficiently. Columns should stand alone on at least one side of the comparison operator when possible. If all photos have the same file extension and you don't have subdirectories in your file table, you can use the refactored example below to build the file namespace path and use the GETPATHLOCATOR function to return the unique path_locator of the photo. The path_locator column is the file table primary key so this will be efficient and ensure only one row is returned per employee.

CREATE VIEW [emp].[ViewEmployees]
    AS SELECT 
        ue.Id, 
        ue.Active,
        ue.Comments,
        ue.Created,
        ue.IsLockedOut,
        ue.LastLoggedOn,
        ue.NameIdentifier,
        e.FirstName,
        e.MiddleName,
        e.LastName,
        e.DisplayName,
        e.Duties,
        e.EmailAddress,
        e.FullPart,
        e.JobTitle,
        e.LegacyId,
        e.OtherPhones,
        e.Phone,
        e.Profile,
        e.StartDate,
        e.ShowOnDirectories,
        ep.name as PhotoName,
        ep.file_stream as PhotoBytes
FROM [security].[UserEntity] as ue 
JOIN [emp].[Employees] as e on 
    ue.Id = e.Id 
LEFT JOIN emp.EmployeePhotos as ep on 
    ep.path_locator =
        GETPATHLOCATOR (
            FILETABLEROOTPATH ( 'emp.EmployeePhotos') 
            + '\' 
            + SUBSTRING(e.EmailAddress, 0, CHARINDEX('@', e.EmailAddress, 0))
            + '.jpg')
WHERE
    ue.UserEntityType = 0;

Alternatively, you could create a unique index on the file table name column and change the file table LEFT JOIN clause to:

WHERE ep.name = SUBSTRING(e.EmailAddress, 0, CHARINDEX('@', e.EmailAddress, 0))
            + '.jpg'

Regarding you question about retrieving the photo via a separate query, it would be best to return the image in the same query, assuming the application actually uses it. If you have cases where this view is used but the photo isn't needed, make sure the PhotoName and PhotoBytes columns are omitted from the SELECT query column list when querying this view. That should allow SQL Server to remove the unneeded file table from the query plan.

\$\endgroup\$
1
  • \$\begingroup\$ Ah, this makes sense. And yes only one part of the page uses the photo at the moment, so I think I'll make 2 views, one with photos and 1 without and regenerate my PetaPoco T4 Templates and I'll end up with two pocos "ViewEmployee" and "ViewEmployeeWithPhoto" or something like that. \$\endgroup\$
    – Ryan Mann
    Oct 5 '15 at 5:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.