Linked Questions

21 votes
1 answer
28k views

Why is "select *" faster than "select top 500 *" in SQL Server? [duplicate]

I have a view, complicated_view -- there are a few joins and where clauses. Now, select * from complicated_view (9000 records) is faster, much faster, than select top 500 * from complicated_view ...
user45867's user avatar
  • 1,683
3 votes
0 answers
52 views

Why does retrieving fewer rows take longer? [duplicate]

I have a view that (left outer) joins multiple recursive CTEs. There are about 10k rows. The results of running SELECT * FROM MyView ORDER BY MyPrimaryKey, with statistics time and io on: SQL Server ...
Ethan Reesor's user avatar
41 votes
6 answers
14k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
Kenneth Fisher's user avatar
53 votes
3 answers
4k views

Huge slowdown to SQL Server query on adding wildcard (or top)

I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not ...
stovroz's user avatar
  • 631
40 votes
2 answers
6k views

IF EXISTS taking longer than embedded select statement

When I run the following code it takes 22.5 minutes and does 106million reads. However, if I run just the inner select statement by itself it only takes 15 seconds and does 264k reads. As a side note, ...
Chris Woods's user avatar
  • 1,771
14 votes
8 answers
6k views

Order By causes a scan on a large table

I have the following query; SELECT TOP 100 ID FROM [dbo].[TableName] WITH (NOLOCK) WHERE TypeId = 2 AND DateTimeUTC < '2022-Aug-04 07:02:40' AND DateTimeUTC > '4/26/2022 7:36:36 AM' ...
Tom's user avatar
  • 191
18 votes
2 answers
6k views

'SELECT TOP' performance question

I have a query which runs much faster with select top 100 and much slower without top 100. The number of returned records is 0. Could you explain the difference in query plans or share links where ...
George K's user avatar
  • 2,261
7 votes
2 answers
3k views

Eliminate key lookup in execution plan

I have the following query: DECLARE @p__linq__0 UNIQUEIDENTIFIER SET @p__linq__0 = '... some guid ...' SELECT TOP 1 [EventId] AS [EventId], [DateCreated] AS [DateCreated], ...
Michael's user avatar
  • 183
4 votes
3 answers
3k views

Why SQL Server uses Non-Clustered but not Clustered Index?

I have a table with 145 million rows CREATE TABLE [dbo].[RFTest]( [SnapshotKey] [int] NOT NULL, [SnapshotDt] [datetime] NOT NULL, [LoanNum] [int] NOT NULL, [GLSourceSystem] [varchar](...
user avatar
3 votes
2 answers
1k views

Clustered Index Scan on partitioned table even when there is covering index

I have a partitioned table which is partitioned based on col1 int. I also have a covering index for the query that I am trying to troubleshoot. https://www.brentozar.com/pastetheplan/?id=BkNrNdgHm ...
DMDM's user avatar
  • 1,738
1 vote
2 answers
2k views

Slow query - big difference between estimated and actual rows

At my work, we have a problem with a search query. If I don't include the lookup into Table_39 (see below query), it takes 0 seconds to execute the query. If I include the lookup (..AND (Not Exists(...
ms-snack's user avatar
0 votes
1 answer
2k views

Can OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY cause to create totally different execution plan and also cause query to run slow?

We have a SELECT query which is taking time (around 90 secs) to execute. It has OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY in it. When we remove OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY, it completes in 4-5 ...
sachin-SQLServernewbiee's user avatar
3 votes
2 answers
150 views

How does TOPN influence a query with group by?

I have a query that looks like the following: Select t1.field1, t2.field2, sum(t1.field3) From t1 inner join t2 on t1.id = t2.id Group by t1.field1, t2.field2 It's simplified, normally there are a ...
kamahl's user avatar
  • 133
1 vote
1 answer
591 views

Adding 'top 20' massively slows down query!

I have a query: select * from Aview where field=20 order by id desc This returns 2700 rows from the view in about 1 second. Adding 'top 20' to the query makes MSSQL return in 43 seconds!! This has ...
Traderhut Games's user avatar
4 votes
2 answers
173 views

Access not properly translating TOP predicate to ODBC/Oracle SQL

I have a MS Access query that is based on a linked ODBC table (Oracle). The DDL for the underlying Oracle table is: create table road_insp ( insp_id integer, road_id integer, insp_date ...
User1974's user avatar
  • 1,457

15 30 50 per page