Filtering database question

  • I have a database (sql server 2005) to store car accident data.

    Related to the main table (CarAccident) i have about a dozen of other tables (Vehicle, TypeOfVehicle,Road, RoadCondition, WetherCondition, City,....)

    Know i want provide users with a form for filtering data per date, type of vehicle involved, street name, cities, age of people involved and so on.

    I thought to use a stored procedure (doSelect) with parameters, but this implies the use of many join between tables and probably a performance question (the CarAccident tabel grows about of 2000 record per year).

    Which is the "best practicse" in these cases?

  • liuc (9/2/2009)


    Which is the "best practice" in these cases?

    Put "Design" as step #1 of project life cycle, not as last step.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • If you have proper indexes performance should not be an issue because of joining tables. You need to determine how you are going to handle NULL or empty parameters as how you handle this will affect performance more than joins will (if you have proper indexes). See this article by Erland Sommerskog

  • This type of system screams for a dynamic sql based search sproc. Difficult to construct properly, but by far the most efficient mechanism you will get. I have designed (or refactored) many of these for my clients, often with 3 or 4 orders of magnitude performance increase over traditional access mechanisms.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • PaulB (9/7/2009)


    liuc (9/2/2009)


    Which is the "best practice" in these cases?

    Put "Design" as step #1 of project life cycle, not as last step.

    Very wise. Totally useless in the context of the question, but a valid point.

    Anyway...

    As Mr. Guru suggests, a dynamic SQL search procedure can work well, if done correctly.

    Mr Guru is available at very reasonable rates I hear 🙂

    Alternatively (or as well), check out Erland Sommarskog's standard reference on the subject here

    Paul

    edit: Apologies Jack - I missed the fact that you had already linked to Erland's article.

  • No problem Paul, you just put me in good company.

  • Jack Corbett (9/8/2009)


    No problem Paul, you just put me in good company.

    High five.

  • As Mr. Guru suggests, a dynamic SQL search procedure can work well, if done correctly.

    Mr Guru is available at very reasonable rates I hear

    Actually my rates are definitely too low. In the last few months I have had not one but TWO new clients tell me they were shocked at what I charge for my services!! I have made some adjustments based on that input! 😎 May need to adjust some more because the latest didn't blink an eye when we discussed remuneration.

    Unfortunately for the OP even if he/she did want some help from me I am oversubscribed. Knocking on some serious wood here!!!!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (9/8/2009)


    Actually my rates are definitely too low. In the last few months I have had not one but TWO new clients tell me they were shocked at what I charge for my services!! I have made some adjustments based on that input! 😎 May need to adjust some more because the latest didn't blink an eye when we discussed remuneration.

    Unfortunately for the OP even if he/she did want some help from me I am oversubscribed. Knocking on some serious wood here!!!!!

    I believe we SQL people are extremely good value, compared to (say) lawyers 😎

    I hope your good situation continues - it looks likely to, all things considered...

  • I believe we SQL people are extremely good value, compared to (say) lawyers 😎

    Glad you didn't say like doctors there since my wife is an OBGYN! 🙂 Of course if you spread out her money, which isn't as much as people might think (ESPECIALLY after you take out malpractice premiums), she make a ridiculously low per hour rate! :crying:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I'm available nights & weekends at the moment... at reasonable rates.

    Steve

    (aka smunson)

    :-):-):-)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • smunson (9/9/2009)


    I'm available nights & weekends at the moment... at reasonable rates.

    Steve (aka smunson)

    :-):-):-)

    I hope you mean for SQL work! :laugh:

  • Well, unlike Barry, I won't reply the way he might, such as "Dang, so much for that idea...", and yes, it's for SQL work. However, perhaps I should be more concerned about why I've been chosen for roasting (yes, I saw your laugh emoticon) ??? :w00t::w00t::w00t:

    Then again, maybe one should wonder why you went in that direction first ? :-D:-D:-D

    Steve

    (aka smunson)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • It makes you think, doesn't it.

  • Paul White (9/9/2009)


    It makes you think, doesn't it.

    Things that make you go "HHHMMMMMMMM"!! :hehe:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 1 through 15 (of 21 total)

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