March 10, 2004 at 5:36 pm
Ok, this is ugly, I hope I explain it well.
I'm dealing with a system that allows a user to add a form, and different types of controls to the form. Then, it stores the data in a horrible way.
In one table it will enter a log Id, and then in a second table it stores the following
LogId, dataId (the id of the control on the form), and the data recorded for that control.
I need to be able to report from this data and it's getting to be a real pain.
here's the type of query I've been using
Select eventlog.event_Id, [2100].data as [2100], [2101].data as [2101] FROM
eventlog inner join clindata [2100] on eventlog.event_Id = [2100].event_Id and [2100].data_Id = 2100 Inner Join Clindata [2101] on eventlog.event_Id = [2101]event_Id and [2101].data_Id = 2101
And so on. These forms (applications calls them events, but they're really forms), can and often do hold more than 50 different controls (50 different data_Id's i.e. [2100] and [2101]). As you can see I hope this gets really annoying and terribly slow. I'm trying to report from a form holding well over 150 of these data items,
Does anyone have any good ideas / techniques I can use to make this more efficient? To give you an idea of what a mess this is, there are about 160,000 different eventlog id's (different instantiations of the forms), and over 2.5 million records in the clindata table (the one holding the id of the control on a form and that control's data)
Any ideas are appreciated!
Thanks
Eric
March 16, 2004 at 9:58 am
I think this may give you some idea to implement, above explained situation.
write in-line xml data and try binding all the controls to local to it.
So, when you submit the data submit only xml data. Your second table should consist
LoginId, xml data (text type etc.,). This makes retrieval also easy and effort to display the data is zero.
G R Konda
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply