none
SQL Server 2014 CTP2 taking time for the query

    Question

  • Hi,

    Before telling my problem, let you know that I am migrating my database to Microsoft SQL Server 2014 (for the moment CTP2).

    In my database, I have a table with 50K records. My Software used to execute following query to get some contacts after first 20000 contacts. This query will get records from a single table which has clustered index for primary key, table name is Contact_Header. Following is the query.

    select Contact_Unique_Ref=RTrim(Contact_Unique_Ref), Contact_Type=RTrim(Contact_Type), User_Unique_Ref=RTrim(User_Unique_Ref), Source_Unique_Ref=RTrim(Source_Unique_Ref), Contact_Category=RTrim(Contact_Category), Rule_Unique_Ref=RTrim(Rule_Unique_Ref) FROM Contact_Header where Contact_Unique_Ref NOT IN (select top 20000 Contact_Unique_Ref=RTrim(Contact_Unique_Ref) from Contact_Header order by Contact_Unique_Ref) order by Contact_Unique_Ref desc

    This query takes no time in execution at SQL Server 2008 R2 Enterprise Edition. But when I tried it at SQL Server 2014 Enterprise Evaluation 64 bit installed at Windows 2012 R2, it takes 47 minutes approximately.

    Can somebody help me that why this query takes that much time while executing at SQL Server 2014?
    Thursday, January 09, 2014 9:56 AM

