Sql-server – What does the “Edit top 200 Rows” SSMS option actually do

sql serversql-server-2012ssms

I am new the SQL Server DBA role. I have always been the developer, but due to being the only one at my company with moderate SQL Experience, the role has fallen onto my plate. And today I came face to face with a question I did not have an answer for.

I am familiar with the option in SSMS to "Edit top 200 Rows" for a table, but I never understood the HOW of it. I ask this question because a Contractor who's been working on our system since before I took over was complaining of slow query results. The query he had written pulls in data over a large time period with a final result set in the ballpark of 800,000 rows. A sizable chunk of data.

What surprised me more was that said query took over 9 minutes to run, which did not seem correct to me. I asked him to send me the query. I took his query and ran it and it returned for me in 28 seconds. A sizeable difference compared to the over 9 minutes it took him. We both ran the query directly on the server so I knew that was not the case. I then asked him to show me how he ran it. He then opened up the table with the "edit top 200 rows" option, backspaced the TOP 200, and added his conditionals after the FROM and proceeded to execute it. I told him that was his issue there. He told me it should not be. Now I know that this is a "no no" for large data sets, but I lacked the knowledge to explain why.

I was hoping for a little help from the community here. What does the "Edit Top # Rows" option actually do?

Best Answer

The two types of query here use different grids.

You selecting the data (presumably in Query Analyzer) shows a grid that is optimized for display.
Him selecting the data with "edit top xxx rows" displays a grid that is optimized for editing directly in the grid. This gives it more overhead and will make it slower.

Why is `SELECT` slower after `DELETE` completed?

That is because DELETE has only ghosted the rows. The table is just as big as when it had 12M rows, even though it has none. To count the rows (0), it takes just as much time it took to count 12M rows. In time the ghost cleanup process will garbage collect these ghosted records and deallocate pages that contained only ghosts, and your SELECTs will speed up. But right now if you check Skipped Ghosted Records/sec in perfmon is probably skyrocketing during SELECT COUNT(*). You could also speed up things by rebuilding the table: ALTER TABLE ... REBUILD.

TRUNCATE would had also taken care of this problem, as it leaves no ghosts behind.

Sql-server – Optimization: One time vs Staging

The difference between one and two queries may not be as large as you'd think. For example, compare this query:

declare @t table (id int)
insert into @t select id from Table1 where col1 = 7
update Table1 set col2 = 8 where id in (select id from @t)

to this query:

update Table1 set col2 = 8 where id in (select id from Table1 where col1 = 7)

For the second query, SQL Server could run the subquery first, and store the result in a temporary table. If it did that, that would be the EXACT SAME way it executes the first query!

So the effect of splitting a query in two is that it limits the query optimizer's choice. The first query demands that the subquery is executed before the updates. The second query leaves the optimizer free to fetch one row, then update the table, and then fetch the next row.

Now when you upgrade the hardware, SQL Server version, or if the database changes, you would ideally re-verify that your limitation is in fact beneficial. That's kind of expensive.

So I would prefer not split the query, or enforce a query plan, unless the expense is offset by a considerable advantage. Going from 0.07 to 0.3 seconds doesn't look like a big difference to me, unless the query is run more than once every minute or so.

Best Answer

Related Solutions

Sql-server – SQL Server “empty table” is slow after deleting all (12 million) records

Why is SELECT slower after DELETE completed?

Sql-server – Optimization: One time vs Staging

Related Question

Why is `SELECT` slower after `DELETE` completed?