Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 16 of 16 total |
Update table issue |
Wed, Jul 17 2013 1:08 AM | Permanent Link |
Ben Sprei CustomEDP | After some more testing Ive come to the conclusion that the CAST statement
does not cause any issues. I tried populating a new field in AR file with 74000 records using a cast statement that was applied to every record and the execution time was very reasonable. The problem is definitely with the IN statement. I tried adding a composite index for the three fields used in the IN statement with no benefit whatsoever. The view also executes in reasonable time. I do not see any delay in opening the view nor in executing a SQL statement similar to the one that creates the view. I there fore dont think that any time should be wasted on that. The only problem, as stated above, is the excution of the IN statement. And that seems to be an EDB quirk or a problem. If anyone can get brother Tim involved it might be helpful. Per your request Ive attached an execution plan for this query. SQL Update (Executed by ElevateDB 2.11 Build 3) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ================================================================================ UPDATE "ARLines" SET "Keyoff" = ("IX") WHERE ("KeyOff" IS NULL OR "KeyOff" = ' ') AND (("Phone", "PhnSeq", "InvNum") IN (SELECT ALL "Phone" AS "Phone", "PhnSeq" AS "PhnSeq", "InvNum" AS "InvNum" FROM "ClearedARTrans" ORDER BY "Phone", "PhnSeq", "InvNum")) Target Table ------------ ARLines: 74351 rows Filtering --------- The following filter condition was applied to the ARLines table: (("Phone", "PhnSeq", "InvNum") IN (SELECT ALL "Phone" AS "Phone", "PhnSeq" AS "PhnSeq", "InvNum" AS "InvNum" FROM "ClearedARTrans" ORDER BY "Phone", "PhnSeq" "InvNum")) AND ("KeyOff" IS NULL OR "KeyOff" = ' ') Row scan (ARLines): 74351 rows, 19.29MB estimated cost Hint: Create index(es) (ARLines) on column(s) "KeyOff" for possible better performance Here is the execution plan for the original SQL Statement: SQL Update (Executed by ElevateDB 2.11 Build 3) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ================================================================================ UPDATE "ARLines" SET "Keyoff" = ("IX") WHERE (("Phone", "PhnSeq", "InvNum") IN (SELECT ALL "Phone" AS "Phone", "PhnSeq" AS "PhnSeq", "InvNum" AS "InvNum" FROM "ClearedARTrans" ORDER BY "Phone", "PhnSeq", "InvNum")) Target Table ------------ ARLines: 74351 rows Filtering --------- The following filter condition was applied to the ARLines table: (("Phone", "PhnSeq", "InvNum") IN (SELECT ALL "Phone" AS "Phone", "PhnSeq" AS "PhnSeq", "InvNum" AS "InvNum" FROM "ClearedARTrans" ORDER BY "Phone", "PhnSeq" "InvNum")) Row scan (ARLines): 74351 rows, 19.29MB estimated cost This enforces my theory that that use of the IN statement is unacceptably inefficient. Any suggestions "Roy Lambert" <roy@lybster.me.uk> wrote in message news:3E8B1D4D-6D6F-4C34-B578-863BE9D09997@news.elevatesoft.com... > Ben > > > Since it complete can you ask for and post the execution plan? The > execution plan for the view (as a query) may also be useful. > > Have you tried the idea of creating a temporary table and seeing what > happens with that? > > Roy Lambert [Team Elevate] > |
Wed, Jul 17 2013 4:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
<<I tried adding a composite index for the three fields used in the IN statement with no benefit whatsoever.>> I presume this was in ARLines? Did you also add one for KeyOff? This is what the execution plan suggests. Try doing that and if the performance is then acceptable great - if not keep reading. The next part again comes with a warning - I'm guessing (we have asked Tim for a technical article on interpreting execution plans). I don't think the problem at its root is the IN statement, its the fact that its being carried out via a row scan ie an inefficient mechanism compared to an index scan, and it can't do anything else because you can't index a view. I'll suggest again create a temporary (or in-memory) table and use that not a view - you can use the view to create the temporary table if you want. You can add indices to a temporary table where you can't to a view. Roy Lambert [Team Elevate] |
Wed, Jul 17 2013 11:36 AM | Permanent Link |
Ben Sprei CustomEDP | From this discussion it seems to me that really is no efficient way to
accomplish this task via SQL. I think the simplest way would be: 1. Create View 2. Execute Query for the detail 3. Use this as a Master/Detail compbination 4. Use code to step thru view and update the detail (query). It doesnt really pay to invest any more time in this since it will be faster by code. I ques SQL has its limitations and old fashioned coded cannot be replaced entirely by SQL Ben "Roy Lambert" <roy@lybster.me.uk> wrote in message news:1F340A19-B4AB-41C2-BCDB-816331ABFAD1@news.elevatesoft.com... > Ben > > <<I tried adding a composite index for the three fields used in the IN > statement with no benefit whatsoever.>> > > I presume this was in ARLines? Did you also add one for KeyOff? This is > what the execution plan suggests. > > Try doing that and if the performance is then acceptable great - if not > keep reading. > > The next part again comes with a warning - I'm guessing (we have asked Tim > for a technical article on interpreting execution plans). > > I don't think the problem at its root is the IN statement, its the fact > that its being carried out via a row scan ie an inefficient mechanism > compared to an index scan, and it can't do anything else because you can't > index a view. > > I'll suggest again create a temporary (or in-memory) table and use that > not a view - you can use the view to create the temporary table if you > want. You can add indices to a temporary table where you can't to a view. > > > Roy Lambert [Team Elevate] > |
Wed, Jul 17 2013 3:13 PM | Permanent Link |
Barry | Ben
What happened when you replaced the "IN" with "=" as I suggested a couple of days ago? Any faster? (I am assuming the 3 fields in the Select statement are part of a compound index) Barry |
Thu, Jul 18 2013 2:28 PM | Permanent Link |
Ben Sprei CustomEDP | This is the result
ElevateDB Error #1011 An error occurred with the query (SELECT ALL "Phone" AS "Phone", "PhnSeq" AS "PhnSeq", "InvNum" AS "InvNum" FROM "ClearedARTrans") (A scalar query can only return a single value) Ben <Barry> wrote in message news:A65AFA9E-9CAC-4D5F-B7E5-0097799E533D@news.elevatesoft.com... > Ben > > What happened when you replaced the "IN" with "=" as I suggested a couple > of days ago? Any faster? > (I am assuming the 3 fields in the Select statement are part of a compound > index) > > Barry > |
Sun, Jul 21 2013 2:24 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ben,
<< If anyone can get brother Tim involved it might be helpful. Per your request Ive attached an execution plan for this query. >> This is going to require a support session to analyze. You can request one by emailing: support@elevatesoft.com. Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
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 |