• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

SQL Statement locks up when using a sub query

When I run the below query, it just locks up not giving me any error message and it never actually executes.

-- Top 10 and Others:
SELECT Top10.names, Top10.counts
FROM (
      -- Top 10:
         SELECT TOP 10
                              call_type_explain
                  AS Names
               , count(call_type_explain) AS counts
      FROM    police_event_history
where time_created >= @datea and time_created<= (dateadd(day, 1, @dateb))
         GROUP BY call_type_explain
            Order By count(call_type_explain) desc
        ) AS Top10
UNION all
SELECT Other.names, other.counts
FROM (
      -- Others excluding the top 10:
         SELECT TOP 100 PERCENT
                 'Other' as names, count(call_type_explain) as counts
         FROM    police_event_history
        --PROBLEM IS HERE!!!!
WHERE  time_created >= @datea and time_created<= (dateadd(day, 1, @dateb)) and call_type_explain not IN
            (
                SELECT TOP 10 call_type_explain
                FROM   police_event_history
                GROUP BY call_type_explain
                ORDER BY count(call_type_explain) DESC
     )
      
    ORDER BY count(call_type_explain) DESC
 
 )

I am trying to use this query to give me the top 10 results based on number of incidents (THis is calculated by the count(call_type_explain) function)  and leave the rest lumped together as 'others' hence the subqueries.
Only when I add in the date parameter on the second subquery does it lock up. If I rewrite the query as this:

-- Top 10 and Others:
SELECT Top10.names, Top10.counts
FROM (
      -- Top 10:
         SELECT TOP 10
                              call_type_explain
                  AS Names
               , count(call_type_explain) AS counts
      FROM    police_event_history

         GROUP BY call_type_explain
            Order By count(call_type_explain) desc
        ) AS Top10
UNION all
SELECT Other.names, other.counts
FROM (
      -- Others excluding the top 10:
         SELECT TOP 100 PERCENT
                 'Other' as names, count(call_type_explain) as counts
         FROM    police_event_history
        WHERE  call_type_explain not IN
            (
                SELECT TOP 10 call_type_explain
                FROM   police_event_history
                GROUP BY call_type_explain
                ORDER BY count(call_type_explain) DESC
     )
      
    ORDER BY count(call_type_explain) DESC
   
 )

AS Other



It works fine however I dont have a date parameter. What can I do to add in a date paramater so it will limit the results on all of the results? THis is intended for a pie chart so it will have the top most occuring results as well as all the other incidents just grouped together as 'others.' Is there another way to go about this?
0
richecker
Asked:
richecker
  • 4
  • 4
1 Solution
 
James MurrellProduct SpecialistCommented:
the guys at work spy on the buffer they use: see code snippet - full detais from http://www.sqlmag.com/articles/index.cfm?articleid=22069&
The sp_outputbuffer Stored Procedure
 
CREATE PROC sp_outputbuffer
-- Produce cleaned-up DBCC OUTPUTBUFFER report for a given SPID.
-- Author: Andrew Zanevsky, 2001-06-29
 
  @spid smallint
AS
SET NOCOUNT ON
SET ANSI_PADDING ON
 
DECLARE @outputbuffer varchar(80),
  @clean varchar(16),
  @pos smallint
 
CREATE TABLE #out  (  
-- Primary key on IDENTITY column prevents rows
-- from changing order when you update them later.
  line int IDENTITY PRIMARY KEY CLUSTERED,
  dirty varchar(255) NULL,
  clean varchar(16) NULL
)
 
INSERT #out ( dirty )
EXEC( 'DBCC OUTPUTBUFFER(' + @spid + ')' )
 
SET @pos = 0
WHILE @pos < 16 BEGIN
  SET @pos = @pos + 1
  -- 1. Eliminate 0x00 symbols.
  -- 2. Keep line breaks.
  -- 3. Eliminate dots substituted by DBCC OUTPUTBUFFER
  --  for nonprintable symbols, but keep real dots.
  -- 4. Keep all printable characters.
  -- 5. Convert anything else to blank,
  --  but compress multiple blanks to one.
  UPDATE #out
  SET clean = ISNULL( clean, '' ) +
    CASE WHEN SUBSTRING( dirty, 9 + @pos * 3, 2 ) =
        '0a' THEN char(10)
      WHEN SUBSTRING( dirty, 9 + @pos * 3, 2 )
        BETWEEN '20' AND '7e'
        THEN SUBSTRING( dirty, 61 + @pos, 1 )
      ELSE ' '
    END
  WHERE CASE WHEN SUBSTRING( dirty, 9 + @pos * 3, 2 ) =
        '0a' THEN 1
    WHEN SUBSTRING( dirty, 61 + @pos, 1 ) = '.'
      AND SUBSTRING( dirty, 9 + @pos * 3, 2 ) <>
        '2e' THEN 0
    WHEN SUBSTRING( dirty, 9 + @pos * 3, 2 )
        BETWEEN '20' AND '7e' THEN 1
    WHEN SUBSTRING( dirty, 9 + @pos * 3, 2 ) =
        '00' THEN 0
    WHEN RIGHT( 'x' + clean, 1 )
        IN ( ' ', char(10) ) THEN 0
    ELSE 1
  END = 1
END
 
DECLARE c_output CURSOR FOR SELECT clean FROM #out
OPEN c_output
FETCH c_output INTO @clean
 
