The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Creating a new 1-to-1 table off the Account table
Posted: 26 Oct 06 12:55 PM
|
fiogf49gjkf0d I have a project where I was going to hang a new table off the account table (1-to-1 relationship). I know this can be a performance issue sometimes but my BP suggested that in this case it may be really bad.
The table I'm wanting to add will have about 100 or so columns.
The data is directly related to particular accounts. Basically its industry specific data about the account. The users will not be able to edit this information.
My BP suggested making it a stand alone table and making the join on the fly when the account is pulled up. I'm not sure how I would do this. The table would still need the AccountID to link up to. Additionally this table is populated quartly with updated and new data. Without the 1-to-1 relationship with the account table how would the import know to add a new account record as well as creating my new table record?
I'm not worried about the import functions. That's all pretty much written but it was written to work with a table off the account table. So now I'm stumped  and a bit spooked. Our performance sucks enough as it is. I don't want to make it worse.
Also I'm kinda dead in the water since I don't want to have to add this table multiple times (for testing or production) since it's so many data columns, each with specific data type.
Thoughts?
Marc |
|
|
| |
| |
| |
|
Re: Creating a new 1-to-1 table off the Account table
Posted: 27 Oct 06 12:33 PM
|
fiogf49gjkf0d I'd be more concerned with byte size than with the number of fields. 100 long strings is a bigger impact than 100 booleans.
In the past I've added a 1-1 table to Account with 66 fields and 758 bytes. I didn't notice any particular performance hit, although we've only got around 5,500 accounts in the database.
Maybe I don't understand your point, but I don't see what the problem is with test and production versions. Create it once in test. When you're satisfied with it, create a bundle and add the bundle to production.
You've probably already addressed this, but just in case.... If performance is such a killer for you, check your hardware, both client and server. Anything over two years old could stand to be replaced. SalesLogix on the client is very piggy, especially for remote users who have SQL Server services as well. I got a huge boost in performance when I increased my client memory to 2 GB. If you have the option, SCSI drives with high RPM can make a big difference as well. Of course, your company's hardware upgrade policies may hamstring you on this.
|
|
|
|
Re: Creating a new 1-to-1 table off the Account table
Posted: 27 Oct 06 12:51 PM
|
fiogf49gjkf0d The table I'm wanting to add will have about 100 or so columns.
The number of columns seems to be rather large. Your saying the data would only be updated quarterly. So this makes me think its just static. What is the source of data? Is there another way you could pull from the source instead of duplicating it into a really large table. Maybe you could this like attachments are done. Create a table with the accountid and a field with a link to the data source? This would save alot of duplication and make it easier to maintain IMHO.
John G. |
|
|
|
Re: Creating a new 1-to-1 table off the Account table
Posted: 27 Oct 06 12:54 PM
|
fiogf49gjkf0d You mentioned that you will have a 100 or so fields.
-Are these mostly numeric, date, and small text fields?
-How many of the account records will have some of these fields populated?
I have one client that tracks a lot of data in 3 different categories. Since most of the accounts only have data in one of the categories I broke it up into 3 tables so I did not have to drag all the fields along everytime. This made it easier for my client to find the fields for groups and reports. It also kept things more organized when we needed to add addition fields in one of the categories.
|
|
|
|
Re: Creating a new 1-to-1 table off the Account table
Posted: 27 Oct 06 1:23 PM
|
fiogf49gjkf0d I'll give you all some more background and detail. The data comes on CD quarterly from a data store. There are two separate files I have to import (in .DBF format). We use a scripted function inside SLX so the data will sync out.
Unfortunately the DB Manager doesn't tell me how many fields I created but the table size is 1792 bytes. If I were to guesstimate on the data types I'd say the break down is like so:
40% - Strings (length 20-30 average)
25% - Currency
20% - Float
10% - Long Int
5% - Other
There are about 120,000 Account records total. Of those only about 9000 will have this data associated with them.
The scripted import does more than just put data into this one table though. It'll create a new account if it cannot find one (based on a predefined key field). It'll also merge/create contact records for the account. Addresses are created from this data for the account and contacts as well.
The Account Detail screen will not (no plans anyway) display or work with any of this data. It'll all be in tab views.
I hope this helps clarify some questions.  |
|
|
|
Re: Creating a new 1-to-1 table off the Account table
Posted: 27 Oct 06 1:44 PM
|
fiogf49gjkf0d Don't quote these numbers because I'm going from memory.
Maximum number of columns in a table (SQL defined, not SLX): 1024
Maximum bytes per table: 8000
I'm relatively positive about the number of columns but the number of bytes may be off. I got the values from Mike Spragg on 2 separate ocassions, so maybe he'll touch the post with more accurate numbers.
We have Account, Contact, Opportunity, and Ticket "extended" 1-1 tables with custom fields. Before v7 let you customize the main tables themselves, this was how it was done aside from a relatively painless hack. It can be a challenge to remember the tables all the time but they are in no way some major performance drain, though I suppose they could become one easily. |
|
|
|
Re: Creating a new 1-to-1 table off the Account table
Posted: 30 Oct 06 10:38 AM
|
fiogf49gjkf0d Marc,
There are a couple of other solutions, though they are pretty environment specific. These are not good options for every implementation, but large quarterly imports often fall into this narrow area.
Since this data itself is static and the users are not manipulating it; there is the possibility of summarizing your import data into a third, smaller table. Pull your tab views from that rather than the import table. You can also look at using views, which is nice for the dynamic attribute, but not as efficient as a summary table.
Another thing to consider; while this data is obviously important, how valuable is it compared to the overhead of a constant view? Is it an anaysis tool or absolutely integral to the account? If it be accessed infrequently or is being used for analysis - can consider using reports instead?
Carla
|
|
|
|
Re: Creating a new 1-to-1 table off the Account table
Posted: 30 Oct 06 10:48 AM
|
fiogf49gjkf0d Carla,
Thanks for the reply. The information cannot be easily summarized. I wish it could be. Additionally the information is used very heavily by the sales teams. They use it to determine a sales fit. Many companies use industry and company size to determine this. Our sales team uses this data to do the same. Industry and company size are irrelevant due to the fact that we sell to a specific industry (Banks). The number of employees is less important than the financial data imported.
Marc |
|
|
|
Re: Creating a new 1-to-1 table off the Account table
Posted: 30 Oct 06 10:56 AM
|
fiogf49gjkf0d In that case, you still may want to consider using views (indexed as needed) to limit the volume (and improve the quality) of data accessed by your users. This can help improve performance issues if implemented conscientiously.
Carla
|
|
|
|