select

Question

select

mtz676

SSCrazy Eights

Points: 9212
More actions
July 6, 2009 at 3:19 pm

#207871

I have a table with 2 52 28 272 rows with 8 columns . Its taking 9 minutes to just retrieve the complete table with a simple select * from table. How do I improve the performance here .
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Are you saying your trying to return 25+ million rows? Is there a sort on the query? How wide is each record? How fast is your network connection?

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 2

There is no sort in the query.Just a simple select statement .how do I find out the speed of my network connection ?

David Betteridge Hall of Fame Points: 3264 More actions · Answer 3

Do you need to a select *? Fewer columns might result in a different index being used.

I guess that running the query directly on the server would remove the overhead of any network traffic?

David

LutzM SSC Guru Points: 107049 More actions · Answer 4

What is the "target system" of your 25Mill rows? (flat file, different database/system, maybe even SSMS (???))

What are you trying to do with that rather large amount of data (= what's the business case behind it?)?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

What are you going to do with all columns and all 25 million rows? You can't possibly work with that much data.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 6

I am trying to create a report in visual studio and also using sql server reporting services .

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 7

In that case I'd assume you're doing some kind of grouping with the data.

I'd recommend to do the aggregation on SQL Server side and select the aggregated data into SSRS. You also may use an aggregated select statement rather than a select * ...

AFAIK, a select * from SSRS side actually will copy your 25mill rows from your original database into the SSRS database. I'd assume this will consume most of the 9 minutes you've mentioned.

But without a little more information what you're trying to achieve it's hard to make any more detailed recommendation.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WilliamBendall SSCrazy Points: 2591 More actions · Answer 8

Does it take 9 minutes to select it in management studio, or nine minutes to open the report in SSRS?

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Accessing, sending, receiving and rendering that amount of data (regardless of actual data size of the 8 columns) is going to take a long time. Period. Either accept that time or work to reduce the data volume. It is possible you might be able to improve performance with much faster IO subsystem (do IO stall analysis to determine if this is issue). You could possibly be blocked if not doing read uncommitted select. Maybe you are on 100Mb network and can get a bit there. But still going to take a long time even if you fix every possible performance hindrance you can address.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service