September 2, 2009 at 3:54 pm
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?
September 7, 2009 at 3:42 pm
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.September 8, 2009 at 9:13 am
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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2009 at 2:24 pm
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
September 8, 2009 at 7:00 pm
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 8, 2009 at 7:15 pm
No problem Paul, you just put me in good company.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2009 at 7:41 pm
Jack Corbett (9/8/2009)
No problem Paul, you just put me in good company.
High five.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 8, 2009 at 8:07 pm
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
September 8, 2009 at 8:19 pm
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...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 8, 2009 at 9:44 pm
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
September 9, 2009 at 7:14 pm
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)
September 9, 2009 at 8:03 pm
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:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 10:00 pm
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)
September 9, 2009 at 10:46 pm
It makes you think, doesn't it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 10, 2009 at 8:10 am
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