×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

I have a problem about SQL query. In this query, there are 5 alias tables inter join with themselves and then inter join with other tables.

本文发表在 rolia.net 枫下论坛In where clause, there is a parameter. Does any body have good idea to speed up the query? Can I use view instead of those inter join? or just use stored procedure? Thanks a lot.
SELECT DComment, DataID, DVersData.[DocID], Version, VersionName, FileName, FileType, FileCDate,
FileMDate, DVersData.[ProviderId],
LLProviderData.[providerID], LLProviderData.[providerData],
LLAttrData_Select.[ID], LLAttrData_Select.[DefID],
LLAttrData_Select.[AttrID], LLAttrData_Select.[ValStr],
LLAttrData_Sheet.[AttrID], LLAttrData_Sheet.[ValStr],
LLAttrData_Version.[AttrID], LLAttrData_Version.[ValStr],
LLAttrData_Title.[AttrID], LLAttrData_Title.[ValStr],
LLAttrData_Title2.[AttrID], LLAttrData_Title2.[ValStr]
FROM { oj (((((([Livelink].[LIVELINK].[LLAttrData] LLAttrData_Select
INNER JOIN
[Livelink].[LIVELINK].[LLAttrData] LLAttrData_Sheet ON
LLAttrData_Select.[ID] = LLAttrData_Sheet.[ID] AND
LLAttrData_Select.[VerNum] = LLAttrData_Sheet.[VerNum])
INNER JOIN
[Livelink].[LIVELINK].[LLAttrData] LLAttrData_version ON
LLAttrData_version.[ID] = LLAttrData_Sheet.[ID] AND
LLAttrData_version.[VerNum] = LLAttrData_Sheet.[VerNum])
INNER JOIN
[Livelink].[LIVELINK].[LLAttrData] LLAttrData_Title ON
LLAttrData_Title.[ID] = LLAttrData_Sheet.[ID] AND
LLAttrData_Title.[VerNum] = LLAttrData_Sheet.[VerNum])
INNER JOIN
[Livelink].[LIVELINK].[LLAttrData] LLAttrData_Title2 ON
LLAttrData_Title2.[ID] = LLAttrData_Sheet.[ID] AND
LLAttrData_Title2.[VerNum] = LLAttrData_Sheet.[VerNum])
INNER JOIN
[Livelink].[LIVELINK].[DVersData] DVersData ON
LLAttrData_Sheet.[ID] = DOCID AND
DVersData.[Version] = LLAttrData_Sheet.[VerNum])
INNER JOIN
[Livelink].[LIVELINK].[ProviderData] LLProviderData ON
LLProviderData.[providerID] = DVersData.[ProviderId])
INNER JOIN
[Livelink].[LIVELINK].[DTree] DTree ON
DTree.[DataID] = LLAttrData_Sheet.[ID] AND
DTree.[VersionNum] = DVersData.[version]}

WHERE LLAttrData_version.[DefID] = LLAttrData_Select.[DefID] AND
LLAttrData_Select.[ValStr] LIKE '%' + UserInput + '%' AND
LLAttrData_Sheet.[DefID] = LLAttrData_Select.[DefID] AND更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / I have a problem about SQL query. In this query, there are 5 alias tables inter join with themselves and then inter join with other tables.
    本文发表在 rolia.net 枫下论坛In where clause, there is a parameter. Does any body have good idea to speed up the query? Can I use view instead of those inter join? or just use stored procedure? Thanks a lot.
    SELECT DComment, DataID, DVersData.[DocID], Version, VersionName, FileName, FileType, FileCDate,
    FileMDate, DVersData.[ProviderId],
    LLProviderData.[providerID], LLProviderData.[providerData],
    LLAttrData_Select.[ID], LLAttrData_Select.[DefID],
    LLAttrData_Select.[AttrID], LLAttrData_Select.[ValStr],
    LLAttrData_Sheet.[AttrID], LLAttrData_Sheet.[ValStr],
    LLAttrData_Version.[AttrID], LLAttrData_Version.[ValStr],
    LLAttrData_Title.[AttrID], LLAttrData_Title.[ValStr],
    LLAttrData_Title2.[AttrID], LLAttrData_Title2.[ValStr]
    FROM { oj (((((([Livelink].[LIVELINK].[LLAttrData] LLAttrData_Select
    INNER JOIN
    [Livelink].[LIVELINK].[LLAttrData] LLAttrData_Sheet ON
    LLAttrData_Select.[ID] = LLAttrData_Sheet.[ID] AND
    LLAttrData_Select.[VerNum] = LLAttrData_Sheet.[VerNum])
    INNER JOIN
    [Livelink].[LIVELINK].[LLAttrData] LLAttrData_version ON
    LLAttrData_version.[ID] = LLAttrData_Sheet.[ID] AND
    LLAttrData_version.[VerNum] = LLAttrData_Sheet.[VerNum])
    INNER JOIN
    [Livelink].[LIVELINK].[LLAttrData] LLAttrData_Title ON
    LLAttrData_Title.[ID] = LLAttrData_Sheet.[ID] AND
    LLAttrData_Title.[VerNum] = LLAttrData_Sheet.[VerNum])
    INNER JOIN
    [Livelink].[LIVELINK].[LLAttrData] LLAttrData_Title2 ON
    LLAttrData_Title2.[ID] = LLAttrData_Sheet.[ID] AND
    LLAttrData_Title2.[VerNum] = LLAttrData_Sheet.[VerNum])
    INNER JOIN
    [Livelink].[LIVELINK].[DVersData] DVersData ON
    LLAttrData_Sheet.[ID] = DOCID AND
    DVersData.[Version] = LLAttrData_Sheet.[VerNum])
    INNER JOIN
    [Livelink].[LIVELINK].[ProviderData] LLProviderData ON
    LLProviderData.[providerID] = DVersData.[ProviderId])
    INNER JOIN
    [Livelink].[LIVELINK].[DTree] DTree ON
    DTree.[DataID] = LLAttrData_Sheet.[ID] AND
    DTree.[VersionNum] = DVersData.[version]}

    WHERE LLAttrData_version.[DefID] = LLAttrData_Select.[DefID] AND
    LLAttrData_Select.[ValStr] LIKE '%' + UserInput + '%' AND
    LLAttrData_Sheet.[DefID] = LLAttrData_Select.[DefID] AND更多精彩文章及讨论,请光临枫下论坛 rolia.net
    • I am pretty new to SQL Server. I really appreciate it if anyone can help me. Thanks again.
    • faint. Is that ANSI SQL. A suggestion. First pass only inlcude all the key, if run well, add all the other non-key field.
    • faint again,if you want to speed up.try to use one key field in each table.