January 1, 2008 at 9:48 pm
Hi
I have pasted two queries below. could you able to suggest which query is better? First query is using Exits clause and second query is using joins...
UPDATE JV_ACCOUNT
SET CUSTOMER_ID=SC.CUSTOMER_ID
FROM #JV_SACCODE SC,DBO.JV_ACCOUNT ACC, DBO.JV_CUSTOMER CUST1
WHERE ACC.ACC_NO=SC.ACC_NO AND
ACC.SOURCE_SYS_ID=SC.SOURCE_SYS_ID AND
EXISTS
(SELECT 'RECORD' FROM DBO.JV_CUSTOMER CUST
WHERE ACC.CUSTOMER_ID=CUST.CUSTOMER_ID AND
CUST.DUMMY_SAC='Y') ANDCUST1.CUSTOMER_ID=SC.CUSTOMER_ID
-------------------------------------------------------
UPDATE JV_ACCOUNT
SET CUSTOMER_ID=SC.CUSTOMER_ID
FROM #JV_SACCODE SC,DBO.JV_ACCOUNT ACC, DBO.JV_CUSTOMER CUST1,DBO.JV_CUSTOMER cust
WHERE ACC.ACC_NO=SC.ACC_NO AND
ACC.SOURCE_SYS_ID=SC.SOURCE_SYS_ID AND
ACC.CUSTOMER_ID=CUST.CUSTOMER_ID AND
CUST.DUMMY_SAC='Y' AND CUST1.CUSTOMER_ID=SC.CUSTOMER_ID
January 1, 2008 at 10:17 pm
Second one is the best
January 1, 2008 at 11:30 pm
Have you tried them?
Run them both with the execution plan enabled and see which SQL says has the higher cost. See which (if either) runs faster, and see which (if either) has lower IOs (use SET Statistics IO ON for that)
My guess is that they will be the same.
p.s. Do your joins in the from clause, not the where clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2008 at 12:31 am
Hi,
why joins should be in the from clause not in where clause?
could you pls explain it...
January 2, 2008 at 1:05 am
Joins done in the from are easier to read and reduce the chances of getting an unwanted cross join.
In addition, the old-style outer join syntax (Table1.Col1 *= Table2.Col1) is deprecated and no longer works in SQL 2005 (if the compatability mode is set to 90)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply