Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 16 total |
Update table issue |
Thu, Jul 11 2013 4:45 PM | Permanent Link |
Ben Sprei CustomEDP | Code set #1
UPDATE ARLines SET Keyoff = (Cast (InvNum as VarChar(10))) WHERE ("Phone","PhnSeq","InvNum") IN (SELECT Phone, PhnSeq, InvNum from ClearedARTrans) Code set #2 UPDATE ARLines SET Keyoff = (Cast (InvNum as VarChar(10))) WHERE InvNum = 56789 As a note: The field KeyOff is a Varchar of 10 The field InvNum is an integer and non in the database are greater than 100,000 Set # 1 hangs. The result set from the last line is approx 15 records and records to be updated are approx 40 Set #2 works fine albeit its only three records to update. Why |
Fri, Jul 12 2013 12:44 PM | Permanent Link |
Fernando Dias Team Elevate | Ben
What indexes and how many rows are there in those tables ? -- Fernando Dias [Team Elevate] |
Fri, Jul 12 2013 1:10 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
In addition to what Fernando asked how long did you give it to execute? Is ClearedARTrans another View? If so how long does a simple SELECT * FROM ClearedARTrans take? If its the first time its been opened the view has to be built before anything else can happen. If as I suspect ClearedARTrans is a View then to obtain an execution plan (which will give you a good indication of how to make it faster) you'll need to run the sql that makes up the View as a straight query. Roy Lambert [Team Elevate] |
Sat, Jul 13 2013 12:12 AM | Permanent Link |
Barry | Ben,
If both tables have the same compound index of Phone, PhnSeq, InvNum, have you tried the following: (Replace "IN" with "=") Barry Code set #1 UPDATE ARLines SET Keyoff = (Cast (InvNum as VarChar(10))) WHERE ("Phone","PhnSeq","InvNum") = (SELECT Phone, PhnSeq, InvNum from ClearedARTrans) |
Sun, Jul 14 2013 2:38 PM | Permanent Link |
Ben Sprei CustomEDP | The original table has approx 150 records
The result set is approx 15 records Records to be updated are approx 40. The view to open takes less than 2 seconds An update without the cast statement will take 1-2 seconds An update with the cast statement will hang the query. I left it like that for 10 minutes and it wont move. <Barry> wrote in message news:0DA39191-D897-4E07-BF15-B49E9A5D9D4F@news.elevatesoft.com... > Ben, > > If both tables have the same compound index of Phone, PhnSeq, InvNum, have > you tried the following: > (Replace "IN" with "=") > > Barry > > > Code set #1 > UPDATE ARLines > SET Keyoff = (Cast (InvNum as VarChar(10))) > WHERE ("Phone","PhnSeq","InvNum") = > (SELECT Phone, PhnSeq, InvNum from ClearedARTrans) > |
Sun, Jul 14 2013 8:45 PM | Permanent Link |
Barry | Ben,
This makes no sense, unless it is trying to do a cross product join for some reason. What does the Execution Plan say? What happens if you add "Range 1 to 1"? If none of these suggestions shed any light on the problem then... .. this looks like a job for Tim. Barry |
Mon, Jul 15 2013 3:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
I assume you're referring to this statement UPDATE ARLines SET Keyoff = (Cast (InvNum as VarChar(10))) WHERE ("Phone","PhnSeq","InvNum") IN (SELECT Phone, PhnSeq, InvNum from ClearedARTrans) [WARNING] I'm guessing here [/WARNING] If ClearedARTrans is the same or similar to ClearedAPTrans I think you may have generated an infinite loop. Tim will be able to say. Assuming for the moment that the two views are similar and that both reference Keyoff then each time you update Keyoff the view is rerun which causes the selection to be changed which causes the view to be rerun There's an easy way to test this out. Create a temporary table using the view and then use that in the UPDATE query eg CREATE TEMPORARY Table Fred FROM SELECT Phone, PhnSeq, InvNum from ClearedARTrans UPDATE ARLines SET Keyoff = (Cast (InvNum as VarChar(10))) WHERE ("Phone","PhnSeq","InvNum") IN (SELECT * FROM Fred) Roy Lambert [Team Elevate] |
Mon, Jul 15 2013 3:03 PM | Permanent Link |
Ben Sprei CustomEDP | ClearedAR and Cleared AP are 2 distinct views. They just happen to have a
field named alike. Im NOT trying to cross from one to the other. The only difference is that in AP the InvNum field is a VC and in AR InvNum is an integer. I just happen to be doing a similar operation on either file. When I do it in AP (without the Cast since the InvNum and KeyOff fields are VC) there is no problem. When I do the same thing in AR with a CAST statement its hanging. Ben "Roy Lambert" <roy@lybster.me.uk> wrote in message news:30FA2C52-1BB5-416B-BE16-1DB80E9EADBD@news.elevatesoft.com... > Ben > > > I assume you're referring to this statement > > UPDATE ARLines > SET Keyoff = (Cast (InvNum as VarChar(10))) > WHERE ("Phone","PhnSeq","InvNum") IN > (SELECT Phone, PhnSeq, InvNum from ClearedARTrans) > > [WARNING] I'm guessing here [/WARNING] > > If ClearedARTrans is the same or similar to ClearedAPTrans I think you may > have generated an infinite loop. Tim will be able to say. > > Assuming for the moment that the two views are similar and that both > reference Keyoff then each time you update Keyoff the view is rerun which > causes the selection to be changed which causes the view to be rerun > > There's an easy way to test this out. Create a temporary table using the > view and then use that in the UPDATE query eg > > CREATE TEMPORARY Table Fred FROM SELECT Phone, PhnSeq, InvNum from > ClearedARTrans > > UPDATE ARLines > SET Keyoff = (Cast (InvNum as VarChar(10))) > WHERE ("Phone","PhnSeq","InvNum") IN > (SELECT * FROM Fred) > > > Roy Lambert [Team Elevate] > |
Mon, Jul 15 2013 5:07 PM | Permanent Link |
Ben Sprei CustomEDP | I did some further testing on this issue and here are my findings:
There are distinct views. The name of the derived table (InvoiceTotals) is the same in both views. After running several times I finally got an error as follows: ElevateDB Error #700 An error was found in the statement at line 4 and column 9 (ElevateDB Error #401 The table or view InvoiceTotals5 does not exist in the schema Default) Note the number it added to the derived table name. This is very interesting. I went ahead and changed the derived table name in the view and the error went away. I started executing the UPDATE SQL in the EDB Manager. It ran fine but it took 962 seconds to complete. This is unacceptable. Any suggestions ?? Perhaps Tim can get involved. The actual ARLines table has 74000 records. The view which opens in about two seconds winds up with ten records. Ben "Ben" <arrow1432@verizon.net> wrote in message news:2254557F-CC43-4EA5-9A15-E018609D2491@news.elevatesoft.com... > ClearedAR and Cleared AP are 2 distinct views. They just happen to have a > field named alike. Im NOT trying to cross from one to the other. The > only difference is that in AP the InvNum field is a VC and in AR InvNum is > an integer. I just happen to be doing a similar operation on either file. > When I do it in AP (without the Cast since the InvNum and KeyOff fields > are VC) there is no problem. When I do the same thing in AR with a CAST > statement its hanging. > > Ben > > "Roy Lambert" <roy@lybster.me.uk> wrote in message > news:30FA2C52-1BB5-416B-BE16-1DB80E9EADBD@news.elevatesoft.com... >> Ben >> >> >> I assume you're referring to this statement >> >> UPDATE ARLines >> SET Keyoff = (Cast (InvNum as VarChar(10))) >> WHERE ("Phone","PhnSeq","InvNum") IN >> (SELECT Phone, PhnSeq, InvNum from ClearedARTrans) >> >> [WARNING] I'm guessing here [/WARNING] >> >> If ClearedARTrans is the same or similar to ClearedAPTrans I think you >> may have generated an infinite loop. Tim will be able to say. >> >> Assuming for the moment that the two views are similar and that both >> reference Keyoff then each time you update Keyoff the view is rerun >> which causes the selection to be changed which causes the view to be >> rerun >> >> There's an easy way to test this out. Create a temporary table using the >> view and then use that in the UPDATE query eg >> >> CREATE TEMPORARY Table Fred FROM SELECT Phone, PhnSeq, InvNum from >> ClearedARTrans >> >> UPDATE ARLines >> SET Keyoff = (Cast (InvNum as VarChar(10))) >> WHERE ("Phone","PhnSeq","InvNum") IN >> (SELECT * FROM Fred) >> >> >> Roy Lambert [Team Elevate] >> > > |
Tue, Jul 16 2013 3:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
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 |