?
Solved

SQL 2008 R2 Views

Posted on 2013-10-25
6
Medium Priority
?
456 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 23

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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 99 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 99 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 49

Accepted Solution

by:
PortletPaul earned 102 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

749 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