Any way to optimise this query?

Hi guys

Is there any way I can run this query faster? Should I take out the ORDER BY
clause? This is supposed to return 17,000 rows and takes around 30 minutes or
so. Is there no way at all to get this result faster?

select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
clientinitials, r.clientidno, r.grantedamount, r.bankname,
r.accountnumber, r.status, r.grantdate, r.consultantname, r.propertyaddress,
r.erfdescription, r.commenthistory, br.expectedregdate
from bondtrak..rptdetail r
join ebondprd..bankresponse br
on br.applicationid = r.applicationid
where
r.rundate = '20051010'
and r.primarybankind = 'Y'
and r.status = 'granted'
and r.statusdate between '20020101' and '20050930'
and r.businessunit in ('bond choice', 'ppl')
order by r.sitename, r.consultantname, r.statusdate

Thanks for any help.
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1If you don't need the result ordered then definitely drop the ORDER BY.

Why do you want to return 17,000 rows in one hit anyway? Can't you
process the data server side?

--
David Portas
SQL Server MVP
--|||Do you have any usefull indexes on these tables?

"I sense many useless updates in you... Useless updates lead to
defragmentation... Defragmentation leads to downtime...Downtime leads
to suffering..Defragmentation is the path to the darkside.. DBCC
INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with
you" -- http://sqlservercode.blogspot.com/|||Driesen via SQLMonster.com (u11907@.uwe) writes:
> Is there any way I can run this query faster? Should I take out the
> ORDER BY clause? This is supposed to return 17,000 rows and takes around
> 30 minutes or so. Is there no way at all to get this result faster?
> select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.
> clientinitials, r.clientidno, r.grantedamount, r.bankname,
> r.accountnumber, r.status, r.grantdate, r.consultantname,
> r.propertyaddress,
> r.erfdescription, r.commenthistory, br.expectedregdate
> from bondtrak..rptdetail r
> join ebondprd..bankresponse br
> on br.applicationid = r.applicationid
> where
> r.rundate = '20051010'
> and r.primarybankind = 'Y'
> and r.status = 'granted'
> and r.statusdate between '20020101' and '20050930'
> and r.businessunit in ('bond choice', 'ppl')
> order by r.sitename, r.consultantname, r.statusdate

There might be. But without knowledge of the tables, indexes and how
big they are, all you can get is guesses. If you don't need data to be
sorted, you can remove ORDER BY, but it doesn't take 29 minutes to sort
17000 rows, so the effect is moderate of that operation.

A clustered index on rptdetail(rundste, statusdate) or only (rundate)
should be a good start. An index on bankreponse(applicationid) is also
necessary.

You may also have problems with statistics that are out of date.
UPDATE STATISTICS WITH FULLSCAN on both table can address this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi David

I dropped the ORDER BY and copied it Excel. This is only a once off query for
the big wigs.

Thanks for the help
Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||Hi Erland

Thanks for the help. I did drop the ORDER BY. The only reason this takes so
long to run is becasue our DataBase sits over 500 miles away. We to get
through a whole lot of banking environment security to access our DB.

Driesen

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200510/1|||> We to get
> through a whole lot of banking environment security to access our DB.

So you thought you'd undermine all that security by copying the data to
an insecure Excel spreadsheet... :-)

Maybe you can use DTS to export the data to Excel. Or use BCP to create
a delimited file that can be opened in Excel.

--
David Portas
SQL Server MVP
--