Performance issue on simple SELECT?

I have a database hosted on SQL 2005 and am having an interesting performance problem.

I have a table with a little over 10,0000 records in it. This table is basically a flattened, de-normalized view of several tables. Creation of this table on the server side takes 3 to 6 seconds.

However, selecting from this table with no joins or where clauses (SELECT * FROM tableName) takes over 5 minutes to return the data. If I convert the query to a count query it returns right away.

I was told by support to index and optimize the database but so far it seems to be more of a bandwidth issue in returning the data. The query I'm executing doesn't need indexes.

I can also execute a Count() on a more complex query with multiple joins and get a fast response. However, if the same query then returns around 10K rows of data it takes and excessive amount of time.

Also we have noticed that in SQL Server Management Studio that it can take a considerable amount of time to just expand out the Tables node.

This time also seems to occur between the Web Server and the Database server which rules out external network/Internet influences.

 

Here are some statistics my DBA just ran:

Code:

SET STATISTICS TIME ON;
GO

SET STATISTICS IO ON;
GO

SELECT TOP 1000 * FROM dbo.SiteSearch

 

Thanks for the response.

With the nolock on I got 46 seconds on 1000 rows executing the same query as above.

We're on sql2k505 if that helps any.

 

Here is the same query with a count

Code:

SELECT Count(*) FROM dbo.SiteSearch

 

Our DBA thinks the server is either low on memory resources or the network bandwidth on the server is strained. He suggested running perfmon but I assume you guys would have an automatic eye on that already.

 

I figured you had automatic monitoring in place.

Was something just done? Our manager just ran out to say the CMS system, which is in a sepparate DB on the same server, was much faster now than it has been since the start of the project.

The execution time has been cut in half (25 seconds) on the query I posted above. However, that is still quite long for 1000 rows.

I'll try the MyLittleAdmin interface.

 

Made some interesting observations. Using named columns didn't cause a performance difference but reducing the number of columns in half reduced the time factor by about 6x ( to 4 seconds). I would have expected a more linear response.

However, on our SQL servers there is no significant difference in time when experimenting with the row size on the same query.

I'll try a ticket in the AM but was advised to take the issue to the forum after creating a ticket.

 

Oh, as for why so many records, we don't normally do that. Our application is basically a tower/cellular site search application.

Typically the app will page the data at 50 records a time as requested by the end user. There are also geographic searches that require a distance calculation in the software that might be over a slightly large group but those can't be paged.

However, there is also an export to Excel option in which all records of a search could be returned. So the end user might need to export a list of all of our towers within a particular city or geographic location.

It's not the scanning of the data that seems to be a problem. The servers do an excellent job at that. It's returning the data that seems a bit slow.