1

I am using SQL Server 2022 Developer

Trying get all AccessLog that classified to type 1.

SELECT [t].[Time], [u].[UserName], [t].[Type], [t].[Message]
FROM [AccessLog] AS [t]
         LEFT JOIN [AppUser] AS [u] ON [t].[UserId] = [u].[Id]
WHERE EXISTS (SELECT 1
              FROM [LogCatalog] AS [c]
              WHERE [c].[Type] = 1
                AND [c].[Name] = [t].[Type])
ORDER BY [t].[Time] DESC

For 1M record, it will need ~90s to execute on my computer. Most cost is on sort operate. I already have index on AccessLog.Time DESC, but the plan will sort again still after join. https://www.brentozar.com/pastetheplan/?id=HyXzc9UUp

I have Index on AccessLog:

  1. PK [Id]
  2. IX [Time] DESC
  3. IX [Time] DESC, [Type] ASC
  4. IX [Type] ASC, [Time] DESC
  5. IX [Type] ASC
  6. IX [UserId] ASC
  7. IX [Time] DESC, [UserId] ASC, [Type] ASC

The query filter by [Type] and order by [Time], why the plan can not use the [Time],[Type] index but need to sort again?

3
  • What database technology are you using? Which db engine?
    – DanAllen
    Dec 12, 2023 at 10:00
  • When you say the plan does its own sort, are you referring to where ti says". Transformation -> Sort Unique -> Full Index Scan of [LogCatalog] ? Is that sort using every field in the AppUser and Access log tables? Does the query filter by type sort on all the fields selected from the two tables?
    – DanAllen
    Dec 12, 2023 at 10:05
  • @DanAllen I mean Select -> Parallelism -> Sort. 79% cost is costed by sort here. I also edited my question and provide the full plan.
    – Uni
    Dec 13, 2023 at 2:42

2 Answers 2

0

The query filter by [Type] and order by [Time], why the plan can not use the [Time],[Type] index but need to sort again?

It is likely not seeing any benefit of that index because it would need to scan that then do lookups to find UserId for the join operation.

It is unlikely to be able to do much with indexes on Type because of that sub-select – there may be a better way to write that, but without a little more detail (see below) I would only be guessing and that might not be significant for what little I can make out from that image of the query plan.

These two points just mean that is it resorting to scanning the whole table (a clustered index scan is essentially a table scan), filtering as it goes, and sorting later.

Perhaps an index in Time+User+Type would be useful, or even just INCLUDEing User and Type in one of the Time indexes would help.


I'm assuming those indexes are those on the AccessLog table. It may be useful to know the indexes on the other tables in the query too. In any case, you will get better answers if you provide the full query plan - look at Brent Ozar's paste the plan for a useful way to provide this detail.

Note: you should update the question with this extra information, rather than provide the details in comments to this answer, as that makes the information more visible to others who may be looking and can help.

0

Knowing data type of column Type and Time is important.

Sql optimizer make least expensive plan as qucikly as possible,if the plan is not trivial.In your case [Time],[Type] index must be very wide.Also you must have notice that almost all rows 1 M is selected from table [AccessLog] in output. That means Type Index is not selective enough. When almost all rows are selected in output then Optmizer will straight away Index Scan which cost less than Index Seek.

Once you can try by creating Filtered Index on Type.You can include these column ([Time],[Type],[Message]) in your index

create nonclustered index ix_AccessLog_Type 
on [AccessLog](Type asc)include([Time],[Type],[Message])

Also first AccessLog data is sorted according to PK_AccessLog,and this sorted result it join with table [AppUser].In this case why it will use Type index.

Once you can comment Appuser table and try. or use Index hint and verify the cost

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.