1 / 8

Extreme Visualization of Query Optimizer Search Spaces in Data Management

This paper presents innovative techniques for visualizing the complex search spaces of query optimizers, aiming to improve the understanding and efficiency of database query planning. Through extreme visualization methods, the authors seek to uncover hidden relationships and patterns within the search space, enabling better decision-making when optimizing queries. The research, presented at the ACM SIGMOD International Conference, provides insights into how visualization can facilitate comprehension of the intricate processes of query optimization and ultimately lead to improved performance in database systems.

caden
Download Presentation

Extreme Visualization of Query Optimizer Search Spaces in Data Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SearchSpaceAnalyzer AnisoaraNica, Daniel S. Brotherston, David W. Hillis “Extreme Visualization of Query Optimizer Search Spaces”, ACM SIGMOD International Conference on Management of Data, SIGMOD 2009, 1067-1070

  2. AnisoaraNica, Daniel S. Brotherston, David W. Hillis “Extreme Visualization of Query Optimizer Search Spaces”, ACM SIGMOD International Conference on Management of Data, SIGMOD 2009, 1067-1070

  3. Generated Queries: Object Relational Mapping (ORM) Q1 = SELECT [Project9].[ID] AS [ID],[Project9].[C1] AS [C1],[Project9].[C2] AS [C2],[Project9].[ID1] AS [ID1],[Project9].[SalesOrderID] AS [SalesOrderID], [Project9].[TotalDue] AS [TotalDue] FROM ( SELECT         [Distinct1].[ID] AS [ID],        1 AS [C1],        [Project8].[ID] AS [ID1],        [Project8].[SalesOrderID] AS [SalesOrderID],         [Project8].[TotalDue] AS [TotalDue],        [Project8].[C1] AS [C2]         FROM  (SELECT DISTINCT   [Extent1].[ID] AS [ID]             FROM  [DBA].[Person] AS [Extent1]                 INNER JOIN [DBA].[Sales] AS [Extent2] ON  EXISTS (SELECT   cast(1 as bit) AS [C1]                         FROM    ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]                         LEFT OUTER JOIN  (SELECT [Extent3].[ID] AS [ID]                                 FROM [DBA].[Person] AS [Extent3]  WHERE [Extent2].[ID] = [Extent3].[ID] )AS [Project1] ON cast(1 as bit) = cast(1 as bit)                         LEFT OUTER JOIN  (SELECT  [Extent4].[ID] AS [ID]                                 FROM [DBA].[Person] AS [Extent4]  WHERE [Extent2].[ID] = [Extent4].[ID] ) AS [Project2] ON cast(1 as bit) = cast(1 as bit) WHERE ([Extent1].[ID] = [Project1].[ID]) OR (([Extent1].[ID] IS NULL) AND ([Project2].[ID] IS NULL))  ) ) AS [Distinct1] LEFT OUTER JOIN  (SELECT [Extent5].[ID] AS [ID],  [Extent6].[SalesOrderID] AS [SalesOrderID], [Extent6].[TotalDue] AS [TotalDue],   1 AS [C1]                 FROM  [DBA].[Person] AS [Extent5]            INNER JOIN [DBA].[Sales] AS [Extent6] ON  EXISTS (SELECT    cast(1 as bit) AS [C1]                         FROM    ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable2]                         LEFT OUTER JOIN  (SELECT [Extent9].[ID] AS [ID]                                 FROM [DBA].[Person] AS [Extent9]  WHERE [Extent6].[ID] = [Extent9].[ID] )AS [Project5] ON cast(1 as bit) = cast(1 as bit)                         LEFT OUTER JOIN  (SELECT  [Extent8].[ID] AS [ID]                                 FROM [DBA].[Person] AS [Extent8]  WHERE [Extent6].[ID] = [Extent8].[ID] )AS [Project6] ON cast(1 as bit) = cast(1 as bit) WHERE ([Extent5].[ID] = [Project5].[ID]) OR (([Extent5].[ID] IS NULL) AND ([Project6].[ID] IS NULL))                 ) ) AS [Project8] ON ([Project8].[ID] = [Distinct1].[ID]) OR (([Project8].[ID] IS NULL) AND ([Distinct1].[ID] IS NULL)) )  AS [Project9] ORDER BY [Project9].[ID] ASC, [Project9].[C2] ASC

  4. Generated Queries: ORM (cont.) W = SELECT DISTINCT   [Extent1].[ID] AS [ID]                 FROM  [DBA].[Person] AS [Extent1]                 INNER JOIN [DBA].[Sales] AS [Extent2] ON  EXISTS (SELECT   cast(1 as bit) AS [C1]                         FROM    ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]                         LEFT OUTER JOIN  (SELECT [Extent3].[ID] AS [ID]                                 FROM [DBA].[Person] AS [Extent3]  WHERE [Extent2].[ID] = [Extent3].[ID] )AS [Project1] ON cast(1 as bit) = cast(1 as bit)                         LEFT OUTER JOIN  (SELECT  [Extent4].[ID] AS [ID]                                 FROM [DBA].[Person] AS [Extent4]  WHERE [Extent2].[ID] = [Extent4].[ID] ) AS [Project2] ON cast(1 as bit) = cast(1 as bit) WHERE ([Extent1].[ID] = [Project1].[ID]) OR (([Extent1].[ID] IS NULL) AND ([Project2].[ID] IS NULL))  ) Q1 = SELECT [Project9].[ID] AS [ID],[Project9].[C1] AS [C1],[Project9].[C2] AS [C2],[Project9].[ID1] AS [ID1],[Project9].[SalesOrderID] AS [SalesOrderID],[Project9].[TotalDue] AS [TotalDue] FROM ( SELECT         [Distinct1].[ID] AS [ID],        1 AS [C1],        [Project8].[ID] AS [ID1],        [Project8].[SalesOrderID] AS [SalesOrderID],         [Project8].[TotalDue] AS [TotalDue],        [Project8].[C1] AS [C2]         FROM   ( W ) AS [Distinct1]   LEFT OUTER JOIN  ( W ) AS [Project8] ON ([Project8].[ID] = [Distinct1].[ID]) OR (([Project8].[ID] IS NULL) AND ([Distinct1].[ID] IS NULL)) )  AS [Project9] ORDER BY [Project9].[ID] ASC, [Project9].[C2] ASC

  5. Generated Queries: ORM (cont.) Q1 = selectExtent6.ID as, 1 as C1, 1 as C2, Extent6.ID as ID1, Extent6.SalesOrderID as SalesOrderID, Extent6.TotalDue as TotalDue from DBA.Sales as Extent6 order by Extent6.ID as ID asc

  6. Generated Queries: Hibernate Q2 = SELECT DISTINCT Device.ID,Device.Name FROM Device WHERE Device.ID IN (SELECT DISTINCT Device.ID FROM Device WHERE Device.ID IN (SELECT DISTINCT Device.ID FROM Device WHERE Device.ID IN (SELECT DISTINCT Device.ID FROM Device WHERE Device.ID IN (SELECT DISTINCT Device.ID FROM Device WHERE Device.ID IN (SELECT DISTINCT Device.ID FROM Device WHERE Device.ID IN (SELECT DISTINCT Device.ID FROM Device WHERE Device.ID IN (SELECT Device.ID FROM Device,Entry WHERE Device.dbId = Entry.dd AND UPPER(Entry.ss) LIKE '%192.16.%') OR Device.ID IN ( ( SELECT ID FROM Device WHERE ( UPPER(Device.sName) LIKE '%SSMC%' ) ) ) ) OR Device.ID IN ( ( SELECT ID FROM Device WHERE ( UPPER(Device.sName) LIKE '%SSTB%' ) ) ) ) OR Device.ID IN ( ( SELECT dcrDeviceId FROM Device WHERE ( UPPER(Device.sName) LIKE '%XHFH%' ) ) ) ) OR Device.ID IN ( ( SELECT ID FROM Device WHERE ( UPPER(Device.Name) LIKE '%SSTB%' ) ) ) ) OR Device.ID IN ( ( SELECT ID FROM Device WHERE ( UPPER(Device.Name) LIKE '%XHFH%' ) ) ) ) OR Device.ID IN ( ( SELECT Device.ID FROM Device,Entry WHERE ( ( Device.dbId = Entry.dd ) ) AND ( UPPER(Entry.ss) LIKE '%129.196.9.%' ) ) ) )

  7. Generated Queries: Hibernate (cont.) sq(c0,c1): select DISTINCT T.X from T where T.X IN (select T.X from T JOIN R where R.Y like c0) or T.X IN (select T.X from T where T.Y like c1) sq(co,c1,…,cn): select DISTINCT T.X from T where T.X IN ( sub(c0,c1,..,c(n-1)) ) or T.X IN (select T.X from T where T.Y like cn) sq('%192.16.%‘, '%SSMC%, '%SSTB%‘, '%XHFH%, '%SSTB%‘, '%XHFH%‘, '%129.196.9.%‘)

  8. Generated Queries: Hibernate (cont.) Q2 = SELECT DISTINCT W0.ID,W0.Name FROM Device W0 WHERE ( W0.ID IS NOT NULL and ( W0.ID IN (SELECT W9.ID FROM Device W9,Entry WHERE W9.dbId = Entry.dd AND UPPER(Entry.ss) LIKE '%192.16.%') ) OR ( UPPER(W0.sName) LIKE '%SSMC%' ) OR ( UPPER(W0.sName) LIKE '%SSTB%' ) OR ( UPPER(W0.sName) LIKE '%XHFH%' ) OR (UPPER(W0.Name) LIKE '%SSTB%' ) OR (UPPER(W0.Name) LIKE '%XHFH%' ) OR W0.ID IN ( ( SELECT W12.ID FROM Device W12,Entry WHERE W12.dbId = Entry.dd AND UPPER(Entry.ss) LIKE '%129.196.9.%' )

More Related