AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / user-112822

George K's questions

Martin Hope
George K
Asked: 2023-12-24 03:17:45 +0800 CST

复杂的PostgreSQL查询优化问题

  • 6

请协助查询。开发人员要求提高查询性能。我尝试使用 CTE 代替横向连接和 EXISTS,测试覆盖索引、附加过滤。没有显着的性能优势。可能的建议可能包括:

  1. 附加过滤
  2. 重写的方法我可以在那里看到堆获取,所以我很快就会执行VACUUM,那么除了VACUUM还能做什么呢?感谢你的帮助

https://explain.dalibo.com/plan/e6b0c5757962095a

PG版本:PostgreSQL 14.7

查询:

SELECT
    oh.order_header_id AS SfmId,
    oh.status AS Status,
    oh.case_type AS CaseType,
    oh.partner_id AS CompanyId,
    oh.date_created AS DateCreated,
    oh.update_date_utc AS DateUpdated,
    oh.contact_id AS DoctorId,
    scan_detail.due_date AS DueDate,
    lab_link.partner_id AS LabId,
    COALESCE(milling_site_link.partner_id, -1) AS MillingSiteId,
    COALESCE(int_site_link.partner_id, -1) AS InterpretationSiteId,
    oh.order_tags AS OrderTags,
    oh.patient_guid AS PatientGuid,
    oh.rx_id AS RxId,
    FALSE AS IsConventional,
    COALESCE(prev_wo.work_type, -1) AS PreviousBowId,
    -1 AS LastDetailsId,
    oh.last_work_order_id AS LastWorkOrderSfmId,
    wo.date_created AS LastWorkOrderDateCreated,
    wo.date_updated AS LastWorkOrderDateUpdated,
    oh.direct_to_lab_status AS IsDirectToLab,
    wo.resource_id AS LastResourceId,
    wo.resource_type AS LastResourceTypeId,
    oh.scan_info AS ScanInfo,
    oh.extended_info AS ExtendedInfo,
    oh.file_upload_report AS FileUploadReport,
    wo.status AS LastWorkOrderStatus,
    wo.work_type AS LastBowId,
    wo.order_detail_id AS LastDetailsSfmId,
    od.due_date AS LastDetailsDueDate,
    -1 AS LastWorkOrderId,
    wo.status AS LastWorkOrderStatus,
    oh.order_code AS OrderCode,
    od.date_created AS LastDetailsDateCreated
FROM
    tab1 cpl
    LEFT JOIN tab2 oh ON oh.order_header_id = cpl.order_header_id
    LEFT JOIN LATERAL (
        SELECT
            due_date
        FROM
            tab3 scan_detail
        WHERE
            scan_detail.order_header_id = oh.order_header_id
            AND EXISTS (
                SELECT
                    1
                FROM
                    tab4 ctdc2
                WHERE
                    ctdc2.detail_type = scan_detail.item
                    AND ctdc2.detail_category = 1
            )
        LIMIT 1
    ) AS scan_detail ON TRUE
    LEFT JOIN LATERAL (
        SELECT
            partner_id
        FROM
            tab1 lab_link
        WHERE
            lab_link.order_header_id = oh.order_header_id
            AND lab_link.partner_type = 300
        LIMIT 1
    ) AS lab_link ON TRUE
    LEFT JOIN LATERAL (
        SELECT
            partner_id
        FROM
            tab1 milling_site_link
        WHERE
            milling_site_link.order_header_id = oh.order_header_id
            AND milling_site_link.partner_type = 500
        LIMIT 1
    ) AS milling_site_link ON TRUE
    LEFT JOIN LATERAL (
        SELECT
            partner_id
        FROM
            tab1 int_site_link
        WHERE
            int_site_link.order_header_id = oh.order_header_id
            AND int_site_link.partner_type = 1100
        LIMIT 1
    ) AS int_site_link ON TRUE
    INNER JOIN tab5 wo ON oh.last_work_order_id = wo.work_order_id
    INNER JOIN tab3 od ON oh.order_header_id = od.order_header_id AND wo.order_detail_id = od.order_detail_id
    LEFT JOIN LATERAL (
        SELECT
            *
        FROM
            tab5 prev_wo
        WHERE
            prev_wo.work_order_id = wo.created_by_work_order
        LIMIT 1
    ) AS prev_wo ON TRUE
WHERE
    cpl.partner_id = 8133
    AND cpl.partner_type = ANY (VALUES (200), (500), (1900), (2700))
    AND wo.partner_id != 8133
    AND (
        EXISTS (
            SELECT
                1
            FROM
                tab2 oh2
                INNER JOIN tab3 od2 ON oh2.order_header_id = od2.order_header_id
                INNER JOIN tab5 wo2 ON wo2.order_detail_id = od2.order_detail_id
            WHERE
                oh2.order_header_id = oh.order_header_id
                AND wo2.work_order_id != oh2.last_work_order_id
                AND wo2.partner_id = 8133
                AND wo2.date_updated > (NOW() AT TIME ZONE 'UTC' + INTERVAL '-90 days')
                AND wo2.work_type <> 131
            LIMIT 1
        )
        OR (
            101 = ANY (VALUES (102))
            AND lab_link.partner_id = 8133
        )
    )
    AND (
        wo.work_type > 0
        OR (
            (
                wo.work_type = -1
                OR wo.status <> 1
            )
            AND wo.date_updated > (NOW() AT TIME ZONE 'UTC' + INTERVAL '-7 days')
        )
    )
LIMIT 1500;

这是查询计划

(分析、缓冲区、设置、文本格式)

也在explain.depesz.com上 https://explain.depesz.com/s/j73P#html

Limit  (cost=1771.30..248159.49 rows=1 width=1206) (actual time=47.173..548.541 rows=1500 loops=1)
  Buffers: shared hit=776970
  ->  Nested Loop Semi Join  (cost=1771.30..248159.49 rows=1 width=1206) (actual time=47.172..548.291 rows=1500 loops=1)
        Join Filter: (cpl.partner_type = "*VALUES*".column1)
        Rows Removed by Join Filter: 7412
        Buffers: shared hit=776970
        ->  Nested Loop Left Join  (cost=1771.30..248159.39 rows=1 width=1197) (actual time=46.221..543.890 rows=2978 loops=1)
              Buffers: shared hit=776970
              ->  Nested Loop Left Join  (cost=1770.74..248150.79 rows=1 width=1209) (actual time=46.206..518.898 rows=2978 loops=1)
                    Buffers: shared hit=762079
                    ->  Nested Loop Left Join  (cost=1770.31..248142.30 rows=1 width=1205) (actual time=46.194..506.935 rows=2978 loops=1)
                          Buffers: shared hit=752105
                          ->  Nested Loop Left Join  (cost=1769.88..248133.80 rows=1 width=1201) (actual time=46.179..492.135 rows=2978 loops=1)
                                Filter: ((SubPlan 1) OR ((hashed SubPlan 3) AND (lab_link.partner_id = 8133)))
                                Rows Removed by Filter: 2649
                                Buffers: shared hit=740174
                                ->  Nested Loop Left Join  (cost=1769.43..247893.45 rows=1 width=1197) (actual time=13.117..282.515 rows=5627 loops=1)
                                      Buffers: shared hit=596990
                                      ->  Gather  (cost=1769.00..247875.58 rows=1 width=1189) (actual time=13.069..214.075 rows=5627 loops=1)
                                            Workers Planned: 2
                                            Workers Launched: 2
                                            Buffers: shared hit=570677
                                            ->  Nested Loop  (cost=769.00..246875.48 rows=1 width=1189) (actual time=9.170..289.074 rows=1905 loops=3)
                                                  Join Filter: (oh.tab2_id = od.tab2_id)
                                                  Buffers: shared hit=570677
                                                  ->  Nested Loop  (cost=768.57..236676.59 rows=20408 width=1189) (actual time=9.146..276.915 rows=1905 loops=3)
                                                        Buffers: shared hit=547781
                                                        ->  Nested Loop  (cost=768.00..209231.31 rows=25330 width=1127) (actual time=9.017..152.212 rows=19269 loops=3)
                                                              Buffers: shared hit=258744
                                                              ->  Parallel Bitmap Heap Scan on tab1 cpl  (cost=767.57..66251.14 rows=25330 width=20) (actual time=8.989..41.637 rows=19269 loops=3)
                                                                    Recheck Cond: (partner_id = 8133)
                                                                    Heap Blocks: exact=5996
                                                                    Buffers: shared hit=27514
                                                                    ->  Bitmap Index Scan on ix_tab1_partner_id_partner_type  (cost=0.00..752.37 rows=60792 width=0) (actual time=7.558..7.558 rows=60795 loops=1)
                                                                          Index Cond: (partner_id = 8133)
                                                                          Buffers: shared hit=83
                                                              ->  Index Scan using "PK_tab2" on tab2 oh  (cost=0.43..5.64 rows=1 width=1107) (actual time=0.005..0.005 rows=1 loops=57807)
                                                                    Index Cond: (tab2_id = cpl.tab2_id)
                                                                    Buffers: shared hit=231230
                                                        ->  Index Scan using "PK_tab5" on tab5 wo  (cost=0.56..1.08 rows=1 width=78) (actual time=0.006..0.006 rows=0 loops=57807)
                                                              Index Cond: (tab5_id = oh.last_tab5_id)
                                                              Filter: ((partner_id <> 8133) AND ((work_type > 0) OR (((work_type = '-1'::integer) OR (status <> 1)) AND (date_updated > ((now() AT TIME ZONE 'UTC'::text) + '-7 days'::interval)))))
                                                              Rows Removed by Filter: 1
                                                              Buffers: shared hit=289037
                                                  ->  Index Scan using "PK_tab3" on tab3 od  (cost=0.43..0.49 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=5715)
                                                        Index Cond: (tab3_id = wo.tab3_id)
                                                        Buffers: shared hit=22879
                                      ->  Limit  (cost=0.43..17.84 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=5627)
                                            Buffers: shared hit=26313
                                            ->  Nested Loop Semi Join  (cost=0.43..17.84 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=5627)
                                                  Join Filter: (scan_detail.item = ctdc2.detail_type)
                                                  Rows Removed by Join Filter: 19
                                                  Buffers: shared hit=26313
                                                  ->  Index Scan using "IX_tab3_tab2_id" on tab3 scan_detail  (cost=0.43..16.48 rows=3 width=12) (actual time=0.005..0.006 rows=2 loops=5627)
                                                        Index Cond: (tab2_id = oh.tab2_id)
                                                        Buffers: shared hit=25878
                                                  ->  Materialize  (cost=0.00..1.32 rows=1 width=4) (actual time=0.000..0.001 rows=9 loops=12647)
                                                        Buffers: shared hit=1
                                                        ->  Seq Scan on tab4 ctdc2  (cost=0.00..1.31 rows=1 width=4) (actual time=0.006..0.010 rows=14 loops=1)
                                                              Filter: (detail_category = 1)
                                                              Rows Removed by Filter: 11
                                                              Buffers: shared hit=1
                                ->  Limit  (cost=0.43..8.47 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=5627)
                                      Buffers: shared hit=22214
                                      ->  Index Only Scan using ix_tab2_id_partner_id_partner_type on tab1 lab_link  (cost=0.43..8.47 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=5627)
                                            Index Cond: ((tab2_id = oh.tab2_id) AND (partner_type = 300))
                                            Heap Fetches: 5199
                                            Buffers: shared hit=22214
                                SubPlan 1
                                  ->  Nested Loop  (cost=1.30..231.85 rows=1 width=0) (actual time=0.030..0.030 rows=1 loops=5627)
                                        Join Filter: (wo2.tab5_id <> oh2.last_tab5_id)
                                        Buffers: shared hit=120970
                                        ->  Nested Loop  (cost=0.87..223.38 rows=1 width=32) (actual time=0.027..0.027 rows=1 loops=5627)
                                              Buffers: shared hit=109058
                                              ->  Index Scan using "IX_tab3_tab2_id" on tab3 od2  (cost=0.43..16.48 rows=3 width=32) (actual time=0.003..0.004 rows=3 loops=5627)
                                                    Index Cond: (tab2_id = oh.tab2_id)
                                                    Buffers: shared hit=28948
                                              ->  Index Scan using "IX_tab5_tab3_id" on tab5 wo2  (cost=0.44..68.96 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=18155)
                                                    Index Cond: (tab3_id = od2.tab3_id)
                                                    Filter: ((work_type <> 131) AND (partner_id = 8133) AND (date_updated > ((now() AT TIME ZONE 'UTC'::text) + '-90 days'::interval)))
                                                    Rows Removed by Filter: 2
                                                    Buffers: shared hit=79660
                                        ->  Index Scan using "PK_tab2" on tab2 oh2  (cost=0.43..8.45 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=2978)
                                              Index Cond: (tab2_id = oh.tab2_id)
                                              Buffers: shared hit=11912
                                SubPlan 3
                                  ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                          ->  Limit  (cost=0.43..8.47 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2978)
                                Buffers: shared hit=11931
                                ->  Index Only Scan using ix_tab2_id_partner_id_partner_type on tab1 milling_site_link  (cost=0.43..8.47 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2978)
                                      Index Cond: ((tab2_id = oh.tab2_id) AND (partner_type = 500))
                                      Heap Fetches: 2993
                                      Buffers: shared hit=11931
                    ->  Limit  (cost=0.43..8.47 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2978)
                          Buffers: shared hit=9974
                          ->  Index Only Scan using ix_tab2_id_partner_id_partner_type on tab1 int_site_link  (cost=0.43..8.47 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2978)
                                Index Cond: ((tab2_id = oh.tab2_id) AND (partner_type = 1100))
                                Heap Fetches: 1056
                                Buffers: shared hit=9974
              ->  Limit  (cost=0.56..8.58 rows=1 width=134) (actual time=0.008..0.008 rows=1 loops=2978)
                    Buffers: shared hit=14891
                    ->  Index Scan using "PK_tab5" on tab5 prev_wo  (cost=0.56..8.58 rows=1 width=134) (actual time=0.007..0.007 rows=1 loops=2978)
                          Index Cond: (tab5_id = wo.created_by_tab5)
                          Buffers: shared hit=14890
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=4 width=4) (actual time=0.000..0.001 rows=3 loops=2978)
Settings: effective_cache_size = '88445488kB', maintenance_io_concurrency = '1'
Planning:
  Buffers: shared hit=5660
Planning Time: 5.158 ms
Execution Time: 549.025 ms
postgresql
  • 2 个回答
  • 142 Views
Martin Hope
George K
Asked: 2022-07-28 01:24:29 +0800 CST

PostgreSQL 复制选项

  • -2

社区!我想知道是否有任何新的扩展/解决方案可以复制不是整个 PG 集群而是通过数据库复制?逻辑复制不是一种选择。例如,我希望我的主数据库“test”以两个化身存在于辅助服务器上——“testreplica1”和“testreplica2”能够促进第一个数据库对其进行处理,同时第二个数据库仍将启动——迄今为止的生产

postgresql replication
  • 1 个回答
  • 38 Views
Martin Hope
George K
Asked: 2021-12-22 08:10:20 +0800 CST

临时表数据创建后去哪了?

  • 2

据我所知temp_buffers,设置服务用于存储临时表数据。

但是当我创建了一个临时表时,会立即在内部为它创建文件PGDATA以及主数据库。因此,我将感谢您对几个问题的帮助。

  1. 会发生什么
    。临时表数据是否temp_buffers 足以存储临时表数据?

    湾。临时表数据是否temp_buffers 不足以存储临时表数据?

  2. 是否有任何情况下没有足够的temp_buffers 设置最终溢出pgsql_tmp?

postgresql postgresql-extensions
  • 1 个回答
  • 232 Views
Martin Hope
George K
Asked: 2021-10-08 07:44:17 +0800 CST

PostgreSQL 日志中的错误

  • 0

由于没有剩余空间问题,在主集群关闭后,我们在副本的日志中收到这种错误。更多细节:

LOG:  incomplete startup packet
LOG:  invalid contrecord length 4975 at 9412/F4FFE910

它很重要吗?帮助将不胜感激!)

postgresql
  • 1 个回答
  • 202 Views
Martin Hope
George K
Asked: 2021-09-08 02:41:03 +0800 CST

'emp::regclass' 的数据类型是什么?

  • 2

有一个重要的问题需要回答。我倾向于认为,因为“regclass”是 OID 的别名,所以答案是“OID”。我在这里吗?帮助将不胜感激

postgresql
  • 1 个回答
  • 115 Views
Martin Hope
George K
Asked: 2021-09-07 05:22:06 +0800 CST

真空冷冻 PostgreSQL

  • 1

有时我发现很难理解 postgreSQL 文档。

现在,VACCUM 部分(https://www.postgresql.org/docs/10/sql-vacuum.html)说这句话“在重写表时总是执行积极的冻结”。

这句话是否意味着 VACUUM FREEZE 与 FULL 一样?

postgresql vacuum
  • 1 个回答
  • 324 Views
Martin Hope
George K
Asked: 2019-09-27 03:03:08 +0800 CST

如何在 Windows 上使用 pg_archivecleanup

  • 0

试图找出一种正确的方法来清理位于存档目录中的旧 WAL 文件,以便进行时间点恢复。我知道我可以像这样使用 WAL 段的硬编码名称:

pg_archivecleanup.exe H:\Backup\pgArchive 0000000100000001000000C1

但显然我想自动化这个过程。

谢谢你。

postgresql windows
  • 2 个回答
  • 679 Views
Martin Hope
George K
Asked: 2019-09-27 02:44:34 +0800 CST

sp_updatestats 似乎不适用于非系统管理员登录

  • 3

微软 SQL Server 2017 (RTM-CU16)

当尝试db_owner通过作为数据库角色成员的用户更新统计信息时

EXEC sp_updatestats;

SQL Server 引发错误:

消息 15247,级别 16,状态 1,过程 sp_updatestats,第 15 行 [批处理开始行 0] 用户无权执行此操作。

尽管这里的 MS声明“数据库的所有权 (dbo)”就足够了,但似乎并非如此。

sql-server permissions
  • 2 个回答
  • 3166 Views
Martin Hope
George K
Asked: 2019-08-20 23:38:38 +0800 CST

恢复卡在 ASYNC_IO_COMPLETION

  • 6

当我还原数据库时,还原操作经常卡住并且等待类型为ASYNC_IO_COMPLETION. 它可以处于这种状态 10-20-30 分钟,直到它继续PREEMPTIVE_OS_WRITEFILEGATHER,这就是进度 ( percent_complete) 开始增长的时候。

但是,如果我看到ASYNC_IO_COMPLETION并中断还原过程然后再次运行它,我会立即看到PREEMPTIVE_OS_WRITEFILEGATHER并percent_complete上升。

最好了解这种行为并了解是否有更合适的方法来加快恢复操作。谢谢

sql-server restore
  • 1 个回答
  • 2494 Views
Martin Hope
George K
Asked: 2017-09-29 05:15:24 +0800 CST

SQL Server 实际计划。不正确的行估计

  • 1

在练习调整 StackOverflow 数据库的查询时,我遇到了以下问题:

SELECT TOP 50 Id AS [User Link],
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 1 AND 
          LastEditorUserId = Users.Id AND 
          OwnerUserId != Users.Id
) AS QuestionEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 2 AND 
          LastEditorUserId = Users.Id AND 
          OwnerUserId != Users.Id
) AS AnswerEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE LastEditorUserId = Users.Id AND 
          OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC;

计划就在这里。

执行统计:

(50 row(s) affected)
Table 'Posts'. Scan count 43217677, logical reads 172988050, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 5, logical reads 12692, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 701344 ms,  elapsed time = 192167 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

我在这里有三个问题:

  1. 运算符“持续扫描”指的是什么?
  2. 为什么行估计在以下块中可能不正确:

一个。

在此处输入图像描述

b. 在此处输入图像描述

  1. 怎样做才能使估计更准确?要考虑提高查询性能的索引/技术是什么?

任何提示将不胜感激。

sql-server performance
  • 3 个回答
  • 1088 Views
Martin Hope
George K
Asked: 2017-09-23 01:46:38 +0800 CST

'SELECT TOP' 表现问题

  • 18

我有一个查询,它使用 select 运行得更快,top 100而没有top 100. 返回的记录数为0。您能否解释一下查询计划中的差异或分享解释这种差异的链接?

没有top文字的查询:

SELECT --TOP 100
*
FROM InventTrans
     JOIN
     InventDim
     ON InventDim.DATAAREAID = 'dat' AND 
        InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND 
      InventTrans.ITEMID = '027743' AND 
      InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND 
      InventDim.ECC_BUSINESSUNITID = 'Казахстан';

上述查询计划(不带top):

https://pastebin.com/cbtJpxFf

在此处输入图像描述

IO 和 TIME 统计信息(不带top):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'INVENTDIM'. Scan count 0, logical reads 988297, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 1, logical reads 1234560, physical reads 0, read-ahead reads 14299, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 6256 ms,  elapsed time = 13348 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

使用的索引(不带top):

1. INVENTTRANS.I_177TRANSIDIDX
   4 KEYS:
 - DATAAREAID
 - INVENTTRANSID
 - INVENTDIMID
 - RECID
2. INVENTTRANS.I_177ITEMIDX
   3 KEYS:
   - DATAAREAID
   - ITEMID
   - DATEPHYSICAL 
3. INVENTDIM.I_698DIMIDIDX
   2 KEYS:
   - DATAAREAID
   - INVENTDIMID

查询top:

SELECT TOP 100
*
FROM InventTrans
     JOIN
     InventDim
     ON InventDim.DATAAREAID = 'dat' AND 
        InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND 
      InventTrans.ITEMID = '027743' AND 
      InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND 
      InventDim.ECC_BUSINESSUNITID = 'Казахстан';

查询计划(带TOP):

https://pastebin.com/0dyu6QZd

在此处输入图像描述


查询 IO 和 TIME 统计信息(带 TOP):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 15385, logical reads 82542, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTDIM'. Scan count 1, logical reads 62704, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 265 ms,  elapsed time = 257 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

使用的索引(带TOP):

 1. INVENTTRANS.I_177TRANSIDIDX
     4 KEYS:
     - DATAAREAID
     - INVENTTRANSID
     - INVENTDIMID
     - RECID
 2. INVENTTRANS.I_177DIMIDIDX
    3 KEYS:
    - DATAAREAID
    - INVENTDIMID
    - ITEMID
 3. INVENTDIM.I_698DIMIDIDX
    2 KEYS:
    - DATAAREAID
    - INVENTDIMID
 4. INVENTDIM.I_698ECC_BUSUNITLOCIDX
    3 KEYS
    - DATAAREAID
    - ECC_BUSINESSUNITID
    - INVENTLOCATIONID

将非常感谢有关该主题的任何帮助!

sql-server t-sql
  • 2 个回答
  • 5378 Views
Martin Hope
George K
Asked: 2017-09-12 00:47:26 +0800 CST

定位 Windows 目录时出现 Ola Hallengren DatabaseBackup 错误

  • 3

需要帮助解决使用Ola Hallengren 的存储过程将数据库备份到共享文件夹的问题。

我有一个在 SQL Server 启动时执行的过程。它运行Ola Hallengren的DatabaseBackup程序。SQL Server 服务在NT Service.

共享对运行 SQL 的计算机具有权限。

现在,如果DatabaseBackup手动执行它就可以了。当它在启动时自动执行时,它会失败并出现以下错误:

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 786 [Batch Start Line 0] 目录 \\*** 不存在

如果我用一个子句更改过程Execute As DBO,它会失败并出现同样的错误。

我还尝试Alter了 SP withExecute As Self子句 - 再次出现同样的错误。

所以问题是:

我应该怎么做才能让它工作?

  • 文件路径作为 UNC 路径提供(例如\\machine2\backup\)
  • 这是 SQL Express,所以我无权访问 SQL 代理作业。
  • 因此,这是配置为在 SQL Server 实例启动时运行的存储过程,而不是设置为在 SQL 代理启动时运行的 SQL 作业。
sql-server security
  • 2 个回答
  • 5042 Views
Martin Hope
George K
Asked: 2017-05-29 23:58:08 +0800 CST

在多个 SQL Server Express 实例上运行脚本

  • 1

我要求一个解决方案来SQL Server Express按计划在多个实例上执行自定义 SQL 脚本。

我见过的解决方案ApexSQL Propagate没有Redgate SQL Multi Script调度程序,我有超过 170 个实例可以在它们上运行定期作业。

任何有关如何以最佳方式执行预定脚本的建议都将不胜感激。

sql-server
  • 3 个回答
  • 616 Views
Martin Hope
George K
Asked: 2017-05-22 22:34:25 +0800 CST

帮助我调整长时间运行的查询

  • 4

我在从应用程序 (Microsoft Dynamics AX) 执行以下查询时遇到问题:

