Solved

SQL Statement locks up when using a sub query

Posted on 2007-11-27
9
260 Views
Last Modified: 2011-10-03
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
Comment
Question by:richecker
  • 4
  • 4
9 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20362127
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20362156
how many rows do you have in your table?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20362198

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 25

Expert Comment

by:imitchie
ID: 20362199
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
 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
ID: 20362256
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
 

Author Comment

by:richecker
ID: 20365148
Trying this out now and will let you know. I am returning approximately 2 million records.
0
 

Author Closing Comment

by:richecker
ID: 31411308
Thanks for the help. This runs with very little load on the system.
0
 

Author Comment

by:richecker
ID: 20365307
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
 

Author Comment

by:richecker
ID: 20365495
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question