4 Replies Latest reply on Mar 17, 2016 4:01 AM by Lior Cohen

    Edit SQL Data Source Gets Stuck

    Lior Cohen

      Every time I try to edit my SQL data sources, it gets stuck.

      It relates to the fact that the system is trying to retrieve the schemes list and other parameters from the DB in order to display the data source canvas.

      I checked the Tableau queries that are being sent to the DB.

       

      There are 2 queries:

       

      First Query:

       

      SELECT [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].[ORDINAL_POSITION], [P].[IS_RESULT], [P].[PARAMETER_NAME],

             [P].[DATA_TYPE], [P].[PARAMETER_MODE], [P].[NUMERIC_PRECISION], [P].[NUMERIC_SCALE],

      [P].[NUMERIC_PRECISION_RADIX], [P].[DATETIME_PRECISION], [P].[CHARACTER_MAXIMUM_LENGTH]

        FROM [CONDUIT_DWH].[INFORMATION_SCHEMA].[ROUTINES] AS [R] LEFT OUTER JOIN [CONDUIT_DWH].[INFORMATION_SCHEMA].[PARAMETERS] AS [P]

          ON [P].[SPECIFIC_SCHEMA] = [R].[SPECIFIC_SCHEMA] AND [P].[SPECIFIC_NAME] = [R].[SPECIFIC_NAME]

      WHERE [R].[ROUTINE_TYPE] = 'PROCEDURE' AND [R].[ROUTINE_SCHEMA] <> 'SYS'

      ORDER BY [R].[ROUTINE_SCHEMA], [R].[ROUTINE_NAME], [P].ORDINAL_POSITION

       

       

      Second Query:

       

      select

      TABLE_QUALIFIER = convert(sysname,db_name()),

      TABLE_OWNER     = convert(sysname,schema_name(o.schema_id)),

      TABLE_NAME      = convert(sysname,o.name),

      TABLE_TYPE      = convert(varchar(32),

      rtrim(substring('SYSTEM TABLE TABLE VIEW       ',

      (ascii(o.type)-83)*12+1,

      12))  -- 'S'=0,'U'=2,'V'=3

      ),

      REMARKS = convert(varchar(254),null)    -- Remarks are NULL.

       

              from

      sys.all_objects o

       

              where

      o.type in ('S','U','V') and

      has_perms_by_name(quotename(schema_name(o.schema_id)) + '.' + quotename(o.name),

      'object',

      'select') = 1 and

      charindex(substring(o.type,1,1),@type1) <> 0 and -- Only desired types.

      (@table_name  is NULL or o.name like @table_name) and

      (@table_owner is NULL or schema_name(o.schema_id) like @table_owner)

              order by 4, 1, 2, 3

       

       

      When ‘Read uncommitted data’ IS NOT marked, Both queries are sent, and each of them takes ages…

      When ‘Read uncommitted data’ IS marked, only the second query is sent, but it still takes a lot of time.

       

      When I kill the queries, the data source edit view is displayed immediately.

       

      Meaning – in order to skip the metadata stuck requests, I need to manually kill the queries.

       

      Of course – I assume that killing the queries causes Tableau display the last metadata it retrieved.

       

       

       

      Any idea?