Databases
StackOverflow2013
Postsdbo
POSpecific SQL Query Optimization Help Needed
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSpecific SQL Query Optimization Help Needed
    primarykey
    data
    text
    <p>So I'm working on a data mining project where we're looking at code elements and their relationships and changes to these things over time. What we want is to ask some questions about how often related elements are changed. I've set it up as a view, but it's taking like 10 min to run. I believe the problem is that I'm having to do a lot of subtraction, concatenation, and string comparisons to compare entries (for our window size), but I don't know a good way to fix this. The query looks like</p> <pre><code>select aw.same , rw.k , count(distint concat_ws(',', r1.id, r2.id)) as num from deltamethoddeclaration dmd1 join revision r1 on r1.id=FKrevID join methodinvocation mi on mi.FKcallerID = dmd1.FKMDID join deltamethoddeclaration dmd2 on mi.FKcalleeID = dmd2.FKMDID join revision r2 on r2.id = dmd2.FKrevID join revisionwindow rw join authorwindow aw where (dmd1.FKrevID - dmd2.FKrevID) &lt; rw.k and (dmd2.FKrevID - dmd1.FKrevID) &lt; rw.k and case aw.same when 1 then r1.author = r2.author when 0 then r1.author &lt;&gt; r2.author else 1=1 end group by aw.same , rw.k ; </code></pre> <p>Ok, so revisionwindow stores the revision windows we're interested in (10, 20, 50, 100) and authorwindow stores which author types we want (same, different, and don't care). Part of the problem is, we could have the same revision pair with different elements matching, so the only hack i could come up with was that ugly count(distinct concat()) thing. This should return a table with 12 rows, one for each combination of the author and revision windows. The entries under 'num' are the unique pairs of revisions related in the manner specified (in this case, both change methods and one of the methods calls the other). It works perfectly, it's just crazy slow (~10 min running time). I'm basically looking for any advice or help to make this work better without sacrificing accuracy.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USypercubeᵀᴹ
    1. USKyle P
    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
    1. VO
      singulars
      1. POSpecific SQL Query Optimization Help Needed
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. CODo you have indexes for all the primary/foreign keys? How many rows in each of the tables?
      singulars
      1. POSpecific SQL Query Optimization Help Needed
      1. USJim Garrison
    2. COHow many records are there in `methodinvocation`?
      singulars
      1. POSpecific SQL Query Optimization Help Needed
      1. USQuassnoi
    3. COAll keys are indexed. There are about 110K entries in the methodinvocation table. deltamethoddeclaration has 120K entries, and revision has 14K
      singulars
      1. POSpecific SQL Query Optimization Help Needed
      1. USKyle P
 

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