I am in the process of  exploring EDB in more detail (ie SQL) having migrated an old navigational DBISAM app across  I have noticed that some of filtered DBISAM tables are very slow to load when converted to using EDB especailly if using C/S  It appears to me that using VIEWS may help as they are single source tables however I believe that I cannot sort these in any way  Is that correct and are there any work arounds

So is using a VIEW that best t way forward or can I use other sensitive resilt sets that allow sorting

Thanks in advance

Michael

>I am in the process of exploring EDB in more detail (ie SQL) having migrated an old navigational DBISAM app across I have noticed that some of filtered DBISAM tables are very slow to load when converted to using EDB especailly if using C/S It appears to me that using VIEWS may help as they are single source tables however I believe that I cannot sort these in any way Is that correct and are there any work arounds
>
>So is using a VIEW that best t way forward or can I use other sensitive resilt sets that allow sorting

I can't tell you wether a views is best for you or not. I don't use them but that's mainly due to the fact that I don't have a need for them. Think of a view as being almost like a table but not quite, or like a sensitive result set, but again not quite. In my opinion (was going to write view but thought maybe not) they are best used when you have consistent and unchanging sets of data (eg all companies in Slough) since,  as far as I know, you can't parameterise them. Once they're set up you can access the indices set for the main table so yes they can be sorted.

If your filters fit that sort of definition then a view could be very useful.

You can also include an ORDER BY clause in the SQL that creates the view, or you can create a query that accesses the view and has an ORDER BY clause as part of it.

Views can be created / destroyed on the fly but if you're doing that a lot then its probably not the right approach and a sensitive query would be better

Sensitive result sets can have an ORDER BY clause as long as the clause maps exactly to an index otherwise the query will become insensitive.

Roy Lambert

From what you say it would seem that VIEWS could be what I need so I will explore further.  

One more thing How does this approach differ from other ways  of providing sensitive result sets eg  Could a Stored procedure be an alternative  

Many thanks

Michael


Thinking about it I'm surprised with what you're experiencing. Its quite a while back but when I migrated my main app from DBISAM to ElevateDB I didn't notice much speed difference either way (apart from full text indexing which was better). That was f/s I have done some comparisons using c/s and its better for some things but since I uses tables, persistent fields and navigational methods a lot overall there wasn't much difference.

An approach I've used in the past is

1. pick one of the slow filtered tables
2. in EDBManager create a query using teh filter from that table as the WHERE clause
3. make sure you tick to get an execution plan
4. run the query and the plan should tell you what's needed to make it faster

My guess would be that some index isn't right causing a brute force scan. If you post the plan here we can have a look and see if there's anything obvious

Roy Lambert

Michael,

Roy's answer was faster than me

Anyway I'd like to confirm what he said: EDB should be *very* fast - if
not, the might be an issue with missing indexes.
Roy described the way to check that.

Uli

make sure you tick to get an execution plan

Thats's another  new one for me  I will need to investigate further

My guess would be that some index isn't right causing a brute force scan. If you post the plan here we can have a look and see if there's anything obvious

You are probably right here  For your interest here is the table definition & filter definition  (540000 rows)  I am thinking of splitting it onto 2 tables as in its present form it doesn't work as I would like but it provides a good example of something that could probably be improved greatly and is very slow to load using EDB but quite reasonable using DBISAM

CREATE TABLE "Advice"
(
"PKAdvice" INTEGER,
"FKOrders" INTEGER,
"Adnumber" INTEGER,
"Adline" INTEGER,
"Adaccount" VARCHAR(5) COLLATE "UNI",
"Addate" DATE,
"Adcustref" VARCHAR(12) COLLATE "UNI",
"Adcustline" INTEGER,
"Adpattern" VARCHAR(15) COLLATE "UNI",
"Addeliver" VARCHAR(5) COLLATE "UNI",
"Addescription" VARCHAR(40) COLLATE "UNI",
"Adqty" INTEGER,
"Adinvoiceflag" VARCHAR(1) COLLATE "UNI",
"Adfirst" VARCHAR(1) COLLATE "UNI",
"Adflag1" VARCHAR(1) COLLATE "UNI",
"Adflag2" VARCHAR(1) COLLATE "UNI",
"Adflag3" VARCHAR(1) COLLATE "UNI",
"Advicedate" DATE,
"AdKgs" FLOAT,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("PKAdvice"),
CONSTRAINT "Advice" UNIQUE ("Adnumber", "Adline")


procedure TDM.tbAdviceFilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
 if (DataSet.FieldbyName('Adline').asinteger = 1)  and
    (DataSet.FieldbyName('AdInvoiceFlag').asvariant = null) then
   Accept := true
 else
   Accept := false;
end;

Thanks

Sorry I meant 54000 rows

Michael


>procedure TDM.tbAdviceFilterRecord(DataSet: TDataSet; var Accept: Boolean);
>begin
> if (DataSet.FieldbyName('Adline').asinteger = 1) and
> (DataSet.FieldbyName('AdInvoiceFlag').asvariant = null) then
> Accept := true
> else
> Accept := false;
>end;

This is your problem. I totally misunderstood your "filtered table". My view is you should not use this spawn of the devil unless there's no other alternative.

Create an index on Adline and AdInvoice flag and use a simple filter 'Adline = 1 AND AdInvoiceFlag IS NULL' and it should fly. Even without then a "proper" filter should be faster.

The equivalent query would be 'SELECT * FROM Advice WHERE Adline = 1 AND AdInvoiceFlag IS NULL' - making sure you request a sensitive result set


Roy Lambert

Thanks a lot you were spot on

I had not realised that my this way of filtering  was so inefficient compared with using the Filter property Even with the index added it was still markedly slower using the onfilterrecord event

It so happens that In my DBISAM app I was using the Filter property  so that explains why that was working well even without an index

That has saved me quite a few hours of research although I am sure that there is a ton of useful techniques in EDB I could use but am not aware of

Michael

Sorry to be slow to this thread.

I use VIEWS quite a bit. They are helpful for me in flattening otherwise relational data held in multiple tables, meaning I don't have to rewrite SQL again and again.

They can also be used to hold summaries i.e. Customer, SUM(Value of Orders), MAX(Date of Order).

VIEWS can be treated exactly like tables, filtered, ordered, grouped etc., in SQL they are added and referenced exactly like tables.

i.e.

SELECT
 MyColumns
FROM MyView
WHERE MyConditions

I don't think VIEWS will give you any advantage in terms of speed of data access. For that the overriding issue tends to be appropriate INDEXES.