Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hurkle
Starting Member
5 Posts |
 Posted - 2001-12-12 : 10:14:41
|
Greetings, all.I'm working on an ASP front end to a Sql Server database. Due to silly client requirments, the main table in the database is one big fat table with 100s of fields. (350 and growing.. it's storing answers to a large number of interview questions)Here's my dillema. I need to provide a google type search tool that can search the entire table, 350+fields, 17,000 records, for multiple search criteria entered by the user. I also need to support a keyword that makes the search check for single character mispellings.Now, I've got something that works fast and reliable, but I have not found a single source on the web or in a book that describes how to do either. The only multi field searches I've seen written about involve naming the fields in code, or building a big where clause by looping thru field names. How would you folks handle this? Like a said, I've got a solution that's working, but I'm always wary of using a single approach without knowing alternatives.Thanks.. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-12 : 10:36:22
|
| There's an axiom in project development: Good, fast, cheap. Pick 2.Full-text indexing is probably the only thing that will work without A TON of work on your part. And don't be surprised if it doesn't work well or at all, because the design of this table will defeat you in any endeavor to provide this functionality. "Silly client requirements" are preventing them, and you, from doing this. Sorry, but any table with 350+ columns is not properly designed, especially not for searching. You need to redesign this database if you want to give them this kind of ability. And frankly, if they won't let you do that, you are better off without them as a client. |
 |
|
|
SKIBUM
Starting Member
32 Posts |
Posted - 2001-12-12 : 10:45:10
|
| Couldn't you use the SOUNDEX() Function, see Books OnLine, for the misspelling requirement? |
|
|
|
Hurkle
Starting Member
5 Posts |
Posted - 2001-12-12 : 10:57:05
|
| Thanks for the quick replies, folks.Full text indexing isn't an option at this point. Client requirements are a reality, in this instance, they requested a large flat table. They're going to be doing things with it after we're done with development, and they're not very savvy as far as database design goes.Now, I know a bit, but I'm hardly a guru, which is why I'm asking.I used to believe that any table with 350 plus, or 250 plus, columns, was most likely not properly designed. Of course, I am a wee bit curious as to why sql server supports 1024 columns, as opposed to access mdb's limit of 255..I know for a fact of several enterprise level database applications that have tables with thousands of columns or more, and some with even more complex 3 dimensional structures.Like I said above, I have a solution that works fast and well, and took less than a week to develope. What I'm asking is, how would any of you develope this without using full text indexing?Thanks for the soundex tip, that sounds intrigueing, and I'll check it out. Thanks again. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-12 : 12:23:06
|
| Herkle, let me apologize. I didn't realize how much of an a--hole I sounded like in my first response. Stupid knee-jerk reaction whenever I hear of a (stupid) client demanding a specific design, then asking for something that's near impossible with that design. Won't happen again!Now, hopefully this idea will help you. Have you looked at Merkin's article on keyword searching?http://www.sqlteam.com/item.asp?ItemID=5857I don't know if you only need to search single words or multiple words. Soundex() only works on single words, AFAIK. If there are multiple words in the answer columns, you can use Merkin's technique to split them out, then apply Soundex() to the individual words. The DIFFERENCE() function would get you the spelling match.As to searching all columns at once, you could probably do it using Merkin's technique (I like how that sounds!) with A LOT of UNION statements, but I doubt that's a good way. Since you've got so many columns, I'm pretty sure you'll max out the capabilities of dynamic SQL. I'm wondering if a view might work better for you; you can consolidate the search columns with UNION statements.Once again, I'm sorry I was such a dick before. Hope these ideas help you out, and that you get a solution! Good Luck! |
|
|
|
Hurkle
Starting Member
5 Posts |
Posted - 2001-12-12 : 23:34:48
|
| No worries, RobVolk, and as I said before, I do appreciate the quick replies. I've made some replies in forums that I later thought better of, and I know exactly what it's like.. But hey, I came looking for help, and you were offering. It's all good.I'm very new to working in a sql server environment, and just surfing the forums here is opening my eyes to a lot of possibilities that I never new existed. My only SQL development of any kind has been building select statements on the fly through code.. that's been my hammer, so all my problems have seemed like nails, if you get my drift.My solution was to build another table, with 3 fields, an ID field, another ID field corresponding to the individuals record, and a 3rd field, which is simply one big string with all the values of the other fields strung together, seperated by spaces. I then built a statement based on the search criteria, i.e. SELECT (somefields from the main table) FROM (some funky kind of join between the two tables, on the id field they have in common) WHERE fieldThatsOneBigString LIKE %searchTerm1% OR %searchTerm2% OR %searchTerm3 . If the user enters the keyword I chose to indicate a that a search criteria is to be searched for as a possible mispelling, I loop thru the search term and and something like this.. (previous where clause) AND fldBigString LIKE %_earchterm% OR fld.. LIKE %s_archterm% OR ... %se_rchterm% OR %sea_chterm% and so on.It's working great, it's faster than I hoped. Today I took my original data set and used some code create some randomized records, scaling it up from 20k to 100k records, and all the searches are still completing in about 2 seconds or less.I did look at the examples you referred me to, and I'm starting to make a little bit of sense out of it, but it's definitely a level beyond where I'm at now. It looks like my next project will be 3D Animation rather than another database app, so hopefully I'll have time to mull over and process what I'm coming across here.Thanks again for the help, y'all, and I look forward to being here. |
|
|
|
|
|
|
|
|
Searching all fields, and mispelled names?