Multi Column Sub Query Performance issue

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

General SQL Server Forums

Database Design and Application Architecture

Multi Column Sub Query Performance issue

Author

Topic

abcefg
Starting Member

16 Posts

Posted - 2009-03-13 : 04:07:13

Insert into TargetTable
select * from #dimbandTemp -- (This is STAG_table & #dimbandTemp hold around 100,000 reord )
where
cast(Col1 as varchar(50))
+ cast(Col2 as varchar(50))
+ cast(Col3 as varchar(50))
+ cast(CampaignName as varchar(50))
+ cast (Col5 as varchar(50))
+ cast (Col6 as varchar(50))
not in
(select
cast(fk1 as varchar(50))
+ cast(fk2 as varchar(50))
+ cast(fk3 as varchar(50))
+ cast(fk4 as varchar(50))
+ cast (fk5 as varchar(50))
+ cast (fk6 as varchar(50))
from TargetTable)

Well, Friends .. TargetTable contain more than 72 million or more records. which increase day by day.
THe said Query tooks serval hours to complete it.
How do I rewrite the Query to improve the performance?
Partitioned index and very good hardware we are already using...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 04:19:41

[code]INSERT TargetTable
SELECT DISTINCT
*
FROM #DimBandTemp AS dbt
WHERE NOT EXISTS (
SELECT *
FROM TargetTable AS tt
WHERE tt.fk1 = dbt.Col1
tt.fk2 = dbt.Col2
tt.fk3 = dbt.Col3
tt.fk4 = dbt.CampaignName
tt.fk5 = dbt.Col5
tt.fk6 = dbt.Col6
)[/code]

E 12°55'05.63"
N 56°04'39.26"

abcefg
Starting Member

16 Posts

Posted - 2009-03-13 : 06:37:42

And was missing...
Thanks you,.

INSERT TargetTable
SELECT DISTINCT
*
FROM #DimBandTemp AS dbt
WHERE NOT EXISTS (
SELECT *
FROM TargetTable AS tt
WHERE tt.fk1 = dbt.Col1 And
tt.fk2 = dbt.Col2 And
tt.fk3 = dbt.Col3 And
tt.fk4 = dbt.CampaignName And
tt.fk5 = dbt.Col5 And
tt.fk6 = dbt.Col6

abcefg
Starting Member

16 Posts

Posted - 2009-03-16 : 02:28:06

How can I rewrite this query for better performance....

Inser into DIMDisBand
SELECT
distinct
''as[DIMDisBendNativeId]
,BDHW.dbo.DIMDis.DIMDisId AS fk_DIMDisId
,BLIVE.dbo.DisBand.LLimit
,BLIVE.dbo.DisBand.ULimit
,BLIVE.dbo.DisCode.CName
,BLIVE.dbo.DisBand.PDisValue
,BLIVE.dbo.DisBand.ValueDisVal
,BLIVE.dbo.DisBand.Updated_On AS LastModified
,1 AS fkdimcountryid
FROM BLIVE.dbo.DisBand INNER JOIN
BLIVE.dbo.DisCode ON BLIVE.dbo.DisBand.fkDiscountId = BLIVE.dbo.DisCode.DiscountId INNER JOIN
BDHW.dbo.DIMDis ON BLIVE.dbo.DisCode.DiscountId = BDHW.dbo.DIMDis.DIMDisNativeId
where BDHW.dbo.DIMDis.DIMDisId in (select fk_DIMDisId from BDHW.dbo.DIMDisBand)
and
cast(BDHW.dbo.DIMDis.DIMDisId as varchar(50))
+ cast(BLIVE.dbo.DisBand.LLimit as varchar(50))
+ cast(BLIVE.dbo.DisBand.ULimit as varchar(50))
+ cast(BLIVE.dbo.DisCode.CName as varchar(50))
+ cast (BLIVE.dbo.DisBand.PDisValue as varchar(50))
+ cast (BLIVE.dbo.DisBand.ValueDisVal as varchar(50))
not in
(select
cast(fk_DIMDisId as varchar(50))
+ cast(LLimit as varchar(50))
+ cast(ULimit as varchar(50))
+ cast(BandName as varchar(50))
+ cast (PDisValue as varchar(50))
+ cast (ValueDisVal as varchar(50))
from BDHW.dbo.DIMDisBand )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 03:06:42

The very same way is suggested in my first post. With AND's this time ;-)

E 12°55'05.63"
N 56°04'39.26"

abcefg
Starting Member

16 Posts

Posted - 2009-03-17 : 07:45:49

Dear Friend.
I would i add 3rd table.

As is shown on script.. >>cast(BDHW.dbo.DIMDis.DIMDisId as varchar(50))

abcefg
Starting Member

16 Posts

Posted - 2009-03-17 : 07:47:01

Sorry,. Typing mistake..
------------------------------------------
How I would add 3rd table

As is shown on script.. >>cast(BDHW.dbo.DIMDis.DIMDisId as varchar(50))

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 08:18:35

[code]SELECT
distinct
''as[DIMDisBendNativeId] ,
BDHW.dbo.DIMDis.DIMDisId AS fk_DIMDisId
,BLIVE.dbo.DisBand.LLimit
,BLIVE.dbo.DisBand.ULimit
,BLIVE.dbo.DisCode.CName
,BLIVE.dbo.DisBand.PDisValue
,BLIVE.dbo.DisBand.ValueDisVal
,BLIVE.dbo.DisBand.Updated_On AS LastModified
,1 AS fkdimcountryid
FROM
BLIVE.dbo.DisBand
INNER JOIN BLIVE.dbo.DisCode ON BLIVE.dbo.DisBand.fkDiscountId = BLIVE.dbo.DisCode.DiscountId
INNER JOIN BDHW.dbo.DIMDis ON BLIVE.dbo.DisCode.DiscountId = BDHW.dbo.DIMDis.DIMDisNativeId
WHERE
exists (select fk_DIMDisId from BDHW.dbo.DIMDisBand where fk_DIMDisId=BDHW.dbo.DIMDis.DIMDisId)
and
not exists
(
select
cast(fk_DIMDisId as varchar(50))
+ cast(LLimit as varchar(50))
+ cast(ULimit as varchar(50))
+ cast(BandName as varchar(50))
+ cast (PDisValue as varchar(50))
+ cast (ValueDisVal as varchar(50))
from
BDHW.dbo.DIMDisBand where
(
cast(fk_DIMDisId as varchar(50))
+ cast(LLimit as varchar(50))
+ cast(ULimit as varchar(50))
+ cast(BandName as varchar(50))
+ cast (PDisValue as varchar(50))
+ cast (ValueDisVal as varchar(50))
)
=
( cast(BDHW.dbo.DIMDis.DIMDisId as varchar(50))
+ cast(BLIVE.dbo.DisBand.LLimit as varchar(50))
+ cast(BLIVE.dbo.DisBand.ULimit as varchar(50))
+ cast(BLIVE.dbo.DisCode.CName as varchar(50))
+ cast (BLIVE.dbo.DisBand.PDisValue as varchar(50))
+ cast (BLIVE.dbo.DisBand.ValueDisVal as varchar(50))
)
)[/code]

abcefg
Starting Member

16 Posts

Posted - 2009-03-17 : 08:23:38

thanks love u brother

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-17 : 09:31:32

Never EVER concatenate strings for this kind of checking?
Why are you not using the WHERE NOT EXISTS I displayed to you earlier?

Consider this

LLimit = '123'
ULimit = '456'

and

LLimit = '12'
ULimit = '3456'

They will be considerad a hit, because the concatenated value is the same, '123456' even though the individual column values are not hits.

sigh.

E 12°55'05.63"
N 56°04'39.26"

abcefg
Starting Member

16 Posts

Posted - 2009-03-17 : 09:57:17

Well,.
As you know i am chicken. i hv limited ideas of query.
-------------------------------
In you First Reply. There Were only two table ,.
How Would i join the 3rd table in 1st example??. the <BDHW.dbo.DIMDis.DIMDisId> is compairing with
<fk_DIMDisId> I also do not want to concat the columns.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 10:05:16

pretty much the same way,,

INSERT	TargetTable
SELECT	DISTINCT
	*
FROM	#DimBandTemp AS dbt
WHERE	NOT EXISTS	(
				SELECT	*
				FROM	TargetTable AS tt
				WHERE	tt.fk1 = dbt.Col1 AND
					tt.fk2 = dbt.Col2 AND
					tt.fk3 = dbt.Col3 AND
					tt.fk4 = dbt.CampaignName AND
					tt.fk5 = dbt.Col5 AND
					tt.fk6 = dbt.Col6
			)
AND EXISTS 
			(	SELECT *
				FROM 	BDHW.dbo.DIMDisBand 
				WHERE  fk_DIMDisId=dbt.DIMDisId
			)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 10:14:48

abcdefg, I hope you know that there is a considerable difference in what your original query and the modified query does. Peso's last post shows the difference.

I hope this is what you need.. just thought of checking since you never posted if you wanted to compare LLimit,ULimit,BandName etc individually or combined.

abcefg
Starting Member

16 Posts

Posted - 2009-03-18 : 02:30:46

SELECT DISTINCT
*
FROM #DimBandTemp AS dbt
WHERE NOT EXISTS (
SELECT *
FROM TargetTable AS tt
WHERE tt.LowerLimit = dbt.LowerLimit
-- tt.fk2 = dbt.Col2 AND
-- tt.fk3 = dbt.Col3 AND
-- tt.fk4 = dbt.CampaignName AND
-- tt.fk5 = dbt.Col5 AND
-- tt.fk6 = dbt.Col6
)
AND EXISTS
( SELECT *
FROM BDHW.dbo.DIMDisBand
WHERE fk_DIMDisId=tt.fkDimDisId
)

--------------------------
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tt.fkDimDiscountId" could not be bound
----------------------------------------------------------------------------

Well in 2nd part of query i want to match the record b/w tt and new table.
Sakets 2000 wrote for dbt.
how can i use tt table instead of dbt in last part of query...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources