to run query fast - 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 2005 Forums

Transact-SQL (2005)

to run query fast

Author

Topic

palak
Yak Posting Veteran

55 Posts

Posted - 2008-07-23 : 10:08:17

hi folks,

i have select query

select distinct s.salesno, s.salesrepname, sh.date
from sales s
join saleshistory sh
on s.salesno = sh.salesno
where sh.date > '1/1/1999'

it takes 10mins...so can anyone tell me how can i get the query results fast..what should i do?

thanks in advance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-23 : 10:10:25

Do you have indexes for joins and Where clause saleshistory.date? and how many records it returns?

palak
Yak Posting Veteran

55 Posts

Posted - 2008-07-23 : 10:12:57

i don't have any index and it returns results of - 119283 rows..

thanks

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-23 : 10:17:34

chk this,

select s.salesno, s.salesrepname, sh.date
from sales s
left join saleshistory sh
on s.salesno = sh.salesno
where sh.date > '1/1/1999'

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-23 : 10:31:39

read about indexes and use them

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 11:47:20

Run this code and try to query again.

CREATE CLUSTERED INDEX IX_SalesHistory ON SalesHistory (Date, SalesNo)

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-23 : 14:14:32

quote:
Originally posted by VGuyz

chk this,

select s.salesno, s.salesrepname, sh.date
from sales s
left join saleshistory sh
on s.salesno = sh.salesno
where sh.date > '1/1/1999'

Due to having criteria in the WHERE clause you can turned a LEFT OUTER JOIN into an INNER JOIN which is the same as what the OP had.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources