Page 1 of 4 123 ... LastLast
Results 1 to 15 of 55
  1. #1
    Join Date
    Jun 2007
    Posts
    34

    Red face Unanswered: concatenation for all-in-one search

    Hi there, I am just starting to create a database and it's been a long time so please bear with me.

    I simply want to be able to search a few different fields within the same table. I have different fields such as part number, manufacturer, description, model, etc.

    I want to be able to have a filter that simply searches all other those in one go rather than having a box for each field.

    I want to make sure the database runs as fast as possible when the database grows.

    Is it better to have the system automatically concatenate all seperate fields into its own field and simply index and search that field?

    Maybe this can be done when each record is entered. I am not sure, but thought I'd find your thoughts first.

    Thanks

    Nick
    Last edited by n_s_simpson; 06-04-07 at 17:17.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I don't know if you've checked the Code Bank lately, but check out this post, see if it'll help you.
    Me.Geek = True

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by n s simpson
    Is it better to have the system automatically concatenate all seperate fields into its own field and simply index and search that field?
    You could have the query concatenate the records - but I would not suggest doing this within your tables. Your entities are kept seperate for a reason
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2007
    Posts
    34
    Hi guys

    Thanks for the replies.

    Is it quicker to search through each field seperately rather than concatenate those fields when adding/editing a record and search that one field?

    Nick

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    My gut says each field seperately would be faster.
    If you post your table structure, some sample data and your expected results then it'd be easier to give you a decent answer imo
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2007
    Posts
    34
    So what I'm suggesting is not the norm then I guess.

    I always like to go against the grain

    So maybe I should just stick with the tried and tested method since I get the impression there wouldn't be any significant advantage of simply searching one field.

    Nick

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *lol*
    Again - these things all depend on context.
    Fulltext searches and wildcard searches are not as speedy as simple searches
    Example
    Code:
    WHERE FirstName Like '%Geo%'
    --is slower than
    WHERE FirstName = 'George'
    HTH
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2007
    Posts
    34
    Say I have a record as follows:

    Manufacturer: Intex
    Model: Queen AirBed
    Part Number: 66952
    Description: Memory Foam

    I want one box that I can type in a search such as:

    int air foam 669

    and it will bring up any records where any of the fields have this info. So the word foam could be in description or model and it would still come up.

    This would be preferred over having several boxes, drop downs, etc.

    But you recon that getting the search to find matching records by searching each field would be better than concatanating all the fields beforehand and then searching that one field.

    Nick

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you typing multiple criteria into your textbox - or would you search for each item one at a time?
    I.e. would your textbox contain
    'int air foam 669' <<all items per search
    OR
    'int' <<One item per search
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by n_s_simpson
    Say I have a record as follows:

    Manufacturer: Intex
    Model: Queen AirBed
    Part Number: 66952
    Description: Memory Foam

    I want one box that I can type in a search such as:

    int air foam 669

    and it will bring up any records where any of the fields have this info. So the word foam could be in description or model and it would still come up.

    This would be preferred over having several boxes, drop downs, etc.

    But you recon that getting the search to find matching records by searching each field would be better than concatanating all the fields beforehand and then searching that one field.

    Nick
    This will take some programming and run like a dog. Just so you know before you get started.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I had a play with the idea in SQL Server's QA
    Code:
    DECLARE @s varchar(8000)
    DECLARE @d datetime
    --Enter search string below
    SET	@s = 'water foam'
    
    SET NOCOUNT ON
    
    IF EXISTS (SELECT NULL FROM sysobjects WHERE type = 'u' AND name = 'gvTemp') BEGIN
    	DROP TABLE gvTemp
    END
    
    CREATE TABLE gvTemp
    	(
    	 Manufacturer	varchar(20)
    	,Model		varchar(20)
    	,Part_Number	numeric
    	,iDescription	varchar(200)
    	)
    
    INSERT INTO gvTemp (Manufacturer,Model,Part_Number,iDescription	)
    VALUES		   ('Intex','Queen AirBed',101,'Memory Foam'	)
    INSERT INTO gvTemp (Manufacturer,Model,Part_Number,iDescription	)
    VALUES		   ('Intex','King AirBed',66953,'desc'		)
    INSERT INTO gvTemp (Manufacturer,Model,Part_Number,iDescription	)
    VALUES		   ('Bedz','Queen WaterBed',66954,'desc'	)
    INSERT INTO gvTemp (Manufacturer,Model,Part_Number,iDescription	)
    VALUES		   ('Intex','King WaterBed',66955,'Normal Foam'	)
    
    SET	@d = GetDate()
    
    PRINT	'Searching for: ''' + @s + ''''
    PRINT	''
    SET	@s = LTRIM(RTRIM(@s))
    PRINT	'Removed leading and trailing spaces'
    SET	@s = ' ' + @s
    PRINT	'Single lead space added'
    SET	@s = Replace(@s,'  ',' ')
    PRINT	'Removed double spaces'
    SET	@s = SubString(@s,0,100)
    PRINT	'Search string trimmed'
    SET	@s = Replace(@s, ' ','%'' OR Manufacturer + Model + CONVERT(varchar, Part_Number) + iDescription LIKE ''%') + '%'''
    PRINT	'Inserting criteria...'
    SET	@s = SubString(@s,1,Len(@s))
    PRINT	'  ...Removing first ''OR'''
    SET	@s = 'WHERE ' + SubString(@s, 6, Len(@s))
    PRINT	'  ...Adding ''WHERE'''
    SET	@s = 'SELECT * FROM gvTemp ' + @s
    PRINT	'  ...Adding ''SELECT'''
    PRINT	'Criteria complete'
    EXEC	(@s)
    PRINT	'Executed'
    
    SELECT	CONVERT(varchar,DateDiff(ms,@d,GetDate())) + ' milliseconds' As 'Query execution time'
    
    SET NOCOUNT OFF
    It's very messy and I doubt it'll be quick when the number of rows to search increases - but it's worth a test.
    *shrugs*
    EDIT: Yes I am aware that we are not using SS but this can be adapted easily enough into Access - I think I commented it fairly well
    Last edited by gvee; 06-05-07 at 06:58.
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I just ran the above with an insert of 100,000 records.
    It took 670milliseconds... I would say it fairs quite well in the speed department.

    EDIT: 1,000,000 records took around 7 seconds.
    Last edited by gvee; 06-05-07 at 07:07.
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The time taken will be fairly linear to the size (depth and breadth) of the table. Every field needs to be scanned and no indexes can be used. Testing on a (presumably) dedicated SQL Server is very different to a live JET database. If there are few users and the table is neither too wide nor too deep then it could run ok.
    In short - there is probably no less efficient way to query a table than like this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *lol*
    I didn't think I was on to that much of a winner
    I'm just stuck thinking of a better approach atm when searching for multiple values in multiple fields. If it was a simple search for "Intex" across all 4 fields then absolutely no problemo...
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not criticising your approach nor the OPs question - just pointing out that this is not really a performance problem as it will never perform well.

    One point - if the OP would like people to search for phrases (e.g. "%water bed%") then obviously " " cannot be used as a delimiter.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •