| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| SQL causes MQSI to run slowly |
« View previous topic :: View next topic » |
| Author |
Message
|
| GaryGWood |
 Posted: Thu Mar 11, 2004 6:27 am Post subject: SQL causes MQSI to run slowly |
 |
|
Apprentice
Joined: 14 Oct 2003
Posts: 48
Location: Austin, TX
|
This was running very quickly then we put a new SQL in that causes it to process only 1 message every few seconds (previously was 100+). Any idea why this kills the flow?
| Code: |
SELECT L2.ROLES_ACCESSROLES_COMPREGID AS LOGIN_ID,
C2.ID AS COMP_ID,
L2.FIRSTNAME AS FIRST_NAME,
L2.LASTNAME AS LAST_NAME,
C2.PWSWSTATUS AS STATUS_CD,
L3.ADDRLINEONE AS ADDRESS_LINE1,
L3.ADDRLINETWO AS ADDRESS_LINE2,
L3.CITY AS CITY,
L3.STATE AS STATE,
L3.COUNTRY AS COUNTRY_CD,
L3.ZIP AS ZIP,
L2.EMAIL AS EMAIL_ADDR,
L2.PERSONPHONE_NUMBER AS PHONE,
L1.FAXONE_NUMBER AS FAX,
L2.RECEIVEEMAIL AS EMAIL_FLG,
L1.PROFILEUPDATETIMESTAMP AS LOCATION_UPDATED_DATE,
case when posstr(ucase(C3.companyname),''MyCompany'')>0 then ''Y'' else ''N'' end as comp_flg,
cast (C2.UPDATED_DATETIME AS CHAR(26)) AS UPDATED_DATETIME
FROM
VGNSTG.COMPANY_LOCATIONIDLIST C1,
VGNSTG.COMPANY_PWSW C2,
VGNSTG.COMPANY_COMPANY C3,
VGNSTG.LOCATION_LOCATION L1,
VGNSTG.LOCATION_PEOPLE L2,
VGNSTG.LOCATION_PERSONADDR L3
WHERE
L1.id=L2.id and
L2.id=L3.id and
L2.child_id=L3.parent_id and
L1.id=C1.locationid and
C1.id=C2.id AND
C1.id=C3.id AND
(C2.UPDATED_DATETIME >= ? AND C2.UPDATED_DATETIME < ? )
UNION
SELECT L2.ROLES_ACCESSROLES_COMPREGID AS LOGIN_ID,
C2.ID AS COMP_ID,
L2.FIRSTNAME AS FIRST_NAME,
L2.LASTNAME AS LAST_NAME,
C2.PWSWSTATUS AS STATUS_CD,
L1.contactaddress_ADDRLINEONE AS ADDRESS_LINE1,
L1.contactaddress_ADDRLINETWO AS ADDRESS_LINE2,
L1.contactaddress_CITY AS CITY,
L1.contactaddress_STATE AS STATE,
L1.contactaddress_COUNTRY AS COUNTRY_CD,
L1.contactaddress_ZIP AS ZIP,
L2.EMAIL AS EMAIL_ADDR,
L2.PERSONPHONE_NUMBER AS PHONE,
L1.FAXONE_NUMBER AS FAX,
L2.RECEIVEEMAIL AS EMAIL_FLG,
L1.PROFILEUPDATETIMESTAMP AS LOCATION_UPDATED_DATE,
case when posstr(ucase(C3.companyname),''MyCompany'')>0 then ''Y'' else ''N'' end as comp_flg,
cast (C2.UPDATED_DATETIME AS CHAR(26)) AS UPDATED_DATETIME
FROM
VGNSTG.COMPANY_LOCATIONIDLIST C1,
VGNSTG.COMPANY_PWSW C2,
VGNSTG.COMPANY_COMPANY C3,
VGNSTG.LOCATION_LOCATION L1,
VGNSTG.LOCATION_PEOPLE L2
WHERE
L1.id=L2.id and
L1.id=C1.locationid and
C1.id=C2.id AND
C1.id=C3.id AND
NOT EXISTS (SELECT ID
FROM VGNSTG.LOCATION_PERSONADDR
WHERE ID=L2.ID AND L2.CHILD_ID=PARENT_ID) AND
(C2.UPDATED_DATETIME >= ? AND C2.UPDATED_DATETIME < ? ) '
, InputBody.Message.application.runtime,InputBody.Message.application.endtime
, InputBody.Message.application.runtime,InputBody.Message.application.endtime);
|
The primary difference is the union - wasn't there before. This does run, just very, very slowly. Thanks for any help! |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Thu Mar 11, 2004 6:54 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Ask your DBA for help optimizing your query...
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| GaryGWood |
| Posted: Thu Mar 11, 2004 8:34 am Post subject: |
|
|
Apprentice
Joined: 14 Oct 2003
Posts: 48
Location: Austin, TX
|
Thanks Jeff ...
The thing about this is that when the query is run from the command line it's very quick - returns the result set of just over 50,000 records in only a few seconds. Does MQSI get that result set back and then parse it out (we have it create one message for each record returned)? Or, does MQSI take one record back at a time from the result set and then process it, going back to DB2 and the result set for the next record? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Mar 11, 2004 10:05 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I don't know how WMQI handles it internally. However, I'm pretty sure that the way you've written it, that WMQI is handling the UNION operation itself, rather than have the DB do the UNION.
It might give you better performance to use PASSTHRU.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| scaryjase |
| Posted: Fri Mar 12, 2004 2:05 am Post subject: |
|
|
Novice
Joined: 17 Jul 2003
Posts: 22
|
was that SQL designed by someone with a grudge?? one thing worth bearing in mind is that if the database is returning 50,000 rows in no time at all, there could welll be some caching involved, which will skew the results somewhat.
i think you might have to ask yourself if you REALLY want to run this SQL...
_________________
scary |
|
| Back to top |
|
|
| GaryGWood |
| Posted: Fri Mar 12, 2004 10:00 am Post subject: |
|
|
Apprentice
Joined: 14 Oct 2003
Posts: 48
Location: Austin, TX
|
| scaryjase wrote: |
was that SQL designed by someone with a grudge?? one thing worth bearing in mind is that if the database is returning 50,000 rows in no time at all, there could welll be some caching involved, which will skew the results somewhat.
i think you might have to ask yourself if you REALLY want to run this SQL... |
To be honest, I'm not sure who is ultimately responsible for the SQL design - I just drop in onto the server. What's wrong with it?
I will keep the caching in mind, thanks - that may become important. And no, if I had a choice I would not run it and this would all go away tomorrow...  |
|
| Back to top |
|
|
| mgk |
| Posted: Sun Mar 14, 2004 6:14 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
From the broker point of view, this must already be sent via PASSTHRU, as we do not support UNION selects in ESQL.
How many records are read from the resultset depends on the version of the broker you are running. < 5.0 = 1 row per read from the result-set. After 5.0 = 32 rows per read from result-set.
I can only assume this is slowing down so much, because there is a lot more rows to comming back. Do you believe this to be the case?
Cheers
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
