You know you've done something wrong when it takes 30+ seconds to run a full-text query. The most annoying part of this bug is the fact that it is something very small and inconsequential that "triggers" the bug. It's a lot like an murder investigation where the killer turns out to be a Nun.
Here is the setup---
- I have a full text index on a text field in my database. This table happens to be our Phrase table (for data localization)
- I do a very simple full text search on that field in a SQL Server proc
- I want to test my proc so I add some fixed input
- Setting a value after the fact causes my full text to be *very* slow!!!
Here is the code
ALTER PROCEDURE [dbo].[AdvancedTrackSearch_TEST] ( @TrackTitleCrit xml ,@TrackDescCrit xml ,@RecordLabelCrit xml ,@CategoryCrit xml ,@ComposersCrit xml ,@TrackDuration int ,@TrackDurationOperator int ,@LangID int ,@PageSize int ,@PageNumber int ,@UserID int ) AS BEGIN -- Temporary input DECLARE @TrackTitleStr nvarchar(2000) SET @TrackTitleStr = '"booty poppin"' -- *********************************** -- * THIS LINE CAUSES THE PROBLEMS * -- * BY SIMPLY REMOVING THIS LINE * -- * THE QUERY TIME WILL GO FROM 30+ * -- * SECONDS TO LESS THAN 1 SECOND! * -- *********************************** SET @LangID = 66 DECLARE @TitlePhrases TABLE ( PhraseID int, DictionaryID int ) INSERT INTO @TitlePhrases SELECT PhraseID, DictionaryID FROM [dbo].[Phrase] WHERE --PhraseID NOT IN (SELECT PhraseID FROM @TitlePhrases) CONTAINS([TEXT] , @TrackTitleStr) --[Text] like @Phrase AND LanguageID = @LangID -- Check the output SELECT * FROM @TitlePhrases END -- ****************************** -- NOW WE TRY TO EXECUTE THE PROC -- ****************************** DECLARE @return_value int EXEC @return_value = [dbo].[AdvancedTrackSearch_TEST] @TrackTitleCrit = NULL, @TrackDescCrit = NULL, @RecordLabelCrit = NULL, @CategoryCrit = NULL, @ComposersCrit = NULL, @TrackDuration = NULL, @TrackDurationOperator = NULL, @LangID = NULL, @PageSize = NULL, @PageNumber = NULL, @UserID = NULL GO
It seems so simple and stupid but setting the LangID (even if we pass null into the actual query) causes the query to take a substantially longer time doing the full text search.
I hope someone finds an explanation!
--Nathan Zaugg
UPDATE:
As a matter of fate, me and my friend Phil Gilmore stumbled on the answer. The trick is to "SET ARITHABORD ON" for one of the first things that you do in the query. This is usually linked to arithmetic exceptions and overflows, but for some reason with out it there is little chance your query will perform. If you look at the difference between the execution plans before setting that variable vs. after you can see that the execution plan changes a lot! After ARITHABORT is ON the execution plans are again identical! Check out my post on MSDN forums.
You have no idea how it got there but one day you start your computer and get several new "accounts" you can login as. This is very annoying and frustrating! Fortunately there is a way to "hide" those accounts without affecting the applications that added them.
Here is the process:
- Open up the "Run" dialog. (Windows Key + R)
- Type "Regedit". Note: Vista Users will require Administrative approval
- Navigate the registry keys to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList
- Right click in the window New -> DWORD(32-bit Value)
- Type the name of the account you wish to ignore as the name of the value
- Leave the "Data" at what it defaults to.
When you are done it should look like this:
Last week our camera card filled up. Like most people, we'd almost rather buy a new, bigger card rather than try to go through the process of getting the photos onto the computer, processed, and burned to CD's. We decided that with about 2GB of pictures and videos on our cards we better download them. My wife, Tiffany, likes to rename all of the photo's from their naturally intuitive name like "DSC0001253.JPG" to something a little more clear like "February 2008-01.JPG" and so on.
With literally hundreds of images to rename (and she did this all by hand) I literally felt pain as I watched he do this for only a minuet or two. I got an idea for a program and I was sure I could have it written before she could finish her current folder. Well, it was a tie but now we have this nifty little application that will rename the files for us.
Here is a screen shot:
Instructions:
- Select the path of the photo's you wish to rename. If, for example, you want to rename a months worth of photo's, move them into their own folder first.
- You may use the "..." button to browse to the folder.
- Select a name format. This is the way the photo's will be renamed. The tricky part is to make sure and put the "{0:000}" in the string. This is where the number is going to go. The first zero indicates that is is the first (only) parameter passed into the string format function. The "000" after the colon is the numeric formatter. The formatters are explained here.
- Select the type of file you wish to rename. For example if your camera is like ours it will also take videos. You don't want to accidentally rename a .AVI to a .JPG because it will no longer work.
- When you are done, press the "Run" button. Before it renames any file it will verify the format with you (see below).
- You will see full progress indicators and it should only take a second or two even to do thousands of files.
As always, please drop me a line when you download it and let me know if you like it, hate it, or whatever!
Downloads