All replies

  • There are a standard set of steps to follow when migrating a database from one version to another, including:

    • DBCC CHECKDB
    • DBCC UPDATEUSAGE
    • Update statistics on all tables

    Have you completed these steps?  Here's a guide:

    http://thomaslarock.com/2013/03/upgrading-to-sql-2012-ten-things-you-dont-want-to-miss/

    Try these steps and let us know how you get on.

    Friday, January 10, 2014 4:15 PM
  • Hi,

    Thank you for the reply.

    Actually this is not the migration such that I detach the database from old version and attach to new version. From migration word, I mean that I have made the database at SQL Server 2014 with same script as I used to do at SQL Server 2008 R2 Enterprise Edition. Database creation is part of my product installer and now I am installing at the machine with SQL Server 2014.

    Monday, January 13, 2014 5:45 AM
  • This may be related to the changes we made in cardinality estimation. While in most cases you will get a better plan, in some cases the plan could be worse.

    To check whether it is indeed the new cardinality estimator, you can set your DB compat level to 110, in which case we revert back to the old estimator. If performance is indeed better with compat level 110, could you file an issue on Connect?

    Thanks,

    Jos de Bruijn [MSFT]

    Friday, March 14, 2014 7:42 PM
  • I can reproduce this.  The following query runs in a few seconds on my SQL 2008R2 and SQL 2012 builds, but took over 3 hours on my SQL 2014 CTP2 (12.0.1524 x64) VM.

    USE tempdb
    GO
    
    IF OBJECT_ID('contact_header') IS NOT NULL DROP TABLE contact_header
    CREATE TABLE contact_header
    (
    	Contact_Unique_Ref	VARCHAR(36),	-- NOT NULL,
    	Contact_Type		VARCHAR(36),
    	User_Unique_Ref		VARCHAR(36),
    	Source_Unique_Ref	VARCHAR(36),
    	Contact_Category	VARCHAR(36),
    	Rule_Unique_Ref		VARCHAR(36)
    )
    GO
    
    ;WITH cte AS (
    SELECT TOP 50000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
    FROM master.sys.columns c1
    	CROSS JOIN master.sys.columns c2
    	CROSS JOIN master.sys.columns c3
    )
    INSERT INTO contact_header
    SELECT NEWID(), NEWID(), NEWID(), NEWID(), NEWID(), NEWID()
    FROM cte
    GO
    
    -- Turn off new cardinality model
    --DBCC TRACEON(9481)
    GO
    
    -- Had to correct case for hekaton server.
    SELECT Contact_Unique_Ref = Rtrim(Contact_Unique_Ref), 
           Contact_Type = Rtrim(Contact_Type), 
           User_Unique_Ref = Rtrim(User_Unique_Ref), 
           Source_Unique_Ref = Rtrim(Source_Unique_Ref), 
           Contact_Category = Rtrim(Contact_Category), 
           Rule_Unique_Ref = Rtrim(Rule_Unique_Ref) 
    FROM   contact_header 
    WHERE  Contact_Unique_Ref NOT IN 
    	(
    	SELECT TOP 20000 Contact_Unique_Ref = Rtrim(Contact_Unique_Ref)
    	FROM contact_header 
    	ORDER  BY Contact_Unique_Ref
    	) 
    ORDER  BY Contact_Unique_Ref DESC
    --OPTION ( QUERYRULEOFF LASJtoLASJSEL )


    Disabling the new cardinality model ( with trace flag 9481 ) or temporarily disabling query rule LASJtoLASJSEL corrects the performance.

    To be fair, this is a badly designed query.  I would not use Not In in joins, only for lists ( eg Not In ( 'A', 'B', 'C' ) ) due to the well-known Not In bug with NULLs and potential performance issue with nullable columns.  You could just do TOP 30000 or rewrite query using EXCEPT or LEFT JOIN for example, although you will have to double-check for semantic differences in these queries to your original:

    SELECT TOP 30000 
    	   Contact_Unique_Ref = Rtrim(Contact_Unique_Ref), 
           Contact_Type = Rtrim(Contact_Type), 
           User_Unique_Ref = Rtrim(User_Unique_Ref), 
           Source_Unique_Ref = Rtrim(Source_Unique_Ref), 
           Contact_Category = Rtrim(Contact_Category), 
           Rule_Unique_Ref = Rtrim(Rule_Unique_Ref) 
    
    FROM contact_header ch2
    
    SELECT Contact_Unique_Ref = Rtrim(ch1.Contact_Unique_Ref), 
           Contact_Type = Rtrim(ch1.Contact_Type), 
           User_Unique_Ref = Rtrim(ch1.User_Unique_Ref), 
           Source_Unique_Ref = Rtrim(ch1.Source_Unique_Ref), 
           Contact_Category = Rtrim(ch1.Contact_Category), 
           Rule_Unique_Ref = Rtrim(ch1.Rule_Unique_Ref) 
    FROM   contact_header ch1
    	LEFT JOIN
    	(
    	SELECT TOP 20000 Contact_Unique_Ref = Rtrim(Contact_Unique_Ref)
    	FROM contact_header 
    	ORDER  BY Contact_Unique_Ref
    	) ch2
    	ON ch1.Contact_Unique_Ref = ch2.Contact_Unique_Ref
    WHERE ch2.Contact_Unique_Ref IS NULL
    
    
    SELECT Contact_Unique_Ref = Rtrim(Contact_Unique_Ref), 
           Contact_Type = Rtrim(Contact_Type), 
           User_Unique_Ref = Rtrim(User_Unique_Ref), 
           Source_Unique_Ref = Rtrim(Source_Unique_Ref), 
           Contact_Category = Rtrim(Contact_Category), 
           Rule_Unique_Ref = Rtrim(Rule_Unique_Ref) 
    FROM   contact_header ch1
    
    EXCEPT
    
    SELECT TOP 20000 
    	   Contact_Unique_Ref = Rtrim(Contact_Unique_Ref), 
           Contact_Type = Rtrim(Contact_Type), 
           User_Unique_Ref = Rtrim(User_Unique_Ref), 
           Source_Unique_Ref = Rtrim(Source_Unique_Ref), 
           Contact_Category = Rtrim(Contact_Category), 
           Rule_Unique_Ref = Rtrim(Rule_Unique_Ref) 
    
    FROM contact_header ch2
    ORDER  BY Contact_Unique_Ref

    Removing the last ORDER BY also gives normal performance.


    Interesting to note that upgrading the table to an in-memory OLTP (previously Hekaton) table does not solve the problem - however removing the RTRIM from the inner query does.  Seems like the old query design advice of "don't join on functions" will still be true for in-memory tables, which makes sense when you are chasing performance.  Removing RTRIM from the "normal" query had no effect, ie still poor performance, but as a general design guideline you should remove this from the query.  If the column does have trailing characters remove them once on load of the table or by datafix.

    • Edited by wBob Saturday, March 22, 2014 3:37 AM in-memory table note
    Saturday, March 15, 2014 6:46 PM
  • connect item added here:

    https://connect.microsoft.com/SQLServer/feedback/details/834582/poor-performance-with-not-in-in-sql-2014-ctp2#tabs

    Please vote for it if you can reproduce this problem.

    Saturday, March 15, 2014 7:04 PM
  • Just for completeness, here are the 'no RTRIM' and in-memory OLTP (previously Hekaton) solutions, assuming you have a database set up for this feature and again that simply TOP 30000 isn't what you want.

    Being as OUTER JOINs are not available in compiled stored procedures, my compiled stored proc solution is based on the 'Implementing an Outer Join' article here.  I get second or subsecond performance with both these solutions on my SQL 2014 CTP2 VM.

    USE yourHekatondb
    GO
    
    IF OBJECT_ID('usp_hekatonQuery') IS NOT NULL DROP PROC dbo.usp_hekatonQuery
    GO
    
    IF EXISTS ( SELECT * FROM sys.types WHERE name = 'TYPE_contact_header' )
    DROP TYPE dbo.TYPE_contact_header
    GO
    
    IF OBJECT_ID('contact_header') IS NOT NULL DROP TABLE contact_header
    CREATE TABLE contact_header
    (
    	Contact_Unique_Ref	VARCHAR(36) NOT NULL,
    	Contact_Type		VARCHAR(36),
    	User_Unique_Ref		VARCHAR(36),
    	Source_Unique_Ref	VARCHAR(36),
    	Contact_Category	VARCHAR(36),
    	Rule_Unique_Ref		VARCHAR(36)
    
    	CONSTRAINT PK_contact_header PRIMARY KEY NONCLUSTERED HASH ( Contact_Unique_Ref ) WITH ( BUCKET_COUNT = 50000 ),
    
    )
    WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY )
    GO
    
    
    ;WITH cte AS (
    SELECT TOP 50000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
    FROM master.sys.columns c1
    	CROSS JOIN master.sys.columns c2
    	CROSS JOIN master.sys.columns c3
    )
    INSERT INTO contact_header
    SELECT NEWID(), NEWID(), NEWID(), NEWID(), NEWID(), NEWID()
    FROM cte
    GO
    
    
    
    -- No RTRIM solution
    SELECT Contact_Unique_Ref = Rtrim(Contact_Unique_Ref), 
           Contact_Type = Rtrim(Contact_Type), 
           User_Unique_Ref = Rtrim(User_Unique_Ref), 
           Source_Unique_Ref = Rtrim(Source_Unique_Ref), 
           Contact_Category = Rtrim(Contact_Category), 
           Rule_Unique_Ref = Rtrim(Rule_Unique_Ref) 
    FROM   contact_header 
    WHERE  Contact_Unique_Ref NOT IN 
    	(
    	SELECT TOP 20000 Contact_Unique_Ref = (Contact_Unique_Ref)
    	FROM contact_header 
    	ORDER  BY Contact_Unique_Ref
    	) 
    ORDER  BY Contact_Unique_Ref DESC
    GO
    
    
    -- Compiled stored proc solution
    CREATE TYPE dbo.TYPE_contact_header AS TABLE
    (
    	rowId				INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 50000 ),
    	Contact_Unique_Ref	VARCHAR(36),
    	Contact_Type		VARCHAR(36),
    	User_Unique_Ref		VARCHAR(36),
    	Source_Unique_Ref	VARCHAR(36),
    	Contact_Category	VARCHAR(36),
    	Rule_Unique_Ref		VARCHAR(36)
    
    )
    WITH ( MEMORY_OPTIMIZED = ON )
    GO
    
    CREATE PROC dbo.usp_hekatonQuery
    WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
    AS
    BEGIN ATOMIC
    WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' )
    
    	DECLARE @i INT = 1
    	DECLARE @outer dbo.TYPE_contact_header
    	DECLARE @result dbo.TYPE_contact_header
    
    
    	INSERT INTO @outer
    	SELECT
    		Contact_Unique_Ref,
    		Contact_Type,
    		User_Unique_Ref,
    		Source_Unique_Ref,
    		Contact_Category,
    		Rule_Unique_Ref
    	FROM dbo.contact_header
    	ORDER BY Contact_Unique_Ref DESC
    
    
    	-- Loop to simulate LEFT OUTER JOIN
    	WHILE @i < 30001
    	BEGIN
    
    		INSERT INTO @result
    		SELECT
    			Contact_Unique_Ref,
    			Contact_Type,
    			User_Unique_Ref,
    			Source_Unique_Ref,
    			Contact_Category,
    			Rule_Unique_Ref
    		FROM @outer
    		WHERE rowId = @i
    
    		SET @i += 1
    	
    	END
    
    
    	-- Return resultset
    	SELECT
    		Contact_Unique_Ref,
    		Contact_Type,
    		User_Unique_Ref,
    		Source_Unique_Ref,
    		Contact_Category,
    		Rule_Unique_Ref
    	FROM @result
    	ORDER BY Contact_Unique_Ref DESC
    
    	RETURN
    
    END
    GO
    
    EXEC dbo.usp_hekatonQuery
    

    Saturday, March 22, 2014 4:42 AM