June 28, 2002 at 7:44 am
There are 2 "tables" in the unidata db. Contractor_master(CM) and Contractor_Driver_Profile(CDP.
I am going to be forced to create an active script for this(as the data pulls way too slow on a SIMPLE select). I am going to be pulling 5 fields from CM and 14 fields from CDP. Should I leave the structure alone and put it in 2 tables or should I combine them into 1 table? Most of the fields are date fields/int. I also will be grabbing all of the data at once. INSERTS and UPDATES are going to be done every 5 or 10 or 15 minutes.
My thought would be to put it in one table since I will be selecting the whole thing anyway.
Any suggestions are welcome,
Matt
June 28, 2002 at 9:11 am
If your select is slow the first place to look is your indexing strategy on the two tables. Looks to me that you will have a primary key of Contractor ID on Contractor Master. If you put that as a Foreign Key on Contractor Driver Profile you'll be quite a long way along the road.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 28, 2002 at 9:21 am
Unidata is NOT a relational db system. ITs whats called an extended relational database system (like OLAP I guess) and well its indexed. Actually indexing the PK,FK of the 2 files made it go slower. Besides that it has to convert the date/time/number fields since its in unix format. Everytime I have to select it has to convert all the data to OLEDB standards so I can actually get the data and I have a feeling that is what is making it really slow. It runs super fast on the UNIX system. Just not when I try to select it off the windows server(W2K). So that is why I am going the DTS/active script route. There is not too much data there (12K records but 95% of them won't change) so I am only gonna pull the ones that were last updated at timed intervals. Then I will select off SQL Server for my application which should run super fast. Their tech support at IBM is lousy too since the DB system is limited to what it can do. Thats why I was asking if it would be better when I transfer the data to put it in 2 tables or 1 table.
Matt
June 28, 2002 at 10:29 am
Never mind, I figured exactly how to do it. I will store all the data in the 1 table and in the 2nd table I will store the 2 fields that have multivalue data. Which would make the table relationship 1 to many(the multivalued data stored in table format).
Think this would be the best.
Matt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply