Common indexes for ATTRack

http://www.galkam.com.au/images/attrack_common_indexes.png

These indexes are not required and some may not be useful depending on your site.  Below is a discussion about the ATTRack indexes from some time ago (REPOSTED)

From: Mark Rose, St Vincents Pathology (Melbourne)

Hi Glen.

I have a couple of queries I was wondering you could help me with.

1. Size of the Tracking file

 

We would like to archive the Tracking file in the EQTrak database, as it is getting large, and can take some time to access the data through the application. It is straightforward to create an second copy of the file and to move the archived data in, however, I was wondering whether you would have a tool or preferred method to do this. Also is there a way of accessing this second file through the application / setting up the app to point to another file.

 

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

<<< Mark, I think you might need to schedule some time with your SQL Experts.

a) The default install does not create any indexes. With properly defined indexes, then there should not be a performance issue regardless of your database size. Making second copies of a database on SQL server should not be necessary and is strongly discouraged. I remember discussing with John very early on in the piece about creating indexes so I wonder which ones have been defined? Also SQL Indexes need maintenance and should probably be rebuilt once every couple of months. Attached is a document containing a full set of indexes that are likely to give you better performance. Don't apply them all at once! I am assuming that space is not actually the issue here rather performance? Adding the index will probably make the file size half as big again but a 1000 times faster than with no indexes. All of these indexes are going to create a performance hit on Insertion so you might want to try the 4 most important which are MsgID, EQDeliveryTime, EQReceiveTime and PracID and see how you go. The correct indexes to create for your query side will depend on what you commonly search by eg Do you search most commonly by lab number, Patient or Doctor name? The key will be to add indexes slowly until you get the performance you need. But here's what I recommend straight up. Most Important indexes for the Tracking Table http://www.galkam.com.au/images/attrack_common_indexes.png

EQDeliveryTime Create this now!

MsgID Create this Now!

In the index options create non-clustered indexes using the Pad Index option with a fill factor of 65 for all indexes.

Try those 2 first- these not quite the primary key of the table (being so flat as it is) but if you don't already have them, then this should solve your problem immediately. If you already have these indexes, they probably need rebuilding. Next I Recommend adding:

EQReceiveTime

PracID

As for Lookups as mentioned before: index those fields you common search by. Eg Patient and Lab Number, You can also Create a composite index if your most common query involves more than 1 column

Eg if you commonly search for Patient (surname) & date, then a composite index of PATIENT_EQReceiveTime might be suitable (but is sort of inefficient since EQReceiveTime is already indexed).

Another common query is likely to be PATIENT_PracID, but just don't get carried away with creating indexes for everything.

The HM_CONTENT table does not need indexes as it is only ever referenced by MessageID from the result of a Tracking table query.

If none of these solve the problem, we probably need to run the SQL Profiler and Index tuning wizard to get to the bottom of the problem.

b) As for splitting up the datafile, the Preferred method is to not do it. But if you really must create a second copy of the database, then: 1. Create a new DATABASE called "ATTrackArchive" then use the transformation tool (or a SQL statement if your SQL is really good) to export the data older than say "6 months?" to the new database 2. Copy EQTrack and the UDL file (and a copy of the settings file) into a new folder. Double click on the UDL and point at the new "ATTrackArchive" database instead of the original and that should be it.