Voice of the DBA

Tag: T-SQL

  • SubQuery Performance

    Why would you do this?

    select distinct(hostname),
    (    select count(*) as criticalCnt 
     from @temp where severity_guid='0168A833-1732-411E-8205-C2F6CD91737D' 
         and hostname=t.hostname
     group by hostname),
    (    select count(*) as criticalCnt 
     from @temp where severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' 
         and hostname=t.hostname
     group by hostname),
    (    select count(*) as criticalCnt 
     from @temp where severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' 
         and hostname=t.hostname
     group by hostname)
         from @temp t

    I ran into this on a post where someone had asked about how to basically call a CASE statement. The posted didn’t know how and someone posted this as a way to tally the various counts of alerts.

    I had glossed over it when I saw it, but when someone else replied with this statement, saying performance was better, I decided to look at see how much better.

    select hostname,
    sum(case when severity_guid='0168A833-1732-411E-8205-C2F6CD91737D' 
         then 1 else 0 end) as [Count_Of_0168A833-1732-411E-8205-C2F6CD91737D]
    ,sum(case when severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' 
    then 1 else 0 end) as [Count_Of_CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5]
    ,sum(case when severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' 
         then 1 else 0 end) as [Count_Of_C4CF8A23-A106-4617-BAB0-94DA3CA74EF1]
     from @temp 
    group by hostname

    I set the statistics on for these queries and found these results.

    Query 1:

    Table ‘#45544755’. Scan count 52, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Query 2:

    Table ‘#473C8FC7’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    That’s a huge difference. If this were to be run with any significant frequency on a system, you’d be using (4 x the number of rows) as many scans of the data than you needed to. Even though they are logical scans, it’s still CPU and memory movement you are requiring, and if this were a significant amount of data.

    You can also see a drastic difference in the execution plans:

    ScanPlan1

    ScanPlan2

    I’ll let you figure out which plan goes to which query.

    This is a simple example, but it shows where someone really can get poor performance over time with badly written SQL. It’s nice to have various ways to solve problems, but you also want to choose the appropriate tool. Subqueries make sense at times, but this isn’t one of them.

  • Getting a Page of Results

    The other day I was looking over a couple of articles on paging, looking to see if I could learn something new in T-SQL. I’ve implemented some SQL2000 era paging systems, none of which performed wonderfully, so I checked out Jacob Sebastian’s basic Server Side Paging and Paul White’s Optimizing Paging Part 1.

    I’ve done systems similar to Jacob’s, but he had an interesting use of the OVER clause in his code. He had this code:

    ;WITH emp AS (
  SELECT 
    CASE 
      WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title) 
      WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate) 
      WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City) 
          -- In all other cases, assume that @SortOrder = 'LastName' 
      ELSE ROW_NUMBER()OVER (ORDER BY LastName) 
     END AS RecID,   , LastName
   , FirstName
   , Title
   , HireDate
   , City
   , Country
   , PostalCode
 FROM employees

    This is a great solution in SQL Server 2005. It’s much different than what I had done in SQL 2000, where I’d typically approach the problem by using the sort key to get the next page.

    So say I had this data in a table (Customers):

    CustomerID    Customer

    ———–   ————

    1             Jones

    2             Smith

    3             Johnson

    4             Allen

    5             Gates     

    Then suppose I wanted page 1, 2 results per page, ordered by Customer. I would want to see “Allen, Gates” on page 1. I’d use this code.

    select top 2 Customer

    from Customers

    Order By Customer

    If I wanted page 2, I’d go here:

    select top 2 Customer

    from Customers

    where Customer > ‘Gates’

    Order By Customer

    And this would get me “Johnson” and “Jones” since the WHERE clause would reset results. If you have control of the application code, and you can pass in the previous values, you can easily build pages like this.

    If you switch orders, say to the CustomerID, then you can easily pass that in as well, and use that for ordering.

    There is a downside, however. Any ideas? I’ll post that in my next look at paging.