1

I have a table which has 1.7 mil rows in total in SQL Server 2008 R2.

And here is my concern, I need to display all 1.7 mil records in my program. The standard approach I used was to 

select col1, col2, col3,... , col13 from table

However, in the application end (VB.NET), it would takes approx 1 minute to load all the records in DataGridView control.

Somehow, it wouldn't be nice if the user needs to wait for a minute for viewing.

My question here is, is there any approach that I should consider for a faster Select All statement? Eg: configuration, paging, or etc?

P/s: I have did some read up on indexing. If I'm not mistaken, index is more suitable in situation like select for specific record only rite?

Thanks for all advises and help !

Regards, PC

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pekcheng
  • 354
  • 1
  • 5
  • 14
  • 1
    You could use paging (return x rows each time with a way to get the rest) or you could write your code in a way which returns data as and when it is retrieved. See http://stackoverflow.com/questions/5726551/handling-large-sql-select-queries-read-sql-data-in-chunks – andrewb Jul 29 '13 at 02:33
  • 5
    How is a program that displays 1.7M rows useful to anybody? – Joe Jul 29 '13 at 02:37
  • 2
    Let's look at this a different way. When Google says you're viewing 10 of 1,000,000 results, even with paging you're not going through those. The data you're talking about is ***literally intractable*** and of no value. Get a better understanding of how the users might want to *mine* this data and help them on that front. – Mike Perrenoud Jul 29 '13 at 02:51

2 Answers2

1

I would suggest not returning all rows at once. Is someone going to be looking at each row?

A clustered index is faster to read from since all of the data is stored physically in order by the index. Since you are reading every column, make sure a clustered index is defined.

SQL 2008 R2 Clustered Index

newtonrd
  • 2,295
  • 1
  • 12
  • 11
  • While your question is a great one to ask, your statement about the clustered index being faster to read from is ambiguous at best (faster than what?). Having a clustered index is a good idea, but not for the reason you suggest. – Stuart Ainsworth Jul 29 '13 at 04:49
0

You're right that an index won't help you retrieve all 1.7m records in the table faster. Indexes are lookup-oriented data structures that make it faster to find rows based on the rows' attributes - attributes meaning the values of particular columns or expressions computed in terms of the column values. They're usually some type of tree object that makes it quicker to filter rows in the table down to those matching a predicate, with the goal of avoiding a full table scan like the sort your application is doing.

But indexes are only useful when the number of rows to retrieve is significantly smaller than the total number of rows in the table. When you want to show all the rows, they're no help at all.

I'd suggest you reexamine your application requirements. Is it really necessary to retrieve every row on every page load? Do they change that frequently? Could you put some sort of NoSQL cache layer between the database and the application? Memcached could probably speed this up significantly.

I'm also assuming you really do need all 1.7m of these rows every time the application is used. What are you doing with them?

Will Brannon
  • 91
  • 1
  • 2
  • "Indexes are lookup-oriented data structures that make it faster to find rows based on the rows' attributes", that's not true actually. If I had an index on A and B and then selected A and B from Table it would never have to read a data page to get the data. Reading an index page is ***significantly*** more efficient. – Mike Perrenoud Jul 29 '13 at 02:46
  • Hi all, thanks for all feedbacks! <3 to ur concern, it's for some sort of report generation. Transaction logs. – pekcheng Jul 29 '13 at 02:47
  • @TheSolution yeah, true, I didn't say that very well. covering indexes are great. thanks for the correction. – Will Brannon Jul 29 '13 at 02:57
  • @Tpc How many pages takes such a report? Can you to explain the nature of this report? What are you doing with all these rows? Just output them or process in some way? – peterm Jul 29 '13 at 03:01
  • @peterm i need to output them only. no further processing. 1.7mil is for testing purposes. and the report, is some more of history call logs, mainly for documentation :) – pekcheng Jul 29 '13 at 03:30