how to speed retrieving data from big table data when select from it?

Question

how to speed retrieving data from big table data when select from it?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
December 16, 2018 at 4:31 pm

#373248

Problem
I have table have 20 columns when make select data from new SQL query computer hangs ?
what suggestion to make quickly read data from this table and make performance good ?
What I Try
==========
select * from table where 1=1 take 45 minutes
after minimize column number as
select column 1,column 2,column 3 from table also hangs but take less time as 42 minutes .
when make select data from tables not other programs open only SQL server opened new query.
table have 1 million records .
Computer Capability
==============
i work on SQL server 2012.
select data from this table hangs computer although my computer capability not bad
ram 8 GIGA and processor core I 5 .
I try same Backup of data on another computer it take too much time as above ?
==============
if possible what suggestions to select data quickly from table and best performance ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

pietlinden SSC Guru Points: 63434 More actions · Answer 1

try
- indexing the fields you're filtering on.
- limiting the columns returned
- add RAM, like to 32GB. 8GB for SQL Server is just painful.

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

December 16, 2018 at 6:27 pm

#2016435

how to indexes field i selecting

pietlinden SSC Guru Points: 63434 More actions · Answer 3

There are a bunch of stored procedures that are executed against your tables, right? Maybe use Querystore or something to figure out which queries are being executed most frequently. Look at those and index to make the most important ones run faster. I think Gail Shaw has a good article on it.

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

ahmed_elbarbary.2010 - Sunday, December 16, 2018 4:31 PM
Problem
I have table have 20 columns when make select data from new SQL query computer hangs ?
what suggestion to make quickly read data from this table and make performance good ?
What I Try
==========
select * from table where 1=1 take 45 minutes
after minimize column number as
select column 1,column 2,column 3 from table also hangs but take less time as 42 minutes .
when make select data from tables not other programs open only SQL server opened new query.
table have 1 million records .
Computer Capability
==============
i work on SQL server 2012.
select data from this table hangs computer although my computer capability not bad
ram 8 GIGA and processor core I 5 .
I try same Backup of data on another computer it take too much time as above ?
==============
if possible what suggestions to select data quickly from table and best performance ?

Both of your queries are attempting to return all the rows of data. A WHERE clause with 1 = 1 is the same as no WHERE clause at all. You want to speed up the query, reduce the number of rows you are trying to return.

frederico_fonseca SSCoach Points: 16030 More actions · Answer 5

if you are trying to retrieve onto SSMS it will always take a long time for that volume. SSMS is not made for that purpose.

how long does it take to extract the same data using BCP out?

Grant Fritchey SSC Guru Points: 398711 More actions · Answer 6

You have an 8gb server (half my laptop by the way) and a query that moves ALL the data. How big is this database? If you're moving all the data all the time (a horrible design by the way), the only way to speed things up is to buy more and bigger hardware. Otherwise, as has been stated over and over, filtering the data is the standard way to move data to an application or report (in most cases, not in all).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 7

Indexes aren't going to help much here. The OP is trying to return most of the table to the screen and there are a million rows in the table. What needs to happen is for someone to figure out why returning a million rows to the screen is important. I suspect it's not important at all.

As for the backups, the system being used only has 8GB and we don't know if any of that has been allocated to the operating system, as it should be. We also don't know what kind of disk the backup is being made to. Since the cores are I-5's, this sounds like someone is expecting server level performance from a laptop.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)