2. ASPRunnerPro General questions
  3. Multiple keys on link to detail table
This topic is locked

Multiple keys on link to detail table

 		5/5/2007 05:47:55
ASPRunnerPro General questions
R
 rgke author

Almost all of my database tables have composite keys: eg.
MASTER TABLE

UserName

 ProposalNumber
DETAIL TABLE

UserName

 ProposalNumber

** Product
ASPRUNNER only allows you to specify a single field as the link key between the two (unless I have missed something), and yet as you can see, I need two here.
As a result, I am having to create combined keys in the underlying SQL views, which makes it exceedingly slow to retreve the data. Is there any way round this? I asked about this some time ago, and was led to believe it would be in a future release, but I do not see it in the latest.
Any help would be appreciated - this is a brilliant product, and I can't see it would be very complex to add this feature...
Sergey Kornilov admin 5/7/2007

This feature is planned for the next release (late summer 2007).
R
 		rgke author 5/8/2007

Ok, that is good to know.
Is there a work-around in the mean-time? I had a look at the underlying pages, but could not see where to modify
Sergey Kornilov admin 5/8/2007

No, there is no reasonble workaround at this time.
Alberto 5/9/2007

YESSS there is a really easy work around and it works perfectly.
Just add temporarily (While this option is put on the market for us) a fix field to your table(s) and/or view(s) that shows all key fields together is SQL Server I works like this FiledZ is defined as this formula ([FiledA] + [FiledB] + [FiledC])
In your case
MASTER TABLE

UserName

 ProposalNumber

::::::::::

++ UserNameProposalNumber
DETAIL TABLE

UserName

 ProposalNumber

** Product

::::::::::

++ UserNameProposalNumber
Then use those fields for this purpose only. They are not updateable and the can be removed whenever you want as you will not use them for nothing else but for the link between MASTER/DETAIL relation.
Make sure not to show those on your pages too unless you need them there or planning to live them there forever.
I hope It helps
My table in SQL

CREATE TABLE [dbo].[BT_DBP_DETAILS] (

[CampaignTypeStatusCampaign] AS ([CampaignType] + [Status] + [Campaign]) ,

[CampaignType] [varchar] (10) COLLATE Latin1_General_BIN NOT NULL ,

[Status] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,

[Category] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,

[Age] [varchar] (10) COLLATE Latin1_General_BIN NULL ,

[Campaign] [nvarchar] (100) COLLATE Latin1_General_BIN NULL ,

[StartDate] [datetime] NULL ,

[PatientID] [varchar] (15) COLLATE Latin1_General_BIN NOT NULL ,

[AssociatedOn] [datetime] NULL ,

[AssociationID] [varchar] (38) COLLATE Latin1_General_BIN NULL ,

[AddUpdated] [varchar] (40) COLLATE Latin1_General_BIN NULL ,

[Gender] [varchar] (10) COLLATE Latin1_General_BIN NULL ,

[DrBosley] [varchar] (50) COLLATE Latin1_General_BIN NULL ,

[Country] [varchar] (30) COLLATE Latin1_General_BIN NULL ,

[Candidate] [varchar] (30) COLLATE Latin1_General_BIN NULL ,

[Inactive] [varchar] (20) COLLATE Latin1_General_BIN NULL ,

[DmError] [varchar] (50) COLLATE Latin1_General_BIN NULL ,

[NoMail] [nchar] (1) COLLATE Latin1_General_BIN NULL

) ON [PRIMARY]
R
 		rgke author 5/11/2007

Alberto
Thanks for this. Now in fact, I have tried this in the SQL Views to achieve this, but the problem is that the detail table has over half a million records, and the response time is very slow. I imagine that SQL is having to sift through all the records.
So, is it possible to do the same thing, but have it indexed in some way to improve the response?
I guess this might turn out to be a SQL question rather than ASPRunner
Sergey Kornilov admin 5/11/2007

I think you can use indexed views:

http://www.microsoft.com/technet/prodtechn...5/impprfiv.mspx