Any inputs to optimize query using cross apply

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)

Any inputs to optimize query using cross apply

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-10-14 : 01:22:28

Hi,
I have a table with 2 columns having values with ~ separated integer values. I need to select particular records having matching values in these columns. Below is sample table structure for the table and query I have written. Please provide any inputs to optimize it or any alternate way to accomplish the required result.

Declare @Codes table
(
Code varchar(30),
OPTION_1 varchar(30),
OPTION_2 Varchar(30)
)

INSERT INTO @Codes (Code,Option_1,Option_2)
SELECT '101','1~16','1~2~6' UNION ALL
SELECT '102','1~37','2~3~6' UNION ALL
SELECT '103','16','2' UNION ALL
SELECT '104','1','5' UNION ALL
SELECT '105','16~37','2~6' UNION ALL
SELECT '106','1','6' UNION ALL
SELECT '107','37','1~2~3~4~6'

Select * from @Codes

SELECT DISTINCT TBLRESULT.CODE
FROM (SELECT TBLWEBSITECODES.CODE AS CODE,
TBLUSERGROUPCODES.ITEM AS USERGROUP
FROM (SELECT AC.CODE,
T2.ITEM
FROM @Codes AC
CROSS APPLY DBO.test_web_gettablefromcsv_fn(OPTION_1,'~') T2
) TBLUSERGROUPCODES
INNER JOIN (SELECT TBLCATEGORIES.CODE AS CODE,
TBLCATEGORIES.ITEM
FROM (SELECT AC.CODE AS CODE,
TBLOPTION1.ITEM
FROM @Codes AC
CROSS APPLY test_web_gettablefromcsv_fn(OPTION_2,'~') TBLOPTION1
) TBLCATEGORIES
INNER JOIN test_web_gettablefromcsv_fn('2~4','~') TBLWEBSITES --2~4 is value for SP parameter @Sites
ON TBLWEBSITES.ITEM = TBLCATEGORIES.ITEM) TBLWEBSITECODES
ON TBLUSERGROUPCODES.CODE = TBLWEBSITECODES.CODE) TBLRESULT
INNER JOIN test_web_gettablefromcsv_fn('16~37','~') TBLUSERGROUPS --16~37 is value for SP parameter @USERGROUP
ON TBLRESULT.USERGROUP = TBLUSERGROUPS.ITEM
OR TBLRESULT.USERGROUP = '1'
GO

Below is code for Function used for getting table from ~ separated values.
CREATE FUNCTION dbo.test_WEB_GETTABLEFROMCSV_FN
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
@ParsedList TABLE
(
item VARCHAR(MAX)
)
AS
BEGIN
DECLARE @item VARCHAR(MAX), @Pos INT
SET @List = LTRIM(RTRIM(@List))+ @Delim
SET @Pos = CHARINDEX(@Delim, @List, 1)
WHILE @Pos > 0
BEGIN
SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
IF @item <> ''
BEGIN
INSERT INTO @ParsedList (item)
VALUES (CAST(@item AS VARCHAR(MAX)))
END
SET @List = RIGHT(@List, LEN(@List) - @Pos)
SET @Pos = CHARINDEX(@Delim, @List, 1)
END
RETURN
END

On running above query I get following result as it gets all columns with site values 2,4 and User group 1,16 and 37:
CODE

101
102
103
105
107

Please provide inputs to improve the query.

Regards

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-10-20 : 02:20:35

Its hard to believe that no one has responded to my query. Please provide me some inputs to improve the query. Please let me know if should provide more details to get my query answered.

Regards,

Sunil

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-20 : 03:34:39

What is your input againt @Codes table?

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-10-20 : 04:37:57

quote:
Originally posted by madhivanan

What is your input againt @Codes table?

Madhivanan

Failing to plan is Planning to fail

Thanks for your reply madhi. Inputs against @Codes table are ~ separated values. Below is query written in my stored procedure which has 2 parameters @USERGROUP and @Sites.

SELECT DISTINCT TBLRESULT.CODE
FROM (SELECT TBLWEBSITECODES.CODE AS CODE,
TBLUSERGROUPCODES.ITEM AS USERGROUP
FROM (SELECT AC.CODE,
T2.ITEM
FROM @Codes AC
CROSS APPLY DBO.test_web_gettablefromcsv_fn(OPTION_1,'~') T2
) TBLUSERGROUPCODES
INNER JOIN (SELECT TBLCATEGORIES.CODE AS CODE,
TBLCATEGORIES.ITEM
FROM (SELECT AC.CODE AS CODE,
TBLOPTION1.ITEM
FROM @Codes AC
CROSS APPLY test_web_gettablefromcsv_fn(OPTION_2,'~') TBLOPTION1
) TBLCATEGORIES
INNER JOIN test_web_gettablefromcsv_fn('2~4','~') TBLWEBSITES -- 2~4 is value for SP parameter @Sites
ON TBLWEBSITES.ITEM = TBLCATEGORIES.ITEM) TBLWEBSITECODES
ON TBLUSERGROUPCODES.CODE = TBLWEBSITECODES.CODE) TBLRESULT
INNER JOIN test_web_gettablefromcsv_fn('16~37','~')TBLUSERGROUPS -- 16~37 is value for SP parameter @USERGROUP
ON TBLRESULT.USERGROUP = TBLUSERGROUPS.ITEM
OR TBLRESULT.USERGROUP = '1'
GO

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-10-21 : 02:26:51

