On a small project, pinning won't take forever, and the potential problem of having the top layer shift while quilting is relatively small. So, no real need for the basting spray if it weren't the fact that it is just very convenient.
But, on a bigger quilt, the spray basting becomes so much more advantageous. It reduces the time spent on basting (by days!), it leaves your back intact for quilting, it makes it almost impossible for the layers to shift while quilting, and it reduces the risk of having "bulges" on your quilt since it stabilizes the quilt sandwich and prevents you from stretching the quilt sandwich in an uneven way when you quilt different areas.
And, I've had the 505 brand on a quilt sandwich for over 7 months before I finished the quilting, and it still held the layers together. I don't think the glue effect wears off until you wash the quilt.
As for those who say it is a tricky and sticky process, here's how I do to minimize the risk of getting the glue in places where I don't want it (this is just a baby quilt, but I've done the same with a couple of much bigger quilts):
1. Find large floor space and use masking tape to stretch out the backing fabric:
2. Cut out batting approximately the same size as the backing fabric, and use hands to smooth out the batting to make sure it it is the right size when it lays flat:
3. Lift the batting from one of the shorter ends and fold it back over the other half. Spray the glue ONTO THE BATTING on the half of it that faces upwards:
4. Fold the sprayed half of the batting back down onto the backing fabric, and press lightly with your hands to make it stick. Repeat steps 4 and 5 with the other half of the batting. The backing and batting are now glued together.
5. Place the quilt top on top of the batting:
6. Fold up one half of the quilt top to expose half of the batting, and spray the glue ON THE BATTING, then reposition the top and press gently with your hands to make that half stick.
7. Repeat with the other half, let dry for a few hours and your quilt sandwich is ready for quilting
By doing it this way, you don't risk getting any glue on top of your quilt. 
If you by now have a bit of glue residue on the floor, it can be removed with water and soap.
If you have glue on the parts of the batting and backing sticking out from under the quilt top - all the better! Fold these edges back over the edges of the quilt top, and you'll also prevent the edges from fraying during the quilting
Once you're done with the quilting, you just unfold these edges and square up the quilt before adding the binding.