please help me optimisting my query -->

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

General SQL Server Forums

New to SQL Server Programming

please help me optimisting my query -->

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2011-10-11 : 08:26:00

hi friends,
i have a query as follows


select distinct -- top 15 INDOCNO,CHAR_FIELD2_AR,CHAR_FIELD3,convert(nvarchar,FORWARD_DATE,103) as FORWARD_DATE,convert(nvarchar,close_date,103) as close_date,REVISION_NO,convert(nvarchar,ISHAAR_DATE,103) as ISHAAR_DATE,CHAR_FIELD1,SUBJECT,m.docno as docno,m.confid_id as confid,COALESCE(DC2.CHILD_DOCNO,DC1.CHILD_DOCNO) AS ISLINKED,att.docno as attach,f.docname  
from TECHNICAL_MAIN m 
left outer join oaefile_doclinks dc1 on m.docno=dc1.parent_docno 
LEFT OUTER JOIN OAEFILE_DOCLINKS DC2 ON m.DOCNO=DC2.CHILD_DOCNO 
left outer join TECHNICAL_ATTACH att on m.docno=att.docno 
left outer join dmsdocinfo f on att.attach_docno=f.docid 
where m.indocno between '104889'and '104903' 
Order by m.docno desc

the query run time is about 9 seconds when i select top 15
but when i select between '104889'and '104903' query time more than 20 secs, although its giving the same result.
how to optimise ? what indexings you propose ?
the main table is technical_main having 130000 records
others have more records // docno and indocno are integers
what about millions or records what will happen then!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-10-11 : 08:27:35

Prefix your column names so we know from which table each column derives.

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

ann06
Posting Yak Master

171 Posts

Posted - 2011-10-12 : 03:46:46

i figured the problem was from the distinct keywords if removed it will compute in 0 seconds
thanks

rajarajan
Starting Member

48 Posts

Posted - 2011-10-12 : 10:49:00

point 1 : dont use distinct
Point 2 : when there is a relationship between the table use inner join rather than Left outer join.
point 3 : check the indexes
point 4 : when u have large amount of data you can use CTE first execute the table which as more data and resultset can use below

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-12 : 11:10:33

How To: Optimize SQL Queries

please see the following link:

http://msdn.microsoft.com/en-us/library/ff650689.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources