Solved

SQL Statement locks up when using a sub query

Posted on 2007-11-27
9
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 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