MAX_QUERY_TIME - Command Timeout

[Former Member]

Is there way to set a timeout for long running SQL? MAX_QUERY_TIME doesn't work for INSERT/UPDATE/DELETE statements.

[c_baker]

What exactly is the issue?

Are you doing row-by-row operations?

Are you issuing a 'commit' to allow the transaction to complete?

Chris

[Former Member]

I am not doing row-by-row operation. I have few very complex DML statements takes longer to complete. Just looking for a quick way to kill it if it runs over X number of minutes.

We tried everything to optimize them but the volume of data is so big that it will take longer to complete.

[c_baker]

There is not setting.

Have you looked at the server log?  What comprises 'complex' DML statements?  Are you using cursors?

IQ performs best with set-based SQL and bulk operations.  Your complex DML could still be turning into row-based operations, in which case you could be building up versions in IQ_MAIN and using IQ_SYSTEM_TEMP.

Check your .iqmsg file for any out of space errors.

Chris

[Former Member]

There are no errors and we are not using cursor. The SQL is joining few large tables with billion+ rows.

For example,

INSERT INTO tmpTable1 (ID)

SELECT DISTINCT a.ID

   FROM MainTable A

INNER JOIN Child1 B

ON A.ID = B.CustomerID

INNER JOIN Child2 C

ON A.ID = C.CustomerID

WHERE a.Gender = 'FEMALE'

AND

(

(b.Age = 26 OR b.Age = 27)

OR

(c.Age = 26 OR c.Age = 27)

)

MainTable has just 1 million rows but Child1 and Child2 as nearly 200 million rows.

[c_baker]

That is not a lot of rows for IQ.  What version are you running?

Can you post the HTML query plan?

Is the ID column in B and C a unique constraint or unique identifier (primary key or HG-U index)?

Have you added an HG index to A.Gender and b.Age and c.Age?

If you have not properly identified unique constraints, the optimizer may be trying to resolve a cartesian join.  The query plan will show this.

You can also try placing the OR conditions in the join statement:

insert into tmpTable1 (ID)

select distinct A.ID from MainTable A

inner join Child1 B

on A.ID = B.CustomerID and B.Age in (26,27)

inner join Child2 C

on A.ID = C.CustomerID and C.Age in (26,27)

Also, what are your .cfg settings?  Specifically -iqmc and -iqtc.

Chris

[Former Member]

We are running IQ 15.4

B.CustomerID and c.CustomerID has Unique HG index.

Age has LF index. I can try adding HG index and see if it makes any difference.

-iqmc = 70000

-iqtc = 100000

I will post HTML Query plan shortly.

[c_baker]

LF is fine for 15.4.  What about Gender?

What service pack of 15.4?  The OR processing might be part of the issue, depending on the release, but we should be able to see from your QP.

What is the configuration of your DBSPACE dbfiles?  Are they on the same device?  How many cores are you running?

Chris

[Former Member]

Gender has LF index too.

Here is the version:

Sybase IQ/15.4.0.3019/120816/P/ESD 2/MS/Windows 2003/64bit/2012-08-16 10:58:36

Server is running Windows 2008 64bit with one socket and 4 cores, 196 GB or RAM. This server is part of a 3 node multiplex. We don't use distributed queries on the multiplex.

DBSPACE files are on a different LUNs.

[c_baker]

I assume you have more than 1 dbfile per dbspace for MAIN and TEMP.

Post your QP, but to be honest, your version of IQ is almost 4 years old.  The current patch is ESD 7.

There are many fixes in the ensuing ESDs that might address your issue.  I would suggest upgrading to the latest ESD 7.

Chris

[Former Member]

I will take a look at release notes. Where can I find release notes for ESD 3 through 7? All my search comes up with ESD # 5 only.

[c_baker]

You should be able to find it on the support.sap.com/swdc site under 'Support Packages and Patches' in the 'I' section under SAP IQ 'Sybase IQ->Sybase IQ 15.4'

The filename is IQSERV154007_0-20011039.ZIP

The EBF number is 25635.

Chris