Subscribe to this thread
Home - General / All posts - SQL query autorunning on save
dale

535 post(s)
#02-May-14 05:08

G'day,

I have query that I have modified from Art's post over on gisadvising The intent of the query is to clip interesect two drawing components.

I've run into behaviour that I've not seen before, in that the query will run with no other user interaction in the following conditions:

I mouse over and single click on the query;

I mouse over and double click on the query;

I save the map, the query runs after I click save;

I cut and paste the query text into a new query.

Both the query and the Manifold instance have a * character appended to the map/query name in the window headers.

I cannot open the query component in the project pane by double clicking it, as the query runs.

I've tried importing the two drawing components (PV and EVD) into a new mfd instance, then copying the query text via notepad into a new query component. The behaviour is the same.

TRANSFORM SUM(Area(g))

SELECT NAME

FROM

(SELECT ClipIntersect([PV].[Geom (I)],[EVD].[Geom (I)]) AS g,

[EVD_DESC],[NAME]

FROM [PV],[EVD]

)

WHERE g IS NOT NULL

GROUP BY NAME

PIVOT EVD_DESC

tjhb

8,760 post(s)
online
#02-May-14 06:00

What else is in the project Dale?

Are there any drawings (or tables) linked from that pivot table?

Do you want to post a project exhibiting this behaviour for someone else to confirm?

dale

535 post(s)
#02-May-14 08:01

I'm in the process of anonmyzing the project for posting. One thing I've learned so far, I really ought to be using mfd 64bit on my laptop. I tested the same project file on mfd 64 on a different machine, got the same behaviour

adamw


8,579 post(s)
#02-May-14 09:49

That probably has to do with linked components, which is where Tim is going.

You can try turning off "Refresh linked components after opening file" under Tools - Options, Miscellaneous, then try saving the file again to check.

dale

535 post(s)
#03-May-14 00:56

Summary so far:

There are no linked components in the project. I have turned off the "Refresh linked components..."

I've built a subset of the orginal project to post by using a clipping object to reduce the number of records and objects in both drawing components. This subset does not display the same behaviour.

I've slimmed down the original project to three components, the query, EVD with one field EVD_DEC, and PV with one field NAME.

Single clicking the query component, or menu/file/save, or file/save as causes the query to run.

I'm not able to post the original project on the forum.

tjhb

8,760 post(s)
online
#03-May-14 04:22

Sounds like a bug in the query validator/compiler? I hope you can send some private data to Tech.

If you also need to make it work, there are things you could try.

  • Remove the WHERE condition (NULLs are ignored by SUM, so it's not necessary)
  • Fully qualify all column names (i.e. add table names or table aliases)
  • Make the subquery into a separate query

Failing which, if the number of values of [EVD].[EVD_DESC] is small, you could convert the TRANSFORM query into a manual pivot query using a CASE expression and NULLs.

dale

535 post(s)
#03-May-14 07:30

Thanks Tim.

I can send the data to tech. Can send a copy trans Tasman for independent validation if worthwhile.

Will try all three bullet points, and attempt the last, as there is only 15 or so [EVD].[EVD_DESC] values. All good SQL learning for me.

adamw


8,579 post(s)
#03-May-14 07:17

I think I know what's going on.

Selecting (single-clicking) a query in the Project pane tries to check whether it is valid and if so, determine its type, for the prompt at the bottom of the pane. This includes deducing fields returned by the query. Since the fields returned by a TRANSFORM query are synthesized by the PIVOT clause, this forces the PIVOT clause to look at the data, essentially partially executing the query. That's it.

First, this is expected behavior. Second, this (partial execution of a query when selecting it in the Project pane) only happens with TRANSFORM. Third, we realize this is unexpected and unpleasant, and will fix it.

dale

535 post(s)
#03-May-14 08:04

Thanks Adam.

The EVD drawing has 35 000 records, so when I cut it down to 200 records for my post-able example, that validation check would have been near instantaneous.

I'll need to optimise my query, as some of my drawing components have 3 000 000 records.

tjhb

8,760 post(s)
online
#03-May-14 09:51

Adam (putting aside my earlier stabs at reducing the load on the validation code),

In the meantime, would it help to add CAST operators--especially I suppose to the PIVOT and possibly the TRANSFORM expressions? Or would that perversely increase the validation time required?

Dale, if you do shoot a data set across the ditch I'll happily rewrite the query into a manual pivot. 15 values or so sounds very manageable.

adamw


8,579 post(s)
#03-May-14 11:18

Adding CAST operators will have little effect.

Rewriting TRANSFORM ... PIVOT ... into a SELECT with a number of similar fields doing their own grouping will fix the issue.

Added:

Adding IN to PIVOT will fix the issue as well.

tjhb

8,760 post(s)
online
#03-May-14 11:41

Thanks Adam. Good.

Added:

Adding IN to PIVOT--how? (Intriguing.)

adamw


8,579 post(s)
#03-May-14 12:26

Like this:

--SQL

TRANSFORM SUM(Area(g))

SELECT NAME

FROM

 ...

GROUP BY NAME

PIVOT EVD_DESC IN ("<value1>""<value2>", ..., "<valueN>")

This limits the number of fields returned by TRANSFORM to those for values in the IN list. Consequently, the query engine does not have to look at the actual data values to determine how many fields TRANSFORM is going to return and what their names are going to be - it can simply use the values in the IN list.

The drawback is that you have to know these values, and update them whenever the underlying data changes.

tjhb

8,760 post(s)
online
#03-May-14 22:53

Thanks Adam--and thanks for not just pointing to the manual, which does covers the syntax, though not as well as you have here. I had completely missed it.

The drawback is that you have to know these values, and update them whenever the underlying data changes.

This would be made somewhat easier if the <list> of values could also be an external query, an external table, or (mainly for completeness) a subquery.

-- Query 1

SELECT DISTINCT [EVD_DESC]

INTO [EVD Values]

FROM [EVD];

.

--Query 2

TRANSFORM ...

...

PIVOT [EVD_DESC] IN (SELECT * FROM [EVD Values])

But as it is the IN (<list>) syntax gives a neater way to do an explicit/manual pivot--avoiding a CROSS JOIN, CASE expression and lots of NULLs.

dale

535 post(s)
#03-May-14 11:44

Tim, email on way.

I'll post a link back from Arts blog post on Tabulate Areas, as he started me out exploring options.

I've forgotten to post the original task that triggered my enquiry in the first place. I often have to intersect a number of land parcels with a variety of areas, then produce tabulated data of area and percentages. This could be multiple intersections of public land by tenure, vegetation classes and wildfire boundary.

I may wish to determine percentage of a vegetation class affected by wildfire in a National Park, or area of threatened ecotype expressed as percentage in and out of a Forest Park, National Park and private land.

Obviously SQL can reduce the amount of time taken to run such analysis, and limit the number of interactions in the GUI. As data is starting to increase in size and complexity, Manifold 8 remains our heavy hauler of choice.

Mike Pelletier


1,602 post(s)
#02-May-14 15:40

Until a more elegant fix comes, just put a single quote in front of Transform to make the query invalid and then remove it when you want to run the query. This problem has been around for awhile and pretty sure I reported it back in 2011.

adamw


8,579 post(s)
#02-May-14 16:48

It might be expected behavior.

Mike Pelletier


1,602 post(s)
#02-May-14 20:08

"It might be expected behavior."

... meaning at times the behavior is beneficial? I suppose so, but it sure can come as a surprise and be baffling.

Mike Fisher

899 post(s)
#08-May-14 10:00

meaning at times the behavior is beneficial?

Yes. It can be helpful for the project pane to determine if a query is valid and if so, the type - deducing fields if need be. Deep implementation such as the PIVOT clause examining data in a TRANSFORM situation is part of that. Depending on circumstances that can be elegant - or not.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.