Jeff

>Please don't - the dog at least will miss you (or maybe just the morning
>walks)

Thank you for assisting me in spluttering coffee over my keyboard

Roy

macc2010

>And i confirm you that in paradox ( database desktop ) it give me the results in 4 seconds and paradox handles null ok.
>
>I have a lot of selects in my program with the same structure and i do not know how to proceed.
>
>Is this a dbisam limitation?.

I don't think that expecting DBISAM to handle queries in exactly the same way as Paradox is going to work. You can speed things up for this particular type of query by adding separate indices for each field, but that will only reduce the time taken to c30 seconds vs Paradox's 4 seconds. That means no alteration to your queries. You can get the same sort of speed as Paradox by altering your query as Raul suggested, but that means some work on your behalf.

>Will you correct this limitation in a near future?.

This is a user supported forum so we can make no commitment to how the author will respond, but what I can say is that DBISAM is essentially on maintenance and is unlikely to have any alteration of this magnitude made to it.

Whilst I was a happy DBISAM user and am a happy ElevateDB user neither may be a perfect fit for your requirements. Much as I would hate to lose Elevatesoft a sale  have you tested Nexus, Advantage, Firebird?

Roy Lambert

Roy Lambert wrote:

macc2010

>And i confirm you that in paradox ( database desktop ) it give me the results in 4 seconds and paradox handles null ok.
>
>I have a lot of selects in my program with the same structure and i do not know how to proceed.
>
>Is this a dbisam limitation?.

I don't think that expecting DBISAM to handle queries in exactly the same way as Paradox is going to work. You can speed things up for this particular type of query by adding separate indices for each field, but that will only reduce the time taken to c30 seconds vs Paradox's 4 seconds. That means no alteration to your queries. You can get the same sort of speed as Paradox by altering your query as Raul suggested, but that means some work on your behalf.

>Will you correct this limitation in a near future?.

This is a user supported forum so we can make no commitment to how the author will respond, but what I can say is that DBISAM is essentially on maintenance and is unlikely to have any alteration of this magnitude made to it.

Whilst I was a happy DBISAM user and am a happy ElevateDB user neither may be a perfect fit for your requirements. Much as I would hate to lose Elevatesoft a sale  have you tested Nexus, Advantage, Firebird?

Roy Lambert

With all due respect, I am only doing question about an issue that i have seen in dbisam and elevatedb, and you are inviting me to see other products, ok, but the problem that i have mentioned is a dbisam limitation, if you do not want to see as this, ok, it is your problem, not the mine. I have seen this problem, and i have told you that, but you say me that paradox ... i do not understand, I only wanted that you confirmed me that dbisam has a limitation about the number of fields in the relations, and it does not use the index if the condition has more than one field, althought the index is perfect for that relation. I only want a confirmation about this issue from Elevate Soft.

Ok, dbisam is in maintenance, but i have told you that in elevatedb, the problem is the same.

Ok, forget paradox, lets talk about interbase, sqlserver ..., or nexusdb, just i have tested the same query in Nexusdb and the result is fine, only 0,296 seconds.  

Elevatedb has the same problem, you can test the same query migrating dbisam tables that i have attached in my post #2 and the query delay for 250 seconds and give me a result of one records.

Thank you and best regards.

macc2010


>With all due respect, I am only doing question about an issue that i have seen in dbisam and elevatedb, and you are inviting me to see other products, ok, but the problem that i have mentioned is a dbisam limitation, if you do not want to see as this, ok, it is your problem, not the mine. I have seen this problem, and i have told you that, but you say me that paradox ... i do not understand, I only wanted that you confirmed me that dbisam has a limitation about the number of fields in the relations, and it does not use the index if the condition has more than one field, althought the index is perfect for that relation. I only want a confirmation about this issue from Elevate Soft.

If you want a confirmation from Elevatesoft you will need to email Elevatesoft. This is a user supported forum. Unless you see a post from Tim Young you are talking to a user. Having said that I seem to recall a post saying that only the first field of a compound index is used for other than the ORDER BY clause: caveat - I am a user not the author and my memory is fallible.

