Max Enterprise SQL poor performance

jalbeNew Member

I have been supporting a rather small Maximizer group for the past few months and I am definitely not having a positive experience with it. The performance of Maximizer is very bad. I have a contact database of 11,573 and it takes about 60 to 80 seconds for the database to load and then the same after I do a search and want to go back to the complete list. Maybe I have something wrong here, but I spoke to our old IT Manager and he said he experienced the same thing.

It looks like Maximizer is loading about three or four hundred records and
then pausing before the next three or four hundred.

I tried installing SP2 on my SQL 7 Server and Maximizer Enterprise SQL SR3
and that did not help performance noticeably. I tried reindexing with the scripts provided on the CD and that helped minimally. I did get an error on the recreate portion, something about 'index on xxxx table already exists'.

I did what it said when I set up the database... create database, roles, users, create DSN,create address book folder in SQL, then backup native Maximizer database to file, and then restore into SQL address folder.

Here are some details about the system (and specs on my workstation, which is faster than the machines the sales people are using, I really feel the pain for them):

Server:
Windows NT 4.0, SP 6a, 128 bit security
MS SQL Server 7.0, SP2
Compaq ProLiant 800 - PIII 500, 3x9GB ultra SCSI drives, RAID 5, 512MB
RAM,
10/100 NIC

Database:
326312 total files
Accounts - 0
Address Book Entries - 40065
User-defined Fields - 207996
Expenses - 0
Hotlist - 1
Letters - 4
Column - 12
Notes - 77417
Appointments - 1
Selected Name - 16
User - 12
Cross Reference - 750
Database Control File - 15
Link File - 1
Opportunities - 1
Strategy - 1
Detail File - 9
Link File - 6
Distribution List - 6

My Workstation:
Windows98 SE
Maximizer Enterprise 5, SR3
Sony Vaio notebook - PIII 500, 9GB HD, 128MB RAM, 10/100 NIC

In Between:
Cisco 2900 XL series 10/100 switch
All CAT 5 cables

I have been pulling my hair out for months now and can't seem to come up with any good ideas that have worked... Any advice would be most appreciated.

P.S. Next, I get to install and configure Max Exchange SQL!!

10/26/0011:14 AM

❤️ 0

Original Post

Replies sorted oldest to newest

ianwallaceNew Member

Hi,

The performance can be affected by to things that are worth experimenting with.

1) The client communication method selected.
2) Ultimatly its down to SQL server system tuning.

I have seen Maximzier SQL run very well on SQL server 6 with adequate memory configured. SQL 7 seems more of a challenge to get teh same performance on.

MaximizerSQL does not really make best use of the SQL capabilities of the server and therfore gets it to do rather a lot of work. The Pervasive engine is usually quicker.

If you really want to work with MS SQL in longer term you should consider Entice! which is very similar to Maximzier, it is SQL based but I believe is completly re-written at the SQL level.

Ian Wallace
CABC

10/26/005:51 PM

❤️ 0

jalbeNew Member

Ian, Thank you for the speedy reply!

I have the client connections using TCP/IP, which according to MS, is the fastest to use. From TechNet: "Testing at Microsoft has found that the TCP/IP Net-Libraries are somewhat faster than the other Net-Libraries. Other considerations, however, such as database design, indexing, and the design of queries and applications, usually have a greater impact on performance than the choice of a Net-Library." I only run TCP/IP on my clients and on the SQL server, so it doesn't leave me many options there.

I have not been able to come up with any other performance tuning ideas other than the reindexing and making sure everyone is connecting through 100BT.

Switching to btrieve would seem like a better option to me, considering the investment we have made in Maximizer. I do not see it being financially feasable to switch to Entice! and we are not locked into SQL server per say.

This has been a whole very dissappointing situation. The local Multiactive Partner apparently did not come close to producing the results we were looking for and ate up quite a chunk of cash in the process. This was previous to me working here, so I do not have many details other than we can not have them help us on this project.

Time to kick the computers and pull out some more hair...

Thanks,

Joe

10/27/005:25 AM

❤️ 0

gordNew Member

quote:
I have a contact database of 11,573 and it takes about 60 to 80 seconds for the database to load and then the same after I do a search and want to go back to the complete list. Maybe I have something wrong here, but I spoke to our old IT Manager and he said he experienced the same thing.

During the Maximizer SQL C/S Technician course the instructor hinted that a few Maximizer functions were particularly inefficient under MS_SQL. IIRC, "View all..." was cited as worst-case and should be avoided. If you have over 11,000 Address Book entries, do you really need to routinely view them all at once?

quote:
I have the client connections using TCP/IP, which according to MS, is the fastest to use.

According to my notes, that sounds like the right approach. Win9x clients default to named-pipe connections, which are reported to be slow. We were told to install the ODBC Client Tools on each machine then choose TCP/IP or SPX. Also according to my scribblings, the version of the ODBC driver is significant, and v3.7 was "highly recommended" (at least when I took the course, over a year ago).

10/27/0010:37 PM

❤️ 0

ianwallaceNew Member

Hi,

I would generally agree with both of Gord's points above.

Particularly that there's more than one way to use Maximizer and the more experienced users will rarely list all Addressbook entries, prefering to merely call up the records they need, as and when they need them with a search. Working from a full list is quite good and many people like to work this way, but it gets less efficient as the list gets longer! Also get users to change the list retrieval default at startup to 'Last List' when they start Maximizer as this will also speed up start times (assuming they change to only calling up records when needed). To do this untick file/preferences /system options - "Ask at program startup which addressbook list to view".

You should also look at the column setups in use this can dramatically affect list building times. Try a column layout with just Name and Identification. For comparison, this is about the fastest you'll get. Adding address fields has little impact but adding UDFs will increasingly slow the display time for each one added to the view.

Consider some user re-training on these points.

A couple of other thoughts.

1) You will often find that named pipes works well if your client is windows NT.

2) Multactive have also released Maximizer.SQL 5.1, As far as I can tell this is not very different from the SP release you'r already running but it might be worth obtaining. I would not expect a miricle transformation. However, its almost always a good idea to run the latest version - since you don't know what else may have been fixed!.

If you would like us to help you obtain Maximizer.SQL 5.1 or would like more detail on converting to SQL you can email me at ian.wallace@cabc.co.uk

Ian Wallace
CABC

10/28/004:22 PM

❤️ 0

gordNew Member

quote:
adding UDFs will increasingly slow the display time for each one added to the view.

Good point. It reminds me of another "hint" from the course: Never put a UDF in the first column of a view because that will profoundly degrade performance. (I believe this is true for Maximizer in general, not just the SQL version.)

10/28/009:11 PM

❤️ 0

jalbeNew Member

Thank you for all the advice, The "show last view" rather than the whole address book has made the most "improvement".

Pardon me if I'm being obtuse, but it seems all of these "performance improvements" are limiting the flexiblity of Maximizer. My users want (need) to have UDFs in the addressbook view.

I may contact Multiactive regarding the 5.1 release to see if it will help.

I still have to attempt getting MaxExchange SQL up and running, I hear that is kind of a bitch to do.

10/29/006:33 AM

❤️ 0

gordNew Member

quote:
it seems all of these "performance improvements" are limiting the flexiblity of Maximizer.

Not really. Maximizer is still as flexible as it was before, but now you have a better idea of what some of those features cost in terms of performance. If a particular feature is "worth the wait", then use it.

quote:
My users want (need) to have UDFs in the addressbook view.

Perfect example. If users need UDFs in their Address Book column setup, then so be it. Just be aware of the performance cost of each additional UDF (so they don't load up on them unnecessarily), and don't put a UDF in Column 1 of the Address Book view.

10/29/007:59 PM

❤️ 0

johnNew Member

I have recently come accross performance issues in Maximizer 5.0 ent running on a SQL server 7 backend. Our situation sounds very similar to the one above but our problem is due to a high number of notes in a database. The performance issue lies in the way that Maximizer searches for Notes. The way in which Maximizer user SQL to do it's work is a complete mess and this makes for slow performance. Smaller Databases work fine but as a database has an increased number of notes, the performance really drops. I have found that if you have have over 50000 notes then performance will drop.

If anyone know of any solutions other than to upgrade to Maximizer 6 and Perv 2000 please let me know. Cheers

10/4/011:21 AM

❤️ 0

johnNew Member

MORE INFO RE NOTES PROBLEM ABOVE

We recently upgraded one of our Btrieve 6.15 Max Ent 5.0 databases to Max Ent 5.0 on SQL Server 7.0. All seems to run OK.
However, there appears to be a marked performance impact when searching for notes text, especially against specific date ranges. 25 minutes to return 300 client records from a database of 14,000 clients! Number of rows in notes table is 31312 (nothing excessive).

I have queried the database notes table through Access and it runs almost instantaneously.

Any ideas?

10/4/011:26 AM

❤️ 0

Skip to main content

Add Reply

Membership Required

Remove From Your Block List

Manage Follow Preferences

Block

Please wait...