SRP_Com accessing an indexed datetime MySQL field

michaell

First of all, sorry if this has already been covered. If so, I'd appreciate a pointer to it as I didn't find anything covering this scenario.

We are new to ADO and have just figured out that some MySQL datatypes require a 'cast' operation in the query. Unfortunately, one of our queries uses an indexed datetime field in its where clause. Casting this field so ADO will recognize the content will negate its use as an index which severely slows this query down (1,000's of records).

We have not found any solution to this issue besides setting the connection object's 'CommandTimeout' setting to 90-120 seconds. We are wondering if there are any other options out there.

-- thanks

DonBakke

This is unfamiliar to me. Do you have an example of how this is coded in straight SQL?

michaell

sure ...

MySQL table
ID field is the primary index - BigInt
ConnectTime field is a datetime field

query "select field1, field2, Cast(ConnectTime as char) as ConnectTime2 where Cast(ConnectTime as date) = '2023-04-22'

if we do not cast the datetime field it will replace the 'date' portion with null.

DonBakke

I think I misunderstood your original question. I thought this was an SRP_Com problem and you were trying to figure out how to setup your MySQL calls to resolve the problem. This seems to me that you simply have a MySQL query problem. That is, if you run the same query from any SQL tool you would experience the same problem. Is that correct?

michaell

Not quite Don. Our queries work fine using the HeidiSQL interface. The query using the datetime field in the where clause takes about 18 seconds in HeidiSQL and anywhere from 50 seconds to 70 seconds when submitting it via SRP_Com()'s ADO interface. I'm reviewing the ADO/MySQL referneces to this situation again to see if I missed something.

DonBakke

I see. I don't have any insight into this issue so I can only offer some high-level ideas to help isolate the problem. First, while I'm skeptical that this is an SRP_Com specific issue, it would be worthwhile to swap out your SRP_Com calls with the native BASIC+ OLECreateInstance, OLEGetProperty, OLEPutProperty, and OLECallMethod routines. Assume that produces the same results, then I would check to see if HeidiSQL does any preconfiguration automatically that allows casted field to utilize indexes. This might be a simple configuration you can do via a method call.

michaell

Thanks Don .. I'll look into that. I wasn't trying to imply that SRP_Com() was the problem :) . Just that there seemed to be an issue with MySQL and ADO not working well together with these datatypes. Our code used OLEDB connectors and XOInstance() (version 9.4.x) and moving to OI 10 we no longer have access to XOInstance, which led us to SRP_Com().

I used this forum because 1) it's active and 2) I see a lot of informative information here. Just hoping someone else had seen the same issue. I'll try to get some more data and re-post.

michaell

Don, thanks for your input. I created a DSBFS datasource object pointed to our remote database and so far it is working seamlessly. It's possible the issue I was seeing earlier may have been linked to the MySQL driver we installed to use with the ado connection string. At any rate, at this time I'm setting up this new datasource in our test lab and will hopefully be implementing it soon.

sometimes help is just shining a light in a previously-unseen corner :)

DonBakke

That is good news although it still leaves questions in my mind, especially since DSBFS uses ADO as well. I am somewhat curious how your SRP_Com based ADO code would have worked in OI 9.

michaell

I agree ... once I get a solution in place I'll revisit this to see if I can figure out where the issue actually lies.