Query Analysis

Question

Post reply

Query Analysis

deepika_goyal

Old Hand

Points: 375
More actions
October 11, 2012 at 5:17 am

#151103

Dear All,
Need some Suggestion.
I have two tables one is Master and other one is slave.
MY master table has some set of Data and slave tables contains data set from many slaves.
Here is the sample data
Master
No.Type Version Date
1Sales V1.001/10/2012
2Consumption V1.1029/10/2012
3Daily Needs V1.0113/08/2011
4Overall V2.0101/09/2012
Slave
CodeNo.Type Version Date
S0011Sales V1.001/10/2012
S0012ConsumptionV1.1029/10/2012
S0013Daily NeedsV1.0113/08/2011
S0014Overall V2.0101/09/2012
S0015ElectricityV5.0118/10/2012
S0021Sales V1.0 01/10/2012
S0022ConsumptionV1.1015/10/2012
S0023Daily NeedsV1.100013/08/2011
S0031Sales V1.0 01/10/2012
S0032ConsumptionV1.1029/10/2012
S0034Overall V2.01 01/09/2012
S0033Daily NeedsV1.0113/08/2011
S0034Overall V2.01 01/09/2012
S0035ElectricityV5.0118/10/2012
Now, i have to compare Master with slave and then Slave with Master.
Here are conditions.
1. IF my master and slave matches then report says MATCH
2. IF my master and slave doesnt matches then report says MISMATCH
3. IF my Master contains more data than slave then report says Missing at Slave
4. IF my slave contains more data than Master then report says Missing data at Master.
i know how to get the data, but i want quickest way as my slave table contains millions of record, i want my process to be very quick.
Summarization of test cases.
Final Report
MasterSlaveStatus
CodeNo.TypeVersionDateVersion Date
S0011 ALL Match
S0022 Daily NeedsV1.0113/08/2011 V1.10013 /08/2011Mismatch
S0023Daily NeedsV1.0113/08/2011V1.100013/08/2011Mismatch
S0024OverallV2.0101/09/2012V5.0118/10/2012Missing in Slave
S0035ElectricityV5.0118/10/2012Missing in Master
PLease let me know if anybody have quick way to do it.
thanks
Regards.
Deepika

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

deepika_goyal Old Hand Points: 375 More actions · Answer 1

Hello!!,

NO body has replied on thiss.

Please give some suggestion.

Thanks!!

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

IF you show your code for the solution and the execution plan then you may get some suggestions on optimization.

Clear Sky SQL
My Blog[/url]

deepika_goyal Old Hand Points: 375 More actions · Answer 3

Thanks Dave for your reply..

Still i have to Code, i want to know good approach so that i can start my development.

please let me know any way.

thanks a lot

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

This sounds like a relational division problem,ie some rows, equal rows, more rows...

Joe Celko has done a good write up on the subject, http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

Clear Sky SQL
My Blog[/url]

deepika_goyal Old Hand Points: 375 More actions · Answer 5

Thanks Dave!!:-):-)

will go through it.

Regards,

Deepika

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

deepika_goyal (10/11/2012)
Hello!!,
NO body has replied on thiss.
Please give some suggestion.
Thanks!!

Hello

You are likely to get a much faster response if you provide DDL for your sample data. Here's your 'master' table, can you do the same with the 'slave' table? Make sure it runs without errors and that the data is correct before you post it. Thanks.

CREATE TABLE #Master ([No] INT, [Type] VARCHAR(20), [Version] VARCHAR(10), [Date] DATE)

SET DATEFORMAT DMY

INSERT INTO #Master ([No], [Type], [Version], [Date])

SELECT 1, 'Sales', 'V1.0', '01/10/2012' UNION ALL

SELECT 2, 'Consumption', 'V1.10', '29/10/2012' UNION ALL

SELECT 3, 'Daily Needs', 'V1.01', '13/08/2011' UNION ALL

SELECT 4, 'Overall', 'V2.01', '01/09/2012'

SELECT * FROM #Master

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden