Solved

SQL 2008 R2 Views

Posted on 2013-10-25
6
446 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 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