I'm doing a few things list this

function TTfREMailsForm.RestrictListToOwn: string;
begin
if qApplyPrivileges then Result := SelMsgNo.CommaText else begin
 OwnList := MakeEDBQuery(emDB);
 OwnList.SQL.Text := ' SELECT LIST(CAST(_MsgNo AS VARCHAR)) FROM EMails WHERE _MsgNo IN (' + SelMsgNo.CommaText + ')';
 OwnList.SQL.Add('AND (_fkStaff = ' + QuotedStr(HHCommons.UserID) + ' OR (_fkStaff = '''' AND _fkMailBoxes > 0)');
 OwnList.SQL.Add('OR (SELECT COUNT(*) FROM Staff) = 1)');
 OwnList.ExecSQL;
 if OwnList.RecordCount > 0 then Result := OwnList.Fields[0].AsString;
end;
end;

Its not being called in a tight loop so not massively time constrained but it is part of the user interaction and its annoying having to wait for it. Is there a halfway house between a fully prepared and something like this to speed things up?



Roy Lambert

Roy

I think what you are saying you want is something like this:

 MyQuery.SQL.Text:= ' SELECT LIST(Name) as Names FROM Products WHERE ID IN (:IDList) ';
 MyQuery.Prepare;
 MyQuery.ParamByName('IDList').asString:= '1000, 1001';
 MyQuery.Open;

i.e., being able to prepare a Query with a parameter, but then pass in a set of values to the parameter.

In this way the query could sit in your application prepared, so it would run faster.

EDB Can't do this at the moment ... it reports a data-type mis-match for this parameter as a String is passed in but the query expects a set of Int.

My guess is that the compiler could be extended to cope with this.

The only way I can think of to get round this would be to add a sub-select in place of the "IDList" param, into which a single parameter could be added.

i.e., in the above ":IDList" would be replaced by:

SELECT ProductsID FROM SomeOtherTable WHERE Date = :Date

If you were able to then parameterize on the basis of the date.

Adam

>I think what you are saying you want is something like this:
>
> MyQuery.SQL.Text:= ' SELECT LIST(Name) as Names FROM Products WHERE ID IN (:IDList) ';
> MyQuery.Prepare;
> MyQuery.ParamByName('IDList').asString:= '1000, 1001';
> MyQuery.Open;
>
>i.e., being able to prepare a Query with a parameter, but then pass in a set of values to the parameter.

That is the ideal but as you say ElevateDB can't do it yet (I seem to recall that Tim said he'd see) so I suppose what I'm hoping for is a way to keep at least the table handles open to reduce the time taken to fully prepare and sun the statement.

>The only way I can think of to get round this would be to add a sub-select in place of the "IDList" param, into which a single parameter could be added.
>
>i.e., in the above ":IDList" would be replaced by:
>
>SELECT ProductsID FROM SomeOtherTable WHERE Date = :Date
>
>If you were able to then parameterize on the basis of the date.

Unfortunately the list of ID numbers is generated from items selected in a DBGrid so moving to a subselect would still require the list to be entered. Unless, he says as his brain picks up the necessary caffeine, rather than writing into a stringlist I use a memory table then I might be able to do it. Hmmmmm

Roy

Roy,

<< Its not being called in a tight loop so not massively time constrained but it is part of the user interaction and its annoying having to wait for it. Is there a halfway house between a fully prepared and something like this to speed things up? >>

How fast is it running, currently ?  Did you check the query execution plan ?  There could be some room for optimization.

Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< That is the ideal but as you say ElevateDB can't do it yet (I seem to recall that Tim said he'd see) >>

The issue is that EDB needs array types for this, and that's a big job.  Furthermore, even with that support, there's no way to get the array into a parameter in Delphi (there *is* a TFieldType member, ftArray, but it isn't supported in the TParam class).

Using a string parameter with "implicit separators" is problematic due to the ways that it could break existing code.  Escaping separators isn't difficult, though.

So, it's a tough nut to crack...

Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Its not being called in a tight loop so not massively time constrained but it is part of the user interaction and its annoying having to wait for it. Is there a halfway house between a fully prepared and something like this to speed things up? >>
>
>How fast is it running, currently ? Did you check the query execution plan ? There could be some room for optimization.

About 750 ticks (using gettickcount). The only columns used from EMails are _MsgNo which is the primary key (as an aside I double checked in EDBManager PK is in the index list but its content isn't shown - you have to look in Constraints or the table definition to find it) and _fkStaff which has an index Staff on it.

I'm going to check out the idea generated by Adam's post and see its faster.

At present I'm assuming most of the 750 ticks is spent preparing the query.

Roy

Tim


>How fast is it running, currently ?

I will keep forgetting that D2006, MadExcept & FastMM4 in debug mode slow ElevateDB down a bit. Outside the IDE it drops to 94 ticks and in EDBManager .078 seconds which I think will be fast enough for me

Roy

Tim

There could be some room for optimization.

Does adding an extra set of brackets to give the correct result count as optimisation?

Testing I found that
SELECT
_MsgNo
FROM EMails
WHERE
_MsgNo IN (2018,32064,32156,32157,32158,....)
AND (
_fkStaff = 'XX' OR (COALESCE(_fkStaff,'') = '' AND _fkMailBoxes > 0)
OR (SELECT COUNT(*) FROM Staff) = 1)

gave the right result but

SELECT
_MsgNo
FROM EMails
WHERE
_MsgNo IN (2018,32064,32156,32157,32158,....)
AND
_fkStaff = 'XX' OR (COALESCE(_fkStaff,'') = '' AND _fkMailBoxes > 0)

didn't.

I needed to bracket _fkStaff = 'XX' OR (COALESCE(_fkStaff,'') = '' AND _fkMailBoxes > 0) as well.

Did that and the overall speed went to .031 seconds

Roy

Roy,

<< Did that and the overall speed went to .031 seconds >>

Damn, I'm good....

Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< Did that and the overall speed went to .031 seconds >>
>
>Damn, I'm good....

Yeah - but if you were really good the SQL parser would have read my mind for what I wanted to do rather than what I typed!

Roy