Optimized and fast query for simple join?

Question

Post reply

Optimized and fast query for simple join?

descentflower

Ten Centuries

Points: 1227
More actions
June 16, 2009 at 11:01 pm

#135350

Hi i have two tables,
TABLE1
SIDSNAME
1SAM
2RIG
3MAT
TABLE2
DATEIDPRICE
1/1/1113
1/2/1214
1/3/1313
1/4/1112
1/5/1215
1/6/1312
1/7/1210
1/8/1111
1/9/1112
.
.
.
.
.
TABLE2 contains around 4lakh rows.
I need output in this form
DATESNAMESUM(PRICE)
1/1/1SAM125
1/1/1RIG525
1/1/1MAT546
1/2/1SAM154
1/2/1RIG565
1/2/1MAT512
How will i get this with best performance?
What should i use inner join or subquery or cte or temp tables?
A example will help me a lot.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Pandian S SSChasing Mays Points: 625 More actions · Answer 1

Select T1.Date,t1.SName,SUM(t2.Price) 'Price' from Table1 t1(NOLOCK) JOIN Table2 t2(NOLOCK)

ON(t1.SID = t2.ID)

GROUP BY T1.Date,t1.SName

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Use an INNER JOIN or a CTE. Both should scale equal. Sub-queries may cause implicit cursors. Temp tables make no sense for one statement.

I would avoid NOLOCK if you are about to get valid data.

Flo

descentflower Ten Centuries Points: 1227 More actions · Answer 3

Florian Reischl (6/17/2009)
Use an INNER JOIN or a CTE. Both should scale equal. Sub-queries may cause implicit cursors. Temp tables make no sense for one statement.
I would avoid NOLOCK if you are about to get valid data.
Flo

Ok i will try both CTE and JOIN and will look in Execution Plan which one suits here.

What things to watch in Execution Plan ?

I didnt understand the NOLOCK use for valid data.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

NoLock is very dangerous if you dont understand the side effects

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

Clear Sky SQL
My Blog[/url]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

descentflower (6/17/2009)
What things to watch in Execution Plan ?

Have a look for correct index usage and no scans for many rows. Here you can find a currently free download of a great book by Grant Fritchey:

http://www.red-gate.com/specials/Grant.htm?utm_source=ssc&utm_medium=email&utm_content=Grant080521&utm_campaign=sqltoolbelt

Notice the tiny link below the big red button ;-).

I didnt understand the NOLOCK use for valid data.

NOLOCK causes that concurrent transactions can modify data since your statement does the select. This may cause incorrect/duplicate results. Have a look to BOL for different examples.

Flo