Solved

SQL 2008 R2 Views

Posted on 2013-10-25
6
441 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now