Capesoft Driver Kit for MSSQL, how much work must I expect

[Bjarne_Havnen]

Customer complain about speed again. This time they have tried to run the program using a L2 connection. This slows thing down to a third of normal speed. I don’t expect that to be efficient.

My long term goal with this application is to reduce it to a service without user interaction and move all non-obsolete features to .NET. But that takes time, even with the help of AI and template language to create EF models.

So: If I purchase the SQL driver kit, compile it and change file driver to MSSQL2 driver, how much extra work should I expect? I tried to skim the documentaion and found it will require me to install an ODBC driver and potentially change my connection string, tweak the rediection file a little, but else it appears to be plug&play. I have 300+ tables in a 40+ multi dll app.

I am thinking I would write some template code to change driver using PROP:Driver, if that is supported, hoping to avoid having to change my entire dictionary. That way I could still use MSSQL where it is required (FM3).

[KevinErskine]

I might be wrong, but my understanding is that Bruce has not completed the optimization phase of the drivers. He is focusing on Function. I did not think they were certified for production work yet. Reach out to Bruce, or join ClarionLive webinar this week and ask. (I am sure this would be useful to others)

[Bjarne_Havnen]

Well, in all fairness, it seems the Clarion MSSQL driver is not production ready either.

[Bruce]

In terms of work needed to add it to your application, theres very little. Install, compile etc shouldnt take much time at all.

Equally dropping it into the app is straightforward. Typically one needs to tweak the driver in the dict. (For a large dict id export to dctx, change, and build a new dict. Then youd have equivalent dicts to test with in the short term.

I havent tried setting prop:driver. I’ll play with that, but i suspect that wont easily be possible. Maybe, im not sure.

Performance is not yet where it can be. Some optimizations have been done, some are still to do. But I’d also add that having examples of “slow cases” would allow that side to move quite speedily. I guess it depends a lot on what is currently slow in your program.

I think more importantly the underlying offers more opportunities to gain performance, not just on the client side but the server side as well. There are a lot of things that work together to make something performant, and this exposes a layer (which is currently a black box) and so offers you more control over more of the factors.

At this point, it’s not a 5 minute thing. There is likely to be feedback to me, analysis of your use case, and then tweaks, either to your code or mine. I’m confident though that it wouldnt take a lot, and there are potentially significant gains yo be had.

[JonW]

I think you should expect to be more or less plug and play. Exception (though you can it ignore it) would be anything you do with PROP:SQL. The new drivers I think disable that by default, and the intent is for you to migrate your PROP:SQL to parameterized SQL() statements.

In terms of speed I think the only claim in that department is that inherently limiting the data requests will speed things up (i.e. select top 100 * from sometable where …) rather than asking for the full dataset. It should make a big difference in data with tables with many rows.

I’m intrigued by, but doubtful about, your DRIVER change suggestion. Like, when Clarion compiles PUT(myfile), is the resulting instruction: “call the PUT procedure that is in whatever driver DLL is attached to this file at this time”, or is it “call the PUT procedure in the MSSQL driver (which is the dictionary-defined driver for this file)”? You’d need it to be the first of those if I have understood what you are suggesting. But maybe your template would change things early so that the compiler believes the files are MSSQL2 from the beginning of the compilation process?

[Bjarne_Havnen]

Thank you. We use loads of PROP:SQL, since that has proven to be more efficient in many cases, so that makes a transition quite challenging. PROP:DRIVER is writeable only if Dynamic File Driver is installed, but the shipping drivers have all the same methods. I used it when it was first shipped, swapped a TopSpeed application to MSSQL with no other changes. Actually worked too. Since MSSQL2 adds method, it won’t work using Prop:Driver. I have intercepted template generation before, but to be frank, I am getting rusty with Clarion, so Bruce’s suggestion of txd export/change/import is probably better.

[Bjarne_Havnen]

What is slow… difficult question. UI stuff, like customer always want another column, from another relation, but never want to delete any columns, so views grows to absurdity. Screens are larger, so any calculations going on during loading are done for more rows than before.. We have design ideas that seemed wise at a point (order has several lines that can have several components which in turn has several time- and material calculations). Done wrong, a query builds a cursor over 2 million rows, and refreshes for 15 seconds each change. Sometimes it is a s easy as users adding custom filters and orders that just don’t match, or add same expression for conditional display for several columns in a large table. We have had several workshops trying to optimize, but it will always be challenged again.

The server is a monster, they have poured money into that, and the infracstructure should be just as good. It always boils down to database connectivity.

[Bruce]

@Bjarne_Havnen

We use loads of PROP:SQL, since that has proven to be more efficient in many cases, so that makes a transition quite challenging.

To be clear;
The new drivers encourage a move from unsafe prop:Sql to a safe call to SQL(). However they do not require it. Assuming you’re on Clarion 10 or later, it’s a single line of code to turn prop:sql on for all tables.

System{'Driver:MSSQL2'} = '/unsafe=true'

[Bruce]

@JonW

I’m intrigued by, but doubtful about, your DRIVER change suggestion. Like, when Clarion compiles PUT(myfile), is the resulting instruction: “call the PUT procedure that is in whatever driver DLL is attached to this file at this time”, or is it “call the PUT procedure in the MSSQL driver (which is the dictionary-defined driver for this file)”?

We’re getting a bit into the technical weeds here Jon, but since you raised the question, the answer is “neither”. What happens is a bit different.

So each driver basically exports 1 function - which we’ll call PIPE for now. when the program calls into the driver, it’s not a normal DLL call into a specific function, but rather a generic call into the PIPE function - with appropriate parameters, which in turn then “routes” the call (internally) into the DLL.

So all the program needs to know is “which PIPE function (ie into which driver DLL)” needs to be called. This is managed, under the hood, by the FILE structure. The compiler compiles a FILE structure into a FCB structure (File Control Block.) One of the fields in the FCB is the address of the DLL it belongs to.

In concept we should be able to change the driver at runtime by fiddling with the FCB entry. That’s do’able, but not something I’ve tested, and it may or may not be a reliable approach.

[Bruce]

@Bjarne_Havnen

Since MSSQL2 adds method, it won’t work using Prop:Driver.

The question of making prop:driver writable is unrelated to the added methods. IT may, or may not, be writable (that’s a different question to answer) but it won’t be because there are more methods.

[Bruce]

@Bjarne_Havnen

Done wrong, a query builds a cursor over 2 million rows, and refreshes for 15 seconds each change.

The use of server-side cursors was one of the primary reasons we started this project in the first place. (The other was unreliable connections.) Cursors are expensive because they slow down the server (for everyone) by consuming lots of server-side resources. Plus the entire cursor has to be built before the first page of rows can be returned. Which as you note, can be a delay for large views.

The new drivers do not build server-side cursors. This reduces resources consumed on the server, thus improving overall user performance. It’s hard to measure in a lab (where servers typically have excessive resources) but is aparent in the field where databases are a lot busier.

So your potential for real-world improvements at the client here are significant.

@Bjarne_Havnen

Sometimes it is a easy as users adding custom filters and orders that just don’t match

Most of performance improvement comes down to understanding why something is slow. The new drivers add prop:explain (Clarion Object Based Database Drivers Documentation)
This can be used in places (like browses and reports) to understand what the server is needing to do to perform the request. From this you might choose a better filter, or order, or you might choose to add a key.

Of course if you let your user enter free-form filters, and select any order, then it’s always possible for them to create slow requests. But even slow requests will likely go faster if the server is less busy. (If you are passing those custom values to the server via prop:Sql then you should have serious safety concerns as well.)

@Bjarne_Havnen

We use loads of PROP:SQL, since that has proven to be more efficient in many cases,

This is where things get a little trickier. Two things come into play. (And I’m speaking generally here, obviously I have not seen your specific code.)

Firstly, of course, your SQL code may be less than optimal. By using prop:sql you bypass any built-in functionality in the driver level. In the traditional drivers this was at times necessary, but with the new drivers you may find a more subtle approach is better. After converting to the new driver I would recommend returning to your “slow” prop:sql statements (especially if they are related to browses) and consider replacing them with more fine-grained properties (of which now, a lot more exist).

Think of prop:Sql as a sledge hammer. In the past it’s all we had. So (understandably) folk used it. But it is a very heavy handed approach to something that may be solved with a much smaller intervention. By refactoring that code, into the component properties, your program is able to take advantage of optimisations which are occuring in the driver layer.

Earlier I spoke about a process of improving performance. The important thing to understand is not that “I swapped in the new driver and it got faster” (indeed, it may be faster, or it may be slower) - it’s in the tools the new driver gives you to both understand performance and the ability to control it. There are many factors that come into a performance equation, the server, the client, your code and so on. The way to improving performance is partly yes, a faster client-side driver, but it’s also in the tools for you to improve your code in the longer term.

[CarlBarnes]

@Bruce

Equally dropping it into the app is straightforward. Typically one needs to tweak the driver in the dict. (For a large dict id export to dctx, change, and build a new dict. Then youd have equivalent dicts to test with in the short term.

@Bjarne_Havnen

I have intercepted template generation before, but to be frank, I am getting rusty with Clarion, so Bruce’s suggestion of txd export/change/import is probably better.

You probably have hand coded (not generated) File Declarations with DRIVER('MSSQL') ? Those would not be done by a DCT Driver mass change.

I also thought that an easier alternative to DCT changes would be to change the File.TPW template to spot and change DRIVER('MSSQL') to MSSQL2 at generation time. But that would also not get the hand coded files.

Consider trying a mass scan and change of the DRIVER() to MSSQL2 in the CLW files. Then do a build without generation using MSBUILD or the IDE can do it. That would also let you mass adjust other things, like maybe some DRIVER(,'/options').

There would be other changes. There is a DriverKit template to add. You would have to add the MSSQL2 driver to the Project, or do that in your mass scan and change.

You could also add/exclude special code for MSSQL2 wrapped with OMIT() and COMPILE() to your APPs. E.g. you would have different connect strings. Just put the new one in your current current APP wrapped in COMPILE(,_MsSql2_test_).

After a few passes of Scan, Change, Build and rethink … I would think you could have a testable build. I often did some of this in moving from C5 to C9.

[Gustavo_Saracca]

The best way to make massive changes to the Dictionary, and one that surprisingly worked, was to use the dictionary itself with the search and then replacement functions.

But always filter the tables involved first.

After that, the value can be updated.

[Bruce]

@CarlBarnes

There is a DriverKit template to add.

This template is safe to add to apps, even if the apps don’t use any Object Based Drivers. In other words you can add it to the app regardless.

@CarlBarnes

You could also add/exclude special code for MSSQL2 wrapped with OMIT() and COMPILE()

You don’t need to add conditional compiles. If you call a new command on an old driver you’ll get an Error NoDriverSupport (80). And any new properties will just return a blank string.

If you do want to wrap it I’d suggest the more generic;

if sometable{prop:obd} = 1
! whatever
end

This is less dependent on hard-coding the driver name, and allows the code to work with an object based driver. This gives you more flexibility in your driver selection later on. (For example, there might be an MSSQL3 driver or whatever.)

[For advanced users, there’s also Driver Substitution in Multi-Proj, but that’s a fair bit of work to set up]