Solved

SQL Statement locks up when using a sub query

Posted on 2007-11-27
9
252 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now