need suggestion to improve following query

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)

need suggestion to improve following query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-10-18 : 09:17:13

SELECT SACD_SchedulerContentID as SAC_SchedulerContentID
,(Select top 1 SAC_SchedulerContentGroup from HIST_STANDALONE_INFO where SAC_SchedulerContentID = @SAC_SchedulerContentID) as SAC_SchedulerContentGroup
,CON_MAILCLASS
,CON_CONSIGNEE_ID
FROM HIST_STANDALONE_CONTENT_CONTAINER_INFO
WHERE SACD_SchedulerContentID = @SAC_SchedulerContentID
AND CON_SHIPPER_CONTENT_ID not in (select CON_SHIPPER_CONTENT_ID from STANDALONE_CONTENT_CONTAINER_INFO where SACD_SchedulerContentID!=@SAC_SchedulerContentID)

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

theMC
Starting Member

2 Posts

Posted - 2010-10-18 : 13:04:05

Hi

You can use virtual table to avoid Sub query But
in this way your query will little big in Size.

Mayukh Chowdhury
Email: mayukhmail@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 13:44:45

keep in mind that using top 1 returns a random value of SAC_SchedulerContentGroup from table satisfying SAC_SchedulerContentID = @SAC_SchedulerContentID. There's no concept of order in table unless you explicitly specify it using an ORDER BY

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

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 14:00:18

quote:
Originally posted by visakh16

There's no concept of order in table unless you explicitly specify it using an ORDER BY

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

I don't think it is entirely true.If you have a clustered index defined on the table the result set by default will be returned sorted based on that index until you don't explicitly specify an order by clause.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 14:05:47

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by visakh16

There's no concept of order in table unless you explicitly specify it using an ORDER BY

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

I don't think it is entirely true.If you have a clustered index defined on the table the result set by default will be returned sorted based on that index until you don't explicitly specify an order by clause.

PBUH

Not always. It depends on lot of other factors also. As a matter of fact, you cant guarantee order of retrieval unless you specify order explicitly

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 14:15:15

from BOL

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

source

http://technet.microsoft.com/en-us/library/ms189463.aspx

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

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 14:20:42

quote:
Not always. It depends on lot of other factors also. As a matter of fact, you cant guarantee order of retrieval unless you specify order explicitly

I wonder what makes sql server to return records in some different order than ones already physically sorted in the data pages.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-10-19 : 14:27:15

quote:
Originally posted by visakh16

from BOL

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

source

http://technet.microsoft.com/en-us/library/ms189463.aspx

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

Yes but I am putting a case that if the table has a clustered index(which of course 99% of them have) then the above argument becomes void.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 14:41:08

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by visakh16

from BOL

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

source

http://technet.microsoft.com/en-us/library/ms189463.aspx

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

Yes but I am putting a case that if the table has a clustered index(which of course 99% of them have) then the above argument becomes void.

PBUH

Again not always

see

http://blog.sqlauthority.com/2007/09/24/sql-server-order-of-result-set-of-select-statement-on-clustered-indexed-table-when-order-by-is-not-used/

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

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-10-19 : 15:44:34

First, move the subquery and set a variable - use the variable in your query. The SAC_SchedulerContentGroup is not going to be different for any row, so no need to include the subquery. This probably won't improve performance much (if at all), but it will be cleaner.

Now, your real performance problem is using a NOT IN - and using a <> (inequality) in that subquery. I would create a temp table to hold all of the included values, then change the NOT IN to an IN.

If I read this correctly, you are getting all of the CON_SHIPPER_CONTENT_ID's that are NOT EQUAL to @SAC_SchedulerContentID - and then excluding them from your outer query. I would think it would be easier to identify those that match - and include them.

Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-19 : 16:33:13

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by visakh16

from BOL

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

source

http://technet.microsoft.com/en-us/library/ms189463.aspx

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

Yes but I am putting a case that if the table has a clustered index(which of course 99% of them have) then the above argument becomes void.

PBUH

That's just not true; it's a myth that has been debunked many times.

If you want something in a certain order, you need to use an ORDER BY.

CODO ERGO SUM

qingxin11
Starting Member

3 Posts

Posted - 2010-10-26 : 03:39:30

spam removed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-26 : 03:59:57

[code]SELECT w.SACD_SchedulerContentID as SAC_SchedulerContentID,
z.SAC_SchedulerContentGroup AS SAC_SchedulerContentGroup,
w.CON_MAILCLASS,
w.CON_CONSIGNEE_ID
FROM HIST_STANDALONE_CONTENT_CONTAINER_INFO AS w
LEFT JOIN STANDALONE_CONTENT_CONTAINER_INFO AS x ON x.CON_SHIPPER_CONTENT_ID = w.CON_SHIPPER_CONTENT_ID
AND x.SACD_SchedulerContentID <> w.SACD_SchedulerContentID
LEFT JOIN (
SELECT MAX(SAC_SchedulerContentGroup) AS SAC_SchedulerContentGroup
FROM HIST_STANDALONE_INFO
GROUP BY SAC_SchedulerContentID
) AS z ON z.SAC_SchedulerContentID = w.SACD_SchedulerContentID
WHERE w.SACD_SchedulerContentID = @SAC_SchedulerContentID
AND x.CON_SHIPPER_CONTENT_ID IS NULL[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources