Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
Best approach for queries with variable IN |
Sun, May 7 2017 9:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, May 8 2017 1:18 PM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Tue, May 9 2017 2:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, May 9 2017 12:27 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, May 9 2017 12:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Wed, May 10 2017 2:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 10 2017 4:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 10 2017 5:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 17 2017 4:48 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Did that and the overall speed went to .031 seconds >> Damn, I'm good.... Tim Young Elevate Software www.elevatesoft.com |
Thu, May 18 2017 5:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |