Staying Current – 5 Tactics for Technical Managers

Chess Board

You developers have got it easy when it comes to staying up to date with current tech. Even if you’re not lucky enough to be working with the latest and greatest stuff like the team at PXtech, you’re at least writing code every day, solving software problems, and keeping your tech mind sharp 9-5.

Since becoming development manager, I’ve really had to work hard to keep my development skills up to date. As time has gone on, my workload has shifted away from the code, and more towards reports, organisation, recruitment etc. (you know, all that management stuff). This meant that dipping into the code on the rare occasions I was able to, was just getting more and more difficult, and taking more and more time to get up to speed with what was going on. It didn’t help that making even an hour of space to focus on it without interruptions was nigh on impossible.

Things are changing though. Focusing is easier, I’m able to get productive from a cold start more quickly, and many of the newer frameworks the team are using don’t feel quite as alien anymore. Put another way, I feel in touch. Not as on top of everything as I’d like to be, but I can be productive as a developer alongside my responsibilities as a manager.

I wanted to share some of the tactics I’ve used to get to this point, and will continue to use to maintain the skills that are so valuable to me and, I hope, to my role at PXtech.

Make the space

It sounds obvious, but if you don’t make the space to work on your development skills, you won’t be able to work on them. Making the space is actually the easy bit, protecting it is harder. Distractions such as email, meetings, questions etc. all break your flow, which as you know is just disastrous for programming.

I did a few things to defend this time, which really helped. First I made a conscious effort to get control over when meetings happened. Bunching them together is best and planning the preparation and follow up time appropriately also helps. Second, I closed Outlook (I can sense the collective intake of breath from here). Yes that’s right, I closed my email, and most days I will only check emails at 11am and 3pm. I can’t claim credit for this idea, but this guy can.

Finally, I accepted that I needed to regularly commit time out of work to learning. It’s good practice for developers anyway, but essential if you’re not 100% development focused in your day job.

Embrace generalism

Sounds backwards right? The most successful programmers out there specialise, and for good reason. If you’re a generalist then your skills are in rich supply. If you’re a specialist, then you’re that big fish in a small pond (hat tip to John Sonmez).

Well, all that is true, but now you’re managing a team of specialists and aspiring specialists, and you need to know at least a little bit about what each of them is doing. Chances are your experience will help them hone that speciality and (if you’re doing your job right) apply it to your team’s products in the most effective way through excellent application architecture design.

I try to learn something new every week. Whether it’s a technique one of my team are using, or looking at an emerging technology area so I can have an informed discussion on it with our expert in that area. It helps to push me and my team, as well as keeping my knowledge relevant to what’s being worked on

Have a side project

I can’t stress this enough. It’s all very well aiming to be an active member of the development team, delivering value to the customer by coding features and completing sprint items, but sooner or later you’ll be pulled away and you’ll go through a period where getting anything (technical) done is not, and cannot be top of your priority list. The team will be impacted, sprint goals will be missed, and you’ll feel awful being the one dragging everyone else down. Sorry – it’s a fact, and you can bet it’ll happen at the worst time.

For that reason, I try to limit my direct delivery of backlog features to relatively independent, lower priority items. Naturally, they’re also not always the most interesting.

To afford myself the luxury of playing with the very latest tech in an interesting way and being able to dip in and out as required, I have a side project (an important one, but a side project nonetheless) that only I work on. It’s my playground for want of a better word. Somewhere I can try things out and learn new things safely. It’s been invaluable to me in staying up to date and keeping up with the team around me.

Read endlessly

Read tech blogs, news, books, everything you can to keep a grip on where the industry is going. Not only do you want to make sure you’re focusing your precious time in the right areas, but you need to keep tabs on your team to ensure you have the right mix of capabilities in the right areas.

I find Twitter is a particularly useful tool for this. I check it as frequently as I can, and try to keep the majority of the people I follow relevant to my industry. (If it helps, set up another account for this!)

Learn from your team

One of the best resources available to you, is sat right with you in the same room most of the time. These are the people who you are entrusting and helping to become specialists in their respective fields, so learn from them. Get them to show you what they’re doing, explain it, and help you apply it. They’ll benefit from your eyes and approval of their work, and you’ll benefit from the research and learning they did to get to that point. Win-Win.

So there are the 5 main tactics I have used to keep myself up to date since letting go of development full time. It’s hard – really hard – but I’m not ready to let go of it yet, and I can’t yet imagine a time when I will be.

Ralph Cooper, Head of Software Development

Will ghost kitchens destroy your restaurant business?


When we saw the rise of third-party online ordering websites, the restaurant industry feared the impact it would have. Now the restaurant scene is getting even more competitive with the introduction of delivery-only ‘ghost’ kitchens.

Surpassing the local chippy or Chinese takeaway, these kitchens are offering premium meals directly to people’s doors. Embracing the current demand for food on the go, they half their overhead by shaking off property driven expansions, high rent and labour costs.

By crowding the marketplace even more, the traditional sit-down restaurant is ever more in fear of becoming extinct.

With double the overheads of ghost kitchens, how can you prevent being priced out of the restaurant scene?

If you haven’t already started utilising technology within your restaurant, now is certainly the time.

There’s no doubt that online ordering, payment and delivery options are essential. If you fail to keep up with the demand of being able to offer your meals to customers who dine at home, your restaurant and brand risk being left behind as we move towards more digitalised dining in the future.

Being more technical with your ordering processes is a must. Gone are the days of diners ordering food by calling. Diners want to be able to order from an app or website that allows them to choose, review and pay in one smooth transaction. Having the technologies available to you to do this is one thing but having an integrated system to streamline the process will truly up your online ordering game.

Integrating your online ordering system with your EPOS bypasses those third party services meaning you keep all the profits from the sale and the order is sent directly to the kitchen, optimising communication and reducing order time. This not only ensures customers get their order as quickly as possible with less chance of mistakes but also works to engage the customer through their digital experience and means all transactions are accurately tracked through your POS.

As you no longer have the opportunity to engage diners in your restaurant with great customer service, you need to ensure that your brand ethos is visible to the customer in your technology solutions. Creating an easy and smooth transaction for the customer, whether in the restaurant or at home, will always win repeat custom. Online orders can also be linked directly with loyalty programs so your customers reap the benefits of ordering with you directly and are rewarded for their loyalty.

While there’s much discussion around if diners prefer to eat at home or go out for a meal, there’s no denying that the desire to be able to eat premium food at home is increasing. While being prepared is essential, taking it to that next level by engaging customers online will really help you to stay ahead of your competitors and secure your restaurant’s future in a digital world.

The Importance of Choosing the Right Technology

It’s easy to get whisked away by the possibilities of modern technology. It’s human nature to want the newest gadgets packed with endless features that empower you to be more productive, more efficient, more successful. With a smooth-talking salesman eager to dazzle you with the amazing things technology can do, it’s very easy to be led to believe that you need all of these features and functionalities to operate effectively. But that’s not always the case.

The charity sector has seen an influx of new technologies over the last few years and many are rushing to take advantage of the possibilities and benefits that it can bring. As a charity every penny spent on technology is a penny not going towards your cause and that’s why it’s so important to invest in technologies that will be worth it in the long run, that can and will provide the largest return on your investment.

Today’s POS technology often boasts never-ending lists of functionality, all promising increased Gift Aid sign ups, improved people management, easy stock tracking, better staff scheduling, hosted documents, improved communication, user-friendly interfaces, quicker cashing up processes… It’s hard not to buy into the many features available.

There’s no doubt that these systems can be extremely helpful. They can help you to improve how you work in your charity and having all those features at the touch of a button is incredibly useful. But realistically, how many of those features will you actually use? It’s all well and good having the functionality to manage floor space within a giant warehouse and handle the logistics of international delivery, but if you are running an operation of three shops all in the same county, just how useful will this be?

Having huge functionality in your EPOS system is great – but if you’re paying for something you don’t need, you’re wasting valuable funds that could go towards supporting your cause.

Having an EPOS system is becoming a necessity but it doesn’t need to be a hundred and one things to make you more efficient. It just needs to do what you need it to. Perhaps you’d like to have some help tracking your stock movement but don’t need task lists for your volunteers hosted on your POS. Maybe you just want a simple system that processes transactions. No frills, no added costs and no faff.

So how can you identify the systems that actually do what you need them to?

Finding a good technology provider is crucial. While you may not fully understand what the technology can do for you, they’re experts in it. It’s also important that they understand your issues, your requirements, and how you work in order to choose the right solution for you.

At Charity Expertise, we know we are a good technology partner for charities. We spend time learning and understanding your charity so that we can figure out the best way to help you succeed. When we worked with Save the Children, we collaborated, listened, suggested the right system, and then worked with them to ensure they got the best possible return from their investment. The new system contributed to a 35% increase in Gift Aid claims.

When we launched our new digital Gift Aid sign up solution, Wil-U, that replaces the laborious paper forms, we knew it shouldn’t be tied to an EPOS system. We knew it wasn’t realistic or fair to expect charities struggling to increase Gift Aid sign ups to be forced to buy a whole new suite of technology for their stores in order to solve this problem. We created our digital Gift Aid solution with this in mind – the software is not tied to any system and can be used independently from technology you already use.

While we can provide multi-purpose EPOS systems, we appreciate that sometimes money is tight or that sometimes you just want to solve one problem before starting on the next.

If you’d like to know more about finding technology that truly works for you, get in touch at


CI and Automated Deployments


Automating deployments with TeamCity has saved us hours of headaches! Finding the setup that works best for us has taken some time, but now we’re there, the great mist that was hanging over web deployments has lifted and the world feels like a clearer, simpler place.

A few years ago, we were in a bad place.

We didn’t really know it yet, but we were definitely not set up for reliable, repeatable and scalable deployments. In fairness, what we had worked. With a relatively small team and not many applications, keeping on top of things wasn’t too onerous and generally there weren’t many mistakes.

Fast forward a few months and the system was seeing some growing pains. The development/test environment we experiencing contention, people overwriting each other’s changes and no one really knowing what version they were looking at. All deployments were manual, publishing from developer machines and copying the files. In today’s world though, this is practically a criminal offence! (It’s at least morally indefensible…)

Over the coming months and years we embarked on a journey of discovery. For some of us, the journey felt familiar – we’d covered much of the ground before – but for others it was a mysterious (and scary) voyage into the unknown. Today we’re in a better place, and here’s why…

We’re using git for source control.

Previously we had a large and unmanageable SVN repository. Moving to git was challenging, but in the end the benefits are proving to be well worth it. As things stand:

  • The single large repo is now many smaller ones
  • Managing code between branches (dev, release, feature, hotfix, master) is simple
  • Core modules are distributed using internal NuGet, dependencies are nicely managed
  • All repositories are centrally hosted using Stash

Continuous Integration (CI), is the first step for anyone towards salvation. We have TeamCity working hard validating that all our code in all branches builds once it’s checked in, giving us a confidence in our code base that we just didn’t have before. Additional steps run unit tests and create deployment packages. We also use features in TeamCity such as assembly versioning, to allow us to track which version of the code a particular DLL was built from.

Speed up and take out the human

Automating deployments speed up getting code into a working environment, and takes away the human element of deploying code. We have taken advantage of publish profiles and web deploy in Visual Studio and IIS to control where applications are deployed and with what configuration. Web.config transforms make managing multiple environment configurations a breeze, and you get consistent and reliable results. No more searching for hours to find that a setting wasn’t added to a particular web.config file!

Automating this much of the process has allowed us to create more environments, with better controls and clearer defined purposes. A test and production environment are fine in most cases, until you’re doing formal release testing and want to QA features that are for a future release. So we added another environment specifically for this purpose. Want an environment that’s more like production so you can do final validation checks before deploying? Easy, we created a staging environment.

All in all we now have 5 environments, all built from a specific code base:

Environment Built from Purpose
CI “dev” branch For developers to validate their changes with everyone else’s in an integrated environment
Test “dev” branch Owned by QA. For testing features that are in the current development iteration.
Release Verification “release/x.x” branch Owned by QA. For validating a release candidate. Only bug fixes relating to this release get deployed to this environment.
Staging “master” branch Owned by Infrastructure. Pre-live validation environment to perform final checks on a release before it’s deployed to production.
Production “master” branch The real deal

At first glance that’s a lot of environments, but thanks to automation the overheads are minimal compared to the benefits. Deployments are one click and take just a few minutes.

Streamlined Processes

Even so, we streamlined the process of creating new builds and environments. First of all, I want to say that TeamCity and Web Deploy are fantastic tools. If you’re not using them, you should be!

Creating a new set of environments is fairly straightforward. You do have to create the sites in IIS and point them to a place on the file system. You also have to set up the security correctly, but then that’s about it. Web deploy does the copying of files and the configuration side of things for you.

TeamCity allows you to create build configuration templates, which means new builds can be set up in just a few minutes. By selecting the template, then overriding a few parameters, your build is ready to go!

So, the scary part is clearly the production deployment, and there are some things you can do here to make it less scary:

  • Create a short MSBuild script to run before the deploy step that backs up the current production code to a known location. This means you can easily rollback if something’s gone wrong just by copying the files back into place.
  • Secure access to the production build configurations. There’s nothing worse than an accidental or unexpected deployment, and you can minimise the risk of this in TeamCity by having all your production deploy configurations in a separate project. This also means you can restrict access to just your infrastructure team, that way it’s impossible for developers to accidentally push changes into production without authorisation.

And that’s it! It took us quite a while to get here, and we’re by no means in a perfect place, but a lot of the stress and pain is now gone from validating and deploying our web projects. That means we can spend more time creating great products and less time worrying about deployments.

Ralph Cooper, Head of Software Development

Why You Should Be Engaging Employees Before Customers

Engagement is one of the most discussed topics in the hospitality sector. By now, most business owners will have heard about the benefits of customer engagement. When done right, it has been proven to increase customer loyalty, improve repeat custom and boost overall profitability. But while the benefits of an engaged customer base are endless, employee engagement is often regarded with a little less zeal.

We’re here to tell you that employee engagement is just as important, if not more so, than customer engagement. In fact, we would go as far to say that you cannot begin to engage customers without having an engaged workforce first.

Casual and fast dining restaurants are fast paced and it’s no secret that hospitality has one of the highest employee turnovers of any other sector, reportedly costing the industry a huge £11 billion a year. Despite the overheads of continually having to recruit new employees, an unengaged workforce brings with it a whole host of issues and could be the very thing that is hindering efforts of engaging your customers.

As well as being happier, healthier and more fulfilled at work, engaged employees deliver improved business performance evidenced by productivity, profit, revenue growth, customer satisfaction, innovation and efficiency.  Engaged employees care for the company’s future, are willing to go the extra mile for their customers and know how their work impacts the overall goals and vision of your organisation.

Engaged customers bring a 21% profitability premium to the companies they are engaged with.  Engaged employees increase sales by 20%, their absenteeism drops by 41%, and safety incidents decrease by 70%. Companies that invest in engagement see a 17% rise in productivity and a 40% rise in quality of work compared to those who don’t.[1]

For customers to be engaged, they need to see a workforce that is passionate, innovative and enjoying the work that they do. This will encourage customer interaction, create a more comfortable and happier environment to dine, and customers are more likely to receive the best service possible. No matter how hard you try to engage customers through loyalty apps and social media, if they have a negative dining experience in the restaurant, you’d be lucky to see them again.

An engaged workforce will become a necessity if you wish to truly make gains in coming years and stay ahead of your competition – but where do you start when beginning to think about an employee engagement strategy? It’s all good and well telling you why you should be engaging employees but it’s even more difficult figuring out how to engage them.

This is where your restaurant data plays a huge part. Not only will collecting and analysing your data give you visibility of transactions, sales trends and the best trading times, it can also provide a wealth of knowledge about your employees. This includes how productive they are, when they’re most productive, who they’re most productive with, and how outside factors influence their performance at work. Knowing this information will allow you to create the most engaging working environment for your employees to ensure you’re always getting the best out of your team.

If you want to know more about unlocking the wealth of information waiting to be utilised in your business analytics database, join us for a webinar soon to find out how to use your restaurant data effectively in order engage employees and increase productivity and profitability.

[1] Gallup, Gallup 2016 Q12 Meta-Analysis: Ninth Edition,

The Importance of the Small Stuff

There are a lot of great small charities that sometimes don’t get the recognition they deserve due to the unique nature of their cause. To remind people everywhere that small doesn’t mean unimportant, we wanted to write about a few of the small things you might be forgetting that could make a big difference to your charity.

Reward your volunteers

Something as small as a quick ‘thank you’ to your team of staff and volunteers could go a long way to improving work ethic and engagement. Better still, rewarding them with small gifts when they have achieved something particularly impressive will boost motivation and encourage other volunteers to work harder too.

Make an impressive window display

Make a good first impression by getting creative with your window displays. Enticing displays can be created easily with your team and a few props from your store and could make a huge difference to increasing footfall. Not sure where to begin? Read our blog here on how to create a WOW! Factor window display.

Don’t forget to ask people about Gift Aid

Often one of the largest barriers to increasing the rate of Gift Aid sign ups is staff in store forgetting or feeling too uncomfortable to ask donors if they’re eligible to sign up. Doing so could make all the difference! Don’t forget to urge your volunteers to remember to ask the question. Alternatively, some digital solutions such as Electronic Gift Aid could remove the need to ask uncomfortable questions altogether by allowing donors to input their own information on a modern touchscreen interface.

Allow your volunteers to grow

Giving your volunteers a little extra time to develop their skills in store is essential to building the strongest team out there. Allow them to take courses on the back office PC, practice their selling skills on the shop floor, or let them plan a fundraising event. Giving them room to grow their skills and develop their professional profile is a great way to create lasting, knowledgeable volunteers.

Talk to your donors!

Engaging with donors is one of the most important things you could do as a charity leader. In order to fully engage donors, you should open up a two-way form of communication – talk to them, not at them! Ask for donors’ advice and get them involved in community events to create loyal givers who feel like they really have a connection with you, your store and your cause.

It’s all too easy to get bogged down trying to juggle all the policies and procedures that come with managing a charity shop. While the big things may seem more important, the small stuff adds up. Making sure that someone is taking care of those little things is essential and will create a solid foundation for you and your team to reach their full potential and raise valuable funds for your cause.

Engage employees to engage customers

Engagement comes in many forms and is often difficult to measure. With such a visceral concept, the definition of engagement can be iffy but the basis remains the same: ‘being positively present during the performance of work by willingly contributing intellectual effort, experiencing positive emotions and meaningful connections to others’.[1]

More and more businesses within the hospitality sector are beginning to recognise the positive effects of an engaged workforce. With 88% of businesses planning on improving engagement in 2017, failure to recognise the power it has will leave your business lagging behind your competitors.[2]

Ensuring employees are thoroughly engaged can improve all areas of a business. It is no secret that hospitality has one of the highest employee turnover rates of any industry. Bloomberg BNA estimated that a huge $11 billion is lost annually due to employee turnover. Money is literally walking out the door due to low levels of engagement.

It has also been proven that engagement isn’t only good for improving staff retention. As well as being happier, healthier and more fulfilled at work, engaged employees deliver improved business performance evidenced by productivity, profit, revenue growth, customer satisfaction, innovation and efficiency.  Engaged employees care for the company’s future, are willing to go the extra mile and know how their work impacts the overall goals and vision of your company.

Engaged customers bring a 21% profitability premium to the companies they are engaged with.  Engaged employees increase sales by 20%, their absenteeism drops by 41% and safety incidents decrease by 70%. Companies that invest in engagement see a 17% rise in productivity and 40% rise in quality of work compared to those who don’t.[3]

These are facts. Engagement works. All the statistics show that engaged employees improve the workplace beyond the happy employee; engaged employees actively work to improve profit and decrease unnecessary costs. Engaged employees are more loyal to their company, saving you money and time on recruitment and training. Your HR department would be better off spending less time recruiting new talent, and more time engaging and retaining the great employees your business already has.

With an increasing number of socioeconomic concerns influencing and, in most cases curbing profit, it’s easy to want to take the reins and implement processes and systems that rely on control, direction and compliance. This will only damage engagement levels and cause further financial distress. Instead construct the right teams, analyse productivity levels, ensure you have the right people working together at the right times, and trust them to do their job. Give them the support they need, discuss the larger focus of the business, where you want to be and where you want them to be, and they are more than likely to succeed and be happier while doing so.

Engagement is pinned to be one of the most explored concepts for hospitality this year and looking at the results so far, it is no wonder why. Businesses are being more innovative than ever with their workplace culture and are really going that extra mile to ensure they can provide the best, most engaging, environment for their employees. While installing slides in the office will be a little farfetched for most, if your business isn’t working on its engagement levels, it will be left behind and struggle to stay ahead of the curve. Not to mention spending valuable revenue on recruitment, training and labour costs! Large or small, engagement can help you transform your business for the better and it’s time you started talking about it.

Engagement needs discussing now more than ever; you should not underestimate the power it can have.

If you would like to discuss engagement in more detail and see how your technology can help, please get in touch at or 01332 921 300.

[1] CIPD, Employee Engagement: An Introduction

[2] Virgin Pulse, State of the Industry: Employee Wellbeing, Culture and Engagement

[3] Gallup, Gallup 2016 Q12 Meta-Analysis: Ninth Edition,

Human factor in engagement

Customer Business

Engaged customers bring a 21% profitability premium to the companies they are engaged with.  Engaged employees increase sales by 20%, their absenteeism drops by 41% and safety incidents decrease by 70%. (Gallup 2016 Q12 Meta-Analysis: Ninth Edition)


Still think engagement isn’t important?

Engagement is far more than satisfaction or happiness. It is about emotional commitment. When customers and employees feel engaged and are engaged with a company, they are part of its experience, its adventure. They develop a feeling of pride and a feeling of belonging. They are prepared to go the extra mile to support the company.

The engaged customer will evangelise the company and the brand. The engaged employee will use discretionary effort to solve problems, to drive innovation, and is concerned with the company’s well-being.

Engagement is about people. Technology is the tool to make it successful.

To find out more and to join the conversation on engagement, email us on or give us a call: 01332 921 300!

A Day in the Life of Performance Tuning

Performance tuning

“Can I have all the things in instantly”?

“Make this slow thing fast”

“I want more, faster!”

Okay, yes, I am heinously boiling down the various questions we get asked for on a daily basis for comic effect, but in reality this is not far off the truth. You are about to get an insight into the journey I took to reduce a query from taking minutes to taking seconds.

Let’s start with the culprit;

DECLARE @startDate DATETIME = '2008-01-01',

        @endDate DATETIME = '2016-01-01'


       SELECT tdd.[Date Key],

              dbo.getDate(tdd.[Date Key])

       FROM [Date Dimension] tdd

       WHERE tdd.[Date] BETWEEN @startDate AND @endDate


It’s a simple snippet, it selects some keys from a table and passes one of those keys into a function to get another key between two dates.

It looks very innocent, nothing wrong here. One could ask a broader question about its usage in the scope on entire query but we are just going to take it in isolation here.

It looks innocent until you run it and find out that it takes 36 seconds!

Naturally my eye was drawn to the scalar function as at the moment it’s a black box and could be doing anything. I looked at it and determined that it was doing something reasonable but I could do it quicker.

DECLARE @startDate DATETIME = '2008-01-01',

        @endDate DATETIME = '2016-01-01'


       SELECT tdd.[Date Key],

       STUFF([Date Key], LEN([Date Key]) - 1, 2, '24')

       FROM [Date Dimension] tdd

       WHERE tdd.[Date] BETWEEN @startDate AND @endDate


Low and behold this returned its values 163 milliseconds! I went ahead and compared all the data and it matches as I expected it to. I wasn’t satisfied yet as while this is a great improvement, it is not entirely correct from a data point of view because I have assumptions about what is happening and the structure of data. I also needed to understand why this is quicker, without the understanding of why, you leave your code open to bugs and unforeseen interactions.

So next I asked the question; “Is it the scalar function call itself that’s the problem or is it the work the function is doing?” This directed me to create a function with the STUFF code within it. Creatively I called this “getDate2”.

DECLARE @startDate DATETIME = '2008-01-01',

        @endDate DATETIME = '2016-01-01'


       SELECT tdd.[Date Key],

              dbo.getDate2(tdd.[Date Key])

       FROM [Date Dimension] tdd

       WHERE tdd.[Date] BETWEEN @startDate AND @endDate


No joy. I had hoped it would be as simple as calling a scalar function multiple times was causing the problem but the execution plan and statistics tell me is isn’t with it executing in a measly 267ms. So we have to dig deeper. Let’s try in-lining the original function call statement with the method.

DECLARE @startDate DATETIME = '2008-01-01',

        @endDate DATETIME = '2016-01-01'


       SELECT tdd.[Date Key],


              SELECT tdd_day.[Date Key]

              FROM [Date Dimension] tdd_day

              WHERE tdd_day.[Date Key] = tdd.[Date Key]

              ) as Bleh

       FROM [Date Dimension] tdd

       WHERE tdd.[Date] BETWEEN @startDate AND @endDate


I was convinced that it would be this. In my mind doing this select for each result should be really slow but SQL server is smarter than I gave it credit for, it has looked ahead and created a plan that means it doesn’t have to do this. With this query returning its data in 163ms.

I was scratching my head at this point as the direct execution of a scalar function wasn’t slow and the direct execution of the work the scalar function was doing wasn’t slow. Maybe the scalar function has to interact with a table in some manner? So I created another function to do the same select as shown above but within a scalar function.

DECLARE @startDate DATETIME = '2008-01-01',

        @endDate DATETIME = '2016-01-01'


       SELECT tdd.[Date Key],

dbo.getDate4(tdd.[Date Key])

       FROM [Date Dimension] tdd

       WHERE tdd.[Date] BETWEEN @startDate AND @endDate


(With hindsight I didn’t need to do this because it’s the same as the original, but it was part of the process.)

At last! A result that shows the problem! This query took 22 seconds to return the results. So at this point it appears the SQL server truly treats scalar value functions as black boxes and this shows in the execution plan. The function call is in fact not shown.

It shows the estimated cost as 0 if you check the estimated plan but it is lying. The cost cannot and is not 0 as shown by all the above examples.

So what’s the answer? It’s not exactly good practise to just use the STUFF method. It makes various assumptions about how the data structure will continue to work and completely destroys any reusability we had before. Cue the entry of table valued functions!

Using a simple table valued function SQL can incorporate it into its plan. We can mimic the scalar function with a table valued function very simply by aggregating a result set with only one result in it.

SELECT MAX(tdd_day.[Date Key]) as [Date Key]

FROM   [Date Dimension] tdd_day

WHERE  tdd_day.[Date Key] = @transactionDateKey

We can then use the table valued function in place of our scalar function call;

DECLARE @startDate DATETIME = '2008-01-01',

        @endDate DATETIME = '2016-01-01'


       SELECT tdd.[Date Key], tdd2.[Date Key]

       FROM [Date Dimension] tdd

       CROSS APPLY dbo.GetDateForKey(tdd.[Date Key]) as tdd2

       WHERE tdd.[Date] BETWEEN @startDate AND @endDate


Looking at the execution plan we can now have visibility of the previous behaviour and how it’s been considered into the plan and it executes and returns its results in a cool 323ms down from the original 36 seconds, while maintaining a modicum of good coding principles as we are no longer forced to code in assumptions about structure or reusability failings.

The lessons to be learned here is never make assumptions, check everything. Although sometimes, when you don’t know what you don’t know, this can be hard…

Matt Bird, Developer

Time to embrace technology in Charity Retail?

Online donating is increasing year on year yet the charity retail sector is still lagging behind when it comes to updating fundraising processes. It has been almost 20 years since the first online donation but this still makes up less than 10% of total fundraising for most charities.

However, online donations are continuing to grow and with more people requesting convenient and quick payment methods, the demand for digital payment in-store is increasing.

In a recent study on the retail industry in general, half of young people surveyed said they would be more likely to frequent a business that offered contactless payments, with four in ten saying they preferred the ease of paying with one tap compared to chip and pin. The opportunity for contactless donations therefore is vast and charities could take advantage of this, simply by embracing technology.

Technology offers charities other opportunities to increase revenue. Take Gift Aid for example. Many charities implement a manual process around Gift Aid. An Electronic Gift Aid solution has many benefits; not only saving on storage of paper forms, but eliminating the 20% of forms that are thrown away due to messy handwriting, mistakes, ineligibility or because they’re unusable. Gift Aid is a great way to boost donations and it’s a shame to see potential support going unclaimed.

Digitising the process and allowing donors or volunteers to sign up to Gift Aid on a tablet or computer means that data is stored securely and backed up, and the process is streamlined for all involved. This means charities can increase the amount of Gift Aid sign ups, claim more money for their cause, and make their volunteers’ and donors’ lives that little bit easier.

Moving quickly and embracing these new methods of supporting and fundraising means charities can tap into the generosity of younger, more tech-savvy givers. By ensuring convenience of giving, this can pave the way for future generations of regular donors.

Charities rely hugely on people’s desire to do good in the world; thank them by making it as easy as possible to do so.

chris richards pxtech

Chris Richards, PXtech Head of Charity Retail