sql server programming: can anyone optimize this query -- sql server programming

Re: can anyone optimize this query

Roy Harvey
1/9/2007 3:02:05 PM

I have no idea if performance will be any better or worse than the
version posted. I am not entirely certain that the results will be
the same because I am not entirely certain I understood everything
about the original query.

What I thought I saw was the the original query performed had multple
tests. Each test could be passed in either of two ways, against table
CandCriteriaVal or against table CandCriteriaMatchType. ALL tests had
to be passed.

I removed the nesting withing nesting within nesting. I arbitrarily
numbered the tests 1 through 8. That gave 16 tests to be performed,
two ways for each test. One large set of all "passes", WITH theirTEST
NUMBER, was grouped on cand and only those that passed all 8 tests --
HAVING COUNT(distinct Test) = 8) -- were included.

Every other UNION ALL has a line of --------------- to signify the
breaking point between two different tests.

SELECT C.visible,
C.contact_enabled,
C.notify_extra,
count(*)
FROM dbo.Candidate C
WHERE C.status = 0
AND C.type = 'C'
AND C.id IN
(SELECT cand
FROM (select cand, Test = 1
from dbo.CandCriteriaVal
where type = 101
and (code = 10000002)
union all
select cand, Test = 1
from dbo.CandCriteriaMatchType
where type = 101
and match = 'D'
UNION ALL ---------------
select cand, Test = 2
from dbo.CandCriteriaVal
where type = 146
AND ( code in ( 1 , 3 ) )
union all
select cand, Test = 2
from dbo.CandCriteriaMatchType
where type = 146
and match = 'D'
UNION ALL ---------------
select cand, Test = 3
from dbo.CandCriteriaVal
where type = 107
AND ( code in ( 8400000 , 8401600 , 8401605 ) )
union all
select cand, Test = 3
from dbo.CandCriteriaMatchType
where type = 107
and match = 'D'
UNION ALL ---------------
select cand, Test = 4
from dbo.CandCriteriaVal
where type = 109
AND code <= 505
union all
select cand, Test = 4
from dbo.CandCriteriaMatchType
where type = 109
and match = 'D'
UNION ALL ---------------
select cand, Test = 5
from dbo.CandCriteriaVal
where type = 302
AND ( code = 3 )
union all
select cand, Test = 5
from dbo.CandCriteriaMatchType
where type = 302
and match = 'D'
UNION ALL ---------------
select cand, Test = 6
from dbo.CandCriteriaVal
where type = 260
AND code >= 353
union all
select cand, Test = 6
from dbo.CandCriteriaMatchType
where type = 260
and match = 'D'
UNION ALL ---------------
select cand, Test = 7
from dbo.CandCriteriaVal
where type = 360
AND ( code = 4 )
union all
select cand, Test = 7
from dbo.CandCriteriaMatchType
where type = 360
and match = 'D'
UNION ALL ---------------
select cand, Test = 8
from dbo.CandCriteriaVal
where type = 320
AND ( code = 170 )
union all
select cand, Test = 8
from dbo.CandCriteriaMatchType
where type = 320
)
GROUP BY can
HAVING COUNT(distinct Test) = 8)
GROUP BY C.visible,
C.contact_enabled,
C.notify_extra

Roy Harvey

Re: can anyone optimize this query

Roy Harvey
1/9/2007 3:05:08 PM

On Tue, 09 Jan 2007 15:02:05 -0500, Roy Harvey <roy_harvey@snet.net>
[quoted text, click to view]

Had to fix a syntax error.

Roy

