Today, I experience a slow down in one of website I just finish building up. It was a intereting project which has ability to create Dynamic form and hence the related dynamic database tables. But the problem was in part where we preload user information and later show them on our dynamic forms when user code matches from database. During development I was using list of about 30-40K records and it works just fine, but within couple of days of project going live we encounter that our user database is about 800K records and now the search takes as many as 30 seconds to returns the results.

I create few more indexes on concern table as the SQL was operating on joining 3 tables in first table we have 800K records, other tables has about 200 records each. After indexes the time was shorten to 25s, which again is high time for me. So, I thought to rewrite the query in different manner but suddenly I realise that for user code matching we are using 2 fields from main table and I was concatinating those two fields in one and passing combine value to query. I then thought to test it without concatination and magic happens now the same joins take less than a second to fetch records.

So morale of story is Concatination at runtime is not recommended in where clause, specially.