The reason I suggested other products is  <<I have a lot of selects in my program with the same structure and i do not know how to proceed.>>. Raul has suggested a suitable mechanism for achieving what you require in the sort of times you are obtaining from Paradox but that will, potentially, require you to alter all of the queries with the same structure so an alternative may be your best option if this capability is of paramount importance to you.

I think DBISAM and ElevateDB are brilliant products but if your require the response times you're used to from Paradox and don't want to change your SQL then even if Tim decided to enhance them to use all fields in a compound index I have no idea how long it would take. Tim may very well check the forum and say its only a 5 minute job. I just don't know.

>Ok, dbisam is in maintenance, but i have told you that in elevatedb, the problem is the same.
>
>Ok, forget paradox, lets talk about interbase, sqlserver ..., or nexusdb, just i have tested the same query in Nexusdb and the result is fine, only 0,296 seconds.
>
>Elevatedb has the same problem, you can test the same query migrating dbisam tables that i have attached in my post #2 and the query delay for 250 seconds and give me a result of one records.


If you are willing to alter your table structure, and queries, and don't like Raul's suggestion, I have an idea for ElevateDB

CREATE TABLE "TABLEA"
(
"FIELD1" INTEGER DEFAULT 0,
"FIELD2" INTEGER DEFAULT 0,
"FIELD3" INTEGER DEFAULT 0,
"FIELD4" VARCHAR(1) COLLATE "ANSI",
"IDX" VARCHAR(45) COLLATE "ANSI_CI" COMPUTED ALWAYS AS CAST(Field1 as char(10)) + '-' + CAST(Field2 as char(10)) + '-' +
CAST(Field3 as char(10))+ '-' + Field4,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("FIELD1", "FIELD2", "FIELD3")

CREATE TABLE "TABLEB"
(
"FIELD1" INTEGER DEFAULT 0,
"FIELD2" INTEGER DEFAULT 0,
"FIELD3" INTEGER DEFAULT 0,
"FIELD4" VARCHAR(1) COLLATE "ANSI",
"IDX" VARCHAR(45) COLLATE "ANSI_CI" COMPUTED ALWAYS AS CAST(Field1 as char(10)) + '-' + CAST(Field2 as char(10)) + '-' +
CAST(Field3 as char(10))+ '-' + Field4,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("FIELD1", "FIELD2", "FIELD3")

Select a.Field1, a.Field2, a.Field3, a.Field4
From TABLEA a
 Left Join TABLEB b
 On a.Idx = b.Idx
Where b.Field1 is Null

This gives a sub-second (0.9) result, but, obviously, your original query would still take as long.

Roy Lambert

Sorry Roy,

I thought that you were staff from Elevate, so i was surprised with your reply and that you were suggesting me other products.  

The query that i am running is not anything exclusive from paradox tables, you can run the same query in other products like sql server, firebird, postgresql, mysql, sqlite, and the select will give you the same results. The select is valid to check if the records in table a, exists all in table b.

Thank you and best regards.

macc2010

>Sorry Roy,

No apology needed I'm sorry that I misunderstood your question to start with.

>I thought that you were staff from Elevate, so i was surprised with your reply and that you were suggesting me other products.

If I am I haven't seen a paycheck in years!

>The query that i am running is not anything exclusive from paradox tables, you can run the same query in other products like sql server, firebird, postgresql, mysql, sqlite, and the select will give you the same results. The select is valid to check if the records in table a, exists all in table b.

The query does give you the same result - it just takes a bit longer to do it

I can't think of a faster way to do it without restructuring, but it would be worth emailing Elevatesoft to see what Tim says. For all I know it could be on his roadmap.

If you do go with DBISAM or ElevateDB one recommendation is the strength of the user community - we do screw up occasionally but we do try and help, and once you get his attention Tim is very helpful. Right now he's probably buried deep in the internals of EWB so not visiting the newsgroups as often as he normally does.

Roy Lambert

Roy Lambert wrote:

> emailing Elevatesoft to see what Tim says

I'll echo that. Tim will give you decent information about what is
possible. I note though that this is a new project, so it would
certainly be worth looking at ElevateDB for new stuff. DBISAM is great,
but ElevateDB is going to be a better choice IMO. Not hard to switch
either.

--

Matthew Jones