SELECT C.visible,
C.contact_enabled,
C.notify_extra,
count(*)
FROM dbo.Candidate C
WHERE C.status = 0
AND C.type = 'C'
AND C.id IN
(SELECT cand
FROM (select cand, Test = 1
from dbo.CandCriteriaVal
where type = 101
and (code = 10000002)
union all
select cand, Test = 1
from dbo.CandCriteriaMatchType
where type = 101
and match = 'D'
UNION ALL ---------------
select cand, Test = 2
from dbo.CandCriteriaVal
where type = 146
AND ( code in ( 1 , 3 ) )
union all
select cand, Test = 2
from dbo.CandCriteriaMatchType
where type = 146
and match = 'D'
UNION ALL ---------------
select cand, Test = 3
from dbo.CandCriteriaVal
where type = 107
AND ( code in ( 8400000 , 8401600 , 8401605 ) )
union all
select cand, Test = 3
from dbo.CandCriteriaMatchType
where type = 107
and match = 'D'
UNION ALL ---------------
select cand, Test = 4
from dbo.CandCriteriaVal
where type = 109
AND code <= 505
union all
select cand, Test = 4
from dbo.CandCriteriaMatchType
where type = 109
and match = 'D'
UNION ALL ---------------
select cand, Test = 5
from dbo.CandCriteriaVal
where type = 302
AND ( code = 3 )
union all
select cand, Test = 5
from dbo.CandCriteriaMatchType
where type = 302
and match = 'D'
UNION ALL ---------------
select cand, Test = 6
from dbo.CandCriteriaVal
where type = 260
AND code >= 353
union all
select cand, Test = 6
from dbo.CandCriteriaMatchType
where type = 260
and match = 'D'
UNION ALL ---------------
select cand, Test = 7
from dbo.CandCriteriaVal
where type = 360
AND ( code = 4 )
union all
select cand, Test = 7
from dbo.CandCriteriaMatchType
where type = 360
and match = 'D'
UNION ALL ---------------
select cand, Test = 8
from dbo.CandCriteriaVal
where type = 320
AND ( code = 170 )
union all
select cand, Test = 8
from dbo.CandCriteriaMatchType
where type = 320) as X
GROUP BY can
HAVING COUNT(distinct Test) = 8)
GROUP BY C.visible,
C.contact_enabled,

Re: can anyone optimize this query

Gert-Jan Strik
1/9/2007 9:39:52 PM

I have had a look and came to the conclusion that I just wasted quite a
lot of time (mostly with reformatting your query to some readable
format).

Apart from the fact that you do not explain what the query is supposed
to do (in fact, I even doubt that it does what it is supposed to do),
the fact that you do not specify the current performance and/or desired
performance and the fact that you did not post any constraint or index
definitions, it is some sort of computer generated statement to query
some kind of object oriented data structure.

You should ask the vendor that created this horrible statement to
optimize it (that is: if the query actually needs optimizing).

Gert-Jan

[quoted text, click to view]

can anyone optimize this query

lara169
1/9/2007 11:39:06 PM

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[CANDCRITERIAMATCHTYPE](

[TYPE] [int] NULL,

[CAND] [int] NULL,

[MATCH] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT
('V')

) ON [PRIMARY]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[CANDCRITERIAVAL](

[TYPE] [int] NULL,

[SSID] [int] NULL,

[CODE] [int] NULL,

[CAND] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[CANDIDate](

[ID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[REPRESENTS] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[STATUS] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__CANDIDate__STATU__7C8480AE] DEFAULT ((0)),

[RACE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[MARITAL_STATUS] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,

[HANDICAP] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[VISIBLE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__CANDIDate__VISIB__7D78A4E7] DEFAULT ('Y'),

[CONTACT_ENABLED] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF__CANDIDate__CONTA__7E6CC920] DEFAULT ('Y'),

[NOTIFY_EXTRA] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__CANDIDate__NOTIF__7F60ED59] DEFAULT ('Y'),

[BATCH_NOTIFY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,

[INTERNAL] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__CANDIDate__INTER__00551192] DEFAULT ('N'),

[CREATED_ON] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[MODIFIED_ON] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LAST_MATCH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOURCE_CODE] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,

[RESUME_STATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__CANDIDate__RESUM__014935CB] DEFAULT ('0'),

[PREFCHANGED] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__CANDIDate__PREFC__023D5A04] DEFAULT ('Y'),

[MINIMUM_RANK] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,

[TYPE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__CANDIDatet__TYPE__03317E3D] DEFAULT ('C'),

[DATAPRIV_ACCEPTED_ON] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,

[PROFILE_UPDatetimeD] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SELECT C.visible,

C.contact_enabled,

C.notify_extra,

count(*)

from dbo.Candidate C

where C.status = 0

AND C.type = 'C'

AND C.id in ( SELECT C12.cand

from (

SELECT C10.cand

from (

SELECT C8.cand

from (

SELECT C6.cand

from (

SELECT
C4.cand

from
(

SELECT C2.cand

from (

SELECT C0.cand

from (

SELECT cand

from dbo.CandCriteriaVal

where type = 101

AND ( code = 10000002 )

union all

SELECT cand

from dbo.CandCriteriaMatchType

where type = 101

and match = 'D' ) C0,

(

SELECT cand

from dbo.CandCriteriaVal

where type = 146

AND ( code in ( 1 , 3 ) )

union all

SELECT cand

from dbo.CandCriteriaMatchType

where type = 146

and match = 'D' ) C1

where C0.cand = C1.cand ) C2,

(

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.