Subscribe to this thread
Home - General / All posts - Edit SQLserver data
rhowitt51 post(s)
#04-Oct-18 13:20

I have a SQLserver database table that has 10,000s of land parcels ( polygons ) . In addition to the geom column in the table there is an owner column. There are 100s of owners. What I want to do is bring the parcels into Manifold, so that the parcels can be edited ( edit current parcels, insert new parcels, delete parcels ) . I need to edit the parcels on an owner by owner basis; so , I only want to see the parcels for owner A or owner B, etc. When editing the parcels for one owner I don't want to see the parcels for the other owners.

Is it possible to write a SQL query in Manifold 9 that shows only owner A's parcels, that will allow those records to be edited in Manifold and have those edits saved seamlessly back to SQLserver?

I know I can create views on SQLserver for each of the owners, but this is a labor intensive process, that requires requires 100s of views and new views need to be created every time a new owner is added to the database.

dchall8
513 post(s)
#04-Oct-18 14:58

Just to clarify, because it sounds like your situation is similar to mine. I work in a county appraisal office. We lease a database from a contractor for our 35,000 land and improvement accounts. The registered appraisers make changes to that database, not me. Separate from that database I have a Manifold map with parcels. I make maps so the appraisers can be confident they are looking at the right property. Each parcel on my map has a property ID number that matches the PID in the database. I export a table from the office database every week to update the maps. I use the Table>Relations... function in Manifold 8 to join our database table to my map files. The table exported from the database is created from about 50 other tables within the database. If I make a change to the fields I see, those do not necessarily change the "master" fields in the original database. Are you absolutely certain the changes you propose to make are going to make the database better and not worse?

Dimitri

5,106 post(s)
#04-Oct-18 15:38

Is it possible to write a SQL query in Manifold 9 that shows only owner A's parcels, that will allow those records to be edited in Manifold and have those edits saved seamlessly back to SQLserver?

Sure, if you have the permissions to do that. Results tables for queries are generally read/write.

Here's an example, using MDB so everybody can try it and see for themselves:

Download the books.mdb sample database for Chris Fehily's wonderful series of books on SQL.

Launch 9 in 32-bit mode (yes... this is the downside of .mdb...) and create a new data source called books that links in the books.mdb database. That has an [authors] table in it. Open the authors table and alt-click it to undock it, so you can look at the table while you tinker with queries. Write a query like

SELECT * FROM [books]::[authors] WHERE [state] = 'CA';

And you get a results table with three records. Double-click into the address cell for the second row in the results table and change the value from Waldo Ave to Doorknob St.

Click on the undocked authors table and choose View - Refresh and you'll see the record, which is still sitting inside the linked .mdb, will have been changed to that new name of the street that you edited in the results table for the query. You've just used an edit in a results table for a query within Manifold to edit a record that is stored in an external DBMS that's been linked into Manifold.

In your case, you'll link your SQL Server database into your project, and then do something like SELECT * FROM [the sql server table] WHERE [parcel_owner] = 'A'; and then you can edit the fields in that results table that only shows records for A.

All this assumes your SQL Server table has an index on it, etc, so it is editable, but that's no doubt in place.

rhowitt51 post(s)
#04-Oct-18 18:11

i understand about non-geometry data, but what about being able to edit the geom data columns?

Dimitri

5,106 post(s)
#04-Oct-18 19:39

I haven't tried that, but I don't see why not. The key question: when you create a drawing from a query, can you make edits to that drawing which change the geom in the underlying table from which the query pulls data?

That's easy to test by trying. Here is what I did:

1. Read the Example: Create a Drawing from a Query topic to remember how to create drawings from queries. (It's easy).

2. Import the sample drawing of Mexican provinces.

3. Create a query:

SELECT * FROM [Mexico Table] WHERE [Population] > 2000000;

4. Create a drawing from that query, and open the drawing. It shows only those provinces with a population greater than two million, as expected. Also, open the original Mexico drawing.

5. Select one of the provinces in the query drawing with a Ctrl-Click. Press delete. The province disappears both in the query drawing and in the original drawing.

6. Shift-Alt-Click one of the provinces in the query drawing to choose it for editing. Make some dramatically visible edits by dragging inflection points around. Click Update Record.

7. The province will be edited in the query drawing, and also in the original, source Mexico drawing.

Seems to work!

Manifold works the same regardless of the data source, whether the source drawing/table with geometry is in the local project or is in a data source in the database, or is in a nested project linked in, etc. So doing this with a query in the project is a reasonable proof of principle.

rhowitt51 post(s)
#04-Oct-18 22:15

As I start to work through how this is done here are some issues I am running into:

1) My data is in a SqlServer database. When I create a SQL query and run it against the server using sqlserver as the execute engine I am getting an error "Invalid Type". It has something to do with the geometry column. If I comment out that column the query executes correctly.

SELECT  OID

     ,County

     ,geometry 

 FROM [GISdata].[dbo].[Streams]

 where county = 'Monroe';

If I run the same query using the manifold engine it works

-- $manifold$

SELECT OID

, county

, geometry 

FROM [mSQL1]::[dbo.Streams]

where StringTrim( County,' ') = 'Monroe';

2) If a SQL query is written with a geometry column and that query is used to create a drawing, when I select an object with in that drawing how do I edit the tabular data that is associated with that object?

3) Similarly, if I create a new object in the drawing, how do I edit the tabular data that needs to be associated with that new object?

dchall8
513 post(s)
#04-Oct-18 22:53

I can't help you with number 1, but numbers 2 and 3 are both in the manual and in the YouTube vids posted under Manifold Sales. Basically you open the table, click in the cell you want to edit, and start typing or pasting data.

rhowitt51 post(s)
#05-Oct-18 04:09

I believe a query works differently than a table. From the manual:

Queriesare different. A query is one or more SQL statements that can be used to create aresults table. When a results table from a query contains a geometry field, we can create a drawing based on that query. A selection in a results table is unique to that results table. It is not automatically shared with any window that may be created from the results table. Likewise, a selection in a drawing or image layer created from a results table is unique to that particular window. It is not automatically shared with the results table window or with any other window created from that same results table.

If I understand what this says, than a selection in a drawing IS NOT shared with the results table from a query. So my question is still, when dealing with a very large dataset that requires a SQL query to return a reasonable number of records, how does one edit the tabular data associated with a geometry object selected in a drawing?

adamw


8,242 post(s)
#09-Oct-18 13:41

A selection in a drawing based on a query that fetches data from a table is unique and is not shared with the selection on that table or on components based on that table. But that's just the selection that is not shared. The actual records are shared. If you have a drawing based on a query that fetches data from a table, when you edit a record in that drawing, the changes go all the way into the table.

tjhb

8,399 post(s)
online
#05-Oct-18 00:33

When I create a SQL query and run it against the server using sqlserver as the execute engine

How are you telling it to use SQL Server syntax?

If your query is created inside the linked SQL Server database, then it should use SQL Server syntax by default. If it is in the root of the project, or in an ordinary folder, then it will use SQL9 syntax by default.

In a command window (but not in a query component), you can control this directly by executing one of

!native

!manifold

rhowitt51 post(s)
#05-Oct-18 04:23

I created the query under the DataSource that is used to connect to the database. And if I use the "trim()" function, which is a SQLserver function , without the geometry column, the query executes correctly. if I execute the query against the manifold engine the query fails because trim() is not a valid function.

tjhb

8,399 post(s)
online
#05-Oct-18 06:05

I can't see what is wrong.

Just out of interest, what columns and types do you get, using SQL Server syntax

SELECT *

FROM [GISdata].[dbo].[streams]

WHERE [county] = 'Monroe'

;

rhowitt51 post(s)
#05-Oct-18 13:55

The schema of for the table shows that the geometry column is of type geom and the table has rtree index on that column and btree index on the OID column.

rhowitt51 post(s)
#05-Oct-18 19:42

So, clearly there appears to be an issue with Manifold and executing a query natively against a SQLserver. I have tried this on two different SQL servers, both on the same version (10.50.xxxx) .

rhowitt51 post(s)
#05-Oct-18 19:45

What do I need to do to get my queries to run natively on a SQLserver? Is there a certain version of SQLserver or driver?

dchall8
513 post(s)
#05-Oct-18 21:22

I run a script directly in SQL Server. It starts out like this below.

USE [pacs_oltp]

GO

/****** Object:  StoredProcedure [dbo].[GIS_Get_Accounts]    Script Date: 2/2/2017 12:57:48 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[GIS_Get_Accounts] 

 

AS 

 

SET NOCOUNT ON 

 

 

-- grab empty data set in format of final results first for DTS package 

IF 1=2  -- this won't really execute but allows DTS/SSIS to see final format, 

        -- since temp tables confuse SSIS and DTS 

        -- if SSIS or DTS is no longer used, then this first step cold be removed 

  BEGIN 

  SELECT DISTINCT 

  psa.prop_id, 

  psa.owner_tax_yr, 

  a.file_as_name, 

  pv.legal_acreage, 

  pv.hood_cd, 

  pev.school, 

  pev.city, 

This script was written by our contractor who built the database and goes on for about 100 lines. When I need to have something changed, there is only one person there who is familiar with the code. They have a staff of query writers, but this script comes from the programming department.

It seems the first part of the scrips sets up the tables to be queried into a temporary table. Then it does some measures to prevent damaging the tables by inadvertently joining different years data. Then it assembles several tables to be queried and decides which table(s) to query for data. Then it indexes the tables. Then it runs the SELECT DISTINCT query over all the tables it just made to get the data in my request list. At least I believe that is what it is doing from the comments placed in the script.

rhowitt51 post(s)
#06-Oct-18 14:32

So you start this script from inside Manifold and it returns a result set that has a geometry or it creates a new table?

But it is not a SQL query that returns a geometry. my error seems directly related to returning a geometry from a query. I can open tables and views from the SQLserver with no problem and queries execute correctly as long as they do not contain a geometry.

tjhb

8,399 post(s)
online
#07-Oct-18 02:00

I'm hoping Art or Adam will grab this thread when one of them is available. I don't have currently SQL Server installed on any of my machines so this post is not worth much.

Let's first distinguish two things: a query component, and an SQL command window.

A query component is persistent but editable. An SQL command window is live and volatile, like a sandbox, but it can be saved as a query component.

A query component and an SQL command window can contain exactly the same text, and if they do then in most circumstances they will do the same thing when executed. Also, each can contain multiple SQL statements (and other items), and each of those statements can itself be called a query.

So much for that (and the possible room for confusion). Let's now concentrate on creating a query component in the right place.

Let's say you have already launched a new blank project, and have successfully used Create > New Data Source to connect to an existing database of type "Database: sqlserver" (not in read-only mode).

That new data source now appears in the project pane. If you right-click its name (or any component within the data source), you can use Create > New Query and should get a New Query dialog that has two parts. Above the line, the name of the database and its connection technology ("sqlserver"). Below the line, a box to name the query, with Create Query and other options.

After clicking Create Query, the new query appears inside the data source (not in the project root). If you open it, the query text will begin (by default) with

-- $manifold$

which instructs the engine to use manifold syntax for what follows.

To use native SQL Server syntax, you must delete that -- $manifold$ comment.

(There are also ways to control use of manifold or native syntax from a command window.)

I am flying blind without SQL Server present, so don't hesitate to point out something that does not work as I have said.

rhowitt51 post(s)
#07-Oct-18 14:50

currently i am using SQL: adonetsqlserver, not sqlserver; don't know if that makes a difference.

I have not been able to get the "Database: sqlserver" connection to work. I get an error: Provider=SQLNCLI11..Error code: -2147467259 (REGDB_E_CLASSNOTREG) Provider=SQLNCLI10..Error code: -2147467259 (REGDB_E_CLASSNOTREG)

tjhb

8,399 post(s)
online
#07-Oct-18 22:10

I think you need an SQL Server expert, which is not me.

To prepare for that, I think you will need to provide some more details.

For example, your SQL Server version and edition; its install location, from Azure to network to local machine; possibly who installed it; and even what you mean by the apparently simple statement "I have a SQLserver database table" (how do you currently access it other than from Manifold?).

I might be totally wrong, but I don't think you can use either Manifold or native SQL from Manifold over an adonetsqlserver connection. I think that will give programmatic (.NET) access only, e.g. via a Manifold script.

Here is a useless guess, less a proper stab than a poke at the problem. Try launching Manifold as administrator (if you can), then retrying a connection to Database: sqlserver. Do you even get to the Database Login dialog?

I'm going to butt out now, since you need better help than I can offer.

rhowitt51 post(s)
#08-Oct-18 15:26

two steps forward one step back.

I solved the SQLNCLI11..Error code, the computer running Manifold 9 needed the Microsoft SQL Native Client 10 and SQLNativeClient 11 DLLs. I downloaded these DLLs from MS.

Now when I run the Query from with in the SQLserver datasource the query runs without an error. But now the geometry column is being returned as nvarchar data type, not as a geometry.

Dimitri

5,106 post(s)
#08-Oct-18 18:22

I have a SQLserver database table that has 10,000s of land parcels ( polygons ) .

What software was used to create those parcels? Could it be whatever package was used created them as a text format geometry, such as WKT?

rhowitt51 post(s)
#08-Oct-18 20:37

The data was created by Manifold 8. And as I said the data comes back correctly in View or using the Manifold query engine. It is just when the data is queried by the SQLNCLI10 ( SQL SERVER 2008 R 2) that it retrieves that data incorrectly.

The column is defined as Geometry and the when the data is displayed in the grid it looks like WKB not WKT.

adamw


8,242 post(s)
#09-Oct-18 14:11

Well, yes, whether you use adonetsqlserver or sqlserver does make for a big difference. It's entirely different codepaths - they converge at some point, but they are different, and the failures are different.

The sequence I followed below was for sqlserver. I'll check it against adonetsqlserver just in case, there might be limitations there that will make it not work in full.

In general, it is way better to use sqlserver. ADO.NET is a dead technology, we only provide ADO.NET dataports for compatibility purposes and are not putting any new features into them. I see that you figured out why sqlserver didn't work on your machine before - that's good.

adamw


8,242 post(s)
#09-Oct-18 13:55

When you run something like:

SELECT  OID

     ,County

     ,geometry 

 FROM [GISdata].[dbo].[Streams]

 where county = 'Monroe';

...using the native SQL Server engine, the server tells that the third field in the resulting table is a "user data type" = an opaque value which we cannot make sense of, only the server knows what the bytes it returned as values mean. This happens because of how SQL Server implements geometry types, they aren't implemented the same as numbers and strings, they live on a different level. To make sense of data in the geometry field, convert geometry values to something like WKB, then interpret the returned bytes as WKB.

When you run a similar query against SQL Server tables using the Manifold query engine, you don't have to do anything, because the Manifold query engine will do all necessary conversions automatically.

rhowitt51 post(s)
#12-Oct-18 14:40

Having thought about your answer here, I am a little confused.

It is apparent that Manifold is interrogating every SQLserver object that has a geometry column and querying those objects about their SRID ( and doing this in Native SQLserver syntax). So, if Manifold is able to determine which columns are geometries for the purpose of discovering what projection is being used, why is it not able to determine/remember which column in a result is returning geometry data?

adamw


8,242 post(s)
#15-Oct-18 17:15

Because for a table or view, we can check what the specific field types are from system tables in the database. We cannot do the same for a result of an ad-hoc query, ad-hoc queries do not (and cannot by design) be described in system tables.

We did some preliminary work, however, and it looks like we might still be able to resolve the type of a field in a result table even for an ad-hoc query on SQL Server. The code ends up being specific to SQL Server version - which means that we will have to keep it up to date with changes to SQL Server - but that's fine, we test against all new versions of SQL Server as they appear anyway. We will likely include the detection in the next cutting edge build - currently planned to happen near the end of this week.

rhowitt51 post(s)
#15-Oct-18 20:32

That makes sense.

Sound good on the preliminary work. Can't wait to test this out.

adamw


8,242 post(s)
#09-Oct-18 13:35

I'll check messages below this one, but a suggestion right here - since you don't want to be creating views and want to use a single query which you will adjust (if I understand it correctly), don't put that query onto SQL Server, keep it in the MAP file.

Here is a sequence I just followed, starting from scratch.

1. Open 9, create a new blank project.

2. Import a new drawing from a SHP file (drawing: states, table: states table). The table has an MFD_ID field with a BTREE index on it, which makes the data editable.

3. Normalize geometry of all objects in the drawing using the Transform pane (Normalize transform template). This is currently necessary prior to exporting data to SQL Server.

4. Connect to a SQL Server database.

5. Copy and paste the drawing table (states table) from MAP file to SQL Server. This creates a new table (dbo.states table) and a virtual drawing on the geom field on that table (dbo.states table.geom). The table uses MFD_ID field as a primary key - this is important for inserting new records, the values in that field cannot be NULL.

6. Create a new query in the MAP file (not on SQL Server database):

--SQL9

SELECT * FROM [SQL Server LocalDB]::[dbo.states Table.Geom]

 WHERE [mfd_id] > 10; -- arbitrary filter criteria

Run the query, observe that it works and that the resulting table is editable.

7. Create a new drawing based on the query (right-click the query, select Create - New Drawing, verify the geom field, click OK). Open the drawing, observe that it shows data.

8. Alt-click one of the objects in the drawing based on the query. This shows object field values in the Record pane. Edit one of the values, click Update Record. The changes propagate to the SQL Server database, the record corresponding to the object is changed.

9. Ctrl-click one of the objects in the drawing based on the query. This selects it. Invoke Edit - Delete to delete the object, confirm deletion. The changes propagate to the SQL Server database, the record corresponding to the object is deleted.

10. Invoke View - Mode - Create Point to start inserting points. Click somewhere in the map window to add a point. The system opens the Record pane for the proposed new object. Fill in the value for the MFD_ID field - must not be NULL and must be unique, I used the value that would pass the filter in the query. Click Add Record. The changes propagate to the SQL Server database, there is a new record added. Unlike with changes and deletions, the map window does not show the new record yet, because it does not watch for additions (long story why, but there are reasons for it). Invoke View - Refresh to refresh data manually. Once you do this, the new record should show in the map window as well.

Hope this helps.

adamw


8,242 post(s)
#09-Oct-18 13:37

For 1, you get the error because you have to convert native SQL Server geometry to something, it cannot go into the result table as an object. One option is to convert to WKB: SELECT ... geometry.STAsBinary() ... .

rhowitt51 post(s)
#09-Oct-18 14:22

Thank you for that insightful response. You get what I am trying to accomplish.

I will experiment with your suggestion.

The one thing I have notice is the speed difference between a native SQLserver query and a Manifold 9 query against the same SQLserver.

Once again , Thank you.

rhowitt51 post(s)
#09-Oct-18 21:42

I have been trying to implement your suggestion from above, but am running into some issues.

First some background.

The data bases has a table that contains all of the parcels let's call it geom_of_parcels. Currently there are some 300 views of this table, which return just the parcels for a selected owner.

When I create the "Data Source" for the database in Manifold 9 and then access that data source for the first time it looks like Manifold interrogates the SQLserver. I don't know all of the details but it looks like Manifold gets the schema for all of the views (e.g. GAL_parcels ), creates a Drawing (e.g. GAL_parcels.Geometry) for all of the views, and fetches the SRID for each of the Views.

Here is one of the issues: One of the views is complex, it has 273,721 records. When the SRID query ( SELECT TOP 1 "Geometry".STSrid FROM "dbo"."Geom_of_Complex" WHERE "Geometry" IS NOT NULL ) is run on this view it takes many minutes (15 to 20 minutes ). It not only takes 15 to 20 minutes put it maxes out the CPU of the server. I have tested this query ( in SQL Server Management Studio) and sure enough it maxes out the CPU. If I modify the query to "TOP 3", the result is returned in 4 seconds. How weird is that.

Second Issue: the table: geom_of_parcels, which all of the views are based on, does not have valid schema in Manifold. When I rightclick on geom_of_parcels and select Schema, it displays a Schema window with 3 items <new field>, <new index>, <new constraint> . Manifold did create a drawing (geom_of_parcels Drawing ) and a drawing (geom_of_parcels.Geometry), but when I try to view these the DRAWING window has tab with red Exclamation mark.

Without a valid schema for geom_of_parcels I can not create a query on geom_of_parcels in Manifold.

Third Issue: Manifold is not displaying all of the views that exist in the database.

Is it possible to open and close a data source, or refresh the data source with out closing the project?

Where do I go from here?

tjhb

8,399 post(s)
online
#09-Oct-18 22:38

I have a question. Why are you storing this data on SQL Server? (That's not meant as a challenge by any means, just a question.)

rhowitt51 post(s)
#10-Oct-18 03:10

The database started, 25 years ago, as a tabular data set that is used to manage our business. At about the same time we started storing our spatial data in AutoCAD, the only other choice for spatial data was ArcView. AutoCAD was chosen because, for our purposes, their printing capabilities were superior to ArcView.

Fastfoward to SQLserver 2008 R 2, which added a viable spacial component. With this addition, plus Manifold 8's ability to read this data, it made sense to move our AutoCAD spacial data to the SQLserver database. This provided us with a powerful database that now combined 25 years of tabular data with the spatial data.

With SQLserver Spatial we have been able to use GeoServer and OpenLayers to provide a web interface for our clients. The back end of the web server interfaces our data infrastructure directly.

We also use a combination of Python and OGR2OGR to access the database to produce client requested reports. These reports include tabular, tabular with maps and shape file exports.

The one thing we have been missing is the ability to have a SQL query that can bring in both tabular and spatial data and have both of those editable in real time. In Manifold 8 we used views to sort of accomplish this but we have on the order of 600 views. I am hoping that Manifold 9 will be able accomplish this ability.

tjhb

8,399 post(s)
online
#10-Oct-18 03:35

Great answer, and very interesting. Thank you very much.

tjhb

8,399 post(s)
online
#10-Oct-18 03:39

Is it possible, do you think, that over time you could replace all of those views with fully dynamic queries driven by Manifold 9 (perhaps backed by scripts with list-box choices etc)? It sounds like a lot of overhead and maintenance for something that I regard in Manifold SQL as basically free.

...But perhaps the missing piece there, that you might really like to know, is approximately what a Manifold 9 web server is going to look like (including how will it interface with other standards) and of course, when.

rhowitt51 post(s)
#10-Oct-18 12:04

Yes, my hope is fully dynamic queries. The tabular data side of the database is built on queries that use parameter substitution. Instead of reading 1,000,000 records and then filtering, queries with parameters deliver just records you requested and the results, if primary keys are included in the select statement, are editable.

To a limited extent GeoServer and OpenLayers do this in the web interface, but it is not as dynamic as Manifold.

adamw


8,242 post(s)
#10-Oct-18 09:24

Regarding this:

Is it possible to open and close a data source, or refresh the data source with out closing the project?

Yes, it is possible. You can right-click the data source in the Project pane and invoke Refresh in the context menu.

9 does retrieve the schemas of views and tables, yes - to create virtual components like drawings which let you view spatial data in tables immediately. It is strange that SELECT TOP 1 ... on geom_of_complex takes longer to run than SELECT TOP 3 ..., this might be worth looking into and fixing - that's on SQL Server. But it's also strange that geom_of_parcels fails to return a schema - I suspect the log window might have some complaints, they might contain useful info. The third issue might be a variant of the second one - maybe some views fail a little earlier in the interrogation than geom_of_parcels, but for the same reason - or it might be something else.

In any case, we have one non-critical issue with SQL Server and two critical issues with 9 interacting with SQL Server.

I'd suggest this:

1. Set up a new test database, import some spatial data into it using 9 and work with that database from 9 to get a sense of what the workflows are and what in general should be happening.

2. Recreate geom_of_parcels on the new database and copy a couple of records from the old database into it. Make sure that the reduced version of the table in the new database "works" in SQL Server (in that you can select some data from it and get a result that makes sense), but 9 cannot make sense of its schema. Then back up the new database, contact our technical support, report the issue as a potential bug in 9 (reference this thread) and offer to upload the backup - they will provide you with FTP upload details, you can then upload the backup there (can encrypt it and email the password), we can then look into what's going on and diagnose / fix it.

3. You might do the same with geom_of_complex and the tables it references plus the views that 9 does not recognize - try recreating the issue on a model database then backup it and send us the backup, we will then take a look.

I realize that creating a model database, trying to reproduce the issue on it and then making and sending a backup of it to tech support sounds laborious, but that's unfortunately the nature of issues with databases. File format issues are much easier in that respect because you can just send the offending file. On the plus side, I can assure you that in the end such effort is never wasted - we will look into what is going in the database on this way and we will have a reasonably complete picture. If we trace the issue down to a bug in our code, we will be happy to fix it, and if we trace it down to a bug in someone else's code, we might offer a workaround.

adamw


8,242 post(s)
#10-Oct-18 09:45

(After thinking a bit about SELECT TOP 1 ... vs SELECT TOP 3 ..., perhaps the speed difference is not because of 1 vs 3, but rather the first query is slow and all further queries are fast because the database cached the data for the view. If this is the case, there is nothing to fix on your side - yes, accessing an expensive view for the first time could be slow, but further accesses are going to be fast, maybe we should try to skip accessing the view until the moment we absolutely have to know the SRID for the geometry field, but there are no bugs, things work as they should, etc.)

rhowitt51 post(s)
#10-Oct-18 12:38

I will create a test Database. Manifold 9 is my way forward.

Our tabular business suite was built with Delphi and SQLserver. The power of SQLserver and parameterized queries allows the system to manage millions of records quickly and provide all of that data to multiple users at the same time. One of the few weakness in Manifold 8 is the lack of native parameterized queries. In my humble opinion, it one of the most import things that needs to be in Manifold 9 and work correctly.

I did test Select Top 1 , select top 2 and select top 3.... 1 and 2 both max out the CPU and take a long time. Starting with 3, results are returned within 4 seconds. Caching does not seem to be involved.

The system should hold off interrogating an object until it is used. The database my have 100s of objects, but Manifold will probably access only a handful of those so why waste the time.

Also, why can't Manifold use SQLserver's native client drivers to access geometries in the database? The native queries are amazingly fast, there has to be a way to use those.

adamw


8,242 post(s)
#10-Oct-18 13:10

Agree about holding off interrogating an object until absolutely necessary - we mostly do hold off, but not in this particular case for SRID. We'll think about what we can do here.

We can use the modules that get installed with SQL Server Native Client to decode geometry, the culprit is that we have to know that the binary values are geometry - we cannot know that in all cases. But maybe we don't have to do it in all cases to be useful - we'll look into it as well.

Last, we do allow running parameterized queries on remote databases pretty seamlessly. For example, you can have a Manifold query in a MAP file that would run a parameterized query in a SQL Server database with specific parameter values - you can then create drawings based on that Manifold query, change query text to use different parameter values, etc.

In fact, we allow running statements in SQL native to a database as part of a bigger query in Manifold (EXECUTE [[ ... <sql-that-runs-on-remote-database> ... ]] ON [data-source]). We are working to extend what we can do there as well, this is a pretty hot area.

rhowitt51 post(s)
#10-Oct-18 17:26

Parameterized queries will be very powerful when run against native SQL server; can't wait to get rid of the Views.

I am working on a test database. I am stripping out the tables that are not pertinent to this issue but will try to leave the "geom_of_parcel" and associated views intact. How should this be delivered to you?

Quick question, for a $Manifold$ query that uses tables in SQLserver and contains a "where" statement, is the whole table brought into Manifold and then the filter from the "where" statement applied or are only the records that match the "where" statement returned from SQLserver?

Just trying to understand how to write efficient queries.

adamw


8,242 post(s)
#11-Oct-18 10:00

Contact tech support, tell them that you want to report a potential bug + reference this thread, and ask them where you should upload the data. They will provide you with FTP space. See this page for more details - it is a long one, but only because it covers a lot of ground.

Quick question, for a $Manifold$ query that uses tables in SQLserver and contains a "where" statement, is the whole table brought into Manifold and then the filter from the "where" statement applied or are only the records that match the "where" statement returned from SQLserver?

This depends on what indexes the table on SQL Server has. Roughly speaking, if WHERE contains some filtering that can be performed by the index on the database, then that filtering is performed on the database. Now, things are obviously much more complex than that, but that's what we are generally trying to do.

rhowitt51 post(s)
#11-Oct-18 18:57

While preparing a test database for Manifold I have shed a little more light on the source of the bug. The View that is causing the very long execution, is created via a join between tables in two different databases. If the View is created from tables in the same database, then the query on the view executes quickly.

I will prepare both databases for testing at Manfold and provide the both types of Views.

adamw


8,242 post(s)
#26-Oct-18 17:51

Try 9.0.168.4.

It helps with:

(a) geometry values in ad-hoc queries - these are now automatically recognized (we found a way that works in all cases for SQL Server),

(b) computing SRID values for geometry columns in tables and views - we cannot currently not try to compute them pro-actively at least once, but we are now caching them on the database so that you only compute them once when you connect for the first time and then only ever compute values for new tables / views with geometry columns that you add, also only once per table / view. We will probably stop computing these values completely in the future, but we have to restructure several things first, there are indeed expectations in several places in our code that a geometry column knows its coordinate system even before its table / view is opened. This is specific to SQL Server, other databases already store SRID values associated with geometry columns themselves, avoiding the issue.

rhowitt51 post(s)
#27-Oct-18 13:07

Thank you for including these updates in the current release. I will start posting over in the Cutting Edge as I test the updates in 9.0.168.4.

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