Taking long time for a single table execution

Question

Taking long time for a single table execution

ekkaldevi.naveen

SSC Veteran

Points: 206
More actions
July 22, 2014 at 4:58 am

#397093

Hi,
I am having a table "Test" with 70 columns. Out of them a composite primary key is defined with 12 columns.
There are around 4 million records in that table, when I am trying to retrieve them its taking more than 15 minutes.
When verified the execution plan, it is having the below details
EstimatedExecutionMode=Row
Estimated Operation Cost = 107.19 (100%)
Estimated I/O Cost = 102.831
Estimated CPU Cost = 4.3598
Estimated Subtree Cost = 107.19
Estimated number of executions = 1
Estimated no of rows = 39633220
Estimated row size= 5352 B
Can you please let me know how to make it execute fast
Regards
NAveen

Viewing 14 posts - 1 through 13 (of 13 total)

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

Neil Burton SSC-Insane Points: 22905 More actions · Answer 1

Hi Naveen, welcome to the forum.

To help we're going to need a little bit more detail. When you say you're trying to retrieve them, what do you mean? Do you have a query or a proc that you use to return the rows? What's your system set up?

I'm not sure how much help I can be personally but there are some very big hitters on here that know SQL Server inside out. However, they aren't mind readers and they'll be able to provide possible answers much more easily with more information. A few more details may go a long way.

Neil

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ekkaldevi.naveen SSC Veteran Points: 206 More actions · Answer 2

Hi Neil,

Thanks for your reply

I am using SQL Server 2012, and when I am trying to query the table directly as

select * from test

also I tried by giving column names instead of * but it has helped a little not that much.

Simply to say there is a table Test with nearly 4 million records and each row size is 5500 Bytes.

Please let me know do you need any further details.

Regards

Naveen

Neil Burton SSC-Insane Points: 22905 More actions · Answer 3

Do you know anything about your hardware set up? With the best will in the world, it's going to take a lot longer to run on a 386 than a powerful server.

As I said, I can't help much myself, performance problems aren't my thing. I'm just trying to make it as easy as possible for you to get an answer from one of the big guns 🙂

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Table definitions, index definitions, query and execution plan please.

If it's just a SELECT * FROM Table, why? Why would you return every single row and column to the application? What is anyone going to do with 4 million row? That's 20 GB of data you're fetching there. Surely the app doesn't need every bit of that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ekkaldevi.naveen SSC Veteran Points: 206 More actions · Answer 5

Hi,

Already I posed the execution plan. There is only one table with 70 columns and only 1 primary key (with 12 columns). Apart from that there is no other index on that table

Regards

Naveen

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

ekkaldevi.naveen (7/22/2014)
Hi,
Already I posed the execution plan. There is only one table with 70 columns and only 1 primary key (with 12 columns). Apart from that there is no other index on that table
Regards
Naveen

Do you need all 70 columns? Database engine has to retrieve all 70 columns data. You should retrieve only required columns so IO will be fast compare to all 70 columns data retrieval from Disk.

Thanks

ekkaldevi.naveen SSC Veteran Points: 206 More actions · Answer 7

even when I tried to retrieve 5 columns still taking more than 10 min

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

ekkaldevi.naveen (7/22/2014)
Hi,
I am having a table "Test" with 70 columns. Out of them a composite primary key is defined with 12 columns.
There are around 4 million records in that table, when I am trying to retrieve them its taking more than 15 minutes.
When verified the execution plan, it is having the below details
EstimatedExecutionMode=Row
Estimated Operation Cost = 107.19 (100%)
Estimated I/O Cost = 102.831
Estimated CPU Cost = 4.3598
Estimated Subtree Cost = 107.19
Estimated number of executions = 1
Estimated no of rows = 39633220
Estimated row size= 5352 B
Can you please let me know how to make it execute fast
Regards
NAveen

Sure.

1. Reduce the number of rows returned. 1 is better than 4000000

2. Reduce the number of columns returned. 1 is better than 70.

If it still takes 15 minutes to return 1 value then you know for sure that something is wrong. As it stands, 15 minutes could be perfectly acceptable depending on the hardware hosting your server.

If you really do need to move all that data from one place to another, then you might wish to review your options.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 9

Could the problem here be the 12 column compound key? We got no table definition to look at, but that seems awful wide. One way to test that would be to go

Select * into newtable from oldtable

and then drop the primary index on newtable and try the query

select <blah> from newtable

and see if it's any faster.

Also, your statistics say 40 million rows instead of 4 million, maybe you need to update statistics?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

My question still stands. Why are you retrieving all 4 million rows and sending them to an application. Is there no filtering or aggregating that can be done in the database? What is a user going to do with 4 million rows?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 11

GilaMonster (7/22/2014)
My question still stands. Why are you retrieving all 4 million rows and sending them to an application. Is there no filtering or aggregating that can be done in the database? What is a user going to do with 4 million rows?

There you go again, acting like the things we're asked to do always make sense 😛

But even if it WAS a requirement, for instance a data transfer program in C# needs this data for some reason, you're right it would be better to use analytics to chunk the data in threadpools, so it could be sending and receiving at the same time and not have to wait to get all the rows before sending them.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 12

Are you measuring the time to retrieve the 4 million rows? or are you including the time to show them?

What are you expecting to get? if you're querying the full table, you need to get the full table. Indexes won't help, statistics won't help either, it's a plain simple table scan (or am I wrong?).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 13

Luis Cazares (7/22/2014)
Are you measuring the time to retrieve the 4 million rows? or are you including the time to show them?
What are you expecting to get?

+1

What should be expected response time? Your application need all 4 million rows for next step, really?

Thanks