DECLARE @p1 INT;
SET @p1 = NULL;
DECLARE @p2 INT;
SET @p2 = 0;
DECLARE @p5 INT;
SET @p5 = 2 + 4096;
DECLARE @p6 INT;
SET @p6 = 8193;
DECLARE @p7 INT;
SET @p7 = 0;
EXEC sp_cursorprepexec
     @p1 OUTPUT,
     @p2 OUTPUT,
     N'@P1 nvarchar(5),@P2 int,@P3 nvarchar(5),@P4 int,@P5 nvarchar(5),@P6 nvarchar(5),@P7 datetime,@P8 nvarchar(21),@P9 numeric(28, 12)',
     N'SELECT A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ FROM INVENTTRANS A,INVENTTRANS B,INVENTTABLE C WHERE ((A.DATAAREAID=@P1) AND (A.TRANSTYPE=@P2)) AND ((B.DATAAREAID=@P3) AND (((B.INVENTTRANSID=A.INVENTTRANSIDFATHER) AND (B.TRANSREFID=A.TRANSREFID)) AND (B.TRANSTYPE=@P4))) AND ((C.DATAAREAID=@P5) AND (C.ITEMID=B.ITEMID)) 
AND EXISTS (SELECT ''x'' FROM INVENTSETTLEMENT D WHERE ((D.DATAAREAID=@P6) AND ((((D.TRANSDATE=@P7) AND (D.VOUCHER=@P8)) AND (D.TRANSRECID=A.RECID)) AND (D.COSTAMOUNTADJUSTMENT<>@P9))))
 GROUP BY A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ ORDER BY B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_',
     @p5 OUTPUT,
     @p6 OUTPUT,
     @p7 OUTPUT,
     N'dat',
     8,
     N'dat',
     2,
     N'dat',
     N'dat',
     '2017-05-17 00:00:00.000',
     N'IM17008141934',
     '0.000000000000';
EXEC sp_cursorfetch
     @p2,
     2,
     1,
     1;

此查询的估计计划在这里预计计划

此处为 XML 版本

查询持续运行了将近 3 个小时。我还为此查询会话从 WhoIsActive sp 捕获了指标:

wait_info                       (2ms)PAGEIOLATCH_SH:ecc_wrk:4(*)     
CPU                             88,765
CPU_delta                       47
tempdb_allocations              0
tempdb_current                  0
tempdb_allocations_delta        0   
tempdb_current_delta            0
blocking_session_id             NULL
blocked_session_count           16
reads                           26,610,593
reads_delta                     20,903
writes                          0
writes_delta                    0
physical_reads                  1,212,764
physical_reads_delta            418 
used_memory used_memory_delta   276 
status                          0
open_tran_count                 1
host_name                       ***
database_name                   ***
program_name                    Microsoft Dynamics AX

如我所见,它读取大量数据并执行大量时间。我还捕获了查询并在 management studio 中执行了它,只用了 1 秒就完成了。实际的执行计划在这里:在此处输入图像描述

此处为 XML 版本

我不明白我可能会遇到什么问题以及在哪里挖掘。对于下一步该做什么的任何帮助,我们将不胜感激。

sql-server sql-server-2008-r2
  • 2 个回答
  • 810 Views
Martin Hope
George K
Asked: 2017-04-01 00:01:54 +0800 CST

设计视图时的 TOP (100) PERCENT

  • 6

设计视图时(添加 Order By 子句),SSMS 中的向导会自动添加

前 (100) 百分比

我们需要它做什么?关于 TOP (100) PERCENT 是否有任何性能考虑?

谢谢

sql-server
  • 1 个回答
  • 4966 Views

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

    连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目

    • 12 个回答
  • Marko Smith

    如何让sqlplus的输出出现在一行中?

    • 3 个回答
  • Marko Smith

    选择具有最大日期或最晚日期的日期

    • 3 个回答
  • Marko Smith

    如何列出 PostgreSQL 中的所有模式?

    • 4 个回答
  • Marko Smith

    列出指定表的所有列

    • 5 个回答
  • Marko Smith

    如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

    • 4 个回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

    如何从 PostgreSQL 中的选择查询中将值插入表中?

    • 4 个回答
  • Marko Smith

    如何使用 psql 列出所有数据库和表?

    • 7 个回答
  • Martin Hope
    Jin 连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane 如何列出 PostgreSQL 中的所有模式? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh 为什么事务日志不断增长或空间不足? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland 列出指定表的所有列 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney MySQL 能否合理地对数十亿行执行查询? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx 如何监控大型 .sql 文件的导入进度? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve