Need help on sql query - 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 2005 Forums

Transact-SQL (2005)

Need help on sql query

Author

Topic

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-07-05 : 02:03:25

Hi ,

I have the following 2 queries which returned the same result.

My question is why the 2nd query takes so much longer to completed compared to the first query. Thanks

1) SELECT
COUNT(1)
FROM LOAD_COND_TBL_DATA stage
INNER JOIN intermediate_sap_condition_keys isck
INNER JOIN CUSTOMER vt ON (vt.CUSTOMER_NUMBER = isck.sap_column_value )
ON (stage.load_filename = isck.load_filename AND stage.file_record_no = isck.file_record_no)
WHERE stage.LOAD_FILENAME = 'GSAP.CONDTBLDATA.20100609134321.ctm'
AND isck.sap_column = 'KUNAG'
AND PENDING_RECORD_FLAG <> 'N' AND stage.PENDING_FOREIGN_KEY = 'CUSTOMER'

2) SELECT
COUNT(1)
FROM LOAD_COND_TBL_DATA stage
INNER JOIN intermediate_sap_condition_keys isck
ON (stage.load_filename = isck.load_filename AND stage.file_record_no = isck.file_record_no)
WHERE stage.LOAD_FILENAME = 'GSAP.CONDTBLDATA.20100609134321.ctm'
AND isck.sap_column = 'KUNAG'
AND EXISTS ( SELECT 1 FROM CUSTOMER vt WHERE vt.CUSTOMER_NUMBER = isck.sap_column_value )
AND PENDING_RECORD_FLAG <> 'N' AND stage.PENDING_FOREIGN_KEY = 'CUSTOMER'

beniaminus
Starting Member

21 Posts

Posted - 2010-07-05 : 07:37:03

I'm not entirely sure, but I would have thought it would be to do with your sub-select in the second query. The joins in the first query will speed up the executing time because you are explicitly mapping the columns. Effectivly your second query is doing more work, so the execution time will be longer.

Ben :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-05 : 08:05:16

I think it depends. And we don't know anything about number of records and indexes.
A similar test has given me two similar execution plans.
But if there are lots of records in the customer table AND many customers do not exist then the advantange of exists() is gone away because many customer_numbers will be searched til the end of the data ie. exists() can't stop so soon because there is no hit.

Maybe some other mates here have another mind.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources