September 11, 2008 at 2:55 pm
I need to review developers stored procedures before moving them into production on a SQL Server 2000. Some of the items he wants me to look for is things such as using SET instead of SELECT, Making sure there is a ORDER By in case the proc returns more than one row, things like that. Is there some sort of Best Practices document that I can use to achieve some of tihs?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 12, 2008 at 6:46 am
I don't think that there is a specific Best Practices document on this because so much of it depends on what the stored procedure is intended to do.
Things I would look for are:
2. Mixing DML and DDL - a best practice is to do all your DDL first then DML as mixing it can cause re-compiles.
3. Commenting, especially on conditionals, tell me why you are doing what you are doing.
As far as using SET vs. SELECT there are times when SELECT is appropriate. There is also some debate on using ORDER BY as some believe it is faster to do ordering in the UI than in SQL Server, but if you are counting on a specific order then you MUST use ORDER BY.
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 12, 2008 at 9:28 am
I agree with Jack that best practices are relative to what you're trying to achieve but here are a couple things I constantly ding our developers on.
- Use of SELECT *
- Returning columns that aren't used/needed (kind of goes with SELECT *)
- Not using a column list for INSERT statements, i.e. INSERT INTO [tbl] VALUES (1)
- Comments, comments, comments
_____________________________________________________________________
- Nate
September 12, 2008 at 9:33 am
What about doing multiples JOINS to the same table:
Such as
LEFT OUTER JOIN dbo.employee s1 WITH (NOLOCK) ON (s1.employee_id = t.salesman_employee_id)
LEFT OUTER JOIN dbo.employee s2 WITH (NOLOCK) ON (s2.employee_id = t.secondsales_employee_id)
LEFT OUTER JOIN dbo.employee s3 WITH (NOLOCK) ON (s3.employee_id = t.sales_manager_employee_id)
LEFT OUTER JOIN dbo.employee s4 WITH (NOLOCK) ON (s4.employee_id = t.cdr_id)
LEFT OUTER JOIN dbo.employee s5 WITH (NOLOCK) ON (s5.employee_id = t.fi_manager_employee_id)
LEFT OUTER JOIN dbo.employee s6 WITH (NOLOCK) ON (s6.employee_id = t.sales_mgr2_employee_id)
There has got to be a better way to do this and I see it constantly.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 12, 2008 at 10:31 am
Arthur.Lorenzini (9/12/2008)
What about doing multiples JOINS to the same table:Such as
LEFT OUTER JOIN dbo.employee s1 WITH (NOLOCK) ON (s1.employee_id = t.salesman_employee_id)
LEFT OUTER JOIN dbo.employee s2 WITH (NOLOCK) ON (s2.employee_id = t.secondsales_employee_id)
LEFT OUTER JOIN dbo.employee s3 WITH (NOLOCK) ON (s3.employee_id = t.sales_manager_employee_id)
LEFT OUTER JOIN dbo.employee s4 WITH (NOLOCK) ON (s4.employee_id = t.cdr_id)
LEFT OUTER JOIN dbo.employee s5 WITH (NOLOCK) ON (s5.employee_id = t.fi_manager_employee_id)
LEFT OUTER JOIN dbo.employee s6 WITH (NOLOCK) ON (s6.employee_id = t.sales_mgr2_employee_id)
There has got to be a better way to do this and I see it constantly.
I wish there were a better way to do this, but based on the design it looks like you are stuck with it. In reality the self-join isn't a big deal, but the multiple LEFT OUTER JOINS could be. If someone really can have a one -> many relationship like this the relationships should be broken out into another table that has employee_id, employee_id_relation, relation_type. Then your query would be a single Left Outer Join.
I second what the RP_DBA added as well. Especially the Select *. I also just thought of another addition.
Functions on columns in a where clause these tend to turn index seeks into scans. A common one is something like:
Where
MONTH(order_date) = @month
The QP has to apply the function on every row before it can apply the criteria.
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 12, 2008 at 11:11 am
If there are multiple foreign keys and you need to look up the associated values, then what you've listed is what you do. You can't do a single join to a table for multiple foreign keys.
Do you need the left join? If it's a foreign key relationship, perhaps not.
Do you need the nolock?
The standards I wrote for my previous company had things like this in:
Don't use distinct unless it's really necessary. In 90% of queries it shouldn't be
Don't order by unless the data is needed in a specific order (our front end allowed users to sort and resort data as they liked)
No functions on columns in the where clause
No correlates subqueries in the select clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2008 at 12:14 pm
This is the full statement:
SELECT d.customer_id, d.deal_id, d.delivery_date, d.bookvalue, d.payoff, d.equity, d.inventory_id, d.delivery_id,
d.payoff_date, d.dealer_id, d.modelyear, d.make, d.model, d.style, d.vehnew, d.vehclass,t.salesman_employee_id,
t.fi_manager_employee_id, t.cdr_id, t.sales_manager_employee_id, t.secondsales_employee_id, t.department_id,
t.sales_mgr2_employee_id, d.snapshot_id, dl.deal_type, dl.lender_id, c.cust_first_name, c.cust_last_name,
c.dealer_id cust_dealer_id, s.ad_source_desc ad_source,
s1.employee_first_name+' '+s1.employee_last_name salesman1_name,
s2.employee_first_name+' '+s2.employee_last_name salesman2_name,
s3.employee_first_name+' '+s3.employee_last_name sales_manager_name,
s4.employee_first_name+' '+s4.employee_last_name cdr_name,
s5.employee_first_name+' '+s5.employee_last_name f_i_manager_name,
s6.employee_first_name+' '+s6.employee_last_name sales_manager2_name,
w.descr lead_source, a.usecompanyname, a.company
FROM dbo.delivery d WITH (NOLOCK)
JOIN dbo.customer_detail t WITH (NOLOCK) on (t.customer_id = d.customer_id)
JOIN dbo.customer c WITH (NOLOCK) on (c.customer_id = d.customer_id)
LEFT OUTER JOIN dbo.customer_ad_source_list s WITH (NOLOCK) on (s.ad_source = t.ad_source)
LEFT OUTER JOIN dbo.customer_lead_source w WITH (NOLOCK) on (w.lead_id = t.Initial_source_id)
LEFT OUTER JOIN dbo.employee s1 WITH (NOLOCK) ON (s1.employee_id = t.salesman_employee_id)
LEFT OUTER JOIN dbo.employee s2 WITH (NOLOCK) ON (s2.employee_id = t.secondsales_employee_id)
LEFT OUTER JOIN dbo.employee s3 WITH (NOLOCK) ON (s3.employee_id = t.sales_manager_employee_id)
LEFT OUTER JOIN dbo.employee s4 WITH (NOLOCK) ON (s4.employee_id = t.cdr_id)
LEFT OUTER JOIN dbo.employee s5 WITH (NOLOCK) ON (s5.employee_id = t.fi_manager_employee_id)
LEFT OUTER JOIN dbo.employee s6 WITH (NOLOCK) ON (s6.employee_id = t.sales_mgr2_employee_id)
LEFT OUTER JOIN dbo.customer_address ca WITH (NOLOCK) ON ((ca.customer_id = d.customer_id) and (ca.history_type_id=1))
LEFT OUTER JOIN dbo.address a WITH (NOLOCK) ON ca.customer_address_id = a.address_id
LEFT OUTER JOIN dbo.deal dl WITH (NOLOCK) ON dl.deal_id = d.deal_id
WHERE d.active=1
AND ((t.dealer_id=866))
AND (d.delivery_date >= '2007-08-13 00:00:00')
AND (d.delivery_date < '2008-08-14 00:00:00')
ORDER BY d.delivery_date DESC
Could I knock it down by using a view?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply