?
Solved

SQL Statement locks up when using a sub query

Posted on 2007-11-27
9
Medium Priority
?
272 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

752 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