Post reply

Search on multiple criteria

  • June 4, 2007 at 6:50 am

    #179144

    All,

    A client has asked for a database that will be able to search on multiple criteria at the same time. I have been unable to come up with a database design that will allow for easy entry and search. I currently have a table that has a record for each search type and value type. I can easily search on one type of criteria. I cannot figure out how to search multiple criteria at the same time.

    Thanks,

    Scott

  • August 21, 2007 at 12:32 pm

    #728405

    Check out the dynamic sql articles at http://www.sommarskog.se. he provide a good example of how to do this.

  • August 21, 2007 at 10:13 pm

    #728477

    Scott -

    One thing to take a look at in this circumstance is running multiple querys and aggregating the results (e.g. a union), rather than one nasty query that trys to do it all.  Multiple "OR"'s, etc. will just kill your query performance - it's often faster to perform multiple smaller queries than one nasty one.

    Joe

     

  • May 30, 2009 at 12:17 pm

    #1002411

    Would this work in a scenario where you are searching on up to 40 or 50 criteria? I'm trying to design a super fast search of MLS (real estate, multiple listing) data. Currently we have a table with like 50 rows and we use dynamic SQL. ANy suggestions on how I could do this the way you suggested? Would there be a table for each criteria? IE: A number of bedrooms table, number of baths table, price table, pool view table, etc. etc..

  • May 30, 2009 at 3:21 pm

    #1002426

    one way is to create a cube in analysis services and have the dimension tables specified with location attributes, lot attributes, building attributes, community attributes etc and then the search would be super fast.

  • May 31, 2009 at 6:06 am

    #1002477

    I"m not a BI guy but just did a little research. Are you speaking about creating a star-schema datawarehouse? Would there be a dimensions table for each attribute?

    NumBedroomsDimension

    NumBathsDimension

    LotSizeDimension

    PriceDimension

    PoolDimension

    GolfViewDimension

    MountainViewDimension

    MLSAreaDimension

    etc....

    Everything I find about star schema and data warehouses is about helping organizations manage intelligence, we are looking to implement a fast search triggered by end users on the web, would this still be appropriate?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply