In the database we have a table where all the file names are stored.This table is queried every 1 minute by the application to see a file name already exists or not, So the query is designed as,
The filename is around 190 charecters in length and there are about 100k records in the table and it is effecting performance, I have put an index on the filename, but still it does not show a significant change on the perfromance. All 100k records needs to stay in the table, for a month or so. but even for first 100k itself, i see it cracking up. any way we can fine tune this query?
asked Aug 31 '11 at 01:33 PM in Default
Are the file searches all wild card searches. I can just type in 'a' and you're going to find aardvark, apple, can, zap...? For 100,000 records? Talk to the business. Convince them that the only way to get for sure performance is to modify the requirement so that if you type in 'a' the search will only return aardvark & apple.
That, or, if this is really the way this must behave, you may want to look at one of the NoSQL databases out there that are specifically targeted to doing wild card text searches. Especially if you're only doing a name/value pair search as the example query implies.
answered Sep 01 '11 at 07:49 AM
Grant Fritchey ♦♦
It sounds like your business needs a better naming convention for file names. What you are doing is very inefficient...
answered Nov 16 '11 at 02:28 AM
Apparently I don't have enough "karma" to comment on an individual post... Anyway, I may be wrong, but I don't believe a fulltext index is applicable in this case for two reasons.
I don't think you're allowed to search inside a given "word," the closest is the beginning of the word: http://msdn.microsoft.com/en-us/library/cc879300.aspx
Even if you could, I expect the full text index query would be just as expensive as the like query in this case, because the filenames are probably not made of many words.
Someone please correct me if I'm wrong...
answered Sep 01 '11 at 01:29 PM
Is there a pattern to the filenames that could be used to create a computed column from? You say the files all have the same pattern inside them, the rest is different every minute. If that is so, you could create the computed column to fish out the relevant filename part, then index that column. You then change your query to access the computed column and it should fly.
@Kevin feasel's suggestion of full text indexing would be my next step if my suggestion didn't work out.
answered Sep 01 '11 at 12:41 PM
I'd look into using a full-text index for this query. If you have the server power to keep that index up-to-date, performance would be much faster than the %something% search for the reason @DaniSQL brought up. Rebuilding the index can be pretty slow, but it might be worth giving this a try.
answered Sep 01 '11 at 10:49 AM