SQL Server Logical Reads

Question

We have the same database setup on two machines. Same tables. Same indexes. I am running a query that is self joining:

 SELECT Column1 ,Column2 ,Column3 ,Column4 ,RowID = RowNumber OVER ( Partition By Column1, ORDER BY Column2) INTO #tmp FROM AuditTable SELECT * FROM #tmp t1 JOIN #tmp t2 ON t2.Column1 = t1.Column1 AND t2.RowiD = t1.RowID +1

We run this query with 20,000 rows on one database/server and it takes 2 minutes to complete with logical reads around 3.2 million.
The same query runs on a different database/server with 150,000 records and returns in a second with logical reads of 2240. What about the servers would make the same query on the same table setup (including indexes / fragmentation/ statistics) run so differently on two different servers

It returns 20,000 rows or are there 20,000 rows in the AuditTable? — SQLChao
– SQLChao, Commented Jul 21, 2016 at 13:15
the two databases are on two different servers (which was mentioned in the original post) — karmarro
– karmarro, Commented Jul 21, 2016 at 14:06

Gordon Linoff · Accepted Answer · 2016-07-21 13:13:42Z

I don't know why your servers have different performance.

However, in SQL Server 2012+, you should be using LAG():

SELECT Column1, Column2, Column3, Column4, LAG(Column2) OVER (PARTITION BY Column1 ORDER BY Column2) as prev_Column2, LAG(Column3) OVER (PARTITION BY Column1 ORDER BY Column2) as prev_Column3 FROM AuditTable;

And, in order versions of SQL Server, you should not use a temporary table, unless you are going to add indexes to it for performance. A CTE is simpler and doesn't require materializing the intermediate results.

Collectives™ on Stack Overflow

SQL Server Logical Reads

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related