November 29, 2013 at 2:40 pm
I am trying to create search facility within t sql to be able to search a large table containing addresses, columns include house number, address line 1,2 and 3, town, county, postcode, some of the columns are blank. I have wrote code to concatenate the columns into a string in one column removing spaces etc and applied a full text index. I want to be able to search using one textbox on a frontend whereby I can search the whole address or certain elements ie town, postcode etc am I doing it correctly by combining all columns into one large string? I have experiemented with freetext, wildcards like, contains etc but can't seem to get a solution I want, is it possible to do this kind of search in sql or would I need a frontend to format/build the search string before its passed into the query? Any help or advise is much appreciated
December 2, 2013 at 4:01 pm
While you could certainly do it that way, I absolutely would not. If you concatenate everything and dump it into a new column or create a calculated column, you're just asking for hideous performance.
Writing a Catch-all query is not a great idea either. Gail Shaw has a great article on that here: [/url]
I would read that first and then figure out how to do the query right. the problem you're creating if you create a calculated column to stuff everything into is that your query can only use one index, and if you have a large table, performance will be abysmal.
December 2, 2013 at 5:37 pm
pietlinden (12/2/2013)
Writing a Catch-all query is not a great idea either. Gail Shaw has a great article on that here: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries
To add to pietlinden's suggestion regarding Gail's article, here's a step by step method to use Gail's best practice approach to create a dynamic search procedure.
How to Design, Build and Test a Dynamic Search Stored Procedure [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 3, 2013 at 4:43 am
Pietlinden - I had created the concatenated column in another table based on the main table columns and also with the ID, I then created a full text index on this table. The search looks at the lookup table and then joins in the original to display the address.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply