DISTINCT query resolution

[shabar]

Hi All



I have the following query


SELECT DISTINCT T_Med.Comp, T_Med.ItemID, T_Med.WP, T_Med.DocName, T_Med.TrfCode, T_Med.CatDescr, T_Med.SubCatDescr, T_LKPWP.RuleCode, T_MedWP.RuleCode, T_Med.Group
FROM (T_Med INNER JOIN T_LKPWP ON (T_Med.DocName=T_LKPWP.SDocName) AND (T_Med.[WP]=T_LKPWP.WP)) LEFT JOIN T_MedWP ON T_LKPWP.RuleCode=T_MedWP.RuleCode
WHERE (((T_MedWP.RuleCode) Is Null));


Because of DISTINCT it takes long time to process. As I found out even though I created indexes for those relevant fields it doesn't help foe DISTINCT. Without DISTINCT same query give results less than one sec


Here T_Med has 1070065 records, T_MedWP has 366315 records and T_LKPWP has 55 records


Is there anyway of achieving the expected results in different way. I tried to create a separate table without DISTINCT, even that took longtime.




Appreciate your comments


Thanks in advance


Cheers

Shabar

[orange]

You have a lot of field in your Select Distinct. What exactly are you trying to do in "business" terms?

[shabar]

Hi Orange

Basically I need to get the DISTINCT value of T_Med.Comp while getting the other column values as well.

[orange]

Can you elaborate and tell us the business rationale?
If T_Med.Comp is a company in your company table (and you haven't told us what any of these tables represents)
why are there duplicates?

We only know what you tell us, and all you've said is your need a Distinct list of T_Med.Comp (with all the other data).

I'm afraid you won't get more focused responses until we understand the situation. What do the tables represent?
What are the table structures? What are the primary keys? What are you try to accomplish? There may be options.

[shabar]

Hi Orange

Basically I have got two csv files which I uploaded to T_Med and T_MedWP. These are from two diiferent
enviroments in the same application. So I am doing data comparison of both the table either way (What are exist in
T_Med and not in T_MedWP)


For comparison I use Lookup table (T_LKPWP) in between. There I use following link columns






T_Med.DocName = T_LKPWP.SDocName
T_Med.WP = T_LKPWP.WP




T_LKPWP.RuleCode=T_MedWP.RuleCode




The issue with main two files has duplicates T_Med.DocName & T_Med.WP and T_MedWP.RuleCode
which I cannot get rid of bcause of other column values


Hope this will clear some what for you to get a understand of the scenario

Thax

[orange]

I don't have an immediate solution for this. But I think you have to address what you have based on:

Is this a 1 time effort to correct the current (unacceptable) set up, or
Are you looking for a way to carry on as you are, but with some automated error/correction?

Almost everyone using database has had a similar situation in which a Customer/Client name and address are collected from different systems/sources that have different edit.validation(if any) and are forced to reconcile this mishmash of records. Which one is correct, if any? Sounds like your situation.

I think this is what we used to call data reduction/data cleansing...
Can you imagine the frustration.confusion/uprising if banks handled accounts and balances in this manner?

Do you have any authoritative source that you could compare your values with?

Resolution requires an answer to the earlier question on what your objective is. And a lot of semi-automated and manual analysis to construct an authoritative table of your own.

I have worked in situations where names and addresses were recorded at different locations using different means, others purchased and the goal was to send specific letters to individuals with minimum duplication. No end to the John Smith, Mr. John Smith, J Smith, John H Smith..... there is no simple answer.
I'm not sure about you but I have a couple of accounts at the bank, and I often get duplicate/replicate marketing info using various combinations of my name and address and my wife's name and initials. For general marketing, the bank doesn't care. Sure, it's a little extra postage, but the intent in this case is marketing. So even the bank has different lists of customers/clients/accounts.

Just some thoughts for consideration.

[shabar]

Thanks for your valuable thoughts Orange.

Basically I have to live with this issue and get a comparison outcome. Data correction is not practical at this stage.

Is there a acceptable solution that I can try out Or else do I need to break those csv files into small parts and then run the above mentioned query to get the comparison

Few more clarifications if you don't mind.....

1. Just wondering how many records will be there in the final result if there is 'n' number of duplicates in T_Med and 'm' number of duplicates in T_MedWP table What can be the possible records that I should get when run the query with out DISTINCT. Because even If I go for count() it doesn't give out put (running for long time)

2. Does the query running machine resources effect the processing result output time?


Cheers

Shabar

[orange]

Can you tell us more about the definition/design of your tables T_Med and T_MedWP?

Can you give some sample records from each?

What exactly is T_LKPWP? Design/structure, sample data
What is it used for specifically?

As for load on a system, there are many factors. What is the basic hardware; what other tasks are been executed?
Are you using some indexing?
You will have to tell us more about your environment if we are going to have more focused responses.

[shabar]

Hi Orange


T_Med contains the legacy data and T_MedWP contains the new application data

Look up basically use to map value in both sides with link keys


Please have look on below sample data

T_LKPWP Table


SDocName Waiting Period RuleCode
OPT 6 months 2
AMB 2 months 3
DEN 12 months 6
DEN 12 months 7
DEN 12 months 8
BGH 12 months 10
EHA 2 months 11
CPH 2 months 11




T_Med Table




Comp WP DocName Item ID
ADVPlus 2 months AMB AMTI
ADVPlus 2 months AMB AMTR
ADVPlus 2 Months CPH SYC4
ADVPlus 2 Months CPH SYC5
ADVPlus 2 Months CPH SYC8
ADVPlus 2 Months PHY PHY1
ADVPlus 2 Months PHY PHY1
ADVPlus 2 Months PHY PHY1
ADVPlus 2 Months PHY PHY1
ADVPlus 2 Months PHY PHY1
ADVPlus 2 Months PHY PHY2
ADVPlus 2 Months PHY PHY3
ADVPlus 2 Months PHY PHY3
ADVPlus 2 Months PHY PHY4
ADVPlus 2 Months PHY PHY4
ADVPlus 2 Months PHY PHY4
ADVPlus 2 Months PHY PHY4
ADVPlus 2 Months PHY PHY6
ADVPlus 2 Months PHY PHY8
ADVPlus 2 Months PHY PHYL




T_MedWP Table


TrfCode CatCode CatDescr SubCatCode SubCatDescr ProdCode RuleCode BenTermAsAt ProdOptIntKey OptCode StateCode
JC02 E17 OTHER TECHS E18 JENCRAIG ST 3 19-Nov-2011 10:26:51 AM 1228 CP002 ACT
AMCO A01 AMB TRANSPORTATION A02 AMCO-PAYMENT ST1 11 19-Nov-2011 10:26:51 AM 1228 CP002 ACT
AMCO A01 AMB TRANSPORTATION A02 AMCO-PAYMENT ST2 11 19-Nov-2011 10:26:51 AM 1228 CP002 ACT
AMCO A01 AMB TRANSPORTATION A02 AMCO-PAYMENT ST3 11 19-Nov-2011 10:26:51 AM 1228 CP002 ACT
AMCO A01 AMB TRANSPORTATION A02 AMCO-PAYMENT ST3 15 19-Nov-2011 10:26:51 AM 1228 CP002 NSW


Please check whether you can get an idea regarding the structure

Cheers

Shabar