Sergiy (5/8/2013)
First of all you need to make sure the Sourcetable (a1, a2,a3,a4,a5) have clustered index on (CompanyIdentifier,FieldIDNumber)
Many thanks for your suggestions Sergiy.
Yes there is a clustered index that includes both of those columns, however it also includes a few additional columns that are not used in this particular query but which are used in other queries. Would it speed things up if I added a non-clustered index on just the columns that are used in join criteria and filters of this query, despite the fact that they are all contained in the clustered index/primary key?
Second, if I understand correctly those joined tables are actually the same one joined multiple times to itself. Then you better join it once and put all FieldID's into a single IN condition. Then you may put the returned values into different columns in SELECT part using cross-tab (pivot) kind of query.
And the last.
It's just my speculation because I did not the the actual query, but I recon you do not need those joins at all.
One simple cross-tab query (considering you've got that clustered index in place) will do the job easily and quickly.
You may find good examples for such queries in BOL or on this site.
Yes correct - it is the same table joined to itself multiple times. Interesting suggestion, I will look into this and test it.
Many thanks for your help, much appreciated.