Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 20 total
Thread SQL never completes
Thu, Nov 4 2010 3:29 AMPermanent 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 AMPermanent 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 AMPermanent 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent 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 AMPermanent 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 Smiley ?

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 PMPermanent 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 AMPermanent 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 2Next Page »
Jump to Page:  1 2
Image