Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 20 total |
SQL never completes |
Thu, Nov 4 2010 3:29 AM | Permanent Link |
Xazzy | Hi
DBISAM 3.30, Delphi 2007 SomeTable.dat: 200K rows History.dat: 5 Million rows When using the following SQL on a large test database it never completes (strictly speaking i give up waiting after a few hours): UPDATE "SomeTable.dat" M2 set Status=6 WHERE (M2.Status=5) and (UPPER(M2.Email) IN (SELECT UPPER(Email) FROM "History.dat" H2 WHERE (H2.HistoryCode IN (1999,2000,2001,2002,2003,2007,2008,2009)) AND (H2.Date>="2010-08-27 02:40:50 pm") and (H2.Date<="2010-09-08 04:09:44 pm"))) However if i break up the parts it is is reasonably quick: UPDATE "SomeTable.dat" M2 set Status=6 WHERE (M2.Status=5) and (UPPER(M2.Email) IN ('TEST@TEST.COM') Less than 1 second SELECT UPPER(Email) FROM "SomeTable.dat" H2 WHERE (H2.HistoryCode IN (1999,2000,2001,2002,2003,2007,2008,2009)) AND (H2.Date>="2010-08-27 02:40:50 pm") and (H2.Date<="2010-09-08 04:09:44 pm") 5 seconds So what is the correct way to perform this kind of query? Thanks |
Thu, Nov 4 2010 3:35 AM | Permanent Link |
Xazzy | More Info:
UPDATE "SomeTable.dat" M2 set Status=6 WHERE (M2.Status=5) and (UPPER(M2.Email) IN ('TEST@TEST.COM') Less than 1 second, updating 1 record SELECT UPPER(Email) FROM "SomeTable.dat" H2 WHERE (H2.HistoryCode IN (1999,2000,2001,2002,2003,2007,2008,2009)) AND (H2.Date>="2010-08-27 02:40:50 pm") and (H2.Date<="2010-09-08 04:09:44 pm") 5 seconds, returning 15,000 records |
Thu, Nov 4 2010 3:46 AM | Permanent Link |
Xazzy | Also failing to complete (with progress never progressing past zero):
UPDATE "SomeTable.dat" M2 set Status=6 FROM "SomeTable.dat" LEFT OUTER JOIN "History.dat" H2 ON (M2.EMAIL=H2.EMAIL) WHERE (M2.Status=5) and (H2.HistoryCode IN (1999,2000,2001,2002,2003,2007,2008,2009)) AND (H2.Date>="2010-08-27 02:40:50 pm") and (H2.Date<="2010-09-08 04:09:44 pm") |
Thu, Nov 4 2010 4:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << When using the following SQL on a large test database it never completes
(strictly speaking i give up waiting after a few hours): UPDATE "SomeTable.dat" M2 set Status=6 WHERE (M2.Status=5) and (UPPER(M2.Email) IN (SELECT UPPER(Email) FROM "History.dat" H2 WHERE (H2.HistoryCode IN (1999,2000,2001,2002,2003,2007,2008,2009)) AND (H2.Date>="2010-08-27 02:40:50 pm") and (H2.Date<="2010-09-08 04:09:44 pm"))) >> Most issues like this are due to not having the necessary indexes present. Do you have a case-insensitive index on the Email field in the History table ? -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Nov 4 2010 4:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << Also failing to complete (with progress never progressing past zero): >>
Probably the same issue - lack of index on the Email field for the History table, although in this case you'll want a case-sensitive index (no UPPER() around the joined fields). -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Nov 4 2010 4:43 PM | Permanent Link |
Xazzy | Hi Tim
Sorry i should have mentioned that... History.dat Indexes: HistoryCode Email (Case-Ins) SomeTable.dat Indexes: Status Email (Case-Ins, Unique) Also tried the Join SQL using: ON (UPPER(M2.EMAIL)=UPPER(H2.EMAIL)) With same result. |
Fri, Nov 5 2010 6:23 AM | Permanent Link |
John Hay | Xazzy
> History.dat Indexes: > HistoryCode > Email (Case-Ins) > > SomeTable.dat Indexes: > Status > Email (Case-Ins, Unique) Are these all the indexes? Was the following meant to be FROM "History.dat" ? SELECT UPPER(Email) FROM "SomeTable.dat" H2 WHERE (H2.HistoryCode IN (1999,2000,2001,2002,2003,2007,2008,2009)) AND (H2.Date>="2010-08-27 02:40:50 pm") and (H2.Date<="2010-09-08 04:09:44 pm") Does the following script return in a reasonable time (or at all ? DROP TABLE IF EXISTS "Memory\Temp"; SELECT UPPER(Email) INTO Memory\Temp FROM "History.dat" H2 WHERE (H2.HistoryCode IN (1999,2000,2001,2002,2003,2007,2008,2009)) AND (H2.Date>="2010-08-27 02:40:50 pm") and (H2.Date<="2010-09-08 04:09:44 pm"))); CREATE NOCASE INDEX "Email" ON "Memory\temp" ("Email"); SELECT Status FROM "SomeTable.dat" WHERE Status=5 and UPPER(Email) in (SELECT UPPER(Email) FROM Memory\Temp) If it does can you let us know how many records do you get, and of course you can change the last line of the script to the following to do the actual update? UPDATE "SomeTable.Dat" SET Status=6 WHERE Status=5 and UPPER(Email) in (SELECT UPPER(Email) FROM Memory\Temp) John |
Fri, Nov 5 2010 8:17 PM | Permanent Link |
Xazzy | Hi John
> Was the following meant to be FROM "History.dat" ? Yes, sorry. This part works and returns 15,000 records: DROP TABLE IF EXISTS "Memory"; SELECT Email INTO "Memory" FROM "History.dat" H2 WHERE (H2.HistoryCode IN (1999,2000,2001,2002,2003,2007,2008,2009)) AND (H2.Date>="2010-08-27 02:40:50 pm") and (H2.Date<="2010-09-08 04:09:44 pm"); CREATE NOCASE INDEX "ByEmail" ON "Memory" ("Email"); This part shoots up to 100% progress (in DBUTILS) but then never returns: SELECT Status FROM "SomeTable.dat" WHERE Status=5 and UPPER(Email) in (SELECT UPPER(Email) FROM "Memory") Thanks |
Mon, Nov 8 2010 5:36 AM | Permanent Link |
John Hay | Xazzy
> > This part shoots up to 100% progress (in DBUTILS) but then never returns: > > SELECT Status FROM "SomeTable.dat" WHERE Status=5 and UPPER(Email) in > (SELECT UPPER(Email) FROM "Memory") > Strange with only 200,000 records being tested against an indexed table with 15,000 records. Can you post empty tables or a script to create the tables? John |
Wed, Nov 10 2010 5:40 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << This part shoots up to 100% progress (in DBUTILS) but then never returns: >> As John indicated, if you can post the tables in the DBISAM Binaries newsgroup/forum, or email them to me (support@elevatesoft.com), I can look at this further and tell you what is going on. -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |