0

So for a set of tables I am trying to get personIds for people who are students which is a slow query (~30 seconds) so in an attempt to speed things up I am trying to make a view out of this.

This question is about the view. I have a view with 1 int column and ~1,000,000 rows.

A simple: SELECT [PersonId] FROM [yoda_lat].[dbo].[person_view]

still takes ~ 25 seconds to complete. I am going to suggesting caching the result of this to the people developing for this database but am wondering what else I can do to speed this up.

The query the view is built from shouldn't matter once the view is created yes? I see stuff about adding indexes for more complicated selects, but I am literally trying to grab every value for this single column which is the only column of the view.

lathomas64
  • 1,612
  • 5
  • 21
  • 47
  • Does your base table having any indexes? Even a clustered one on the PK? If not, this table will be scanned on every query. Even if it is just one column, that is a million rows that has to be scanned. – TTeeple Sep 15 '15 at 15:37
  • You could apply an index on the table, if you know what columns are being utilised a lot then this would speed up the data retrieval. However, this slows down inserts or updates to your table. So before implementing one you should weigh up the differences to see if it's worth it. http://stackoverflow.com/questions/2955459/what-is-an-index-in-sql – Dane Sep 15 '15 at 15:37
  • For such a simple query an index is not going to help, as you are doing a table scan (i.e. reading every record). Creating a view certainl won't help. Do you really need to retrieve every record? – The Dumb Radish Sep 15 '15 at 15:39
  • "In an attempt to speed things up I am trying to make a view out of this." Normally, views are just code. Putting a query in a view will not make that query faster, any more than moving a loop to a sub procedure will make loop run faster. There are materialized views which store (cache) the results of the view to speed things up. In SQL Server materialized views are called indexed views. – Shannon Severance Sep 15 '15 at 15:58
  • @TheDumbRadish I am a bit confused on your initial statement. Scans are good on small sets of data, seeks are good on large sets of data. It doesn't matter the complexity of your query, only the size of the data being returned. Whether the query returns 1 column or 20, an index on a million records will almost always be faster than scanning the table. – TTeeple Sep 15 '15 at 16:56
  • @TTeepleyour query is SELECT PersonId FROM View. This will read every record returned by the view. So we need to see your view to determine whether an index will be helpful.If there is no filtering in the view then no index will help, a scan will always be performed. The number of columns is irrelevant. Can you post the definition of the view, and we can go from there. Thanks. – The Dumb Radish Sep 16 '15 at 06:38
  • I misunderstood views it looks like I thought the view would avoid having to hit the original tables in the first place. – lathomas64 Sep 21 '15 at 17:10

1 Answers1

1

Executing a SELECT which will return 1 million rows is not meaningful unless you copy data from your table

SELECT without WHERE clause will read all data pages of your database table. This is FULLSCAN and we try to avoid this type of execution

Practically you need to filter your data according to some criteria. You can create an index on this table fields. This will boost your query performance.

Eralper
  • 6,461
  • 2
  • 21
  • 27