-1

I Have made a summerized view which i need it to cross with another view, both views needs to be indexed by 2 fields (number one field is date) so that i could improve performance on response time. When i try to do it i get the following message:

-- CREATING VIEW FOR VENTAS POR DISTRIBUIDOR, DEPARTAMENTO
create view [dbo].[vw_ventas_dist_dep] as 
SELECT CAST(t.fecha AS DATE)fecha
,d.NombreDistribuidor distribuidor
,u.Nombre departamento
,sum(tr.monto) monto
 from HechosTransferencia tr inner join DimensionTiempo t on tr.DimensionTiempoId=t.DimensionTiempoId
inner join DimensionDistribuidor d on d.DimensionDistribuidorId=tr.DimensionDistribuidorId
  inner join DimUbigeo u on u.DimDepartamentoId=tr.DimDepartamentoId and u.DimProvinciaId='' and u.DimDistritoId=''
WHERE        (t.Fecha BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, - 3, GETDATE()) - 3, 0) AND GETDATE())
 -- and t.fecha between  '2016-12-19' and '2016-12-20'  and d.NombreDistribuidor='auren S.A.'
 and tr.TipoDestino='PDV' and tr.TipoOperacion='Transferencia'
group by CAST(t.fecha AS DATE),d.NombreDistribuidor,u.Nombre ;
GO

CREATE UNIQUE CLUSTERED INDEX PK_fecha ON vw_ventas
(
    fecha,  -- date field
    distribuidor -- string field
);

Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'vw_ventas' because the view is not schema bound.

I have tried placing WITH SCHEMABINDING but dont get a way to make the indexed view.

Luis Cabezudo
  • 35
  • 1
  • 6
  • 1
    (1) Why do you think an index on the view will improve performance? Does the view perform any aggregations of any kind? The *primary* advantage of indexing a view is performing aggregates by reading and returning a very small number of rows compared to performing the same calculations against the base table. (2) The error message seems clear to me (add WITH SCHEMABINDING to the view), but I doubt this will be the end of your problems, or that when you getthe index created, it will magically make things faster. – Aaron Bertrand Jan 10 '17 at 20:52
  • Hi Aaron, because i need to cross two views by some fields and its taking arround 1min, i think indexing the fields i need will improve performance for this summerized view. – Luis Cabezudo Jan 10 '17 at 20:53
  • 2
    @Luis So start by adding appropriate indexes to the base tables. An indexed view is not a magic hammer. [A little more detail here](https://blogs.sentryone.com/aaronbertrand/bad-habits-wrong-optimizations/#opt5). – Aaron Bertrand Jan 10 '17 at 20:54
  • I did so, my fields are indexed too – Luis Cabezudo Jan 10 '17 at 20:55
  • Just because a column is indexed does not mean it will benefit your query. What kind of investigation have you done into why your original query takes a minute? What did you find that led you to believe an indexed view is the answer? – Aaron Bertrand Jan 10 '17 at 20:56
  • 1 minutes because i have millions of rows and i join more than 3 tables for that reason i decided to have a view with the last 2 months because i have data from 2015 and its too heavy. I thouhght having a summerized table and then joining with others view will improve my output time but with index will be more than that – Luis Cabezudo Jan 10 '17 at 21:00
  • Well, you can't create an index on a view that uses functions like `GETDATE()` in the where clause. The whole point of an indexed view is that it materializes a second copy of data from a base table, and it maintains this view at the time a row is modified. How can it enforce inclusion/exclusion of rows based on `GETDATE()`? Should it re-evaluate the materialized view every 3 microseconds? – Aaron Bertrand Jan 10 '17 at 21:04
  • P.S. Feel free to try, but you'll get `Msg 1949, Level 16, State 1 / Cannot create index on view 'viewname'. The function 'getdate' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.` – Aaron Bertrand Jan 10 '17 at 21:05
  • Once again, I suggest you look into real query / index performance tuning, rather than trying to rubber-stamp it with an indexed view that has no chance to work. – Aaron Bertrand Jan 10 '17 at 21:05

1 Answers1

0

You need to use WITH SCHEMABINDING in the definition of your view.

ALTER VIEW vw_ventas WITH SCHEMABINDING
AS
SELECT ...
FROM ...
WHERE ... ;

Check the documentation for CREATE VIEW here. But, depending on the operations you do in the view, the index would mean an improvement to your performance or not.

Most times, indexing the base tables will result in better improvements and an additional index on your table might help other queries too.

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • I got the following mistake placing it on the place you told me: Cannot schema bind view 'dbo.vw_ventas_dist_dep2' because name 'HechosTransferencia' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself. – Luis Cabezudo Jan 10 '17 at 21:02
  • How so Radu, what must i have to correct? – Luis Cabezudo Jan 10 '17 at 21:17
  • 1
    @Luis I told you exactly how to fix that error message. If you add schema prefixes to all the tables in the view, that error message will go away. Then, if the column `monto` is nullable, the next error message you get will be `Msg 8662, Level 16, State 0 / Cannot create the clustered index "foo" on view "bar" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.` Then when you fix that, you'll get another one. And so your day will continue... – Aaron Bertrand Jan 10 '17 at 21:21