performance - SQL Server Query takes 100% CPU usage what is wrong with my query? -

i have queries using 100% cpu. tried fix them not database expert getting worse.

at first script executes query.

select *      (select        [e].[event_id],         [e].[event_name],        [e].[event_datetime],        [v].[name] [venue_name],        [v].[city],        [s].[state_name],        [v].[venue_id],        row_number() on (order [event_datetime]) rownum             [indux].[dbo].[event] [e]     join         [indux].[dbo].[venue] [v] on [e].[venue_id] = [v].[venue_id]     join         [indux].[dbo].[system_state] [s] on [v].[state_id] = [s].[state_id]             [e].[event_id] in (select distinct event_id                            [indux].[dbo].ticket_group                            ticket_group_id in (select distinct ticket_group_id                                                      [indux].[dbo].[ticket] [t]                                                      [t].[actual_sold_price] = -1)                          )        , [e].[event_datetime] >= '2014/08/09'        , [e].[event_datetime] <= '2014/12/09' ) myderivedtable     myderivedtable.rownum between 0 , 250 

and @ in same time execute following query combine results of both display in page using php

select    count([e].[event_id])     [indux].[dbo].[event] [e]     [e].[event_id] in (select distinct event_id                        [indux].[dbo].ticket_group                        ticket_group_id in                                     (select distinct ticket_group_id                                       [indux].[dbo].[ticket] [t]                                       [t].[actual_sold_price] = -1)                      )    , [e].[event_datetime] >= '$today'    , [e].[event_datetime] <= '$to' 

anyone have idea if can fix these queries ? think count in second query problem.

you need join ticket group , ticket on ticket group id , join main query on event id.

a nested query runs once every row of query clause nested in.

nesting twice running loop in loop , whole thing has run once , compared value value every time query returns row. apparent selects can join on values.

select  *whatever*  [indux].[dbo].[event] [e]            join [indux].[dbo].[venue] [v]         on [e].[venue_id] = [v].[venue_id]       join [indux].[dbo].[system_state] [s]       on [v].[state_id] = [s].[state_id] join indux.dbo.ticket_group tg on tg.event_id=r.event_id join  indux.dbo.ticket t on t.ticket_group_id=tg.ticket_group_id t.actual_sold_price=-1  select count(e.event_id) dbo.event e join indux.dbo.ticket_group tg on tg.event_id=e.event_id join  indux.dbo.ticket t on t.ticket_group_id=tg.ticket_group_id e.event_datetime >= '$today'  , e.event_datetime <= '$to' 

edit distinctness

select  *whatever*  [indux].[dbo].[event] [e]            join [indux].[dbo].[venue] [v]         on [e].[venue_id] = [v].[venue_id]       join [indux].[dbo].[system_state] [s]       on [v].[state_id] = [s].[state_id] join (       select distinct tg.event_id        indux.dbo.ticket_group tg        join indux.dbo.ticket t on t.ticket_group_id=tg.ticket_group_id       actual_sold_price = -1) de on de.event_id=e.event_id 

using temp table ie

create table #eventid (id int)  insert #eventid select tg.event_id etc... 

will allow reuse table rather re-query count statement.