February 19, 2008 at 11:21 am
I started this new job and this company had no real SQL Server developer or DBA before. All the query and stored procedures were written by the web developers. God bless them ! Now the company grows bigger and they realize they need someone specialize in database so they hired me.
This developer asked to look at his procedure. It was running fine and he just added a few lines. He said it only took 7 minutes to run during off hour and 30 to 40 minute to run during day time.
Here are a few things I am not sure I should tell him to change.
1. He created a linked server and he used it to do the select statement
SELECT * FROM linkedserver.database.dbo.table
Should I tell him to change to OPENQUERY ? Will it make a big difference?
2. I never see so many cursors in a procedures, as a matter of fact there is a
cursor within a cursor within a cursor.
Should I tell him to re-write not to use cursor? I actually wrote a select
statement to show him how to change not using the cursor.
3. I don't know why. He created a temporary procedure every time before he ran this procedure. CREATE PROCEDURE #LOG
Should I tell him to change to a permanent procedure?
4. He tried to use the new technique for 2005 and he used EXCEPT
SELECT fielda FROM TableA EXCEPT SELECT fielda FROM Tableb
SELECT a.fielda
FROM TableA a
LEFT OUTER JOIN Tableb b ON a.fielda = b.fielda
WHERE b.fielda IS NULL
Does it make a difference using EXCEPT ?
Thanks
February 19, 2008 at 11:36 am
1: I would think that quering the linked server and open query, would not make that big a performance impact.
You may only want to return columns that you need and not select *.
2: Absolutely!!!. Remove all cursors, almost all code can be done without recursion, but there are some exceptions. Think of it this way, each of these cursors is processing data one row at a time, so you are essentially processing 1 row of 1 row of 1 row at a time.
3:I dont understand why he would create a temp stored procedure. I would use only permanent.
4:No. These are the exact same thing. The except keyword actually tells SQL Server to use the same query you posted.
edit removed smiley faces from : )
February 19, 2008 at 11:50 am
Loner,
I would suggest that you should spend some time creating database coding standards that contain best practices and hard-fast rules on how to write effective and efficient database code. You can then use these standards as a measuring stick when performing code reviews or analysis on new or existing stored proceudres. Anything that does not meet your standards should be questioned.
I agree with Adam on the cursors. I would absolutely recommend that they find a set based way to handle their problem in place of the nested cursors. This 7 minutes process with nested cursors is probably a 7 second process with sets.
For the EXCEPT keyword, I personally prefer to see ANSI join syntax in place of EXCEPT/INTERSECT. I think these 2 new keywords are more fitting for comparing multi-column result sets for distinctness. If there is a relationship between certain columns in 2 tables, join the tables on that relationship. EXCEPT/INTERSECT may produce the same query plan as an ANSI join (I haven't tried this by the way), but my personal preference would be to only use these when needed and not as the standard method for joining tables.
Just remember, when you are creating standards or best practices for your shop, you are setting the stage to develop habits (good or bad) for your DB developers. Whatever you let them get away with is what you'll end up dealing with all over the place. If you define good standards up front (and get buy in on them) you'll propoage good coding habits throughout your DB codebase.
February 19, 2008 at 11:57 am
For the EXCEPT keyword, I personally prefer to see ANSI join syntax in place of EXCEPT/INTERSECT. I think these 2 new keywords are more fitting for comparing multi-column result sets for distinctness. If there is a relationship between certain columns in 2 tables, join the tables on that relationship. EXCEPT/INTERSECT may produce the same query plan as an ANSI join (I haven't tried this by the way), but my personal preference would be to only use these when needed and not as the standard method for joining tables.
I agree with John. I would not use this method as a standard for my work environment. While it can be the sames as an outer join looking for is null, I use this method to quickly identify datasets that do not have matching results. The except operator returns the distinct values in the left table that are non existent in the right table.
February 19, 2008 at 11:57 am
Loner (2/19/2008)
I started this new job and this company had no real SQL Server developer or DBA before. All the query and stored procedures were written by the web developers. God bless them ! Now the company grows bigger and they realize they need someone specialize in database so they hired me.This developer asked to look at his procedure. It was running fine and he just added a few lines. He said it only took 7 minutes to run during off hour and 30 to 40 minute to run during day time.
Here are a few things I am not sure I should tell him to change.
1. He created a linked server and he used it to do the select statement
SELECT * FROM linkedserver.database.dbo.table
Should I tell him to change to OPENQUERY ? Will it make a big difference?
If he's pulling every row from the linked table - then no - it won't make much difference. On the other hand - if he starts using a WHERE clause on that, OPENQUERY is a way to "ensure" that the remote server processes the filter. There are things that would make a linked server return the entire table to the local server, who would then have to apply the WHERE clause.
3. I don't know why. He created a temporary procedure every time before he ran this procedure. CREATE PROCEDURE #LOG
Should I tell him to change to a permanent procedure?
Not sure why you'd do that. I would change that to a permanent object also. If the content changes a lot (dynamic SQL, etc..) - then perhaps tag it WITH RECOMPILE as well.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply