QUERY HELP - 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 2008 Forums

Transact-SQL (2008)

QUERY HELP

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-16 : 04:08:24

Is this query correctly? How do I change the.
I want to give the same result.i want to modify this query for performance.

SELECT
TEST_PAGE
FROM
TEST_TEST
WHERE
IS_VIEW = 1 AND
TEST_TYPE = 1 AND
MODULE_ID = 47 AND
TEST_PAGE NOT IN
(
SELECT
TEST_PAGE
FROM
TEST_TEST EPD,
TEST EP
WHERE
EPD.IS_VIEW = 1 AND
EPD.TEST_TYPE = 1 AND
EPD.MODULE_ID = 47 AND
EP.POSITION_CODE = 5AND
(
EPD.POSITION_CODE = EP.POSITION_CODE OR
EPD.POSITION_CAT_ID = EP.POSITION_CAT_ID OR
EPD.USER_GROUP_ID = EP.USER_GROUP_ID
)
)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-16 : 04:24:03

Do you have any indexes?
Depends on the size of the tables and distribution of data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-16 : 04:31:27

everything(index) is okay, but only i want modify the query .i think maybe i dont need (not in) , i do it with only one (anti join). is it possible?

quote:
Originally posted by nigelrivett

Do you have any indexes?
Depends on the size of the tables and distribution of data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-16 : 04:49:32

SELECT tp.TEST_PAGE
FROM TEST_TEST tp
left join ( SELECT
TEST_PAGE
FROM
TEST_TEST EPD,
TEST EP
WHERE
EPD.IS_VIEW = 1 AND
EPD.TEST_TYPE = 1 AND
EPD.MODULE_ID = 47 AND
EP.POSITION_CODE = 5AND
(
EPD.POSITION_CODE = EP.POSITION_CODE OR
EPD.POSITION_CAT_ID = EP.POSITION_CAT_ID OR
EPD.USER_GROUP_ID = EP.USER_GROUP_ID
)
) tmp
on tp.TEST_PAGE = tmp.TEST_PAGE
WHERE
tp.IS_VIEW = 1 AND
tp.TEST_TYPE = 1 AND
tp.MODULE_ID = 47 AND
tmp.TEST_PAGE is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-16 : 04:59:32

I think this might be th same as the code you have

SELECT tp.TEST_PAGE
FROM TEST_TEST tp
WHERE
and tp.IS_VIEW = 1
and TEST_TYPE = 1
and MODULE_ID = 47
and tp.POSITION_CODE <> 5
and tp.POSITION_CAT_ID not in (select distinct POSITION_CAT_ID from TEST)
and tp.USER_GROUP_ID not in (select distinct USER_GROUP_ID from TEST)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-16 : 05:03:27

This query bad than orginal query by performance

quote:
Originally posted by nigelrivett

SELECT tp.TEST_PAGE
FROM TEST_TEST tp
left join ( SELECT
TEST_PAGE
FROM
TEST_TEST EPD,
TEST EP
WHERE
EPD.IS_VIEW = 1 AND
EPD.TEST_TYPE = 1 AND
EPD.MODULE_ID = 47 AND
EP.POSITION_CODE = 5AND
(
EPD.POSITION_CODE = EP.POSITION_CODE OR
EPD.POSITION_CAT_ID = EP.POSITION_CAT_ID OR
EPD.USER_GROUP_ID = EP.USER_GROUP_ID
)
) tmp
on tp.TEST_PAGE = tmp.TEST_PAGE
WHERE
tp.IS_VIEW = 1 AND
tp.TEST_TYPE = 1 AND
tp.MODULE_ID = 47 AND
tmp.TEST_PAGE is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-16 : 05:09:43

[code]SELECT DISTINCT tt.TEST_PAGE
FROM dbo.TEST_TEST AS tt
LEFT JOIN dbo.TEST AS t ON t.POSITION_CODE = tt.POSITION_CODE
AND (
t.POSITION_CAT_ID = tt.POSITION_CAT_ID
OR t.USER_GROUP_ID = tt.USER_GROUP_ID
)
WHERE tt.IS_VIEW = 1
AND tt.TEST_TYPE = 1
AND tt.MODULE_ID = 47
AND t.POSITION_CODE IS NULL[/code]

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

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-16 : 05:20:43

THANK sir but query result is not same by orginal query.

quote:

Originally posted by SwePeso

SELECT DISTINCT	tt.TEST_PAGE
FROM		dbo.TEST_TEST AS tt
LEFT JOIN	dbo.TEST AS t ON t.POSITION_CODE = tt.POSITION_CODE
			AND	(
					t.POSITION_CAT_ID = tt.POSITION_CAT_ID
					OR t.USER_GROUP_ID = tt.USER_GROUP_ID
				)
WHERE		tt.IS_VIEW = 1
		AND tt.TEST_TYPE = 1
		AND tt.MODULE_ID = 47
		AND t.POSITION_CODE IS NULL

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-16 : 05:41:04

Think I made a similar mistake to SwePeso

SELECT tp.TEST_PAGE
FROM TEST_TEST tp
WHERE
and tp.IS_VIEW = 1
and TEST_TYPE = 1
and MODULE_ID = 47
and tp.POSITION_CODE <> 5
and tp.POSITION_CAT_ID not in (select distinct POSITION_CAT_ID from TEST where POSITION_CODE = 5)
and tp.USER_GROUP_ID not in (select distinct USER_GROUP_ID from TEST POSITION_CODE = 5)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-08-16 : 06:11:00

thank you sir;
i examine query plan, query plan of this query is the same query plan of orginal query.

quote:
Originally posted by nigelrivett

Think I made a similar mistake to SwePeso

SELECT tp.TEST_PAGE
FROM TEST_TEST tp
WHERE
and tp.IS_VIEW = 1
and TEST_TYPE = 1
and MODULE_ID = 47
and tp.POSITION_CODE <> 5
and tp.POSITION_CAT_ID not in (select distinct POSITION_CAT_ID from TEST where POSITION_CODE = 5)
and tp.USER_GROUP_ID not in (select distinct USER_GROUP_ID from TEST POSITION_CODE = 5)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-16 : 07:47:20

Doesn't surprise me too much.
Are you still sure that your indexes and structures are the best they can be?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources