0

I had a long running query (relative to the data) that was hitting an indexed view. I thought an indexed view was physically stored data? I dumped everything into a table and ran the same query against the table and found that it executed almost 3x as fast. I was under the impression, which apparently was wrong, that an indexed view would act the same as a physical table in terms of performance. Just looking for an explanation.

S3S
  • 24,809
  • 5
  • 26
  • 45
JMG
  • 55
  • 7
  • 4
    What edition of SQL Server are you running under? Downlevel editions require the use of the `NOEXPAND` query hint in order to benefit most from the indexed view. – Damien_The_Unbeliever Nov 16 '18 at 18:57
  • Microsoft SQL Server Management Studio 13.0.16106.4 – JMG Nov 16 '18 at 19:00
  • 2
    SQL Server Management Studio version doesn't usually matter. run `select @@version` on your system and see if it matches what you posted, which would be sql server 2016 – S3S Nov 16 '18 at 19:02
  • Microsoft SQL Server 2016 (SP1-GDR) (KB4057118) - 13.0.4210.6 (X64) Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 – JMG Nov 16 '18 at 19:06
  • Tested out WITH (NOEXPAND) - nailed it. Thanks guys! – JMG Nov 16 '18 at 19:13

1 Answers1

0

What edition of SQL Server are you running under? Downlevel editions require the use of the NOEXPAND query hint in order to benefit most from the indexed view. – Damien_The_Unbeliever

This was the answer, thanks again

JMG
  • 55
  • 7