GM on MSSQL performance/optimization/suggestions

[weinerk]

GM on MSSQL performance/optimization/suggestions

I have about 100,000 records in GM on MSSQL

wanted to setup a filer where
KEY1=X
KEY2=Y
optimized by ZIP

I thought those where all indexed fields...
Takes forever sometimes looking at it skipping along through records looking for what I need.

Coparable SQL queries are a blast.

Need advice on optimization and record management.
Suggestions of what to read are also welcome.

 

[dougscrm]

I would setup a SQL query:

SELECT
company
,contact
,zip
,accountno
FROM
contact1
WHERE
key1 = 'X'
AND key2 = 'Y'
ORDER BY zip

You can then build a group on this sql query if you need to operate on it inside GoldMine.

SQL queries like this execute in under a second for 100k contacts. As long as accountno is included in the query the user can browse the results and it sync the contact window to the record highlighted in the SQL query result window.

Filters don't work well in MSSQL based GoldMine systems because they were designed for dBase where you have the entire file open.

Doug


Doug Steinschneider
DCS Group LLC
Fairfield, CT

http://www.dcsgroupllc.com

 

[goner]

It might help if you set your sort to one of the indexed fields as you skipped through the records. Right click on any of the three fields (zip is probably your best bet) and select to sort by it from a record that fits the filter criteria... Performance should be better..

Note, however, that this slowdown is entirely normal and that scrolling through records is not the primary purpose of GoldMine and therefore is not going to be particularly fast. Use the preview tab if you want to see the results of a filter and roll through them quickly, but otherwise, use your filter and then print a report or merge form, schedule a call, etc. and enjoy the performance of the real-world features as opposed to the benchmark-ish scrolling up and down which is actually much more database intensive that you might think as it has to evaluate every record in the database (in order) against the filter so it can return the next logical record for you (only to have you hit page-down again).


Doug Castell
Computer Control Corp
(310)396-6811

http://www.ccc24k.com