Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 5 of 5 total |
Update with join |
Tue, Dec 15 2020 2:45 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Any chance that we can get an UPDATE with JOIN for ElevateDB?
At the moment we can do updates like this: update table1 set column1='newvalue' where table1.idfield in (select idfield from table2 where x=x) and this is rather slow. We have changed code so we do a 1. query for subselect 2. loop in delphi for each row in subselect and update table1 Would be cool, if this can be done with ONE update command. -- -- Yusuf Zorlu | MicrotronX |
Tue, Dec 15 2020 8:32 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | Yusuf Zorlu
<< update table1 set column1='newvalue' where table1.idfield in (select idfield from table2 where x=x) >> There is an index available (first field) for table1.idfiled and another one to table2.x ? If not, try creating the indexes and see the results. Eduardo |
Wed, Dec 16 2020 1:07 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Jose Eduardo Helminsky wrote:
> Yusuf Zorlu > > << > update table1 set column1='newvalue' > where table1.idfield in (select idfield from table2 where x=x) > > > > > There is an index available (first field) for table1.idfiled and > another one to table2.x ? > > If not, try creating the indexes and see the results. > > Eduardo Hi Eduardo, all fields are indexed. The problem lies within the IN statement which slows down somehow. -- -- Yusuf Zorlu | MicrotronX |
Wed, Dec 16 2020 4:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Yusuf
My understanding (could be wrong) is that the IN test is being carried out for every row in the source table. Its possible that its the WHERE clause in the subselect that's preventing optimisation. If you can post a copy of the database with information and the actual query I'll be happy to see if I can find a faster method. Roy Lambert |
Thu, Dec 17 2020 2:52 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Roy Lambert wrote:
> My understanding (could be wrong) is that the IN test is being > carried out for every row in the source table. Its possible that its > the WHERE clause in the subselect that's preventing optimisation. > Hi Roy, i also think that the IN is beeing done for each row. I don't know if the optimizier can change the IN into a INNER-JOIN automatically? At the moment we have a solution which is good ... but best solution in my opinion would be, when the IN will automatically optimized into INNER JOIN. -- -- Yusuf Zorlu | MicrotronX |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |