小型数据库的超慢查询 [英] Super Slow Query on Small Database

问题描述

我对一个相对较小的数据库进行查询处理12小时后无法返回结果！我尝试了不同类型的索引，只有
次要影响（当我将结果时间段（where子句）减少到十五年后的一年）是否有大量的连接导致这种情况？任何帮助/建议都非常感谢！

查询附件

 SELECT 
 bo2_b。[日期]，
 AD_B。[关闭] AS AD_B，
 BAX_B。[关闭] AS BAX_B，
 BO2_B。[关闭] AS BO2_B，
 BP_B。[关闭] AS BP_B ，
 C2_B。[关闭] AS C2_B，
 CC2_B。[关闭] AS CC2_B，
 CD_B。[关闭] AS CD_B，
 CL2_B。[关闭] AS CL2_B，
 CT_B。[关闭] AS CT_B，
 CU_B。[关闭] AS CU_B，
 DX2_B。[关闭] AS DX2_B，
 EBL_B。[关闭] AS EBL_B，
 EBM_B。[关闭] AS EBM_B，
 ED_B。[关闭] AS ED_B，
 ES_B。[关闭] AS ES_B，
 FCH_B。[关闭] AS FCH_B，
 FDX_B 。[关闭] AS FDX_B，
 FEI_B。[关闭] AS FEI_B，
 FFI_B。[关闭] AS FFI_B，
 FLG_B。[关闭] AS FLG_B，
 FSS_B。[关闭] AS FSS_B，
 FV_B。[关闭] AS FV_B，
 GC2_B。[关闭] AS GC2_B，
 HG2_B。[关闭] AS HG2_B，
 HMH_B。[close] AS HMH_B，
 HO2_B。[close] AS HO2_B，
 JAU_B。[close] AS JAU_B，
 JGL_B。[close] AS JGL_B，
 JPL_B。 [关闭] AS JPL_B，
 JRU_B。[关闭] AS JRU_B，
 JY_B。[关闭] AS JY_B，
 KC_B。[关闭] AS KC_B，
 KPO_B。[关闭] ] AS KPO_B，
 LCO_B。[关闭] AS LCO_B，
 LC_B。[关闭] AS LC_B，
 LGO_B。[关闭] AS LGO_B，
 LH_B。[close] AS LH_B，
 MFX_B。[关闭] AS MFX_B，
 MP_B。[关闭] AS MP_B，
 NG2_B。[关闭] AS NG2_B，
 NQ_B。[关闭] AS NQ_B， 
 RB2_B。[关闭] AS RB2_B，
 RS_B。[关闭] AS RS_B，
 S2_B。[关闭] AS S2_B，
 SB2_B。[关闭] AS SB2_B，
 SF_B。[关闭] AS SF_B，
 SI2_B。[关闭] AS SI2_B，
 SM2_B。[clo se] AS SM2_B，
 SSI_B。[close] AS SSI_B，
 STW_B。[close] AS STW_B，
 SXE_B。[close] AS SXE_B，
 S_B。[close] AS S_B，
 TU_B。[close] AS TU_B，
 TY_B。[close] AS TY_B，
 US_B。[close] AS US_B，
 W2_B。[close] AS W2_B 
 FROM 
 BO2_B 
 JOIN 
 AD_B 
 ON bo2_b.date = AD_B.date 
 JOIN 
 BAX_B 
 ON bo2_b.date = BAX_B.date 
 JOIN 
 BP_B 
 ON bo2_b.date = BP_B.date 
 JOIN 
 C2_B 
 ON bo2_b.date = C2_B.date 
 JOIN 
 CC2_B 
 ON bo2_b.date = CC2_B.date 
 JOIN 
 CD_B 
 ON bo2_b.date = CD_B.date 
 JOIN 
 CL2_B 
 ON bo2_b.date = CL2_B.date 
 JOIN 
 CT_B 
 ON bo2_b.date = CT_B.date 
 JOIN 
 CU_B 
ON bo2_b.date = CU_B.date 
 JOIN 
 DX2_B 
 ON bo2_b.date = DX2_B.date 
 JOIN 
 EBL_B 
 ON bo2_b.date = EBL_B .date 
 JOIN 
 EBM_B 
 ON bo2_b.date = EBM_B.date 
 JOIN 
 ED_B 
 ON bo2_b.date = ED_B.date 
 JOIN 
 ES_B 
 ON bo2_b.date = ES_B.date 
 JOIN 
 FCH_B 
 ON bo2_b.date = FCH_B.date 
 JOIN 
 FDX_B 
 ON bo2_b.date = FDX_B.date 
 JOIN 
 FEI_B 
 ON bo2_b.date = FEI_B.date 
 JOIN 
 FFI_B 
 ON bo2_b .date = FFI_B.date 
 JOIN 
 FLG_B 
 ON bo2_b.date = FLG_B.date 
 JOIN 
 FSS_B 
 ON bo2_b.date = FSS_B.date 
 JOIN 
 FV_B 
 ON bo2_b.date = FV_B.date 
 JOIN 
 GC2_B 
 ON bo2_b.date = GC2_B.date 
 JOIN 
 HG2_B 
 ON bo2_b.date = HG2_B.date 
 JOIN 
 HMH_B 
 ON bo2_b.date = HMH_B.date 
 JOIN 
 HO2_B 
 ON bo2_b.date = HO2_B.date 
 JOIN 
 JAU_B 
 ON bo2_b.date = JAU_B。 date 
 JOIN 
 JGL_B 
 ON bo2_b.date = JGL_B.date 
 JOIN 
 JPL_B 
 ON bo2_b.date = JPL_B.date 
 JOIN 
 JRU_B 
 ON bo2_b.date = JRU_B.date 
 JOIN 
 JY_B 
 ON bo2_b.date = JY_B.date 
 JOIN 
 KC_B 
 ON bo2_b.date = KC_B.date 
 JOIN 
 KPO_B 
 ON bo2_b.date = KPO_B.date 
 JOIN 
 LCO_B 
 ON bo2_b。 date = LCO_B.date 
 JOIN 
 LC_B 
 ON bo2_b.date = LC_B.date 
 JOIN 
 LGO_B 
 ON bo2_b.date = LGO_B.date 
 JOIN 
 LH_B 
 ON bo2_b.date = LH_B.date 
 JOIN 
 MFX_B 
 ON bo2_b.date = MFX_B.date 
 JOIN 
 MP_B 
 ON bo2_b.date = MP_B.date 
 JOIN 
 NG2_B 
 ON bo2_b .date = NG2_B.date 
 JOIN 
 NQ_B 
 ON bo2_b.date = NQ_B.date 
 JOIN 
 RB2_B 
 ON bo2_b.date = RB2_B.date 
 JOIN 
 RS_B 
 ON bo2_b.date = RS_B.date 
 JOIN 
 S2_B 
 ON bo2_b.date = S2_B.date 
 JOIN 
 SB2_B 
 ON bo2_b.date = SB2_B.date 
 JOIN 
 SF_B 
 ON bo2_b.date = SF_B.date 
 JOIN 
 SI2_B 
 ON bo2_b.date = SI2_B.date 
 JOIN 
 SM2_B 
 ON bo2_b.date = SM2 _B.date 
 JOIN 
 SSI_B 
 ON bo2_b.date = SSI_B.date 
 JOIN 
 STW_B 
 ON bo2_b.date = STW_B.date 
 JOIN 
 SXE_B 
 ON bo2_b.date = SXE_B.date 
 JOIN 
 S_B 
 ON bo2_b.date = S_B.date 
 JOIN 
 TU_B 
 ON bo2_b.date = TU_B.date 
 JOIN 
 TY_B 
 ON bo2_b.date = TY_B.date 
 JOIN 
 US_B 
 ON bo2_b.date = US_B.date 
 JOIN 
 W2_B 
 ON bo2_b.date = W2_B.date 

解决方案

我对一个相对较小的数据库进行查询，该数据库在处理12小时后无法返回结果！我尝试了不同类型的索引，只有
次要影响（当我减少结果时时间段（where子句）到十五年的一年）是否有大量的连接导致这种情况？非常感谢任何帮助/建议！

美好的一天，

>>  是否有大量的连接引起这种情况？

可能是

>>   非常感谢任何帮助/建议！

1.你应该考虑以一种有意义的方式重新设计你的系统你使用数据的方式...

乍一看，似乎你应该有一个包含所有数据的表而不是多个表用于相同的数据结构。 

2.同时，

2.1确保每个表中的列[date]都有索引，并且包含列[close]。

2.1确保按照每个表返回的行数的顺序使用表JOIN。当连接数量很高时SQL Server查询引擎通常使用JOIN的顺序，因为它在查询文本中。

3.我们需要有关表结构的更多信息，包括现有的INDEX和您获得的执行计划（XML完整  ;真正的执行计划，而不仅仅是执行计划的图像）

I have a query on a relatively small database that could not return the results after 12 hours of processing! I've tried different types of indexing with only minor impact (when I reduce the results time period (where clause) to a year from fifteen years) Is it a large number of joins causing this? Any help/suggestions greatly appreciated!

Query attached

SELECT
        bo2_b.[date],
        AD_B.[close] AS AD_B,
        BAX_B.[close] AS BAX_B,
        BO2_B.[close] AS BO2_B,
        BP_B.[close] AS BP_B,
        C2_B.[close] AS C2_B,
        CC2_B.[close] AS CC2_B,
        CD_B.[close] AS CD_B,
        CL2_B.[close] AS CL2_B,
        CT_B.[close] AS CT_B,
        CU_B.[close] AS CU_B,
        DX2_B.[close] AS DX2_B,
        EBL_B.[close] AS EBL_B,
        EBM_B.[close] AS EBM_B,
        ED_B.[close] AS ED_B,
        ES_B.[close] AS ES_B,
        FCH_B.[close] AS FCH_B,
        FDX_B.[close] AS FDX_B,
        FEI_B.[close] AS FEI_B,
        FFI_B.[close] AS FFI_B,
        FLG_B.[close] AS FLG_B,
        FSS_B.[close] AS FSS_B,
        FV_B.[close] AS FV_B,
        GC2_B.[close] AS GC2_B,
        HG2_B.[close] AS HG2_B,
        HMH_B.[close] AS HMH_B,
        HO2_B.[close] AS HO2_B,
        JAU_B.[close] AS JAU_B,
        JGL_B.[close] AS JGL_B,
        JPL_B.[close] AS JPL_B,
        JRU_B.[close] AS JRU_B,
        JY_B.[close] AS JY_B,
        KC_B.[close] AS KC_B,
        KPO_B.[close] AS KPO_B,
        LCO_B.[close] AS LCO_B,
        LC_B.[close] AS LC_B,
        LGO_B.[close] AS LGO_B,
        LH_B.[close] AS LH_B,
        MFX_B.[close] AS MFX_B,
        MP_B.[close] AS MP_B,
        NG2_B.[close] AS NG2_B,
        NQ_B.[close] AS NQ_B,
        RB2_B.[close] AS RB2_B,
        RS_B.[close] AS RS_B,
        S2_B.[close] AS S2_B,
        SB2_B.[close] AS SB2_B,
        SF_B.[close] AS SF_B,
        SI2_B.[close] AS SI2_B,
        SM2_B.[close] AS SM2_B,
        SSI_B.[close] AS SSI_B,
        STW_B.[close] AS STW_B,
        SXE_B.[close] AS SXE_B,
        S_B.[close] AS S_B,
        TU_B.[close] AS TU_B,
        TY_B.[close] AS TY_B,
        US_B.[close] AS US_B,
        W2_B.[close] AS W2_B 
    FROM
        BO2_B 
    JOIN
        AD_B 
            ON bo2_b.date = AD_B.date 
    JOIN
        BAX_B 
            ON bo2_b.date = BAX_B.date 
    JOIN
        BP_B 
            ON bo2_b.date = BP_B.date 
    JOIN
        C2_B 
            ON bo2_b.date = C2_B.date 
    JOIN
        CC2_B 
            ON bo2_b.date = CC2_B.date 
    JOIN
        CD_B 
            ON bo2_b.date = CD_B.date 
    JOIN
        CL2_B 
            ON bo2_b.date = CL2_B.date 
    JOIN
        CT_B 
            ON bo2_b.date = CT_B.date 
    JOIN
        CU_B 
            ON bo2_b.date = CU_B.date 
    JOIN
        DX2_B 
            ON bo2_b.date = DX2_B.date 
    JOIN
        EBL_B 
            ON bo2_b.date = EBL_B.date 
    JOIN
        EBM_B 
            ON bo2_b.date = EBM_B.date 
    JOIN
        ED_B 
            ON bo2_b.date = ED_B.date 
    JOIN
        ES_B 
            ON bo2_b.date = ES_B.date 
    JOIN
        FCH_B 
            ON bo2_b.date = FCH_B.date 
    JOIN
        FDX_B 
            ON bo2_b.date = FDX_B.date 
    JOIN
        FEI_B 
            ON bo2_b.date = FEI_B.date 
    JOIN
        FFI_B 
            ON bo2_b.date = FFI_B.date 
    JOIN
        FLG_B 
            ON bo2_b.date = FLG_B.date 
    JOIN
        FSS_B 
            ON bo2_b.date = FSS_B.date 
    JOIN
        FV_B 
            ON bo2_b.date = FV_B.date 
    JOIN
        GC2_B 
            ON bo2_b.date = GC2_B.date 
    JOIN
        HG2_B 
            ON bo2_b.date = HG2_B.date 
    JOIN
        HMH_B 
            ON bo2_b.date = HMH_B.date 
    JOIN
        HO2_B 
            ON bo2_b.date = HO2_B.date 
    JOIN
        JAU_B 
            ON bo2_b.date = JAU_B.date 
    JOIN
        JGL_B 
            ON bo2_b.date = JGL_B.date 
    JOIN
        JPL_B 
            ON bo2_b.date = JPL_B.date 
    JOIN
        JRU_B 
            ON bo2_b.date = JRU_B.date 
    JOIN
        JY_B 
            ON bo2_b.date = JY_B.date 
    JOIN
        KC_B 
            ON bo2_b.date = KC_B.date 
    JOIN
        KPO_B 
            ON bo2_b.date = KPO_B.date 
    JOIN
        LCO_B 
            ON bo2_b.date = LCO_B.date 
    JOIN
        LC_B 
            ON bo2_b.date = LC_B.date 
    JOIN
        LGO_B 
            ON bo2_b.date = LGO_B.date 
    JOIN
        LH_B 
            ON bo2_b.date = LH_B.date 
    JOIN
        MFX_B 
            ON bo2_b.date = MFX_B.date 
    JOIN
        MP_B 
            ON bo2_b.date = MP_B.date 
    JOIN
        NG2_B 
            ON bo2_b.date = NG2_B.date 
    JOIN
        NQ_B 
            ON bo2_b.date = NQ_B.date 
    JOIN
        RB2_B 
            ON bo2_b.date = RB2_B.date 
    JOIN
        RS_B 
            ON bo2_b.date = RS_B.date 
    JOIN
        S2_B 
            ON bo2_b.date = S2_B.date 
    JOIN
        SB2_B 
            ON bo2_b.date = SB2_B.date 
    JOIN
        SF_B 
            ON bo2_b.date = SF_B.date 
    JOIN
        SI2_B 
            ON bo2_b.date = SI2_B.date 
    JOIN
        SM2_B 
            ON bo2_b.date = SM2_B.date 
    JOIN
        SSI_B 
            ON bo2_b.date = SSI_B.date 
    JOIN
        STW_B 
            ON bo2_b.date = STW_B.date 
    JOIN
        SXE_B 
            ON bo2_b.date = SXE_B.date 
    JOIN
        S_B 
            ON bo2_b.date = S_B.date 
    JOIN
        TU_B 
            ON bo2_b.date = TU_B.date 
    JOIN
        TY_B 
            ON bo2_b.date = TY_B.date 
    JOIN
        US_B 
            ON bo2_b.date = US_B.date 
    JOIN
        W2_B 
            ON bo2_b.date = W2_B.date

解决方案

I have a query on a relatively small database that could not return the results after 12 hours of processing! I've tried different types of indexing with only minor impact (when I reduce the results time period (where clause) to a year from fifteen years) Is it a large number of joins causing this? Any help/suggestions greatly appreciated!

Good day,

>> Is it a large number of joins causing this?

Probably

这篇关于小型数据库的超慢查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！