SQL Server Increase the performance of this SQL query

l7wslrjt 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(117)

I am running the below query, and it has a performance issue.

SELECT TOP 5000 
    mseg.werks
FROM
    mkpf
INNER JOIN 
    mseg ON mkpf.mandt = mseg.mandt
         AND mkpf.mblnr = mseg.mblnr
GROUP BY 
    mseg.werks

Execution plan

Indexes

MKPF - clustered index on columns mandt, mblnr, abc in that order
MSEG - non-clustered index on the column werks
MSEG - clustered index on columns mandt, mblnr, abc, xyz in that order

Row counts:

MKPF - 50 million rows
MSEG - 150 million rows

Initially, it was just the two clustered indexes, and the query was very slow. So, I created the non clustered index on the column werks . But, it's still taking a little over two minutes to produce results. I would like to get it under 30 seconds or at least a minute.

Any help would be appreciated, thank you.

Edit

Adding a bit more context: The above query is part of a larger query. But, the larger query in this case is essentially doing what I have posted. The rest of it comes into play in other use cases where other columns need to be pulled from the tables MSEG and MKPF depending on what the user wants to see. Due to this, there are limitations to how much the query can be rewritten as all the columns need to be accessible from MSEG and MKPF . Not sharing the whole query as it is part of an IP.

sql-server

来源：https://stackoverflow.com/questions/77153487/increase-the-performance-of-this-sql-query

1条答案

按热度按时间

zte4gxcn1#

I would guess, given the low cardinality of distinct mseg.werks values (estimated 2141 in this case), the best option is probably to create an indexed view.

CREATE VIEW vw_Werks
WITH SCHEMABINDING
AS

SELECT
    mseg.werks,
    COUNT_BIG(*) AS TotalCount
FROM
    dbo.mkpf
INNER JOIN
    dbo.mseg ON mkpf.mandt = mseg.mandt
            AND mkpf.mblnr = mseg.mblnr
GROUP BY 
    mseg.werks;

CREATE UNIQUE CLUSTERED INDEX IX ON vw_Werks (werks);

Now query it like this, note the use of NOEXPAND

SELECT TOP (5000)
    mseg.werks
FROM
    vw_Werks mseg WITH (NOEXPAND);

The server will automatically maintain the indexed view, and it effectively functions as a pre-joined index.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Increase the performance of this SQL query

1条答案

相关问题

热门标签

最新问答