Databases
StackOverflow2013
Postsdbo
POQuery Optimization for multiple query joined by union
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery Optimization for multiple query joined by union
    primarykey
    data
    text
    <p>I'm trying to find a way to enhance my SQL Query. Can someone recommend me the best possible way to optimize this query. In short I want to make it faster. This query is used to count the number of people currently inside the grandstand per <strong>up to this HOUR</strong> basis. I have to get the <strong>max LogDatetime</strong> of each ticket until that hour and then join it to the base view so I could get the <strong>Log Information (Log Location and Log Type)</strong>. LogType = 1 is Checkin tickets, 0 = Checkout. </p> <p>This is the Query:</p> <pre><code>SELECT DISTINCT fld.Grandstand,log9h.LogHour,Count(log9h.TicketNo) AS TotalOccupancy FROM ( SELECT DISTINCT TicketNo, logDay,9 as Loghour, MAX(LogDateTime) AS LastLog FROM dbo.viewF1LogDetail WHERE LogDay = 14 AND LogHour &lt; = 9 GROUP BY TicketNo, logDay ) AS log9h INNER JOIN dbo.viewF1LogDetail AS fld ON fld.TicketNo = log9h.TicketNo AND fld.LogDateTime = log9h.LastLog WHERE (fld.ScanningLogLocationType =('Grandstand')) AND (fld.LogType = 1) GROUP BY fld.Grandstand,log9h.LogHour UNION SELECT DISTINCT fld.Grandstand,log10h.LogHour,Count(log10h.TicketNo) AS TotalOccupancy FROM ( SELECT DISTINCT TicketNo, logDay,10 as Loghour, MAX(LogDateTime) AS LastLog FROM dbo.viewF1LogDetail WHERE LogDay = 14 AND LogHour &lt; = 10 GROUP BY TicketNo, logDay ) AS log10h INNER JOIN dbo.viewF1LogDetail AS fld ON fld.TicketNo = log10h.TicketNo AND fld.LogDateTime = log10h.LastLog WHERE (fld.ScanningLogLocationType =('Grandstand')) AND (fld.LogType = 1) GROUP BY fld.Grandstand,log10h.LogHour UNION SELECT DISTINCT fld.Grandstand,log11h.LogHour,Count(log11h.TicketNo) AS TotalOccupancy FROM ( SELECT DISTINCT TicketNo, logDay,11 as Loghour, MAX(LogDateTime) AS LastLog FROM dbo.viewF1LogDetail WHERE LogDay = 14 AND LogHour &lt; = 11 GROUP BY TicketNo, logDay ) AS log11h INNER JOIN dbo.viewF1LogDetail AS fld ON fld.TicketNo = log11h.TicketNo AND fld.LogDateTime = log11h.LastLog WHERE (fld.ScanningLogLocationType =('Grandstand')) AND (fld.LogType = 1) GROUP BY fld.Grandstand,log11h.LogHour UNION SELECT DISTINCT fld.Grandstand,log12h.LogHour,Count(log12h.TicketNo) AS TotalOccupancy FROM ( SELECT DISTINCT TicketNo, logDay,12 as Loghour, MAX(LogDateTime) AS LastLog FROM dbo.viewF1LogDetail WHERE LogDay = 14 AND LogHour &lt; = 12 GROUP BY TicketNo, logDay ) AS log12h INNER JOIN dbo.viewF1LogDetail AS fld ON fld.TicketNo = log12h.TicketNo AND fld.LogDateTime = log12h.LastLog WHERE (fld.ScanningLogLocationType =('Grandstand')) AND (fld.LogType = 1) GROUP BY fld.Grandstand,log12h.LogHour UNION SELECT DISTINCT fld.Grandstand,log13h.LogHour,Count(log13h.TicketNo) AS TotalOccupancy FROM ( SELECT DISTINCT TicketNo, logDay,13 as Loghour, MAX(LogDateTime) AS LastLog FROM dbo.viewF1LogDetail WHERE LogDay = 14 AND LogHour &lt; = 13 GROUP BY TicketNo, logDay ) AS log13h INNER JOIN dbo.viewF1LogDetail AS fld ON fld.TicketNo = log13h.TicketNo AND fld.LogDateTime = log13h.LastLog WHERE (fld.ScanningLogLocationType =('Grandstand')) AND (fld.LogType = 1) GROUP BY fld.Grandstand,log13h.LogHour UNION SELECT DISTINCT fld.Grandstand,log14h.LogHour,Count(log14h.TicketNo) AS TotalOccupancy FROM ( SELECT DISTINCT TicketNo, logDay,14 as Loghour, MAX(LogDateTime) AS LastLog FROM dbo.viewF1LogDetail WHERE LogDay = 14 AND LogHour &lt; = 14 GROUP BY TicketNo, logDay ) AS log14h INNER JOIN dbo.viewF1LogDetail AS fld ON fld.TicketNo = log14h.TicketNo AND fld.LogDateTime = log14h.LastLog WHERE (fld.ScanningLogLocationType =('Grandstand')) AND (fld.LogType = 1) GROUP BY fld.Grandstand,log14h.LogHour UNION SELECT DISTINCT fld.Grandstand,log15h.LogHour,Count(log15h.TicketNo) AS TotalOccupancy FROM ( SELECT DISTINCT TicketNo, logDay,15 as Loghour, MAX(LogDateTime) AS LastLog FROM dbo.viewF1LogDetail WHERE LogDay = 14 AND LogHour &lt; = 15 GROUP BY TicketNo, logDay ) AS log15h INNER JOIN dbo.viewF1LogDetail AS fld ON fld.TicketNo = log15h.TicketNo AND fld.LogDateTime = log15h.LastLog WHERE (fld.ScanningLogLocationType =('Grandstand')) AND (fld.LogType = 1) GROUP BY fld.Grandstand,log15h.LogHour ORDER BY Grandstand,Loghour </code></pre> <p>Sample Data:</p> <pre><code>Grandstand LogHour TotalOccupancy Main 11 11 Main 12 15 Main 13 12 Main 14 14 Main 15 22 Main 16 25 Main 17 31 Main 18 22 Main 19 11 West 10 2 West 11 22 West 12 23 West 13 24 West 14 55 West 15 56 West 16 57 West 17 22 West 18 23 West 19 11 South 10 22 South 11 21 South 12 26 South 13 55 South 14 56 South 15 78 South 16 99 South 17 22 South 18 11 South 19 1 </code></pre> <p>Not a convincing query right? Thanks in advance for your recommendations.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRomeo
    1. USRomeo
    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
    1. VO
      singulars
      1. POQuery Optimization for multiple query joined by union
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POQuery Optimization for multiple query joined by union
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo there's nothing wrong, except you want it to be faster? You should try posting here: [CodeReview](http://codereview.stackexchange.com)
      singulars
      1. POQuery Optimization for multiple query joined by union
      1. USSteve
    2. COThanks @Steve .. will do so..
      singulars
      1. POQuery Optimization for multiple query joined by union
      1. USRomeo
    3. COBTW, you want more people to work on this, make it easy for them to help out. Add a script to generate the tables and some sample data. I usually jump all over those because the person asking the question shows they are serious about wanting help, not just getting someone else to do thier work for them.
      singulars
      1. POQuery Optimization for multiple query joined by union
      1. USSteve
 

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