Query Execution Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Execution Problem

Author

Topic

mikejs78
Starting Member

5 Posts

Posted - 2010-04-05 : 10:37:03

I have an application that generates the following query:

select count(subscriber_id) FROM subscriber (nolock)
where publisher_id=11
and (valid=1 OR valid IS NULL)
and
( //first condition
( subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='HTI Sales Team' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='PRINT_HTI' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='PRINT_STAR' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='PRINT_TAW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='PRINT_TW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='SIP_AUSTRIA' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='SIP_NCL' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='SIP_NEWORLEANS' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='SIP_PALMBEACH' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='SIP_PRINCEHAWAII' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='SIP_Starwood' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='SIP_Yucatan' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='WEB_HTI' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='WEB_HTIOLD' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='WEB_INTELLIGUIDE' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='WEB_STAR' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='WEB_TAW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI Apr09 TAW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI Apr09 TW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI Aug09 TAW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI Aug09 TW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI Jul09 TW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI Dec09 TW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI Jul09 TAW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI Jun09 TW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI May09 TW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI FEB10 TW NEW' AND Value = 'x')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='RBI FEB10 TAW NEW' AND Value = 'x')

) And (

subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='StreetCountry' AND Value = 'mexico')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='StreetCountry' AND Value = 'united states')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='StreetCountry' AND Value = 'canada')
OR subscriber_id NOT IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='StreetCountry' and value is not null and value<>'')

) And (

(Email NOT LIKE '%@aol.%' or Email is null)
AND (Email NOT LIKE '%@mci.%' or Email is null)
AND subscriber_id NOT IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='OPTOUT_INDUST' AND Value is not null and value<>'')

)) or (( //second condition

(Email NOT LIKE '%@aol.%' or Email is null)
AND (Email NOT LIKE '%@mci.%' or Email is null)
AND JobFunction LIKE 'Travel%'
AND subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='WEB_TW' AND Value = 'x')
AND subscriber_id NOT IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='OPTOUT_INDUST' AND Value = 'x')

) And ( subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='StreetCountry' AND Value = 'mexico')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='StreetCountry' AND Value = 'canada')
OR subscriber_id IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='StreetCountry' AND Value = 'united states')
OR subscriber_id NOT IN (SELECT subscriber_id FROM dbo.Custom WHERE Field='StreetCountry' and value is not null and value<>'')

))

If I execute the query before the part labeled //second condition, the query takes about 15 seconds. If I execute the query after the part labeled //second condition, and remove the first part that starts after //first condition, it takes less than a second. However, combining the two takes over 2 1/2 minutes.

Looking at the execution plan, it seems when I try to run the full result set, I get a warning in the execution plan "No join predicate".

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 11:16:59

i really cant understand why you've so much inner queries linked by IN condition. Is this query generated fully by app? do u have any control over it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikejs78
Starting Member

5 Posts

Posted - 2010-04-05 : 11:26:11

THe query is built dynamically by the application based on user conditions. The user's have a heavy querying requirement. I have control over the app in as much as I can get our development team to change the way they assemble the query, just looking for some input on how to better structure this query.

In this case, a subscriber can have multiple entries in the custom table, which is made up of name/value pairs. A subscriber can have up to 480 possible rows in the Custom table (480 possible "fields"), but they only have rows for the fields where the data has been filled in, so we have to check for conditions of not in the custom table for that subscriber.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 11:58:11

quote:
Originally posted by mikejs78

THe query is built dynamically by the application based on user conditions. The user's have a heavy querying requirement. I have control over the app in as much as I can get our development team to change the way they assemble the query, just looking for some input on how to better structure this query.

In this case, a subscriber can have multiple entries in the custom table, which is made up of name/value pairs. A subscriber can have up to 480 possible rows in the Custom table (480 possible "fields"), but they only have rows for the fields where the data has been filled in, so we have to check for conditions of not in the custom table for that subscriber.

Any ideas?

for this isnt it enough to apply PIVOT based on name field to get values in different columns i.e cross tab type of query?

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikejs78
Starting Member

5 Posts

Posted - 2010-04-05 : 12:36:26

I have not used pivot tables much before. To get the values of the pivot columns, how does one do that? I see easily how to get aggregates (sum, count, max, etc.) but not the actual value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 12:40:00

for getting actual value just apply MIN() or MAX() based on name field values. If you can provide a sample data and expected o/p you want, I may be able to provide you query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikejs78
Starting Member

5 Posts

Posted - 2010-04-05 : 12:44:41

select * from
(
select distinct field as pivot_col,s.subscriber_id,s.email from subscriber s inner join custom c
on s.subscriber_id=c.subscriber_id
where s.subscriber_id =999
) as t
pivot
(
max([Value]) for pivot_col in ([EditProfileURL],[OPTIN_HS_CRUISE],[OPTIN_HS_FAM],[OPTIN_HS_HOTEL],[OPTIN_HS_TOUR],[OPTIN_HS_VACAT],[OPTOUT_INDUST],[OPTOUT_NONINDUST],[PROMO_NTM_RES],[WEB_HTI],[WEB_TW])
) as p

Just a simple one for testing purposes. The field names in the Custom table are NAME and VALUE.

So if Name=[OPTIN_HS_FAM], the value may be x, y, or null. So I'd want to see:

Subscriber Email EditProfileURL OPTIN_HS_CRUISE
123 abc@abc.com x y
124 efg@efg.com y x

etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 12:51:54

Doesnt above query work?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikejs78
Starting Member

5 Posts

Posted - 2010-04-05 : 13:16:57

It did once I added c.value to the select list.

Ok, so I tried to re-write the original query using pivots, but I still cannot get any performance improvement. In fact, the pivot takes longer becasue I believe it is generating an entire pivot table of my 400000 subscribers + the 600000 rows of custom data, before even attempting to run the conditions.

select count(*) from
(
select distinct field as pivot_col,s.subscriber_id,jobfunction,s.email,c.value from subscriber s inner join custom c
on s.subscriber_id=c.subscriber_id where publisher_id=11
and (valid=1 OR valid IS NULL)
) as t
pivot
(
max(value) for pivot_col in ([HTI Sales Team],[PRINT_HTI],
[PRINT_STAR],
[PRINT_TAW],
[PRINT_TW],
[SIP_AUSTRIA],
[SIP_NCL],
[SIP_NEWORLEANS],
[SIP_PALMBEACH],
[SIP_PRINCEHAWAII],
[SIP_Starwood],
[SIP_Yucatan],
[WEB_HTI],
[WEB_HTIOLD],
[WEB_INTELLIGUIDE],
[WEB_STAR],
[WEB_TAW],
[RBI Apr09 TAW],
[RBI Apr09 TW],
[RBI Aug09 TAW],
[RBI Aug09 TW],
[RBI Jul09 TW],
[RBI Dec09 TW],
[RBI Jul09 TAW],
[RBI Jun09 TW],
[RBI May09 TW],
[RBI FEB10 TW NEW],
[RBI FEB10 TAW NEW],[StreetCountry],[OPTOUT_INDUST],[Web_TW])
) as p
where
(
([HTI Sales Team]='X'
or [PRINT_HTI]='x'
or [PRINT_STAR]='x'
or [PRINT_TAW]='x'
or [PRINT_TW]='x'
or [SIP_AUSTRIA]='x'
or [SIP_NCL]='x'
or [SIP_NEWORLEANS]='x'
or [SIP_PALMBEACH]='x'
or [SIP_PRINCEHAWAII]='x'
or [SIP_Starwood]='x'
or [SIP_Yucatan]='x'
or [WEB_HTI]='x'
or [WEB_HTIOLD]='x'
or [WEB_INTELLIGUIDE]='x'
or [WEB_STAR]='x'
or [WEB_TAW]='x'
or [RBI Apr09 TAW]='x'
or [RBI Apr09 TW]='x'
or [RBI Aug09 TAW]='x'
or [RBI Aug09 TW]='x'
or [RBI Jul09 TW]='x'
or [RBI Dec09 TW]='x'
or [RBI Jul09 TAW]='x'
or [RBI Jun09 TW]='x'
or [RBI May09 TW]='x'
or [RBI FEB10 TW NEW]='x'
or [RBI FEB10 TAW NEW]='x')
and
([StreetCountry] in ('mexico','united states','canada') or [StreetCountry] is null)
and
(email not like '%aol.%' and email not like '%mci.%' and [OPTOUT_INDUST] is null))
or
((jobfunction like 'Travel%' and Web_TW='x' and email not like '%aol.%' and email not like '%mci.%' and OPTOUT_INDUST is null)
and
([StreetCountry] in ('mexico','united states','canada') or [StreetCountry] is null))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources