Re: Index View

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/05/04 

Date: Thu, 05 Aug 2004 17:45:55 +0200

On Thu, 5 Aug 2004 19:52:02 +0530, Prabhat wrote:

>Hi All,
>
>I Have 2 Large TABLES and A View On which retrives records from Both the
>Tables Like:
>
>CREATE VIEW REAPPEALS_RECORDS
>AS
>SELECT
> A.Col1, A.Col2,
> B.Col1, B.Col2
> FROM Tabl1 A INNER JOIN Tabl2 B ON A.ID = B.ID
> WHERE B.Col5 = 'FLCC'
> AND B.Col6 < GETDATE()
> AND B.Col7 = 1
> AND (B.Col8 > 0 AND B.Col8 < 3)
> AND B.Col9 IN ('E', 'V')
>GO
>
>This takes around 15 - 20 sec. to retrive Data (Around 2000 rows.)
>How Can I Use / Convert this View to Index View so That I can Solve my
>performance problem.
>
>I am Using SQL Server 2000 Proff. Is Index View is Supported in This Ver? or
>Only I have to Use Ent. Ed to Use this feature. I also have Ent. Ed
>Installer in Different PC.
>
>Please Help.
>
>Thanks
>Prabhat
>

Hi Prabhat,

Indexed views are supported by SQL Server 2000. But I'm afraid that you
can't use this for your query. Views may only be indexed if they meet a
lot of requirements; one of these requirements is (quote from BOL): 

---
All functions referenced by expressions in the view must be deterministic.
The IsDeterministic property of the OBJECTPROPERTY function reports if a
user-defined function is deterministic. For more information, see
Deterministic and Nondeterministic Functions.
---
Since GETDATE() is a deterministic function, the phrase "AND B.Col6 <
GETDATE()" prohibits this view from being indexed.
You could consider adding B.Col6 to the output of the view, removing the
test on B.Col6 versus GETDATE() from the view definition and adding that
when you select from the view. In that case, you can make this view
indexed; check out the information in Books Online on indexed views for
details (like using two-part naming etc.)
But another thing you might consider is trying to enhance the performance
of the existing query. How many rows are there in Tabl1 and Tabl2? What is
the DDL of these tables? What indexes do you have defined?
If you run SELECT * FROM REAPPEALS_RECORDS in QA with the option to show
execution plan on (Ctrl-K), what indexes get used by SQL Server?
Best, Hugo
-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Index View
    ... >>performance problem. ... I also have Ent. ... > GETDATE()" prohibits this view from being indexed. ... what indexes get used by SQL Server? ...
    (microsoft.public.sqlserver.programming)
  • Re: Simple question about: GETDATE() , DATEADD etc.
    ... Columnist, SQL Server Professional ... I have easy question. ... In my query I want get all rows from last 7 days. ... Select * from myTable where myDateTime> DATEADD(day,-7, GETDATE()) then DATEADD) return full date with hour, minute etc. ...
    (microsoft.public.sqlserver.programming)
  • Re: Frontpage Query
    ... That wordage "near '>'" is MS SQL Server error type verbiage. ... getdate() function in place of the VBA/JET function Date. ... > I am trying to query a database and have it return a list ... > I keep getting a syntax error. ...
    (microsoft.public.access.queries)
  • Re: SQL date compare problem
    ... >> If you want to check today date why don't you use getdateof sql server? ... > Becuase I didn't know it existed. ... I saw references to getdate() ... > directly in the query. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Better way to sum 0 entries?
    ... Columnist, SQL Server Professional ... I want to have a query that gives me a list of the count of all books in all ... libraries, *INCLUDING* those libraries that have no books. ... INSERT INTO Library VALUES (1, 'West', 'West branch', GETDATE()) ...
    (microsoft.public.sqlserver.programming)