faster query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

faster query

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-05-09 : 00:21:10

Hi everybody
I need help for better faster query that does the same.
thank you


select t.ABRNCHCOD,t.TBDPTYPE,t.CFCIFNO,t.TDSERIAL,max(tb.TBRATEDATE) TBRATEDATE
from tcdeposit t
left outer join tbintrate tb
on t.TBDPTYPE=tb.TBDPTYPE
and t.TDRNWDAT>= tb.TBRATEDATE
where t.TDOPNDAT <=@date and tb.TBRATEDATE <=@date
group by t.ABRNCHCOD , t.TBDPTYPE  , t.CFCIFNO , t.TDSERIAL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-09 : 03:25:21

1. Add a proper index.

N 56°04'39.26"
E 12°55'05.63"

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 07:21:05

proper indexes on columns, involved in JOIN,WHERE and GROUP BY clauses

--------------------------
http://connectsql.blogspot.com/

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-05-11 : 18:01:10

1. Replace outer with inner join or move condition tb.tbRateDate <= @date from where clause to join .. on.
2. Take care of indexes. If you provide more info about number of rows in tables and number of distinct values of each column we might be able to recommend an index.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-12 : 01:23:19

quote:
Originally posted by mmarovic

1. Replace outer with inner join or move condition tb.tbRateDate <= @date from where clause to join .. on.

Not really. What is there are not matching records between the two tables. MAX will return NULL in original query.
With INNER JOIN that is not longer a possibility.

N 56°04'39.26"
E 12°55'05.63"

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-05-12 : 02:12:42

quote:
Originally posted by Peso

quote:
Originally posted by mmarovic

1. Replace outer with inner join or move condition tb.tbRateDate <= @date from where clause to join .. on.
Not really. What is there are not matching records between the two tables. MAX will return NULL in original query.
With INNER JOIN that is not longer a possibility.

N 56°04'39.26"
E 12°55'05.63"

It will not return null as long as condition tb.tbRateDate <= @date is in where clause.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-12 : 12:16:42

Ah. Good point.
I read that " tb.tbRateDate <= @date " was part of the JOIN condition, not the WHERE clause.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources