Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Since this is your version control, I am guessing you probably don't have more than a couple hundred thousand rows in the table. It wouldn't be the end of the world to run through both tables, but like Peter Schott says, it would be good index Revision:</p> <pre><code>CREATE nonclustered index &lt;name&gt; on SubversionLog (revision) CREATE nonclustered index &lt;name&gt; on PathsLog (revision) </code></pre> <p>From personal experience, I don't think that doing a few string manipulations is severely slowing you down. I do think creating multiple temporary tables is slowing you down, since you are creating a new row for most rows in your original tables, and your temp tables are not indexed. Hence, I recommend getting rid of those temporary tables, and simplifying your query:</p> <pre><code>;with filteredData as ( SELECT [path], Substring([path], 0, Len([path]) - Charindex('/', Reverse([path])) + 1) AS [Directory], CONVERT(CHAR(4), [logdate], 120) + '-' + CONVERT(CHAR(2), [logdate], 110) AS [Month] FROM [subversionlog] JOIN [pathslog] ON [subversionlog].[revision] = [pathslog].[revision] WHERE [path] LIKE '/%/%/%/_%' ), countRevisions as ( SELECT [month], [directory], count(*) as [Count] FROM filteredData GROUP BY [MONTH], [directory] ), rankDirectories as ( SELECT *, RANK() over (partition by month order by count desc) as [Rank] from countRevisions ) select [month], [directory], [count] from rankDirectories WHERE [rank] = 1 </code></pre> <p>EDIT:</p> <p>I don't think there's much more you can do without caching some results. You should take a look at the query plan to see what needs to be optimized. I am guessing it's in the grouping, ordering, and/or ranking, possibly joining or key lookup. For the key lookup and joining, you can create covering indices. For the other stuff, you'll need to cache the results. I would not cache with a real table since that would mean an extra table to keep up to date. Instead, I would use a materialized view so that SQL Server keeps it updated for you. Sure this means that updates would be slower, but at a few updates a minute (for a source control log), I don't think this would be a huge problem.</p>
    singulars
    1. This table or related slice is empty.
    1. POOptimization - Finding the most committed to subversion directory month by month
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJohn Tseng
    1. USJohn Tseng
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOptimization - Finding the most committed to subversion directory month by month
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COWow, thank you very much. You made my query about 20x faster.
      singulars
      1. PO
      1. USsirdank
    2. CO@sirdank 20x faster than a few minutes means that the query still takes more than a few seconds, right? I added a few more suggestions that should bring the query down to less than a second.
      singulars
      1. PO
      1. USJohn Tseng
 

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