SET @outputbuffer = ''
 
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @outputbuffer = @outputbuffer +
    CASE WHEN RIGHT( @outputbuffer, 1 ) = ' '
      OR @outputbuffer = ''
    THEN LTRIM( ISNULL( @clean, '' ) )
  ELSE ISNULL( @clean, '' )
END
 
IF DATALENGTH( @outputbuffer ) > 64 BEGIN
  PRINT @outputbuffer
  SET @outputbuffer = ''
END
 
  FETCH c_output INTO @clean
END
PRINT @outputbuffer
 
CLOSE c_output
DEALLOCATE c_output
 
DROP TABLE #out 
 
GO

Open in new window

0
 
imitchieCommented:
how many rows do you have in your table?
0
 
imitchieCommented:

SELECT
  TOP10.NAMES,
  TOP10.COUNTS
FROM   (-- Top 10:
  SELECT
    TOP 10 CALL_TYPE_EXPLAIN         AS NAMES,
    COUNT(CALL_TYPE_EXPLAIN)  AS COUNTS
  FROM     POLICE_EVENT_HISTORY
  where time_created >= @datea
    and time_created<= (dateadd(day, 1, @dateb))
  GROUP BY CALL_TYPE_EXPLAIN
  ORDER BY COUNT(CALL_TYPE_EXPLAIN) DESC) AS TOP10
UNION ALL
SELECT
  OTHER.NAMES,
  OTHER.COUNTS
FROM   (-- Others excluding the top 10:
  SELECT TOP 100 PERCENT 'Other'                   AS NAMES,
    COUNT(CALL_TYPE_EXPLAIN)  AS COUNTS
  FROM     POLICE_EVENT_HISTORY
  WHERE    CALL_TYPE_EXPLAIN NOT IN (
    SELECT   TOP 10 CALL_TYPE_EXPLAIN
    FROM     POLICE_EVENT_HISTORY
    where time_created >= @datea
      and time_created<= (dateadd(day, 1, @dateb))
    GROUP BY CALL_TYPE_EXPLAIN
    ORDER BY COUNT(CALL_TYPE_EXPLAIN) DESC)
    AND time_created >= @datea
    and time_created<= (dateadd(day, 1, @dateb))
  ORDER BY COUNT(CALL_TYPE_EXPLAIN) DESC
) AS OTHER

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
imitchieCommented:
ignore that, just try this one
SELECT
  TOP10.NAMES,
  TOP10.COUNTS
FROM   (-- Top 10:
  SELECT
    TOP 10 CALL_TYPE_EXPLAIN         AS NAMES,
    COUNT(CALL_TYPE_EXPLAIN)  AS COUNTS
  FROM     POLICE_EVENT_HISTORY
  where time_created >= @datea
    and time_created<= (dateadd(day, 1, @dateb))
  GROUP BY CALL_TYPE_EXPLAIN
  ORDER BY COUNT(CALL_TYPE_EXPLAIN) DESC) AS TOP10
UNION ALL
SELECT
  OTHER.NAMES,
  OTHER.COUNTS
FROM   (-- Others excluding the top 10:
  SELECT 'Other'                   AS NAMES,
    COUNT(CALL_TYPE_EXPLAIN)  AS COUNTS
  FROM     POLICE_EVENT_HISTORY
  WHERE    CALL_TYPE_EXPLAIN NOT IN (
    SELECT   TOP 10 CALL_TYPE_EXPLAIN
    FROM     POLICE_EVENT_HISTORY
    where time_created >= @datea
      and time_created<= (dateadd(day, 1, @dateb))
    GROUP BY CALL_TYPE_EXPLAIN
    ORDER BY COUNT(CALL_TYPE_EXPLAIN) DESC)
    AND time_created >= @datea
    and time_created<= (dateadd(day, 1, @dateb))
  ORDER BY COUNT(CALL_TYPE_EXPLAIN) DESC
) AS OTHER

Open in new window

0
 
imitchieCommented:
this will serve you better:
select
CASE WHEN R > 10 then 'OTHER' else NAMES END as NAMES,
SUM(COUNTS) as COUNTS
from 
(-- Top 10:
  SELECT Top 100 PERCENT
    ROW_NUMBER() OVER (ORDER BY COUNT(CALL_TYPE_EXPLAIN) DESC) AS R,
    CALL_TYPE_EXPLAIN AS NAMES,
    COUNT(CALL_TYPE_EXPLAIN) AS COUNTS
  FROM POLICE_EVENT_HISTORY
  where time_created >= @datea
    and time_created<= (dateadd(day, 1, @dateb))
  GROUP BY CALL_TYPE_EXPLAIN
  ORDER BY COUNT(CALL_TYPE_EXPLAIN) DESC) RANKED
GROUP BY CASE WHEN R > 10 then 'OTHER' else NAMES END

Open in new window

0
 
richeckerAuthor Commented:
Trying this out now and will let you know. I am returning approximately 2 million records.
0
 
richeckerAuthor Commented:
Thanks for the help. This runs with very little load on the system.
0
 
richeckerAuthor Commented:
Unfortunately after further checking the case statement solution, it was giving me incorrect results. The solution above works though however it is killing the system.
0
 
richeckerAuthor Commented:
Please disregard the last post...it is in fact correct however it is not ordered correctly but I think I can fix that.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now