You Couldn’t Write it !! ( part 1 )
This post was inspired by a developer and I think illustrates the gulf that can sometimes exist between IT and the business.
I should point out that this post is the diplomatic version!
Initially I was sent a simple search for a person with a question about why the query plan showed a sort when there was no sort in the query and why did the sort show it was 40% of the query.
( The point about the sort belongs to another post some time. )
Easy answer to the duration was that this was a leading wild card search, so I decided to remove most of the query and just concentrate on the table containing the peoples names we were searching on.
My production server isn't a shoddy bit of kit so in the interests of science I performed the cut down query on the actual server.
Even with the all the required data in cache it took over 3 seconds to return a single name.
Now that's not very impressive to my mind,there's only a few million rows in the table and I'm searching one single column in memory which is indexed so no lookups are required, yes it has to scan but it’s in memory for goodness sake!
Well that clearly provided the evidence for why the search was so slow, I couldn’t initially just turn round and say it was the leading wild card I have to show it is, sigh!
So what happens if we drop the leading wild card but leave the trailing wild card? Answer - so quick I had to use profiler to get a time, something under 100 ms.
Some time later in one of our UAT environments there were similar issues, our test systems don't have the resource of production so you expect some degradation.
I asked the developers how these searches were presented to the users, just to add to the confusion we have several interfaces or applications which front the same database.
In one application the default search is "Begins with ... " e.g. trailing wild card, alternative is "Contains...." leading and trailing wild card.
In the other main application default is "Contains..." with alternative of "Exact Match ... " , so most users choose "Contains..." regardless.
So there's a simple solution here, the same users can use both systems let's make them the same and we'll get a definite improvement.
The solution was put to the business - there was a very quick response - "Please make all searches "Contains.." and remove other options.”