Select query too slow > 5min

I have a tableMyTable with 29,000 rows.

MyTable structure {
  StudentId bigint,
  ....
}

Number of columns > 10 columns开发者_高级运维. The database in the hosting server.

From SSMS i execute the query:

SELECT *
  FROM MyTable

Is it normal that the execution lasts more than 5 min?

First of all, retrieving all the data from a remote database is never a good idea. You are using an important share of bandwidth. Hopefully, the query you are using is only used for debugging purpose and should never hit production.

You did not mention if it took 5 minutes before you started receiving something or if you are receiving your data over the course of that 5 minutes, at a constant rate.

In the first situation, not receiving rows at all might indicating a that a lock is effective on your table, due to another operation.

In the latter situation, you are constantly receiving rows, but at a slow rate. Bandwidth and server load play a big part in that. To get you a rough idea of the amount of data that you are downloading, run this stored procedure:

EXEC sp_spaceused 'YourTableName';

Consider that the server has to upload that data and that you have to download the data.

Binary and xml fields (also called BLOB field) usually take a lot of data and you may not be able to control the amount of data stored by the user in those field.

Try checking the size of your variable length fields (varchar, xml and varbinary) by running a DATALENGTH on your column:

SELECT DATALENGTH(MyField) FROM MyTable

You can also get an average:

SELECT AVG(DATALENGTH(MyField)) FROM MyTable

A good idea concerning BLOB field is to retrieve them only when needer and not when you are loading a list of data.

For example, assume a XML field stored in a PurchaseOrder table. If you wish to display the list of PO to your user, you usually don't need to retrieve that field, unless the user open the PO.

Many recent ORM, like nHibernate, offers lazy loading for columns, along with paging so you can retrieve a small amount of row.

Ayende posted a rent about loading unbounded result set two weeks ago.

You're right - the select query shouldn't take that long. It's not the number of rows. Likely it's the type of data you've got on that table/view, and perhaps the storage configuration (slow disk, filegroups config, etc).

Some ideas to consider to remedy this performance problem:

• be specific in the columns that you want to retrieve. For ad-hoc queries, SELECT * is fine, but recognize that the RDBMS will work slightly harder to determine which columns are on the table/view.
• gathering the values any columns of datatype text, varbinary will take proportionally longer depending on the data within those fields.
• consider the indexes (do you have any?) on the table/view?
• is this a Prod database, where more/other activity might be hitting this table?

If you edit your question, perhaps include the full table definition so that we can get a real look at what's happening with the datatypes.

I would recommend that you consider OMG Ponies's recommendation - it could be due to the bandwidth between the box and your machine, so

• try to remote the box and see how long the query takes on that machine.
• If it takes almost same amount of time, then the problem lies either in the database design or underlying hardware, or other factors (table datatypes, wrong indexes, overall load on the machine, overall hits to this table, etc)
• if it takes significantly less amount of time, then the problem is surely between your machine and the box - ideally this shouldn't be a big problem, because the web server will be closer to the db server, probably on same LAN (so it should be much faster in the real world). Also, I'm sure you wouldn't use a 'Select *' in the actual app to pick 29000 rows, so it shouldn't create a lot of problem.