Solved

SQL 2008 R2 Views

Posted on 2013-10-25
6
445 Views
Last Modified: 2013-11-04
If I have a table with 25 fields, 350,000 records and i only want to process the records that have a 5 in the field called Number, what is best to do:

Build a view over the table only selecting the 5 records  ?
or
Build a index over table (indexing Number field) ?  
or
Something else...

Other reports use the table and need to see the 5 records.
0
Comment
Question by:thayduck
6 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39600744
An index is probably your best bet.

If you create a view, it won't make the select any better - it would perform the query using the same access path to the base table (providing there were no other filtering predicates or what not).

If you want to make the reporting easier so they don't always have to code WHERE NUMBER = 5, then you could present them with a view just showing that data.

But the index would seem to be the most efficient way of getting to the data based on what you've just told us.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 33 total points
ID: 39600746
Define 'process'.  Does this mean just select those rows, or edit them in some fashion?

An index isn't going to work when there's an expression such as LEFT(Number, 5).

>Build a view over the table only selecting the 5 records  ?
This works.  Also works would be a stored proc that does this.

Also, is the 'Number' field numeric or text?
0
 

Author Comment

by:thayduck
ID: 39600822
Lets just say the field is  5 (Char).

The field contains either the word YES or NO.

I only want to process the YES records in my SSRS report

Right now in my stored procedure I compare this field to a report parm.

They can either choose YES or NO.

I figured a view only selecting the YES records would speed things up.

Since there are only 100,000 YES records and 250,000 NO records I do not want to have to process 350,000 records to get the 100,000 YES records..
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 33 total points
ID: 39600847
If you're just comparing text and not using any functions on the left of the where (which invalidates use of an index anyway), run a couple of tests and check your execution plans for each.

Run the query with no index, compare execution time and search method
Run the query with an index, compare execution time and search method.

It's possible, based upon the way the data sits in the table that the optimizer may decide that it is more efficient to scan the entire table anyway, if the distribution of your YES/NO's are scattered all through the table.

But you'll need to look at the plans to see what it's going to do.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 34 total points
ID: 39604840
>>I figured a view only selecting the YES records would speed things up.

By itself, probably not. A view is a stored query, and while they don't need to be re-evaluated by the optimizer to determine an execution plan, they still perform close to the same work as an interactive query, e.g.

-----------------
create my_view as select * from x where field_y = 'YES'; -- scans 350k rows : 100k output

select
*
from my_view; -- 100k rows to process

----------------- is very similar to:

select
*
from (
         select *
         where field_y = 'YES'; -- scans 350k rows : 100k output
        ) as my_nested_query -- 100k rows to process

-----------------

i.e not a lot of difference but there might be a small gain.
Whereas an index can make a difference to "how" the 350k records get scanned
(but it can't be guaranteed as noted by comments made previously).

Is this YES/NO field the only thing that you need to filter by?


btw: this is NOT a criticism of views in general! They can be extremely useful.
0
 

Author Comment

by:thayduck
ID: 39605370
I was just using the Yes or No field as an example.

From every ones comments, a view in this situation will not gain me much.

I do have index's built over the main fields I am selecting on, so I suppose that will be sufficient enough.

I will close this question and award points to all that have helped unless there is more input.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question