No reply!!. Am i missing any details for my query?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-21 : 04:10:24

@sites for Option_1 and @USERGROUP fro Option_2?

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-10-21 : 04:32:56

quote:
Originally posted by madhivanan

@sites for Option_1 and @USERGROUP fro Option_2?

Madhivanan

Failing to plan is Planning to fail

No, its other way. @SITES for OPTION_2 and @USERGROUP for OPTION_1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-21 : 05:08:11

Try this
declare @SITES varchar(100),@USERGROUP varchar(100)
select @SITES ='2~4',@USERGROUP='16~37'
select * from
(
Select * from @Codes as c cross apply DBO.test_web_gettablefromcsv_fn(OPTION_1,'~') as spl1
where '~'+@USERGROUP+'~' like '~'+spl1.item+'~'
) as t cross apply DBO.test_web_gettablefromcsv_fn(OPTION_2,'~') as spl2
where '~'+@SITES+'~' like '~'+spl2.item+'~'

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-10-21 : 10:35:21

quote:
Originally posted by madhivanan

Try this
declare @SITES varchar(100),@USERGROUP varchar(100)
select @SITES ='2~4',@USERGROUP='16~37'
select * from
(
Select * from @Codes as c cross apply DBO.test_web_gettablefromcsv_fn(OPTION_1,'~') as spl1
where '~'+@USERGROUP+'~' like '~'+spl1.item+'~'
) as t cross apply DBO.test_web_gettablefromcsv_fn(OPTION_2,'~') as spl2
where '~'+@SITES+'~' like '~'+spl2.item+'~'

Madhivanan

Failing to plan is Planning to fail

I tried following but it did not return any result.

Declare @Codes table
(
Code varchar(30),
OPTION_1 varchar(30),
OPTION_2 Varchar(30)
)

INSERT INTO @Codes (Code,Option_1,Option_2)
SELECT '101','1~16','1~2~6' UNION ALL
SELECT '102','1~37','2~3~6' UNION ALL
SELECT '103','16','2' UNION ALL
SELECT '104','1','5' UNION ALL
SELECT '105','16~37','2~6' UNION ALL
SELECT '106','1','6' UNION ALL
SELECT '107','37','1~2~3~4~6'
declare @SITES varchar(100),@USERGROUP varchar(100)
select @SITES ='2~4',@USERGROUP='16~37'
select * from
(
Select * from @Codes as c cross apply DBO.test_web_gettablefromcsv_fn(OPTION_1,'~') as spl1
where '~'+@USERGROUP+'~' like '~'+spl1.item+'~'
) as t cross apply DBO.test_web_gettablefromcsv_fn(OPTION_2,'~') as spl2
where '~'+@SITES+'~' like '~'+spl2.item+'~'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-22 : 02:47:55

Try

select * from
(
Select * from @Codes as c cross apply DBO.test_web_gettablefromcsv_fn(OPTION_1,'~') as spl1
where '~'+@USERGROUP+'~' like '%~'+spl1.item+'~%'
) as t cross apply DBO.test_web_gettablefromcsv_fn(OPTION_2,'~') as spl2
where '~'+@SITES+'~' like '%~'+spl2.item+'~%'

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2009-10-26 : 02:01:26

quote:
Originally posted by madhivanan

Try

select * from
(
Select * from @Codes as c cross apply DBO.test_web_gettablefromcsv_fn(OPTION_1,'~') as spl1
where '~'+@USERGROUP+'~' like '%~'+spl1.item+'~%'
) as t cross apply DBO.test_web_gettablefromcsv_fn(OPTION_2,'~') as spl2
where '~'+@SITES+'~' like '%~'+spl2.item+'~%'

Madhivanan

Failing to plan is Planning to fail

Thanks Madhi, It gives the required result and looks efficient too.
I will test this query with different scenarios. Thanks again for your help.

Regards,

Sunil

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-26 : 02:53:34

quote:
Originally posted by sunil

quote:
Originally posted by madhivanan

Try

select * from
(
Select * from @Codes as c cross apply DBO.test_web_gettablefromcsv_fn(OPTION_1,'~') as spl1
where '~'+@USERGROUP+'~' like '%~'+spl1.item+'~%'
) as t cross apply DBO.test_web_gettablefromcsv_fn(OPTION_2,'~') as spl2
where '~'+@SITES+'~' like '%~'+spl2.item+'~%'

Madhivanan

Failing to plan is Planning to fail

Thanks Madhi, It gives the required result and looks efficient too.
I will test this query with different scenarios. Thanks again for your help.

Regards,

Sunil

Ok. Post your performance result

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources