62 reputation
6
bio website
location Peterborough, United Kingdom
age 45
visits member for 3 years, 7 months
seen 2 days ago

Jul
2
awarded  Curious
Jun
11
comment Constrain referenced rows to two different tables to belong to the same row in another table
Ah that was useful. Thanks.
Jun
10
comment Constrain referenced rows to two different tables to belong to the same row in another table
No. Samples can have many locations (indirectly via occurrences) but they should all be the same site as the sample. For example, say you are visiting a nature reserve with lots of ponds in it. The site is the nature reserve, the locations are each pond, and you can record occurrences of, say, frogs at any pond within the reserve as part of one sample.
Jun
10
comment Constrain referenced rows to two different tables to belong to the same row in another table
There can be more than one location used within a sample via the occurrences. But the locations for that sample all need to be in the same site.
Jun
10
revised Constrain referenced rows to two different tables to belong to the same row in another table
added 2 characters in body
Jun
10
asked Constrain referenced rows to two different tables to belong to the same row in another table
Sep
20
awarded  Commentator
Sep
20
comment SQL Server WHERE clause on CLR method (spatial) peformance
Unfortunately, the query was still very slow using TOP so I bit the bullet and re-factored. I selected all the data for the reportGUID into a table along with STGeometryType() stored as a string. Then I queried this table based on the string's value. I'm still interested to see if the performance can be improved directly in the query, though, so will keep the question open and investigate a bit further.
Sep
19
comment SQL Server WHERE clause on CLR method (spatial) peformance
Thanks for the advice. The query runs pretty quickly without that final part of the WHERE clause. Unfortunately, the value - geom.STGeometryType() - is a call into the .NET CLR, which I've assumed has some overhead and is causing the poor performance when it is called over the whole locations table unnecessarily. However, I wasn't aware that TOP had this effect and that may just solve the problem. Thank you.
Sep
19
comment SQL Server WHERE clause on CLR method (spatial) peformance
Yes. This table holds the output of reports generated by users of the application. The GUID identifies a single report (usually in the region of 5K - 25K records).The SQL above selects the records for the report and subsequent .NET code exports them as an ESRI shapefile. There are similar queries to export Lines and Polygons, since ESRI shapefiles only support single geometry types.
Sep
19
comment SQL Server WHERE clause on CLR method (spatial) peformance
Thanks. Wasn't aware of that article.
Sep
19
revised SQL Server WHERE clause on CLR method (spatial) peformance
added 106 characters in body
Sep
19
revised SQL Server WHERE clause on CLR method (spatial) peformance
added 121 characters in body
Sep
19
comment SQL Server WHERE clause on CLR method (spatial) peformance
Okay. How is it best to do that? As an image (it's quite large ...)?
Sep
19
asked SQL Server WHERE clause on CLR method (spatial) peformance
Nov
16
awarded  Editor
Nov
16
comment Selecting values that are not associated with one side of a many to many relationship
Thanks. Fixed it.
Nov
16
revised Selecting values that are not associated with one side of a many to many relationship
Fixed typo
Nov
16
accepted Selecting values that are not associated with one side of a many to many relationship
Nov
16
comment Selecting values that are not associated with one side of a many to many relationship
Fantastic. Thank you very much.