Re: Index View
From: Eric Sabine (mopar41_at_hyotyt_mail_nounderscores.com)
Date: 08/05/04
- Next message: Eric Sabine: "Re: Dropping Indexes on SQL Server"
- Previous message: Aaron [SQL Server MVP]: "Re: char yymmdd get to mm/dd/yyyy"
- In reply to: Prabhat: "Re: Index View"
- Next in thread: Hugo Kornelis: "Re: Index View"
- Messages sorted by: [ date ] [ thread ]
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)
- Next message: Eric Sabine: "Re: Dropping Indexes on SQL Server"
- Previous message: Aaron [SQL Server MVP]: "Re: char yymmdd get to mm/dd/yyyy"
- In reply to: Prabhat: "Re: Index View"
- Next in thread: Hugo Kornelis: "Re: Index View"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
