Slow Query on table with many memo records

[mattP]

Ok my database tracks escalations through my team, I have a main table that stores the unique ID's from the other tables I use in my Combo boxes, this part works really well, no problems.

My issue is with a tableI have claled "TBL_EscJournal", this table has the following fields:

JournalID (Unique Ref, generated by autonumber)
EscID (the escalation Id that this journal is relevant to)
Journalcreator (captures name of person who as entered the journal)
JournalNotes (memo field where you enter your update)
JournalDate (Date/Time the journal was entered)

So typically when viewing the main detailed form for a particular escalation I have a subform that shows all the journal entries relevant ot that escalation.

This table is huge, about 70% the total size of my database, partly because of the number of journal entries and partly because it is a memo field and a lot of data is required sometimes.

Up until now th edatabase has been located on a local server and has been fine for local users (2-3 of us) however there is a requirement for another office to use this database.

I am now experiencing massive performance issues, whereby the data is tkaig a long time to refresh on the other sites.

I have migrated the DB over to a SQL back end but still finding performance issues, which further testing has shown that the TBL_EscJournal is the cause.

So a coupel of things really, is there another way I can layout this table to improve performance or should I be uerying the data from this tabel in another way, would it be better for me to split TBL_EscJournal in two, the first part keeping the date/time and person who entered and the second part keeping the notes. Possibly increasing the query speed by carrying out the query on the first part of the table and not on the notes (memo) part ?

I hope this makes sense, if not feel free to PM me,

Appreciate any help or assitance you could offer

MattP

 

[Len Boorman]

First thing. You say table is huge. How many records ?.

Assume primary key is defined

You could also try adding some indexes which use the fields of whatever wuery you are using to select data

say EscID and Journal notes (Opps this is memo so think unable to cuse as index.

leave off say creator and date and have these within a popup modal form from a command button so that users can see the data if they want to.

How often is actually a memo field required. If it is only occassionally then think about having a 255 text field instead and a separate table for Additional Information with the memo field and the relevant ID. Then show this only when users hits a command button for additional info

Its all a bit suck and see. Also look at the Users pc's.

Check also when you bring back records what locking you are doing. Try opening the data on a read only form.

Try things basically

len

 

[FoFa]

Is your primary key to this table indexed? I assume the tie between this table and you main table is JournalID? Is the EscID indexed? Since uit is used in a subform, the child key that links the main/subforms are the ones that would be of the most interest for indexes. If those are indexed properly, than you may be just experiencing network lag. Do you have a Citrix server you can try running theapplication on instead to see if that helps any?

 

[mattP]

Len, Fofa,

Thank you both for your replies, just to answer some of the queries below:

The unique ID for the table, EscJornalId, is indexed, I will be testing this though with the indexes set on the other fields, to see fi that speeds up the search at all.

Also the 255 limit is too small, I had it set to this originally and it was causing problems.

The table has a total of 8815 records in it at present, these are against a total of approx 1000 records in the main table (escalations) so for each escalation I am opening there are a number of records to pull out from EscJournal.

The other users system is generally ok, when he was in my local office he had no problems with the DB, it's only when trying to run this remotely.

I'll have a play around with indexing and see how we go.

Thanks again for the advice.

MattP

 

[Len Boorman]

The number of records is not that large really. My suspicion would be firmly in the "remote" access area.

method and type of connection spring to mind

Len