July 21, 2008 at 1:09 am
Hi people, unfortunately i've inherited a database where lots of emphasis has been put on the storage of XML strings. Its obvious that the initial design did not include the somewhat important;) fact that databases actually want referential integrity so that retrieval of relational data is accurate and true.
I producing a stored procedure that follows a transaction through the database. The transaction foot prints itself through 7 tables where all of these tables lack a primary key.
Any advice on how best to build a stored procedure to give me the best possible chance of proper data?
July 21, 2008 at 1:48 am
Re-design your database first. Add primary keys to your tables: I've never seen a good reason not to add a primary key to a table.
-- Gianluca Sartori
July 21, 2008 at 3:32 am
Either re-design ur Database or jst add Identity columns in each of ur table, so tht u can easily put joins between them.
July 21, 2008 at 4:46 am
Could you perhaps post a couple of the table structures, so we have a better idea what we're talking about. Also, if possible that proc that you're working on.
Thanks
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
July 21, 2008 at 5:09 am
It will be better for you to create PKs and FKs. But, generally these kind of databases (without PKs and FKs) have alot of data problems. Alot of Duplications, wrong referances and so on.
You have alot of work to do. It will be simpler if you logically look for the relationships and go for the query.
GOD Bless You.
Atif Sheikh
July 21, 2008 at 6:35 am
You've got two different issues you're going to be working with. First, the lack of PK & FK in the database could lead to serious integrity issues. That means the possibility of lots of duplicated or incorrect data. No avoiding that. The other issue, based on what you've said and not said, is that I doubt they've got a good set of clustered indexes on these tables. I seriously doubt there are any good indexes at all.
Without redesigning and rebuilding the database, you can't do much about the first problem. The second problem can be addressed. It will help you. It won't solve all your problems. Follow Gail's advice. Post some structure and some sample code and describe what you're trying to do. If you can also post execution plans for your queries, that will help too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2008 at 7:03 am
MovementCollection:
1.MovementIdentifierContext- 'movement type'
2.MovementIdentifier - '000000012' --->not unique
3.MovementIdentifierComposite - '1+2' -->not unique
4.BinaryData - 'image'
5.XML - 'Full XML'
MovementRelationship
1.MovementIdentifierContext - 'movement type'
2.MovementIdentifier - '00000012' --->not unique
3.MovementIdentifierComposite - '1+2' --->not unique
4.Relationship - 'nature of relationship'
5.RelationshipIdentifierContext - 'type of relationship'
6.RelationshipIdentifierComposite - (1+2+3)
MovementClassification
1.MovementIdentifierContext -'movement type'
2.MovementIdentifier- '00000012' --->not unique
3.MovementIdentifierComposite- (1+2) -->not unique
4.Classification 'base movement'
MovementState
1.MovementIdentifierContext -'movement type'
2.MovementIdentifier- 00000012 --->not unique
3.MovementIdentifierComposite - (1+2) --->not unique
4.State - movent state ie. create, execute, failed, risk, suspend etc...
5.StateTime - time of movment
movementIdentifier is not unique because of state.
Emphasis wasnt put on reporting in this database, the objective was just to store the XML. Now since the db is growing exponentially OBVIOUSLY there is a need for reporting and stats...
Basically i need to follow a movement through all these tables
July 21, 2008 at 7:12 am
Is it safe to assume that MovementIdentifierContext, MovementIdentifier and MovementIdentifierContext, while not unique as noted, combined identify a given Movement? Or is it just the IdentifierContext and IdentifierComposite fields, those look like the join criteria in the Relationship table.
Data types would be helpful too.
Assuming one of the two above, I'd put a clustered index on the two or three columns defined (depending on the data type). That however depends on the queries you're trying to generate. You should show one of those too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2008 at 7:33 am
indexes are gona improve query performance, but without the PK's or FK's i would be joining like crazy to get results, thats IF i get the proper results.... let me hit u with this stored proc im reverse engineering...
declare @temp table
(
[Account Name] varchar(64),
Source_Destination varchar(64),
Date DateTime,
[Merchant Transaction ID] varchar(64),
[PSL Transaction ID] varchar(64),
[PSL Wallet ID] varchar(64),
[Customer First Name] varchar(64),
[Customer Last Name] varchar(64),
[Currency] varchar(30),
[Amount] money,
[Description] varchar(255),
[Fee] money
)
insert @temp
select CA.PublicIdentifier as 'Account Name',
'Destination' as Source_Destination,
AJ.StateTime as 'Date',
MERCHRef.RelationshipIdentifier,
GG.RelationshipIdentifier as 'PSL Transaction ID',
MV.PublicIdentifier as 'PSL Wallet ID',
FNAMELNAME2.FirstName as 'FirstName',
FNAMELNAME2.LastName as 'LastName',
ISNULL(dbo.f_ConvertXML(MCL.Data).value('(/*[1]/*[local-name()="Destination"]/*[local-name()="Amount"]/*[local-name()="Currency"])[1]',
'varchar(30)'),
MCL.XML.value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Currency"])[1]',
'varchar(30)')) AS Currency,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Destination"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]',
'varchar(30)'),
MCL.XML.value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]',
'varchar(30)')) AS Amount,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Annotation"])[1]',
'varchar(30)'),
MCL.XML.value('(/*[1]/*[local-name()="Annotation"])[1]',
'varchar(30)')) AS [Description],
Fee.Amount as 'Fee'
from CardAuthentication CA
inner join ( select MR1.MovementIdentifier,
MR1.RelationshipIdentifier,
MI.StateTime
from MovementRelationship MR1
inner Join ( select MR.MovementIdentifier,
MR.RelationshipIdentifier,
MS.StateTime as StateTime
from MovementRelationship MR
inner join ( select MovementIdentifier,
max(StateTime) as StateTime
from MovementState
group by MovementIdentifier
) MS on MR.MovementIdentifier = MS.MovementIdentifier
where MR.Relationship = 'Movement.Logical'
) MI on MI.MovementIdentifier = MR1.MovementIdentifier
where MR1.Relationship = 'Movement.Destination.Card'
) AJ
inner join ( select MovementRelationship.MovementIdentifier,
MovementRelationship.RelationshipIdentifier
from MovementRelationship
inner join MovementClassification on MovementRelationship.MovementIdentifier = MovementClassification.MovementIdentifier
where MovementRelationship.Relationship = 'Movement.Logical'
and MovementClassification.Classification not like '%Fee%'
) LM on LM.MovementIdentifier = AJ.MovementIdentifier
left outer join ( select MovementIdentifier,
RelationshipIdentifier
from MovementRelationship
where Relationship = 'Movement.Merchant.Reference'
) MERCHRef on MERCHRef.MovementIdentifier = LM.RelationshipIdentifier
inner join ( select MovementIdentifier,
RelationshipIdentifier
from MovementRelationship
where Relationship = 'Movement.Transaction'
and RelationshipIdentifier = @PslRequestId
) GG on GG.MovementIdentifier = LM.RelationshipIdentifier
inner join MovementCollection MCL on MCL.MovementIdentifier = AJ.MovementIdentifier
inner join ( select MovementIdentifier,
PublicIdentifier
from CardAuthentication
inner join MovementRelationship on CardIdentifier = RelationshipIdentifier
where Relationship in (
'Movement.Destination.Card',
'Movement.Source.Card' )
) MV on MV.MovementIdentifier = AJ.MovementIdentifier on AJ.RelationshipIdentifier = CA.CardIdentifier
left outer join ( select MovementIdentifier,
RelationshipIdentifier
from MovementRelationship
where Relationship = 'Movement.Destination.Card'
) SD on SD.MovementIdentifier = AJ.MovementIdentifier
left outer join ( select MovementIdentifier,
RelationshipIdentifier
from MovementRelationship
where Relationship = 'Movement.Source.Card'
) SD2 on SD2.MovementIdentifier = AJ.MovementIdentifier
left outer join ( select EntityIdentifier,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]', 'varchar(64)'),
XML.value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]',
'varchar(64)')) AS [FirstName],
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]', 'varchar(64)'),
XML.value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]',
'varchar(64)')) AS [LastName]
from EntityCollection
) EC on EC.EntityIdentifier = SD.RelationshipIdentifier
left outer join ( select PublicIdentifier,
CardIdentifier
from CardAuthentication
) EC2 on EC2.CardIdentifier = SD2.RelationshipIdentifier
left outer join ( SELECT ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]', 'varchar(30)'),
XML.value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]',
'varchar(30)')) AS FirstName,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]', 'varchar(30)'),
XML.value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]',
'varchar(30)')) AS LastName,
CRS.RelationshipIdentifier,
CRS.CardIdentifier
from ( select CardIdentifier,
RelationshipIdentifier
from CardRelationship
) CRS
inner join EntityRelationship on CRS.RelationshipIdentifier = EntityRelationship.RelationshipIdentifier
inner join EntityCollection ECL on ECL.EntityIdentifier = EntityRelationship.EntityIdentifier
) FNAMELNAME2 on FNAMELNAME2.CardIdentifier = SD2.RelationshipIdentifier
inner join ( select RelationshipIdentifier,
MovementIdentifier
from MovementRelationship
where Relationship = 'Movement.Physical'
) Ph on Ph.MovementIdentifier = LM.RelationshipIdentifier
inner join ( select a.MovementIdentifier,
Sum(ISNULL(a.Amount, 0)) as Amount
from ( select MovementCollection.MovementIdentifier,
MovementClassification.Classification as FeeClassification,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]', 'money'),
XML.value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]', 'money')) AS Amount
from MovementCollection
inner join MovementClassification on MovementClassification.MovementIdentifier = MovementCollection.MovementIdentifier
inner join ( select distinct
( MovementIdentifier )
from MovementRelationship
) MovementRelationship on MovementRelationship.MovementIdentifier = MovementCollection.MovementIdentifier
where MovementClassification.Classification in (
'Fee.Acquirer.PercentageFee',
'Fee.Acquirer.FixedFee',
'Fee.Account.FixedFee',
'Fee.Account.PercentageFee',
'Fee.Account.Fees' )
) a
group by a.MovementIdentifier
) Fee on Fee.MovementIdentifier = Ph.RelationshipIdentifier
where MV.PublicIdentifier like '%@%'
union all
select CA.PublicIdentifier as 'Account Name',
'Source' as Source_Destination,
AJ.StateTime as 'Date',
MERCHRef.RelationshipIdentifier,
GG.RelationshipIdentifier as 'PSL Transaction ID',
MV.PublicIdentifier as 'PSL Wallet ID',
FNAMELNAME.FirstName as 'FirstName',
FNAMELNAME.LastName as 'LastName',
ISNULL(dbo.f_ConvertXML(MCL.Data).value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Currency"])[1]',
'varchar(30)'),
MCL.XML.value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Currency"])[1]',
'varchar(30)')) AS Currency,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]',
'varchar(30)'),
MCL.XML.value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]',
'varchar(30)')) AS Amount,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Annotation"])[1]',
'varchar(30)'),
MCL.XML.value('(/*[1]/*[local-name()="Annotation"])[1]',
'varchar(30)')) AS [Description],
Fee.Amount as 'Fee'
from CardAuthentication CA
inner join ( select MR1.MovementIdentifier,
MR1.RelationshipIdentifier,
MI.StateTime
from MovementRelationship MR1
inner Join ( select MR.MovementIdentifier,
MR.RelationshipIdentifier,
MS.StateTime as StateTime
from MovementRelationship MR
inner join ( select MovementIdentifier,
max(StateTime) as StateTime
from MovementState
group by MovementIdentifier
) MS on MR.MovementIdentifier = MS.MovementIdentifier
where MR.Relationship = 'Movement.Logical'
) MI on MI.MovementIdentifier = MR1.MovementIdentifier
where MR1.Relationship = 'Movement.Source.Card'
) AJ
inner join ( select MovementRelationship.MovementIdentifier,
MovementRelationship.RelationshipIdentifier
from MovementRelationship
inner join MovementClassification on MovementRelationship.MovementIdentifier = MovementClassification.MovementIdentifier
where MovementRelationship.Relationship = 'Movement.Logical'
and MovementClassification.Classification not like '%Fee%'
) LM on LM.MovementIdentifier = AJ.MovementIdentifier
left outer join ( select MovementIdentifier,
RelationshipIdentifier
from MovementRelationship
where Relationship = 'Movement.Merchant.Reference'
) MERCHRef on MERCHRef.MovementIdentifier = LM.RelationshipIdentifier
inner join ( select MovementIdentifier,
RelationshipIdentifier
from MovementRelationship
where Relationship = 'Movement.Transaction'
and RelationshipIdentifier = @PslRequestId
) GG on GG.MovementIdentifier = LM.RelationshipIdentifier
inner join MovementCollection MCL on MCL.MovementIdentifier = AJ.MovementIdentifier
inner join ( select MovementIdentifier,
PublicIdentifier
from CardAuthentication
inner join MovementRelationship on CardIdentifier = RelationshipIdentifier
where Relationship in (
'Movement.Destination.Card',
'Movement.Source.Card' )
) MV on MV.MovementIdentifier = AJ.MovementIdentifier on AJ.RelationshipIdentifier = CA.CardIdentifier
left outer join ( select MovementIdentifier,
RelationshipIdentifier
from MovementRelationship
where Relationship = 'Movement.Destination.Card'
) SD on SD.MovementIdentifier = AJ.MovementIdentifier
left outer join ( select MovementIdentifier,
RelationshipIdentifier
from MovementRelationship
where Relationship = 'Movement.Source.Card'
) SD2 on SD2.MovementIdentifier = AJ.MovementIdentifier
left outer join ( select EntityIdentifier,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]', 'varchar(64)'),
XML.value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]',
'varchar(64)')) AS [FirstName],
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]', 'varchar(64)'),
XML.value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]',
'varchar(64)')) AS [LastName]
from EntityCollection
) EC on EC.EntityIdentifier = SD.RelationshipIdentifier
left outer join ( select PublicIdentifier,
CardIdentifier
from CardAuthentication
) EC2 on EC2.CardIdentifier = SD2.RelationshipIdentifier
left outer join ( SELECT ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]', 'varchar(30)'),
XML.value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="First"])[1]',
'varchar(30)')) AS FirstName,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]', 'varchar(30)'),
XML.value('(/*[1]/*[local-name()="Identity"]/*[local-name()="Name"]/*[local-name()="Last"])[1]',
'varchar(30)')) AS LastName,
CRS.RelationshipIdentifier,
CRS.CardIdentifier
from ( select CardIdentifier,
RelationshipIdentifier
from CardRelationship
) CRS
inner join EntityRelationship on CRS.RelationshipIdentifier = EntityRelationship.RelationshipIdentifier
inner join EntityCollection ECL on ECL.EntityIdentifier = EntityRelationship.EntityIdentifier
) FNAMELNAME on FNAMELNAME.CardIdentifier = SD.RelationshipIdentifier
inner join ( select MovementIdentifier,
RelationshipIdentifier
from MovementRelationship
where Relationship = 'Movement.Physical'
) Ph on Ph.MovementIdentifier = LM.RelationshipIdentifier
left outer join ( select a.MovementIdentifier,
Sum(ISNULL(a.Amount, 0)) as Amount
from ( select MovementCollection.MovementIdentifier,
MovementClassification.Classification as FeeClassification,
ISNULL(dbo.f_ConvertXML(Data).value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]', 'money'), XML.value('(/*[1]/*[local-name()="Source"]/*[local-name()="Amount"]/*[local-name()="Amount"])[1]', 'money')) AS Amount
from MovementCollection
inner join MovementClassification on MovementClassification.MovementIdentifier = MovementCollection.MovementIdentifier
inner join ( select
distinct ( MovementIdentifier )
from MovementRelationship
) MovementRelationship on MovementRelationship.MovementIdentifier = MovementCollection.MovementIdentifier
where MovementClassification.Classification in (
'Fee.Acquirer.PercentageFee',
'Fee.Acquirer.FixedFee',
'Fee.Account.FixedFee',
'Fee.Account.PercentageFee',
'Fee.Account.Fees' )
) a
group by a.MovementIdentifier
) Fee on Fee.MovementIdentifier = Ph.RelationshipIdentifier
where MV.PublicIdentifier like '%@%'
select ll.Source,
mm.Destination,
mm.Date,
mm.[Merchant Transaction ID],
mm.[PSL Transaction ID],
mm.[PSL Wallet ID],
mm.[Customer First Name],
mm.[Customer Last Name],
mm.[Currency],
mm.[Amount],
mm.[Description],
ll.Fee
from ( select case when Source_Destination = 'Source'
then [Account Name]
end as Source,
case when Source_Destination = 'Destination'
then [Account Name]
end as Destination,
Date, --,
[Merchant Transaction ID],
[PSL Transaction ID],
[PSL Wallet ID],
[Customer First Name],
[Customer Last Name],
[Currency],
[Amount],
[Description],
Sum(ISNULL([Fee], 0)) as 'Fee'
from @temp
group by Source_Destination,
[Account Name],
Date, --,
[Merchant Transaction ID],
[PSL Transaction ID],
[PSL Wallet ID],
[Customer First Name],
[Customer Last Name],
[Currency],
[Amount],
[Description]
) ll
inner join ( select case when Source_Destination = 'Source'
then [Account Name]
end as Source,
case when Source_Destination = 'Destination'
then [Account Name]
end as Destination,
Date, --,
[Merchant Transaction ID],
[PSL Transaction ID],
[PSL Wallet ID],
[Customer First Name],
[Customer Last Name],
[Currency],
[Amount],
[Description],
Sum(ISNULL([Fee], 0)) as 'Fee'
from @temp
group by Source_Destination,
[Account Name],
Date, --,
[Merchant Transaction ID],
[PSL Transaction ID],
[PSL Wallet ID],
[Customer First Name],
[Customer Last Name],
[Currency],
[Amount],
[Description]
) mm on ll.[PSL Transaction ID] = mm.[PSL Transaction ID]
where ll.Source IS NOT NULL
and mm.Destination IS NOT NULL
and mm.Date IS NOT NULL
and mm.[Amount] > mm.[Fee]
group by ll.Source,
mm.Destination,
mm.Date,
mm.[Merchant Transaction ID],
mm.[PSL Transaction ID],
mm.[PSL Wallet ID],
mm.[Customer First Name],
mm.[Customer Last Name],
mm.[Currency],
mm.[Amount],
mm.[Description],
ll.Fee
order by Date asc
July 21, 2008 at 11:58 am
thegreatsamarth (7/21/2008)
Either re-design ur Database or jst add Identity columns in each of ur table, so tht u can easily put joins between them.
I am curious as to how just adding Identity columns to each of the table, is going to help with the join?
July 22, 2008 at 2:25 am
Maybe when you have special one-row table (for example with some application parameters), then primary-key is useless 🙂
July 22, 2008 at 9:09 am
Michal Mokros (7/22/2008)
Maybe when you have special one-row table (for example with some application parameters), then primary-key is useless 🙂
A one-row table usually means that columns are being used in place of rows. I can't think of a situation in which a one-row table, defined to have one and only one row, is a good design.
I've seen the one-row parameter table done before. However, it's not a good design. In most cases, the columns should be rows. What you end up with, after a few years and various developers, is a one-row table in which some columns are no longer used and others contain multi-valued attributes because developers are afraid (or not allowed) to alter the schema. These facts can only be ascertained by digging into all the calling code because no one knows what those columns are for. A parameter table, if you must have one, should look more like this:
CREATE TABLE Parameter (
Code VARCHAR(8) PRIMARY KEY,
Value VARCHAR(255),
Description VARCHAR(255) NOT NULL
)
The Code column contains a mnemonic that identifies the parameter. The Value column can be cast to the appropriate data type. The Description column describes what the parameter is and what it's used for. A set of sprocs can be used to get and set parameters, even doing type-checking if necessary.
Because this table uses one row per parameter, it is easy to add new ones instead of making existing ones multi-valued, and easy to delete unused ones.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply