1

Topic: SQL server 2012 and sort order problem

I have just noticed some odd behaviour with our new installation on SQL Server 2012 when running xml reports (attached). This is a report I have used many many times in the past without problems.

The report simply creates a list of unique species recorded at a site, or its subsites (via a right click on the site in the locations hierarchy, but I also have a run-menu version of it). I don't think there is anything in it which is non-standard for Recorder, so it should run on anyone's system, if you are interested, feel free. I should warn however that I wrote it several years ago and there have been a variety of changes in the way species names can be reported since then, so it may not display the most up to date version of the names (it might but I don't have time to check now).

However, the report is intended to be ordered, first by TAXON_GROUP.SORT_ORDER and then by ACTUAL_NAME taken from INDEX_TAXON_NAME, where TAXON_LIST_ITEM_KEY is the RECOMMENDED_TAXON_LIST_ITEM_KEY of the taxon actually recorded. Consequently it has always returned a species list with e.g. all the Flowering plants together arranged alphabetically by scientific name. It still does this on our old installation (under SQL server 2000).

On our new server though, the report returns a list which has no discernible sort order.

It looks as if SQL 2012 does not interpret an ORDER BY clause in the same way as 2000.

This has me really worried since I am currently spending a lot of time trying to get the 2012 version working properly, with a view to rolling it out to all staff as soon as possible.

Any thoughts?

Post's attachments

WSBRC Quick species list (location only) v1-2.xml 3.88 kb, 6 downloads since 2013-10-17 

You don't have the permssions to download the attachments of this post.
Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

2

Re: SQL server 2012 and sort order problem

I just modified the report to sort on INDEX_TAXON_NAME.SORT_ORDER only (full taxonomic sort order) and it doesn't sort on that either. So it is clearly ignoring the ORDER BY clause altogether, or failing to sort properly anyway.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

3

Re: SQL server 2012 and sort order problem

This is a known issue on all versions of SQL Server, but may be worse in SQL Server 2012. I am not sure what will be involved in fixing this in Recorder 6. The WHERE clause needs to be added to the SQL used in the query on the temp table #Report.


The issues (No 99 in Mantis - our error reporting system is as follows).   
When producing very large reports they do not necessarily come out in the specified sort order. The number of records where this occurs depends on the system in use and may vary from one run of a report to another on the same system. This seems to be because recorder sorts the records when generating the temporary report table on the assumption the when this table is used in the final report the records will come out in this order. Microsoft do not guarantee this. To fix the sort order needs to be applied in the final query which generates the report and not at the stage when the temporary table.

It is also mentioned in the XML Report Guide. 

Large datasets. Records are not sorted by the order by clause when large datasets are returned. I suspect the problem is one of timing and that the point at which it occurs is dependent of machine configuration, memory etc. I find that it occurs at about 60,000 records. Nothing seesm to fix this with any certainty, but I have found that adding a WAITFOR statement at the end of the report sometimes helps. I suspect that this allows some internal process to complete.

WATFOR DELAY '00:01'&#59;  (this is equivalent to WAITFOR DELAY '00:01'; a one minute delay. )

Mike Weideli

4

Re: SQL server 2012 and sort order problem

We have now given a high priority to  finding a solution to this as it is clearly a much bigger problem with later version of SQL Server. I recently noticed it on modest size report run under SQL server 2008 .

Mike Weideli

5

Re: SQL server 2012 and sort order problem

Thanks for the reply Mike. Not sure I can make sense of it though. You say "The WHERE clause needs to be added to the SQL used in the query on the temp table #Report." My xml has no such table and I can't work out to what where clause you refer.

Guessing that you meant the final query which selects all the output records (from #MAIN_RESULTS), I have added an ORDER BY SORT_ORDER clause, but this has no effect on the output.

As for the WAITFOR (and I am not very clear whether you mean me to use WAITFOR DELAY '00&#58;01'&#59; or WAITFOR DELAY '00:01';, but the result is the same in either case), you say 'at the end of the report', but where precisely? If placed after the last select query there is a delay, but the results are still unsorted. If I put it after the final </Where> it causes an error when the report is run.

It is worth drawing attention to the fact that we are not talking a huge dataset here, although I have only noticed the problem in connection with our reserves, which might be expected to have more records than most sites. Still the initial query probably only returns one or two thousand records at most and this number is reduced greatly by the DISTINCT grouping of the query to no more than a few hundred results in the output.

I have tried removing the DISTINCT keyword from the main query & adding it to the final select query, this produces a different output, but still not well sorted (with or without the delay).

I can confirm though that the same query executed on our old SQL 2000 system sorts correctly, so whatever the problem is, it is exacerbated by SQL 2012.

I don't dispute however that we may yet have a problem of poor network timing or server operation with regard to our 2012 installation, as mentioned elsewhere.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

6 (edited by RobLarge 14-11-2014 15:24:21)

Re: SQL server 2012 and sort order problem

OK now we are fully migrated to SQL2012, this one has come back to bite me as well.

Does anyone have a solution to this one which doesn't involve adding the SORT_ORDER field to the report & exporting to sort in Excel or similar?

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

7

Re: SQL server 2012 and sort order problem

There is a solution for this as far as XML reports are concerned, implemented in version 6.22 of Recorder.  See CCN 99 in the release notes.

For details see http://www.recorder6.info/WebHelpR6XML/

The new tag  OutputTableOrderby  replaces the Orderby tag , however the Orderby tag is retained for backwards compatibility so it is necessary to change  the report. If you were not using  Orderby, but relying on Order By in the your SQL then you will need to add an OutputTableOrderby tag (removing the sort in your SQL might speed things up a bit). 
.  The field you want to sort on will need to be in the report, but you can stop it showing using the Column tag (see example) . Note that  the sort SQL required the column name as used in the report with no table or table alias. 

<OutputTableOrderby name="by Taxonomic Sort Order" SQL="Order By sort_order"/>
<OutputTableOrderby name="by Scientific Name" SQL="Order By Actual_name"/>

If there is just one entry for OutputTableOrderby the sort will default to this without offering any option.

I attach a report which implements the new method.

Post's attachments

CS_P1_Polygon.xml 7.37 kb, 2 downloads since 2014-11-14 

You don't have the permssions to download the attachments of this post.
Mike Weideli

8

Re: SQL server 2012 and sort order problem

Thanks Mike

I will look into that when I have time

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

9

Re: SQL server 2012 and sort order problem

That worked a treat thanks, although there is a small error in the web help file. On the sort order page the example code includes the line:

< OutputTableOrderby ="by Surname " SQL="Order By Surname" />

Which should I think read

< OutputTableOrderby name="by Surname " SQL="Order By Surname" />

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

10

Re: SQL server 2012 and sort order problem

Thanks. I will correct the help.

Mike Weideli

11

Re: SQL server 2012 and sort order problem

Mike, not sure is this is one for you or for Chris. The sort order works fine now, for the most part, however we have noticed a few anomalies.

I have just been passed 3 separate outputs from an xml report which were sorted taxonomically using ITN.SORT_ORDER

In each of them there is a single non-flowering plant species which has ended up in amongst the flowering plants.

The examples I have are
Cystopteris fragilis (Fern)
Juniperus communis (Conifer)
Syntrichia virescens (Moss)

The sort is correct, according to the SORT_ORDER, so there must be a problem with the way the sort order field is populated.

I have no idea whether there is a wider problem, but I suspect there may be. A quick inspection seems to show a significant overlap between the ranges of the various taxon groups' sort orders.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

12

Re: SQL server 2012 and sort order problem

This is  a bit concerning. These problems can be in the NHM list or in some of the additional information held in R6. However in the cases you mention the taxa (at least the recommended ones) are in the correct place. The complete structure of the Organism table with  the sort is available as a download at     

http://www.recorder6.info/AA1_1_Hierarchy.zip

You can see from this,  which is generated from the R6 data,  that Taxon Groups and sort orders are very much in line, with a few exceptions, probably where the parent Organism is not set up correctly. 

If your sort order is different  then we need to try and find out why.

Mike Weideli

13

Re: SQL server 2012 and sort order problem

Can you clarify for me Mike,

I was assuming that the SORT_ORDER field provided complete taxonomic ordering on its own, without additional reference to the taxon group order field, from taxon group.

If that is the case, why this (from your spreadsheet)?
Mentha pulegium (Flowering plant) 0001071C09090P0A00000000000000
Juniperus communis (Conifer) 0001071F0301060D03000000000000
Spiranthes spiralis (Flowering plant) 0001071F0408010G03000000000000

I feel like I am missing something fundamental here.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

14 (edited by RobLarge 28-11-2014 10:15:56)

Re: SQL server 2012 and sort order problem

Running through your spreadsheet, sorted by sort order, after the algae, there is a block of flowering plants starting with Tracheophyta (0001071C0000000000000000000000), there are 5021 flowering plant taxa ending with Peltaria alliacea (0001071C0E05270000000000000000).
Then the mosses start & everything looks fine through ferns to the conifers until after the conifers we have
Magnoliopsida (0001071F0400000000000000000000) followed by another 5207 flowering plant taxa ending with Houttuynia cordata (000107750100000000000000000000) and then on to the protozoa.

Really what have I missed?

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

15

Re: SQL server 2012 and sort order problem

They shouldn't be out of order. I will take a more detailed look. Sort order shouldn't need the Taxon Group.

Mike Weideli

16 (edited by RobLarge 28-11-2014 10:03:27)

Re: SQL server 2012 and sort order problem

I have just modified the xml report to incorporate taxon group order, which has fixed the gross problem, but there is still a problem within the flowering plants. A test run produced the following:

conifer    Juniperus communis
flowering plant    Mentha pulegium (Magnoliopsida)
flowering plant    Eriophorum angustifolium (Liliopsida)
flowering plant    Spiranthes spiralis (Liliopsida)
flowering plant    Onobrychis viciifolia (Magnoliopsida)
flowering plant    Menyanthes trifoliata (Magnoliopsida)
insect - butterfly    Hesperia comma

That's not taxonomic is it.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

17

Re: SQL server 2012 and sort order problem

The flowering plant problem is being caused by the NHM having two different systems  for classifying plants.  I don't think I can fix in Recorder, because I can only tweak the order within that defined by the hierarchy. I will raise the issue with Chris.

I think perhaps there is something else going on with your dictionary as it doesn't look like Juniperus communis should be that far out of order.

Mike Weideli

18

Re: SQL server 2012 and sort order problem

It isn't. Juniperus is in the right place, so are the moss and the fern. It's the vascular plants that are wrong. Our sort codes seem to match yours so I doubt there is a problem with our dictionary.

I am sure then that this is all connected with the fact that the BSBI plant list has not been incorporated yet (or even finalised I expect). Chris can't fix it properly til they supply the correct details, even if he didn't have all those other groups to deal with.

As a botanist though, I find it highly frustrating.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

19

Re: SQL server 2012 and sort order problem

Hi Rob

Run this query then rebuild Index_Taxon_Name (not necessary to do the other indexes). You will find that this has a major effect on the sort order of the flowering plants. If you think it is helpful I will include it in the next dictionary upgrade.

Update Organism set Weight = 8 where Organism_Key =  'NBNORG0000113032'

Mike Weideli

20

Re: SQL server 2012 and sort order problem

Thanks Mike

I have done that and it looks like a massive improvement, although probably still not perfect.

I ran (before and after) a query which selected RTLIK, Preferred name, sort order and (via TLI & TV to TG) Taxon group name, sorted on the sort order.
Before the update I was getting a huge block of vascular plants after the algae but before the mosses (clearly wrong) and then another huge block in the right place (between the conifers and the protozoa).
After the update the first block has disappeared, presumably into the second block.

There remain a scattering of taxa which my query calls Vascular plants, which are dotted about in the wrong places, but most of these are higher taxa or obscure things I have never heard of.

Having done some further testing, I am now confident that the vascular plants are still not in taxonomic order, since there are still two large blocks of monoctyledons at the beginning (mostly lilies and orchids) before the dicotyledons and another group of monocots (mostly grasses, sedges as rushes) at the end where they should be, I think?

Whatever, this looks like a positive step to me. Thanks for your help.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre