Hi Folks,

I have a query that takes about 5 minutes and I am not sure where the issue is. Is there anyway someone can give an insight to this query plan please? Here are the tables and indexes definitions:

-----Table 1

CREATE TABLE [dbo].[PropVal](
[Item] [nvarchar](16) NOT NULL,
[Symbol] [nvarchar](23) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Value] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [PropVal_PK] ON [dbo].[PropVal]
(
[Symbol] ASC,
[Item] ASC
)GO

-----Table 2

CREATE TABLE [dbo].[Crons](
[CronID] [nvarchar](23) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[XMLBlob] [xml] NOT NULL,
PRIMARY KEY CLUSTERED ([CronID] ASC )
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [test_idx_crons] ON [dbo].[Crons]
( [Date] ASC )
INCLUDE ( [CronID]) 
GO

-------

below is the script 

SELECT  'CLIENTIDS' AS Item
  , Symbol
  , CONVERT(NCHAR(8), GETUTCDATE(), 112) AS Date
  , STUFF(CAST(( SELECT DISTINCT
                        ',' + [Value]
      FROM   DBO.PropVal WITH (NOLOCK)
      WHERE  [Symbol] IN (
             SELECT DISTINCT
                    ( [Value] )
             FROM    DBO.PropVal WITH (NOLOCK)
             WHERE   [Item] = 'USERID'
                     AND [Value] NOT LIKE 'RIMES-%'
                     AND [Symbol] IN (
                          SELECT  DISTINCT a.[Symbol]
                          FROM    DBO.PropVal a, DBO.Crons b WITH (NOLOCK)
                          WHERE   a.[Item] = 'SYSTEM'
                                  AND a.[Symbol] = SUBSTRING(b.[CronID],1,CHARINDEX('.',b.[CronID])-1)
                                  AND b.[Date] > DATEADD(MONTH,-1,GETUTCDATE())
                                 AND a.[Value] = symbols.Symbol ) )
                     AND [Item] = 'CLIENTID'
             FOR
                XML PATH('')
             ) AS varchar(MAX)), 1, 1, '') AS Value

FROM    ( SELECT DISTINCT
                    [Value] AS Symbol
          FROM      DBO.PropVal WITH (NOLOCK)
          WHERE     [Item] = 'SYSTEM'
          AND       [Value] LIKE 'SYS-%'
        ) AS symbols

UNION ALL
SELECT  'USERIDS' AS Item
  , Symbol
  , CONVERT(NCHAR(8), GETUTCDATE(), 112) AS Date
  , STUFF(CAST(( SELECT DISTINCT
                        ',' + [Value]
      FROM   DBO.PropVal WITH (NOLOCK)
      WHERE  [Symbol] IN (
             SELECT  [Symbol]
             FROM    DBO.PropVal WITH (NOLOCK)
             WHERE   [Item] = 'SYSTEM'
                     AND [Value] = symbols.Symbol )
      AND [Item] = 'USERID'
      AND [Value] NOT LIKE 'RIMES%'
      FOR
         XML PATH('')
      ) AS varchar(MAX)), 1, 1, '') AS Value

FROM    ( SELECT DISTINCT
                    [Value] AS Symbol
          FROM      DBO.PropVal WITH (NOLOCK)
          WHERE     [Item] = 'SYSTEM'
          AND       [Value] LIKE 'SYS-%'
        ) AS symbols;



• Edited by Danycha 10 hours 57 minutes ago Adding query plan image

I only had the time to give this is a quick lock, but it seems that the query could serve from an index on the PropVal table with Item as the leading column.

Not that this is a fix to performance, but this query may not be doing what you want.  About 11 lines into the query you have a WHERE clause that reads

WHERE   [Item] = 'USERID'
                     AND [Value] NOT LIKE 'RIMES-%'
                     AND [Symbol] IN (
                          SELECT  DISTINCT a.[Symbol]
                          FROM    DBO.PropVal a, DBO.Crons b WITH (NOLOCK)
                          WHERE   a.[Item] = 'SYSTEM'
                                  AND a.[Symbol] = SUBSTRING(b.[CronID],1,CHARINDEX('.',b.[CronID])-1)
                                  AND b.[Date] > DATEADD(MONTH,-1,GETUTCDATE())
                                 AND a.[Value] = symbols.Symbol ) )
                     AND [Item] = 'CLIENTID'

Notice that that is of the form

WHERE   [Item] = 'USERID'
                     AND <some other stuff>
                     AND [Item] = 'CLIENTID'

So, since no row can have Item = 'USERID' and have Item = 'CLIENTID', whis WHERE clause will never return any rows.

Tom