Data Difference.... - 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

General SQL Server Forums

New to SQL Server Programming

Data Difference....

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-12-06 : 04:43:52

Hi M with an Another problem....
I want the Data Difference between two tables as i have two tables Like....

Tbl_A

Slab_Id Cust_Id
1 457745
4 47846
7 145797

Tbl_B

slab_id cust_id voch_entry

....Some Data

so i like to take differnce between the data ....in both tables...
as tbl_B have not all the values in tbl_A

The key fields for data is Slab_id and cust_id......

i use that query for that purpose but its too slow on the data of more then 4 lacs record......

i post that query..please optimize that query for me...

Select slab_id,cust_id
FROM Tbl_A
WHERE NOT EXISTS
(SELECT 1 FROM tbl_B
WHERE Tbl_A.slab_ID = tbl_B.slab_ID
AND Tbl_A.cust_id = tbl_B.cust_id
)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2008-12-06 : 08:40:39

That's about the most efficient version you can write. If the performance is poor, it's likely because Slab_id and/or Cust_id are not indexed in one or both tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 00:52:09

try this too and see if its any better


Select slab_id,cust_id
FROM Tbl_A 
LEFT JOIN tbl_B 
ON Tbl_A.slab_ID = tbl_B.slab_ID 
AND Tbl_A.cust_id = tbl_B.cust_id 
WHERE  tbl_B.slab_ID IS NULL

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-12-08 : 00:32:19

ohkkk i tried it.....
i also indexed on slab_id,,,cust_id is already indexed,,,
i also tried vishak's method it also takes same time to return results any another method to get results,,,
i have an idea,,,
but m not able to apply that,,,
i like to use,,,
UNIONAll in between these,,,,
so any body can help me in this,,,plzz,
its important for me,,

vijayraj105
Starting Member

2 Posts

Posted - 2008-12-09 : 05:18:54

try Except.......

Select slab_id,cust_id
FROM Tbl_A
Except
Select slab_id,cust_id
FROM Tbl_B

i think performance will be better if u r retrieving only two fileds....slab_id,cust_id

Regards,
Vijayraj

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-09 : 08:15:43

Are slab_id and cust_id indexed?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 09:32:56

quote:
Originally posted by vijayraj105

try Except.......

Select slab_id,cust_id
FROM Tbl_A
Except
Select slab_id,cust_id
FROM Tbl_B

i think performance will be better if u r retrieving only two fileds....slab_id,cust_id

Regards,
Vijayraj

i've heard EXCEPT is a costly operation. would be interesting to see how it perform

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources