question

bobbo avatar image
bobbo asked

Select statement is too slow

Hi, My SELECT takes too long to execute. It takes about 8 sec and this is not ok. Does anybody know if and how I should rewrite the SELECT to get it faster? SELECT ID_ACNT_CT_CRD, PA_PRS.NM_PRS_OFCL AS NAME, CARD_INFO.EXT_SC_ACNT_CT_CRD, ADDR.EXT_A2_ADS, ADDR.EXT_PC_CNCT, ADDR.EXT_CI_CNCT, ID_ACNT_CT_CRD, min(CN.CN_INF_VALUE) AS CONTACT FROM PA_PRS JOIN PA_PRTY ON PA_PRS.ID_PRTY_PRS = PA_PRTY.ID_PRTY JOIN PA_RO_PRTY ON PA_PRTY.ID_PRTY = PA_RO_PRTY.ID_PRTY LEFT JOIN LO_PRTY_CN_INF CN ON PA_RO_PRTY.ID_PRTY = CN.ID_PRTY JOIN LO_PRTY_ADS ADDR ON PA_RO_PRTY.ID_PRTY = ADDR.ID_PRTY JOIN PA_CT ON PA_RO_PRTY.ID_PRTY = PA_CT.ID_PRTY JOIN LE_CTAC ON PA_CT.ID_CT = LE_CTAC.ID_CT JOIN EXT_ACNT_CT_CRD_CTAC_AFLN CARD_AFLN ON LE_CTAC.ID_CTAC = CARD_AFLN.ID_CTAC JOIN LE_ACNT_CT_CRD CARD_INFO ON CARD_AFLN.ID_ACNT_CT_CRD = CARD_INFO.AI_ACNT_CT_CRD WHERE LOWER(CARD_AFLN.ID_ACNT_CT_CRD) like '%%' AND PA_PRS.NM_PRS_OFCL like '%bygg%' AND LOWER(ADDR.EXT_A2_ADS)like '%%' AND LOWER(ADDR.EXT_PC_CNCT)like '%%' AND LOWER(ADDR.EXT_CI_CNCT)like '%%' AND (LOWER(CN.CN_INF_VALUE) like '%%' OR '%%'='%%') GROUP BY ID_ACNT_CT_CRD, PA_PRS.NM_PRS_OFCL,EXT_SC_ACNT_CT_CRD, EXT_A2_ADS limit 500
selectmysqljoin
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mjharper avatar image mjharper commented ·
should there be text within the LIKE clauses. At the moment you have LIKE '%%' - which will just match everything.
0 Likes 0 ·
Gazz avatar image Gazz commented ·
Sorry for reply to your question with a question, but what is this bit doing? (LOWER(CN.CN_INF_VALUE) like '%%' OR '%%'='%%')
0 Likes 0 ·
bobbo avatar image bobbo commented ·
Well, the reason is to match everything in the DB whit the word %%bygg%% And the answer to the question about the string (LOWER(CN.CN_INF_VALUE) like '%%' OR '%%'='%%') I really dont know!! Its a earlier developer from India who have write that bit :(
0 Likes 0 ·
Gazz avatar image Gazz commented ·
Another thing you can do is index the tables based on their joining fields. (Note indexing will result in the table taking up more space and when you INSERT or UPDATE data you may notice a delay as the indexes have to reorder)
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·

2 Answers

·
mjharper avatar image
mjharper answered
I would think the query below would give the same results. Hard to say what impact it will have on the performance. You'd probably need to provide the queryplan along with the query for people to help you with that. SELECT ID_ACNT_CT_CRD, PA_PRS.NM_PRS_OFCL AS NAME, CARD_INFO.EXT_SC_ACNT_CT_CRD, ADDR.EXT_A2_ADS, ADDR.EXT_PC_CNCT, ADDR.EXT_CI_CNCT, ID_ACNT_CT_CRD, min(CN.CN_INF_VALUE) AS CONTACT FROM PA_PRS JOIN PA_PRTY ON PA_PRS.ID_PRTY_PRS = PA_PRTY.ID_PRTY JOIN PA_RO_PRTY ON PA_PRTY.ID_PRTY = PA_RO_PRTY.ID_PRTY LEFT JOIN LO_PRTY_CN_INF CN ON PA_RO_PRTY.ID_PRTY = CN.ID_PRTY JOIN LO_PRTY_ADS ADDR ON PA_RO_PRTY.ID_PRTY = ADDR.ID_PRTY JOIN PA_CT ON PA_RO_PRTY.ID_PRTY = PA_CT.ID_PRTY JOIN LE_CTAC ON PA_CT.ID_CT = LE_CTAC.ID_CT JOIN EXT_ACNT_CT_CRD_CTAC_AFLN CARD_AFLN ON LE_CTAC.ID_CTAC = CARD_AFLN.ID_CTAC JOIN LE_ACNT_CT_CRD CARD_INFO ON CARD_AFLN.ID_ACNT_CT_CRD = CARD_INFO.AI_ACNT_CT_CRD WHERE PA_PRS.NM_PRS_OFCL LIKE '%bygg%' GROUP BY ID_ACNT_CT_CRD, PA_PRS.NM_PRS_OFCL, EXT_SC_ACNT_CT_CRD, EXT_A2_ADS LIMIT 500 ammended query to check for NOT NULL as suggested in comments... SELECT ID_ACNT_CT_CRD, PA_PRS.NM_PRS_OFCL AS NAME, CARD_INFO.EXT_SC_ACNT_CT_CRD, ADDR.EXT_A2_ADS, ADDR.EXT_PC_CNCT, ADDR.EXT_CI_CNCT, ID_ACNT_CT_CRD, min(CN.CN_INF_VALUE) AS CONTACT FROM PA_PRS JOIN PA_PRTY ON PA_PRS.ID_PRTY_PRS = PA_PRTY.ID_PRTY JOIN PA_RO_PRTY ON PA_PRTY.ID_PRTY = PA_RO_PRTY.ID_PRTY LEFT JOIN LO_PRTY_CN_INF CN ON PA_RO_PRTY.ID_PRTY = CN.ID_PRTY JOIN LO_PRTY_ADS ADDR ON PA_RO_PRTY.ID_PRTY = ADDR.ID_PRTY JOIN PA_CT ON PA_RO_PRTY.ID_PRTY = PA_CT.ID_PRTY JOIN LE_CTAC ON PA_CT.ID_CT = LE_CTAC.ID_CT JOIN EXT_ACNT_CT_CRD_CTAC_AFLN CARD_AFLN ON LE_CTAC.ID_CTAC = CARD_AFLN.ID_CTAC JOIN LE_ACNT_CT_CRD CARD_INFO ON CARD_AFLN.ID_ACNT_CT_CRD = CARD_INFO.AI_ACNT_CT_CRD WHERE CARD_AFLN.ID_ACNT_CT_CRD IS NOT NULL AND PA_PRS.NM_PRS_OFCL LIKE '%bygg%' AND ADDR.EXT_A2_ADS IS NOT NULL AND ADDR.EXT_PC_CNCT IS NOT NULL AND ADDR.EXT_CI_CNCT IS NOT NULL GROUP BY ID_ACNT_CT_CRD, PA_PRS.NM_PRS_OFCL, EXT_SC_ACNT_CT_CRD, EXT_A2_ADS LIMIT 500
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gazz avatar image Gazz commented ·
This will not ignore NULL values I *think* that: LOWER(ADDR.EXT_PC_CNCT)like '%%' is really: ADDR.EXT_PC_CNCT IS NOT NULL So where you have just ignored this you could get more data back then bobbo wants
1 Like 1 ·
mjharper avatar image mjharper commented ·
Good spot Gazz. I've added an ammended query to include the check for NOT NULL.
0 Likes 0 ·
bobbo avatar image bobbo commented ·
Thanks, but it is still going way to slow now 7,3 sec. U have any more ideas?
0 Likes 0 ·
bobbo avatar image bobbo commented ·
Whats a queryplan?
0 Likes 0 ·
mjharper avatar image mjharper bobbo commented ·
The queryplan tells you how SQL will execute your query. For example what indexes will get used etc. There's an introduction to them here: https://www.simple-talk.com/sql/performance/execution-plan-basics/ You can see it by pressing CTRL+L in SSMS and can save it from there.
1 Like 1 ·
KenJ avatar image KenJ commented ·
the question is tagged mysql. running the query prefixed with the EXPLAIN keyword (or EXPLAIN EXTENDED) will give you the mysql equivalent of a SQL Server query plan. Here is an EXPLAIN introductory article - http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/
0 Likes 0 ·
bobbo avatar image
bobbo answered
Is it also comment to use join multiple times as in this question? Is it maybe why it is slow?
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.