2

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?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.