Databases
StackOverflow2013
Postsdbo
POsql server parent-child join and slow query performance
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POsql server parent-child join and slow query performance
    primarykey
    data
    text
    <p>I have 2 tables (cannot change them)</p> <pre><code>Parent (id, date, amount) Child (parent_id, key, value) </code></pre> <p>indexes</p> <pre><code>Parent.pk (id) Parent.idx1 (id, date) include (amount) Child.pk (parent_id, key) Child.idx1 (parent_id, key, value) </code></pre> <p>and query</p> <pre><code>select sum(amount) from Parent as p left outer join Child as c1 on c1.parent_id = p.id and c1.key = 'X' left outer join Child as c2 on c2.parent_id = p.id and c2.key = 'Y' where p.date between '20120101' and '20120131' and c1.value = 'x1' and c2.value = 'y1' </code></pre> <p>Problem is performance.<br> Parent has ~1 500 000 records and Child ~6 000 000 records<br></p> <p><strong>Take 1</strong></p> <p>This query takes ~3sec which is too much for my scenario - it must be <strong>less than few milliseconds</strong>.</p> <p>Execution plan shows me that SQL Server is doing index scan on <code>Parent.idx1</code> and than merge join with <code>Child.idx1</code> clustered index seek - which is not optimal because it scans whole 1500000 records even when I filter them by date.</p> <p><strong>Take 2</strong></p> <p>When I change <code>Parent.idx1</code> to </p> <pre><code>Parent.idx1 (date, id) include (amount) </code></pre> <p>Sql server chooses Clustered index scan on <code>Parent.pk</code> and than again merge join with <code>Child.idx1</code>. Execution time is ~6s.</p> <p><strong>Take 3</strong></p> <p>When I force it to use <code>Parent.idx1 (date, id) include (amount)</code> then it sorts the result before merge join and execution time is even worse ~11s.</p> <p><strong>Take 4</strong></p> <p>Tried to create indexed view but cannot use it because of LEFT OUTER JOIN.</p> <p><strong>Is there any way to make such query - Parent-Child join with filters on both of them - faster?</strong><br> Without de-normalization.</p> <p><strong>Update 2013-07-04:</strong><BR> To those answering use INNER JOIN - Yes it's much faster, but I cannot use it.<br> What I showed here is simplified version of what I really need.<br> I need to create SQL View for MS Dynamics NAV "G/L Entry" (Parent) and "Ledger Entry Dimension" (Child) tables so that I will be able to read it from that application. Complete view looks like this right now:</p> <pre><code>create view analysis as select v.id as view_id , p.date , p.Amount , c1.value as value1 , c2.value as value2 , c3.value as value3 , c4.value as value4 from Parent as p cross join analysis_view as v left outer join Child as c1 on c1.parent_id = p.id and c1.key = v.key1 left outer join Child as c2 on c2.parent_id = p.id and c2.key = v.key2 left outer join Child as c3 on c3.parent_id = p.id and c3.key = v.key3 left outer join Child as c4 on c4.parent_id = p.id and c4.key = v.key4 </code></pre> <p>where analysis_view contains 8 records currently and looks like this: <code>analysis_view (id, key1, key2, key3, key4)</code><br> and then aplication may query it like this</p> <pre><code>select sum(amount) from analysis where view_id = 1 and date between '20120101' and '20120131' and value1 = 'x1' and value2 = 'x2' </code></pre> <p>or</p> <pre><code>select sum(amount) from analysis where view_id = 1 and date between '20120101' and '20120131' and value1 = 'x1' and value3 = 'z1' </code></pre> <p>MS Dynamics NAV already have de-normalized table for it and queries from it are fast, but it's huge in our case (~10GB) and locks the whole system for around one hour when somone creates new analysis view. Also NAV doesn't know how to produce joins, that's why I must define it on SQL Server side.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSeeR
    1. USSeeR
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre 'X' and 'Y' the only values of `key`?
      singulars
      1. POsql server parent-child join and slow query performance
      1. USHart CO
    2. COAre you supposed to be counting the amount for a record twice if it has both a child with key of X and a key of Y? Or are you supposed to use the amount for summing if it has a child of either X or Y? Because you are doing the first, but the second is much more common.
      singulars
      1. POsql server parent-child join and slow query performance
      1. USRobert McKee
    3. CO"Less than a few milliseconds" implies all the required data will be in cache or any I/O will be done to SSDs, perhaps in a RAID set to improve read bandwidth.
      singulars
      1. POsql server parent-child join and slow query performance
      1. USHABO
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload