Databases
StackOverflow2013
Postsdbo
POHow to optimize painfully slow SQL query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to optimize painfully slow SQL query?
    primarykey
    data
    text
    <p>I have two tables. The first 'user' table, consists of rows that record login events from a user. The table looks something like this:</p> <pre><code>LoginTable LoginID Name Time 100 Joe 12:00pm 101 Bob 3:30pm 102 Joe 4:00pm 103 Sue 6:15pm </code></pre> <p>The second table records additional metrics about each login, tho not common, the number of additional items recorded could change. The table looks something like this:</p> <pre><code>MetricsTable MetricID LoginID Label Data 500 100 IPAddress 1.2.3.4 501 100 Attempts 25 502 100 Status Good 503 101 IPAddress 1.2.3.5 504 101 Attempts 19 505 101 Status Bad 506 102 IPAddress 1.2.3.6 507 102 Attempts 35 508 102 Status Hold 509 103 IPAddress 1.2.3.7 510 103 Attempts 4 511 103 Status Trial </code></pre> <p>I am trying to build a query of the LoginTable that would for each row, show additional columns representing the extra metrics data associated with each login event.</p> <p>One solution is to do something like this:</p> <pre><code>SELECT LoginTable.LoginID, LoginTable.Name, LoginTable.Time, (SELECT MetricsTable.Data FROM MetricsTable WHERE MetricsTable.LoginID=LoginTable.LoginID and MetricsTable.Label="IPAddress") as IPAddressEx FROM LoginTable </code></pre> <p>That query would show one extra column that shows the 'IPAddress' value recorded for that event. I could then add 2 more sub queries like that to show additional columns for 'Attempts' and 'Status' (note these are just fake examples). I am not concerned about being able to automatically identify there are more potential columns, I can add them manually if they come along. However this technique of adding a sub query for each extra column appears to be painfully slow. </p> <p>What is a better way to accomplish this?</p> <p>Please assume that I cannot restructure the tables.</p> <p>Thanks!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USsthede
    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. VO
      singulars
      1. POHow to optimize painfully slow SQL query?
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POHow to optimize painfully slow SQL query?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to optimize painfully slow SQL query?
      1. This table or related slice is empty.
      1. VTDownMod
    1. COYou didn't mention indexes. Do you have any, on which columns, and when were they last rebuilt/reorganized? Are statistics up to date?
      singulars
      1. POHow to optimize painfully slow SQL query?
      1. USp.campbell
    2. COCan you show the explain plan for the query?
      singulars
      1. POHow to optimize painfully slow SQL query?
      1. USSam D
    3. COWhy can't you simply join the two tables?
      singulars
      1. POHow to optimize painfully slow SQL query?
      1. USDan Bracuk
 

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