Clarion 11 with MSSQL Extremely Slow with Browses across more than one MSSQL Database

questions
1

Hello Everyone

I would be most appreciative if anyone could assist please.

One of my major apps works on data from a number of companies. The data from each of these companies is stored in Private MSSSQL Databases. There is however common data between all companies that I have have moved into a “SharedTables” database.

I have changed my data dictionary appropriately and have two different connection strings for the tables in the SharedTables and in the Private Tables databases.

I have noticed that if I load an ABC Browse where there is a join between a table in the Shared Database and Private Database, my Browses are now degraded. ie. typically from 0.5 seconds previously to in the region of 45 seconds - to display the browse. When I return the table in question back to the Private Tables database - (thus no data in any other database) the speed is once again restored to its former glory.

Has anyone noticed this before?
Is anyone able to provide me with any insight into this please?
Is there something that I need to do to work around this?

Any assistance greatly appreciated.

Thanks
G

2

I’d use Clarion driver tracing to see where the delay actually is.
I’d also double check the DNS settings, DNS problem/fall back can cause that sort of thing.

3

As Sean suggests, tracing would probably be informative.

Are permissions the same on both databases (connections)?
Do you have BUSYHANDLING correctly set before a file is open on each connection?

For common data that’s static, I generally download that into an in-memory table when the app starts.

Another thing you can do is to create a view in each client database that joins the Shared and Private tables and returns a result set.
Import that into your dictionary and see whether things run faster.

1 Like
4

Yes, this is a well-known issue I discovered as well. You can’t join tables from different databases. Because of different connections (in Clarion terms).

As a simple workaround just create a View (SQL part) in your main database, this View will point your table in your second database. And then you can use this View (defined as a Table in DCT) in JOIN. Since they are both in the same database (and the same connection) you will have no slow issue…

2 Likes
5

Hi Jane

I have had many problems when changing the options for the MSSSQL connections. So the best result to date by far has been to have the options as blank.

Do you know where I can get more information on exactly how to create a MSSSQL view to implement in an ABC browse - because it sound exactly what I need - and I have never done this before.
Thanks
G

6

Hi Sean
DNS set correctly - it has been confirmed. To check further I decided to run the app ON the MSSQL server - and got the same results - but only when 2 databases are concerned.

7

you can easily create a SQL VIEW, like below

on your main database

CREATE VIEW [dbo].[CWTOWN]
AS
SELECT       *
FROM  YOUR_SECOND_DATABASE.dbo.CWTOWN
1 Like
8

Hi, Grant,

We’ll agree to disagree on the MSSQL connection options.

As for a view, you can create a raw view containing everything from a table in your second database as Guennadi describes.

But you can also do your joining on the server and create a view with the columns you actually need from both tables that you were joining in your Clarion browse.

Such as

CREATE VIEW dbo.myView
AS
SELECT m.itemName,m.itemCategory,i.price,i.taxRate,i.companyBrand
FROM yourIndividualDatabase.dbo.customerItems AS i INNER JOIN
yourSecondDatabase.dbo.masterItemTable AS m
ON i.itemId = m.itemId;

Then you can go into the Clarion dictionary editor and import that view just as if it is a table.
The only place that gets tricky is if you’re inserting and/or updating data. There’s plenty of online help about that.
You can also create a “filtered view” to limit the records (such as only non-obsolete products, or whatever).

2 Likes
9

I suspect the issue here is that you are loading data in one browse from different connectionstrings in the same file schematic. The way Clarion solves this is loading one dataset (connection) and fetching corresponding records in the other datasset (connection). You should be able to confirm this by analyzing the queries in a Clarion Trace.
The solution could be to use the same connectionstring and use linked the databases. If that is not possible, try loading data manually in a queue or reducing the dataset with filters as much as possible.

3 Likes
10

Why not link your shared database to all of the private databases and then hand-code your queries? I’ve used this technique in the past and haven’t seen any big performance issues.

1 Like
11

Last to the party, but I agree with most all of the suggestions and analysis (except maybe for the need to hand-code queries).
Faced with two different connection strings Clarion has no idea that the tables are actually on the same server, and will get a record from the “main” table and then get any matching records that match that single row from the secondary table(s). Basically a one-at-a-time slow-by-slow approach.

I think you should be able to get back to normal if:

  1. you grant (in the database) all your private users access to the shared table
  2. you declare the table in the dictionary with the same connection string as the private tables, but with a table name that points to the shared table (e.g. shared.shared_table)

Creating views on the server would work too, but I don’t think it is necessary to solve your problem.

12

Hi, the problem is that Clarion converts two tables with differents DSN in: one query, a pointer for each record and another query for each one instead of only one query with both.
If you use Sql Profiler you should see that.

In out experiencie in that cases we keep only one table in table-schematic, and the other we use .fetch in setqueue record.