Re: Index View

From: Eric Sabine (mopar41_at_hyotyt_mail_nounderscores.com)
Date: 08/05/04 

Date: Thu, 5 Aug 2004 15:13:22 -0400

Your impending indexed view doesn't take any advantages of grouped
operations, such as sum() and count_big(), so I don't see why pursuing an
indexed view is going to help. Have you checked the execution plan of the
query itself? You might be able to improve performance by adding a new
index or perhaps by updating stats.

Start with the execution plan.

hth,
Eric

Prabhat wrote:
> Hi Hugo,
>
> Thanks for the reply.
>
> If I Remove the B.Col6 < GetDate() and Put the Col6 as OutPut Column
> then I Can Use the Index On View. So If I Do that Can U Please Help
> me out to to Create Index on the View and Also Can I Call the View
> Like :
>
> Select * from REAPPEALS_RECORDS where Col6 < GetDate()
>
> If Yes. Please Give me the Hint How Can I Proceed?
>
> Thanks
> Prabhat
>
> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
> news:q3l4h0lqrmpg0dnp6toae4a69gsdu9dj4j@4ax.com...
>> 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: Which is FASTER?
    ... In order to determine which one is faster i'd run set statistics io on as ... well as take a look at execution plan of the query ... > If I have QUERY like this then which will be faster? ...
    (microsoft.public.sqlserver.programming)
  • Re: Views
    ... discarded by examining the query plan. ... Thank you for using the Microsoft MSDN Managed Newsgroup. ... Based on the above the execution plan of the view query should be as same ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.clients)
  • Re: Whats the deal with PAGEIOLATCH_SH?
    ... Does SQL Server have trouble when a single table is this size? ... About 20 minutes into the query everything goes bad. ... >> go over some threshold, which seems to vary, the disk queue length ... >> I'll work on getting the real execution plan, ...
    (microsoft.public.sqlserver.programming)
  • Re: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... Hugo Kornelis wrote: ... >>And then for a 10 million record database. ... >>this query would run, please. ... I don't have the money to spare on the kind of hardware that would be ...
    (comp.object)
  • Re: Index chosen is wrong index
    ... constraints/indexes, do you have a clustered index (if so, what is its ... and the union can affect the query plan. ... > The execution plan for the query below selects the wrong ... > SDDRQJ, SDTRDJ, SDPDDJ, SDOPDJ, ...
    (microsoft.public.sqlserver.server)