January 31, 2008 at 1:09 pm
Guys,
I have a table with ~500,000 records. It has a few columns - most of them INT types, but one of them is a TEXT data type. I need to search the TEXT column for certain strings. I have been doing searches like this:
SELECT A.ID
FROM A
WHERE B LIKE '%ABC%'
The return is usually ~ 100 records.
Sometimes this query takes 10 seconds, but at other times it runs for 10 minutes and does not complete. I have been trying to figure out what is causing it, but still have no idea.
So, my questions:
1. Is there an efficient way of string search or this is the only thing I can do?
2. Why would the same query take seconds at times, but minutes at other times?
I checked to see if any heavy jobs were running at the time the query was taking long, but could not find anything. I wonder if I am overlooking something.
Please advise.
Thanks in advance!
January 31, 2008 at 1:34 pm
That kind of syntax is going to force a scan of the entire table. So it's going to be a rather intense search, expecially to complete a full table scan of the table
depending on how the search is set up or what is performing the search, there are some tricks that might allow some results to be returned much faster (like the, or FIRSTFASTROW or FAST(n) hints) while the rest of the query keeps on going. You've also got something like TOP n while will cause the query to stop as soon as it has n rows. In the same way certain UI's like MSAccess only require one "page" of data, so it will pause execution as soon it gets 20 or so rows (however many make up one screen full). So - you might just be seeing what seems to be luck of the draw because the rows "early on" in the table qualify.
Depending on your searches, you might consider using full-text indexing, but that's an entirely different kind of search.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 1, 2008 at 5:35 am
I don't think this would make a difference but it might be worth a try just to see. Agree with Matt that this does sound like just the thing full text indexing was made for...
use pubs
-- your current approach
select pr_info from pub_info
where pr_info like '%99%'
-- different approach but likely to still have a table scan --
select pr_info from pub_info
where charindex('99', pr_info) > 0
Toni
February 1, 2008 at 7:30 am
>> Sometimes this query takes 10 seconds, but at other times it runs for 10 minutes and does not complete.
Perhaps when it took 10 seconds, the data pages were already in the buffer cache, while in the other case, most or all of the data pages had to be physically read from disk.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 1, 2008 at 12:01 pm
Thank you all for the suggestions.
I ran the full report today (the full report consists of about 15 of such types of queries).
It took about 1 hour to complete. I then re-ran it and it took 5 mins 25 seconds. I re-ran it again, and it took 2 minutes and 19 seconds.
My assumption is that (as many of you noted) there is some caching going on. I see no other explanation.
Since I need to run this report once every day, and data would change, I am assuming it will always take ~ 1 hour to complete (unfortunately) ...
Thanks!
February 6, 2008 at 8:16 am
you should think about building a full-text index and using CONTAINS instead of LIKE ... 😉 (difference in perf should be huge... - speaking from experience)
February 6, 2008 at 8:24 am
Got it.
Thank you for the advice!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply