Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
SQL Query on Enterprise Edition slowed down using the VSIFT indexed view.
CraigNeedham
Member Posts: 3
Dear Experts
I have a very simple Query that selects records from a table where the timestamp (cast as a BigInt) is greater than a previously stored BigInt. It runs on SQL 2012 EE used to be quite fast (~100ms) and overnight slowed down (~5 Seconds). I looked at the execution plan and noticed it is using an Indexed View. I am not sure if it has always used the indexed view, but I suspect it swapped to using the indexed view and was using the primary key before. When I restructure the Query by swapping the cast, the query behaves differently on this server and uses the Primary Key and is quite fast.
There has been no Server configuration changes, Hardware or software changes and the data in the database is changing constantly.
My Question is: In what circumstances can SQL swap to using the Indexed View on EE when in fact the Primary key executes the query faster? The Query is very straight-forward:
SELECT DISTINCT cast(tab.[Document Type] as varchar)+','+tab.[Document No] AS RecID FROM [MyTableName] AS tab WHERE tab.[timestamp] > cast(cast(918273645 as bigint) as timestamp) AND (tab.[Document Type]=1)
I would really appreciate your insights into the inner workings of SQL Server 2012 Enterprise Edition, why would it all of a sudden execute the same code significantly slower than the day before...
Kind regards and thank you.
I have a very simple Query that selects records from a table where the timestamp (cast as a BigInt) is greater than a previously stored BigInt. It runs on SQL 2012 EE used to be quite fast (~100ms) and overnight slowed down (~5 Seconds). I looked at the execution plan and noticed it is using an Indexed View. I am not sure if it has always used the indexed view, but I suspect it swapped to using the indexed view and was using the primary key before. When I restructure the Query by swapping the cast, the query behaves differently on this server and uses the Primary Key and is quite fast.
There has been no Server configuration changes, Hardware or software changes and the data in the database is changing constantly.
My Question is: In what circumstances can SQL swap to using the Indexed View on EE when in fact the Primary key executes the query faster? The Query is very straight-forward:
SELECT DISTINCT cast(tab.[Document Type] as varchar)+','+tab.[Document No] AS RecID FROM [MyTableName] AS tab WHERE tab.[timestamp] > cast(cast(918273645 as bigint) as timestamp) AND (tab.[Document Type]=1)
I would really appreciate your insights into the inner workings of SQL Server 2012 Enterprise Edition, why would it all of a sudden execute the same code significantly slower than the day before...
Kind regards and thank you.
0
Answers
See https://forum.mibuso.com/discussion/71637
This topic will be deleted after a week.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!