Performance impact: UNION ALL views, ANSI_PADDING, and bad query plans

Linchi Shea — Fri, 04 Dec 2009 19:43:00 GMT

Whether or not you specify it explicitly, ANSI_PADDING setting is there when you create a table, and can have an impact on the performance of some queries. If you are not careful, it can even hurt performance big time!

Let’s demonstrate that with an extremely simple example.

Assume that you have this simple view with a UNION ALL between two tables:

CREATE VIEW myView

select * from tb1

UNION ALL

select * from tb2

The two tables are created as follows:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING OFF

-- Create the main table

CREATE TABLE tb1 (

[TicketID] [char](13),

[Edition] [smallint],

[FlowType] [char](3),

[Amount] [float] NOT NULL,

[Code] [char](3) NOT NULL,

[LogDate] [datetime] NOT NULL,

[id] [int] NOT NULL

)

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [tb2](

[TicketID] [char](13),

[Edition] [smallint] NOT NULL,

[FlowType] [char](3),

[Amount] [float] NOT NULL,

[Code] [char](3) ,

[LogDate] [datetime] NOT NULL,

[id] [int] NOT NULL

)

And you run the following query on the view to return a single row from tb1:

SELECT TicketID, Amount

FROM myView

WHERE FlowType ='2'

and TicketID = '200002000'

and Edition = 0

Since both tb1 and tb2 have a covering index on this query (see the attached repro script for the indexes), you’d expect an efficient index seek. But you may discover that SQL Server chooses to process the query with an index scan, which could easily sink the performance if the table is rather large.

What’s going on?

Well, notice that the only difference between tb1 and tb2 is that tb1 is created with ANSI_PADDING set to OFF whereas tb2 is created with ANSI_PADDING set to ON. If you create these two tables with the same ANSI_PADDING setting, SQL Server does generate a correct plan for the query.

So for some reason, the SQL Server 2005/2008 optimizer can’t deal with this type of mismatched ANSI_PADDING settings in a view intelligently. Although one may question whether the two tables should have different ANSI_PADDING settings, it’s clear that the generated plan is not optimal. And I’d argue that this is a bug in the SQL Server optimizer because there does exist an optimal plan that uses an index seek, and the optimizer fails to find it.

To see the details of the behavior for yourself, you can run the attached repro script in any test database. The repro demonstrates the bad plans on both SQL Server 2005 and SQL Server 2008. On SQL Server 2000, you should see a good plan with an index seek. But this does not mean that SQL Server 2000 is immune to the problem. In fact, with a different but slightly more complex repro script, I see the same bad plans on SQL Server 2000.

Note that to save time, the repro script populates the table with 100,000 rows, and can finish in several seconds on any decent machine. With 100,000 rows, you won’t feel any pain even with a bad plan that does an index scan. If the table had millions of rows, the pain would be quite acute.

Also, I have filed a bug report at Microsoft Connect. Please vote there to get some traction from Microsoft on this issue.

Search results matching tags 'Performance', 'Query Processing', and 'Optimizer Bug'

Performance impact: UNION ALL views, ANSI_PADDING, and bad query plans