none
can indexed view be used in this situation.

    Question

  • Dear All

    create table Pole (Poleid int,Position decimal(5,2))  

    create table Distance (Did int,FromPosition decimal(5,2), ToPosition decimal(5,2),ReprotNo varchar(10)
    ,DateOfReport datetime,Activity int)



    Distance table brings data from few tables , it keeps reports of actities of poles

    insert into Distance (FromPosition,ToPosition,ReprotNo,DateOfReport,Activity)

    select FromPosition,ToPosition,ReprotNo,DateOfReport,a.Activity
    from a
    join b on a.aid=b.aid and b.cat=0
    union
    select b.f as FromPosition,b.t as ToPosition,ReprotNo,DateOfReport,a.Activity
    from a
    join b1 b on a.aid=b.aid and b.cat=1



    Then i have to create following table PoleReport to get the latest report of a poleid from distance table
    create  PoleReport( PoleId int,ReprotNo varchar(500))

    insert into PoleReport (Poleid , ReprotNo)

    SELECT P.Poleid , D.ReprotNo

    FROM

    Pole P

    JOIN

    Distance D ON P.Position BETWEEN D.FromPosition AND D.ToPosition

    WHERE

    D.Did = ( SELECT TOP 1 D1.Did FROM 

    Pole P1

    JOIN Distance D1 ON P.Position BETWEEN D1.FromPosition AND D1.ToPosition

    ORDER BY D1.DateOfReport )

    Q1) Please tel me is it possible that the PoleReport gets created auto matically using indexed view as an when entry is done .
    Because above query takes good time, so i have to run it at afternoon or at night, when no body is seeing reports.

    yours sincerely

    Sunday, March 23, 2014 6:27 AM

Answers

  • There are lot of restrictions of what constructs you can use in an indexed view. I don't have all these restrictions in my head, but I don't think your query could qualify. I believe that subqueries are not permitted.

    Generally, indexed views creates quite some overhead for updates, since the conditions has to be reevaluated.

    However, I think your query can be improved:

    WITH numbering AS (
       SELECT P.Poleid , D.ReportNo,
              row_number() OVER() (PARTITION BY D.Did ORDER BY D.DateOfReport) AS rn
       FROM   Pole P
       JOIN   Distance D ON P.Position BETWEEN D.FromPosition AND D.ToPosition
    )
    SELECT PoleID, ReportNo
    FROM   numbering
    WHERE  rn = 1

    I'm not 100% sure that it is eqvuivalent, but you could give it a try.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 23, 2014 10:57 AM

All replies

  • Generally I use an aggregation function in the indexed view...Perhaps in your query you need  to have properly defined indexes to speed up the query?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 23, 2014 8:24 AM
    Answerer
  • If you want table to be created automatically why not create a job in sql server agent which will call a t-sql stored procedure to populate the PoleReport table based on your schedule. Then you can add the required indexes after population to speed up the queries. For daily loads drop indexes first, do data load and recreate indexes again if data volume is huge.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, March 23, 2014 8:40 AM
  • There are lot of restrictions of what constructs you can use in an indexed view. I don't have all these restrictions in my head, but I don't think your query could qualify. I believe that subqueries are not permitted.

    Generally, indexed views creates quite some overhead for updates, since the conditions has to be reevaluated.

    However, I think your query can be improved:

    WITH numbering AS (
       SELECT P.Poleid , D.ReportNo,
              row_number() OVER() (PARTITION BY D.Did ORDER BY D.DateOfReport) AS rn
       FROM   Pole P
       JOIN   Distance D ON P.Position BETWEEN D.FromPosition AND D.ToPosition
    )
    SELECT PoleID, ReportNo
    FROM   numbering
    WHERE  rn = 1

    I'm not 100% sure that it is eqvuivalent, but you could give it a try.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 23, 2014 10:57 AM