Reputation: 1076

TSQL: View Performance Issue

I have a Problem with the following Query. It works and gives me the needed result, BUT it is quite slow, because of my subselects with joins. My TSQL skills are not good enough to optimize this Query. Maybe some of you can help.

SELECT
    Id AS InstanceId,
    (SELECT COUNT(DISTINCT tiq.Id) AS Expr1
     FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
            dbo.tProcessLog AS tpr ON tpr.InstanceConfigId = tic.Id AND tpr.InputQueueEntryId = tiq.Id
     WHERE  (tpr.ProcessResultId = 32)) AS Received,
    (SELECT COUNT(DISTINCT tiq.Id) AS Expr1
     FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
            dbo.tProcessLog AS tpr ON tpr.InstanceConfigId = tic.Id AND tpr.InputQueueEntryId = tiq.Id
     WHERE        (tiq.InMsgStateFlags = 1)) AS Queued,
    (SELECT COUNT(DISTINCT tiq.Id) AS Expr1
     FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
            dbo.tProcessLog AS tpr ON tpr.InstanceConfigId = tic.Id AND tpr.InputQueueEntryId = tiq.Id
     WHERE  (tiq.InMsgStateFlags = 2)) AS Processed,
    (SELECT COUNT(DISTINCT tiq.Id) AS Expr1
     FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
            dbo.tProcessLog AS tpr ON tpr.InstanceConfigId = tic.Id AND tpr.InputQueueEntryId = tiq.Id
     WHERE  (tiq.InMsgStateFlags = 128)) AS Error,
    (SELECT COUNT(DISTINCT tiq.Id) AS Expr1
     FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
            dbo.tProcessLog AS tpr ON tpr.InstanceConfigId = tic.Id AND tpr.InputQueueEntryId = tiq.Id
     WHERE  (tiq.InMsgStateFlags = 256)) AS Rejected,
    (SELECT COUNT(tiq.Id) AS Expr1
     FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
            dbo.tProcessLog AS tpr ON tpr.InstanceConfigId = tic.Id AND tpr.InputQueueEntryId = tiq.Id
     WHERE  (tpr.ProcessResultId = 16)) AS Duplicates,
    (SELECT COUNT(DISTINCT tiq.Id) AS Expr1
     FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
            dbo.tProcessLog AS tpr ON tpr.InstanceConfigId = tic.Id AND tpr.InputQueueEntryId = tiq.Id
     WHERE  (tpr.ProcessResultId = 2)) AS Nack 
FROM
    dbo.tInstanceConfig AS tic 
WHERE
    (InstanceGroupId = 1)

This Query runs as a View.

If you Need further Information, let me know.

Here are the table Informations

tProcessLog

    [Id] [bigint] IDENTITY(1,1) NOT NULL,
[InstanceConfigId] [int] NOT NULL,
[InputQueueEntryId] [bigint] NOT NULL,
[OutputQueueEntryId] [bigint] NULL,
[ProcessingDtm] [datetime] NOT NULL,
[ProcessResultId] [int] NOT NULL,
[SeverityId] [int] NOT NULL,
[LogText] [varchar](max) NOT NULL,
[IsHidden] [bit] NOT NULL,
[ReceiverId] [int] NOT NULL,

tInputQueueEntry

    [Id] [bigint] IDENTITY(1,1) NOT NULL,
[Created] [datetime] NOT NULL,
[DataTypeId] [int] NOT NULL,
[CodePage] [int] NOT NULL,
[InMsgStateFlags] [int] NOT NULL,
[ContentData] [nvarchar](max) NOT NULL,
[ContentHash] [nvarchar](32) NOT NULL,

tInstanceConfig

    [Id] [int] IDENTITY(1,1) NOT NULL,
[IsActive] [bit] NOT NULL,
[Type] [varchar](50) NOT NULL,
[Description] [varchar](50) NOT NULL,
[ComponentConfig] [xml] NOT NULL,
[InstanceGroupId] [int] NOT NULL,

UPDATE

Finally, i think there is no way to get much more Performance. I used the MSSQL Tuning Advisor which had some recommendations, now the query tooks ~500ms. That is okay for me.

Thanks to all answers ! I doesnt help the Performance issue, but i learned cool new tsql stuff :)

Upvotes: 1

Answers (3)

Chinthaka Senaratne

Reputation: 110

Cant you GROUP BY using InMsgStateFlags and get the count(1) value since other conditions are same? Just write one SELECT statement.

Upvotes: 1

Ashish Kumar

Reputation: 157

DECLARE @Received INT,@Queued INT,@Processed INT,@Error INT,@Rejected INT,@Duplicates INT,@Nack INT

SET @Received =  SELECT COUNT(DISTINCT tiq.Id) AS Expr1
         FROM   
            dbo.tInputQueueEntry AS tiq 
         INNER JOIN
            dbo.tProcessLog AS tpr ON tpr.InputQueueEntryId = tiq.Id
         INNER JOIN dbo.tInstanceConfig AS tic ON tpr.InstanceConfigId = tic.Id
         WHERE  (tpr.ProcessResultId = 32)

SET @Queued =  SELECT COUNT(DISTINCT tiq.Id) AS Expr1
         FROM   
            dbo.tInputQueueEntry AS tiq 
        INNER JOIN
            dbo.tProcessLog AS tpr ON tpr.InputQueueEntryId = tiq.Id
         INNER JOIN dbo.tInstanceConfig AS tic ON tpr.InstanceConfigId = tic.Id
         WHERE        (tiq.InMsgStateFlags = 1) 

SET @Processed =  SELECT COUNT(DISTINCT tiq.Id) AS Expr1
                 FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
                        dbo.tProcessLog AS tpr ON tpr.InputQueueEntryId = tiq.Id
                        INNER JOIN dbo.tInstanceConfig AS tic ON tpr.InstanceConfigId = tic.Id
                 WHERE  (tiq.InMsgStateFlags = 2)   

SET @Error   = SELECT COUNT(DISTINCT tiq.Id) AS Expr1
                 FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
                        dbo.tProcessLog AS tpr ON tpr.InputQueueEntryId = tiq.Id
                        INNER JOIN dbo.tInstanceConfig AS tic ON tpr.InstanceConfigId = tic.Id 
                 WHERE  (tiq.InMsgStateFlags = 128)

SET @Rejected = SELECT COUNT(DISTINCT tiq.Id) AS Expr1
             FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
                    dbo.tProcessLog AS tpr ON tpr.InputQueueEntryId = tiq.Id
                    INNER JOIN dbo.tInstanceConfig AS tic tpr.InstanceConfigId = tic.Id
             WHERE  (tiq.InMsgStateFlags = 256) 

SET @Duplicates = SELECT COUNT(tiq.Id) AS Expr1
                 FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
                        dbo.tProcessLog AS tpr ON tpr.InputQueueEntryId = tiq.Id
                        INNER JOIN dbo.tInstanceConfig AS tic ON tpr.InstanceConfigId = tic.Id
                 WHERE  (tpr.ProcessResultId = 16)  
SET @Nack = SELECT COUNT(DISTINCT tiq.Id) AS Expr1
			 FROM   dbo.tInputQueueEntry AS tiq INNER JOIN
					dbo.tProcessLog AS tpr ON tpr.InputQueueEntryId = tiq.Id
					INNER JOIN dbo.tInstanceConfig AS tic ON tpr.InstanceConfigId = tic.Id
			 WHERE  (tpr.ProcessResultId = 2)                 

SELECT
    Id AS InstanceId,
    @Received AS Received,
    @Queued AS Queued,
    @Processed AS Processed,
    @Error AS Error,
    @Rejected AS Rejected,
    @Duplicates AS Duplicates,
    @Nack AS Nack 
FROM
    dbo.tInstanceConfig AS tic 
WHERE
    (InstanceGroupId = 1)

Upvotes: 2

Eric

Reputation: 5733

Like this?

SELECT
    tic.Id AS InstanceId,
    count.*
FROM
    dbo.tInstanceConfig AS tic 
    OUTER APPLY
    (
        SELECT 
            COUNT(DISTINCT CASE WHEN tpr.ProcessResultId = 32 THEN tiq.Id END) AS Received,
            COUNT(DISTINCT CASE WHEN tiq.InMsgStateFlags = 1 THEN tiq.Id END) AS Queued,
            COUNT(DISTINCT CASE WHEN tiq.InMsgStateFlags = 2 THEN tiq.Id END) AS Processed,
            COUNT(DISTINCT CASE WHEN tiq.InMsgStateFlags = 128 THEN tiq.Id END) AS Error,
            COUNT(DISTINCT CASE WHEN tiq.InMsgStateFlags = 256 THEN tiq.Id END) AS Rejected,
            COUNT(DISTINCT CASE WHEN tpr.ProcessResultId = 16 THEN tiq.Id END) AS Duplicates,
            COUNT(DISTINCT CASE WHEN tpr.ProcessResultId = 2 THEN tiq.Id END) AS Nack
        FROM   
            dbo.tInputQueueEntry AS tiq 
            INNER JOIN dbo.tProcessLog AS tpr ON tpr.InstanceConfigId = tic.Id AND tpr.InputQueueEntryId = tiq.Id
    ) count
WHERE
    (InstanceGroupId = 1)

Upvotes: 3

TSQL: View Performance Issue

Answers (3)

Related Questions