October 18, 2013 at 4:52 am
Hi, I have one table in SQL server. Table is partitioned in 14 segment and in its latest partition (14th) records are around 40 Billion. I need to fetch the records from that 14th partition, but SQL is talking aorund 1 hour to execute. is there any suggestion on how can I improve my SQL performance ?
SQL which I am using
SELECT column1,column2 FROM
tablename
WHERE $PARTITION.Partition_Function (column_name) = 14
October 18, 2013 at 4:57 am
Are you executing that directly in SSMS?
It's 40 billion (you sure it's not million?) rows, did you expect it to go fast?
If it's in SSMS, return data as text instead of grid, it goes faster.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 18, 2013 at 5:03 am
Hi Koren,
Yes its 401415793 number of rows in that partition. As we are in TELECOM domain that table contain usage records for all subscribers.
I am using SQL Server Managment studio.
October 18, 2013 at 5:07 am
401,415,793 is about 400 million. It's not even close to 40 billion.
Anyway, why would you want to select that amount of rows in SSMS?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 18, 2013 at 5:16 am
Have you got a covering index for this query ? Is this index also partitionned ?
However, it's a lot of data, it will take some time.
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 18, 2013 at 5:17 am
oops 🙂
any ways is there any fastest way to retirval the data from this table.
If I am fetching data for previous day then query is talking around 30 min.
October 18, 2013 at 5:24 am
Yes, that column is also indexed.
October 18, 2013 at 5:27 am
pawan.khandelwal (10/18/2013)
Yes, that column is also indexed.
Are column1 and column2 of your select clause included in the index ? If not, this is not a covering index.
Hope it helps.
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 18, 2013 at 5:37 am
ok..can you just give me rough estimate how much time SQL to take to fetch 20 million records
October 18, 2013 at 5:46 am
pawan.khandelwal (10/18/2013)
ok..can you just give me rough estimate how much time SQL to take to fetch 20 million records
How can someone give an estimate on this?
It depends on too many factors: network, I/O, server hardware, fragmentation, indexing, row size, ...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 18, 2013 at 8:25 am
pawan.khandelwal (10/18/2013)
ok..can you just give me rough estimate how much time SQL to take to fetch 20 million records
What is it that you're doing with those "records"? If you're trying to display them to the screen, I have to as "WHY"? It's certainly not like someone can read them all.
Like I said, what is it that you're actually trying to do with the "records"?
--Jeff Moden
October 18, 2013 at 8:31 am
Why are you retrieving hundreds of millions of rows? What are you going to do with them?
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
October 18, 2013 at 8:37 am
Just a wild guess but...
If are you returning 1k bytes per row for 40G rows...
it can be a network bottleneck check how much time your bandwidth needs to handle that chunk of data.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply