help needed in code tuning.

Question

Post reply

help needed in code tuning.

HanumanC

Valued Member

Points: 60
More actions
August 18, 2010 at 10:53 pm

#228094

Please share your thoughts and views on code tuning this code and redefining this into some other format(like USP).
SQL query for the Northwind sample database (free download). The results obtained by the SQL query return the exact data required, but the query itself does not meet our review standards for queries used in a production application.
i. Please rewrite the query here so as to return the same results, but with the goals of maximum readability and overall efficiency in mind.
ii. Please describe how you have determined the query’s efficiency has improved.
DECLARE @OrderId int
DECLARE @Orders TABLE (
OrderId int,
ProductId int,
ItemTotal money
)
DECLARE CaracasOrders CURSOR FAST_FORWARD READ_ONLY
FOR SELECT OrderID FROM Orders WITH (NOLOCK) WHERE ShipCity = 'Caracas'
OPEN CaracasOrders
FETCH NEXT FROM CaracasOrders INTO @OrderId
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @Orders
SELECT @OrderId, ProductId, (Quantity * UnitPrice) As ItemTotal FROM [Order Details] WHERE OrderID = @OrderID
FETCH NEXT FROM CaracasOrders INTO @OrderId
END
CLOSE CaracasOrders
DEALLOCATE CaracasOrders
SELECT * FROM @Orders
Regards,
Hanuman

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

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

Hello,

Please take a look at below query:

SELECT orders.OrderID,

[Order Details].ProductId,

( [Order Details].Quantity * [Order Details].UnitPrice ) AS ItemTotal

FROM dbo.orders

INNER JOIN dbo.[Order Details] ON orders.OrderID = [Order Details].OrderID

WHERE orders.ShipCity = 'Caracas'

1. Whenever possible, try to avoid cursors because it affects performance.

2. Always use qualified names for table as well as columns.

Above query should give the result as per your requirement.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

g.hanuman.c (8/18/2010)
SQL query for the Northwind sample database (free download). The results obtained by the SQL query return the exact data required, but the query itself does not meet our review standards for queries used in a production application.
i. Please rewrite the query here so as to return the same results, but with the goals of maximum readability and overall efficiency in mind.
ii. Please describe how you have determined the query’s efficiency has improved.

This looks like homework or an exam question (seeing as no business problem is ever going to want to optimise an old sample database)

You learn nothing if you get other people to do your work for you, and I don't need the qualification. Give it a try yourself, if you get stuck ask specific questions on what you're stuck on, show the work you've done and someone will likely help you.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Krishnraj SSCrazy Points: 2027 More actions · Answer 3

Yes, I completely agree with Gilamonster...

as he said - "You learn nothing if you get other people to do your work for you"

first try to find it by doing some googling, i m sure u gets a lot of article from the net!!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Krishnraj (8/19/2010)
Gilamonster...
as he said -

Gila is not "HE"... GilaMonster @ Gail Shaw is a girl and SHE is a MVP in SQL Server as well...:-)

Krishnraj SSCrazy Points: 2027 More actions · Answer 5

Krishnraj

SSCrazy

Points: 2027

August 19, 2010 at 6:49 am

#1209065

OOPSSSS!!!!!!:-P....

really so sorry....

sturner SSC-Insane Points: 22411 More actions · Answer 6

Consider that the posters who ask these "homework" or exam questions on here desire to be the DBA experts of tomorrow. Having someone else do you homework is not going to cut it.

When I first started in DB architecture and Administration there were no forums such as this. We had to learn from the ground up and read the books (when Microsoft actually printed entire sets of manuals for SQL Server lol)

The probability of survival is inversely proportional to the angle of arrival.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

ColdCoffee (8/19/2010)
Krishnraj (8/19/2010)
Gilamonster...
as he said -
Gila is not "HE"... GilaMonster @ Gail Shaw is a girl and SHE is a MVP in SQL Server as well...:-)

Ya but she looks surprisingly like her Avatar in real life... altho much more of a feminine ninja! :hehe:

Derrick Smith SSChampion Points: 10833 More actions · Answer 8

When I was first learning SQL, I had to google a million things a day. I actually still do look things up, every single day without fail.

Answers to this kind of question are easy to find if you searched. Being self-sufficient is the first step to being successful in pretty much any IT field, but DBA especially (since very few companies have more than 1 SQL DBA, and you will be a one man shop).

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Comment withdrawn. I couldn't find the MS reference that I remember seeing to support what I said so I've withdrawn my comment. If I find the MS reference, I'll repost it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Jeff Moden (8/20/2010)
Comment withdrawn. I couldn't find the MS reference that I remember seeing to support what I said so I've withdrawn my comment.

Now I'm curious... PM or email?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Derrick Smith SSChampion Points: 10833 More actions · Answer 11

Jeff Moden (8/20/2010)
Comment withdrawn. I couldn't find the MS reference that I remember seeing to support what I said so I've withdrawn my comment. If I find the MS reference, I'll repost it.

I saw your comment and was curious which part of that was deprecated..I looked around a bit myself and couldn't find anything either.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

GilaMonster (8/20/2010)
Jeff Moden (8/20/2010)
Comment withdrawn. I couldn't find the MS reference that I remember seeing to support what I said so I've withdrawn my comment.
Now I'm curious... PM or email?

Nah... it's ok. I'm embarassed but it's nothing I can't post.

I was referring to using actual table names as part of 2 part notation in the SELECT list. I remember very clearly seeing something from MS about code like the following not being supported at some future date...

SELECT tablename.columnname1,

tablename.columnname2

FROM dbo.tablename

The thing I read (and can no longer find) said that you should either use just the column name (obviously not recommended when joins are present) or to use an alias for the table name to be used in the SELECT list columns as many of us currently do.

The closest thing I can find in the deprecation lists is the fact that you won't be able to use 3 and 4 part naming in the SELECT list in the future, but I can't find the part about the 2 part naming. The MS link I'm talking about (and can no longer find) had the 2 part nuance of all that clearly explained.

Of course, that "find" happened when I was on some serious meds for Bronchitis so it may have been a manifestation on my part. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Jeff Moden (8/21/2010)
I was referring to using actual table names as part of 2 part notation in the SELECT list. I remember very clearly seeing something from MS about code like the following not being supported at some future date...

I remember something similar, can't remember the details. I'll search, dunno if I'll find anything though

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Here's a weird idea, start worrying about it when you plan to move to sql 201? and having the upgrade advisor throwing errors and warnings.

In the mean time I never used tlename.colname so I won't lose too much sleep over this :w00t: and I'm pretty sure you shouldn't have to either!