Query why taking much more time? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Query why taking much more time?

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-10-13 : 03:16:05

i have the below query its used to select first 20 records for page display. when using as it is its taking 3 mins to finish abt 50,000 record.

SELECT TOP 20 VW_LATEST_DWG_TECHNICAL.INDOCNO ,VW_LATEST_DWG_TECHNICAL.doc_kind
FROM dbo.VW_LATEST_DWG_TECHNICAL with ( NOLOCK )
Where (VW_LATEST_DWG_TECHNICAL.INDOCNO)
not in
( SELECT TOP 0 (VW_LATEST_DWG_TECHNICAL.INDOCNO)
FROM dbo.VW_LATEST_DWG_TECHNICAL with (NOLOCK)
Where VW_LATEST_DWG_TECHNICAL.doc_kind = 'O and M Manuals'
ORDER BY VW_LATEST_DWG_TECHNICAL.INDOCNO
)
AND VW_LATEST_DWG_TECHNICAL.doc_kind = 'O and M Manuals'
ORDER BY VW_LATEST_DWG_TECHNICAL.INDOCNO

but when i remove ",VW_LATEST_DWG_TECHNICAL.doc_kind " from the first select its taking only 3 seconds.
why its taking so much time more if adding this column to the result set although its giving the same result each time?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 03:38:56

Try this, since TOP 0 is of no use.

SELECT TOP 20	INDOCNO,
		doc_kind 
FROM		dbo.VW_LATEST_DWG_TECHNICAL WITH (NOLOCK)  
WHERE		doc_kind = 'O and M Manuals' 
ORDER BY	INDOCNO

One reason for adding the doc_kind column taking much more time, is that doc_kind column is not indexed.
Therefore SQL Server must use a bookmark lookup to find the column value, or use an index scan or table scan instead of index seek. Scans are slower than seeks.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 03:46:26

Oh wait... The query above is a result of a dynamic query for paging?

SELECT TOP 20	s.INDOCNO,
		s.doc_kind
FROM		dbo.VW_LATEST_DWG_TECHNICAL AS s WITH (NOLOCK)
WHERE		NOT  EXISTS (	SELECT TOP 0	e.*
				FROM		dbo.VW_LATEST_DWG_TECHNICAL AS e WITH (NOLOCK) 
				WHERE		e.doc_kind = 'O and M Manuals'
						AND e.INDOCNO = sa.INDOCNO
				ORDER BY	e.INDOCNO 
			) 
		AND s.doc_kind = 'O and M Manuals' 
ORDER BY	s.INDOCNO

SELECT TOP 20	s.INDOCNO,
		s.doc_kind
FROM		dbo.VW_LATEST_DWG_TECHNICAL AS s WITH (NOLOCK)
LEFT JOIN	(
			SELECT TOP 0	e.*
			FROM		dbo.VW_LATEST_DWG_TECHNICAL AS e WITH (NOLOCK) 
			WHERE		e.doc_kind = 'O and M Manuals'
			ORDER BY	e.INDOCNO 
		) AS e ON e.INDOCNO = sa.INDOCNO
WHERE		s.doc_kind = 'O and M Manuals' 
		AND e.INDOCNO IS NULL
ORDER BY	s.INDOCNO

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

ann06
Posting Yak Master

171 Posts

Posted - 2008-10-13 : 05:10:00

Yeah its for dynamic paging
Thanks mr Peso very helpful

ann06
Posting Yak Master

171 Posts

Posted - 2008-10-13 : 05:12:20

by the way now it took only 2 seconds :*

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 05:17:59

Thank you.

There are more efficient paging algorithms available.
If you have the time, please search this site for other algorithms.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources