Design Meeting Notes - January 24, 2013

Buffering data reader perf

The change to make EF queries buffer by default caused some of our perf tests to regress. Changes were made to bring back some of this perf by, for example, switching to sequential access. After these changes:

  • A small number of tests are still showing a regression
  • Many more tests are not showing any regression or are showing improvements
  • The spatial tests are still showing significant regressions

Preliminary numbers are below. Note that:

  • These numbers may change as the results are stabilized.
  • We don’t yet have numbers for all the other tests.

Directly impacted by regression

           

Test Name

Dev11 Baseline

Before Regresion

After Regresion

After Regresion Deviation From Baseline

After Fix

After Fix Deviation From Baseline

Query_Execution_TPT_model_Ordering_OrderBy

4.25

3.89

6.19

-45.65%

4.71

-10.82%

Query_Execution_TPT_model_Ordering_OrderBy_Precompiled

4.17

3.81

6.17

-47.96%

4.80

-15.11%

Query_Execution_TPT_model_Ordering_OrderBy_DisablePlanCaching

6.77

6.31

8.60

-27.03%

7.26

-7.24%

Query_Execution_TPH_model_Ordering_OrderBy

4.43

4.41

6.30

-42.21%

4.99

-12.64%

Query_Execution_TPH_model_Ordering_OrderBy_Precompiled

4.33

4.31

6.27

-44.80%

5.04

-16.40%

Query_Execution_TPH_model_Ordering_OrderBy_DisablePlanCaching

5.33

5.26

7.34

-37.71%

6.00

-12.57%

Query_Execution_TPT_model_Join_Join

1.95

1.92

2.14

-9.74%

2.06

-5.64%

Query_Execution_TPT_model_Join_Join_Precompiled

1.81

1.79

2.05

-13.26%

1.88

-3.87%

TVF_Fetch_Complex_Type_Populate_Anonymous_TPT

3.28

3.32

3.69

-12.50%

2.87

12.50%

Query_Execution_TPT_model_Projection_Nested_Precompiled

3.90

3.82

4.21

-7.95%

3.57

8.46%

Query_Execution_TPH_model_Aggregate_Count

2.47

2.47

2.72

-10.12%

2.49

-0.81%

 

Indirectly impacted by regression (and partially fixed)

           

Test Name

Dev11 Baseline

Before Regresion

After Regresion

After Regresion Deviation From Baseline

After Fix

After Fix Deviation From Baseline

Geography_Materialization_Basic

2.57

2.62

4.77

-85.60%

3.79

-47.47%

Geometry_Materialization_Basic

1.76

1.73

3.21

-82.39%

2.63

-49.43%

 

Example of test not impacted by regression, but benefiting from fix

           

Test Name

Dev11 Baseline

Before Regresion

After Regresion

After Regresion Deviation From Baseline

After Fix

After Fix Deviation From Baseline

TVF_Fetch_Complex_Type_TPH

2.64

2.79

2.83

-7.20%

2.23

15.53%

 

Conclusions/next steps:

  • The spatial regressions are likely caused by a different aspect of the change than the other regressions
    • For example, possibly the serialization of spatial types for conversion
    • These will be investigated separately and there seems a good chance improvements can be made
  • We need to understand better why joins and especially ordering are specifically regressing
  • We need to get the full stabilized numbers and take a holistic view of perf across all scenarios
    • If we are getting significant improvements in other areas, then small regressions here may be acceptable
    • This is especially true since using AsStreaming brings perf back to where it was
  • Other changes (such as o-c caching) may also help improve perf in important scenarios
  • If, after the investigations above, the regressions are unacceptable, then buffering may need to be off by default
    • This means that MARS may still be required in many common scenarios
    • Switching on connection resiliency will need to enable buffering

Related: what should happen if AsStreaming is used while connection resiliency is enabled?

Options are:

  • Let things continue, but then potentially get inconsistent state if a retry happens
  • Throw only if a retry is needed, but this may not happen until the app is in production, and it is not very obvious that this could happen unless you know intimately the way AsStreaming and resiliency interact
  • Fail fast for the combination. This is safe and helps drive developers to success. We will do this. CodePlex 822

Provider-agnostic testing

We want to allow more testing against non-SQL Server providers/backends. Investigation of our existing test suites shows that many of the tests assume that they are running against SQL Server. For example, the tests use:

  • Store-specific types
  • Store-specific SQL
    • Queries
    • Create table
    • Create procedure
    • Drop table
    • Execute sp_*
  • Store-specific features
    • Schemas
    • Identity/Auto-increment
  • Provider-specific metadata
    • Invariant name
    • Manifest token
    • SSDL
  • Provider-specific types
    • SqlConnection
    • SqlCeConnection
  • Optional provider functionality
    • Database.Create
    • Database.Exists
    • Database.Delete
    • Migrations
    • Spatial

Also many of the existing tests that would work with other providers do so because they don’t really make use of the provider at all. For example, tests that change states in the state manager. There is no real value in running such tests against different providers.

Provider agnostic tests should cover the following:

  • Required
    • Transform delete/query/insert/update command trees into DML
    • Store schema mappings (for reverse engineer)
    • Translate between EDM and store types
    • Get supported facets for an EDM type
    • Get store functions
    • Get store types
  • Optional
    • Create a database
      • Transform SSDL into DDL
    • Check if a database exists
    • Delete a database
    • Migrations support
    • Spatial support

Conclusions:

  • It doesn’t really make sense to try to get all the existing tests to run against different providers for the reasons above
  • We should instead create a new provider-agnostic test suite
    • Existing functional tests that make use of the provider and are already agnostic can be moved to this suite
    • Specifically written new provider agnostic coverage will be added here
      • Also, if a new test makes use of the provider and can be written in an agnostic way, then it should also be added here
    • The agnostic suite will be run as part of “build” for check-ins, but only against SQL Server
      • We could possibly change this to SQLite in the future
    • On the CI machines the suite will be run against multiple providers
      • We should contact provider-writers to get additional providers onto the CI machines

Porting existing SQL tests

Following on from the discussion last week, we will:

  • Port the existing tests as end-to-end tests with assertions against the results
    • This is not as expensive as doing full-on CQT assertions
    • This provides some good end-to-end coverage we are missing right now
    • This is more robust that the SQL baselining
  • We should still investigate CQT assertions

Last edited Jan 25, 2013 at 3:32 PM by ajcvickers, version 2

Comments

No comments yet.