Новичок
Профиль
Группа: Участник
Сообщений: 7
Регистрация: 30.11.2009
Репутация: нет
Всего: нет
|
Доброго времени суток! Есть запрос, который ниже (база данных PostgreSQL). Время его выполнения критически большое: около 53 секунд. Запрос генерируется Hibernate Criteria. Буду очень признателен, если выскажите хоть какие-нибудь идеи по оптимизации данного запроса. Заранее благодарю. | Код |
SELECT
this_.location_id AS location1_165_26_,
this_.STATUS AS STATUS165_26_,
this_.ONLINE_STATUS AS ONLINE3_165_26_,
this_.HOSTING_TYPE AS HOSTING4_165_26_,
this_.TITLE AS TITLE165_26_,
this_.DESCRIPTION AS DESCRIPT6_165_26_,
this_.RESTRICTIONS AS RESTRICT7_165_26_,
this_.ARCHITECT AS ARCHITECT165_26_,
this_.COPYRIGHTS AS COPYRIGHTS165_26_,
this_.CREATION_DATE AS CREATION10_165_26_,
this_.BLOCK_TILL_DATE AS BLOCK11_165_26_,
this_.DATE_STATUS_CHANGE AS DATE12_165_26_,
this_.TIMEZONE AS TIMEZONE165_26_,
this_.LOCATION_CODE AS LOCATION14_165_26_,
this_.ACCOUNT_ID AS ACCOUNT15_165_26_,
this_.REPRESENTATIVE_ID AS REPRESE16_165_26_,
this_.ADDRESS_ID AS ADDRESS17_165_26_,
this_.GPS_ID AS GPS18_165_26_,
(SELECT COUNT(lr.location_request_id)>0
FROM location_request lr
WHERE lr.location_id = this_.location_id
AND lr.status IN (5, 6, 7, 9)
) AS formula7_26_,
(SELECT COUNT(lr.location_request_id) = 0
FROM location_request lr
WHERE lr.location_id = this_.location_id
AND lr.status > 2
) AS formula8_26_,
accountdto2_.account_id AS account1_135_0_,
accountdto2_.CONFIRMATION_STATUS AS CONFIRMA2_135_0_,
accountdto2_.LOGIN AS LOGIN135_0_,
accountdto2_.PASSWORD AS PASSWORD135_0_,
accountdto2_.ANSWER AS ANSWER135_0_,
accountdto2_.MAIL_LOCALE AS MAIL6_135_0_,
accountdto2_.DATE_FORMAT AS DATE7_135_0_,
accountdto2_.DISTANCE_FORMAT AS DISTANCE8_135_0_,
accountdto2_.TIME_FORMAT AS TIME9_135_0_,
accountdto2_.TRAVEL_TIME_TYPE AS TRAVEL10_135_0_,
accountdto2_.GPS_FORMAT AS GPS11_135_0_,
accountdto2_.NUMBER_OF_LICENSES AS NUMBER12_135_0_,
accountdto2_.REGISTRATION_PERIOD AS REGISTR13_135_0_,
accountdto2_.REGISTRATION_CHANGED AS REGISTR14_135_0_,
accountdto2_.REGISTRATION_EXPIRATION AS REGISTR15_135_0_,
accountdto2_.CREATION_DATE AS CREATION16_135_0_,
accountdto2_.AUTO_PROLONG AS AUTO17_135_0_,
accountdto2_.KEEP_ME_UPDATED AS KEEP18_135_0_,
accountdto2_.FREE_REQUESTS_AVAILABLE AS FREE19_135_0_,
accountdto2_.LAST_ACCOUNT_INVOICE_ID AS LAST20_135_0_,
accountdto2_.PENDING_ACCOUNT_INVOICE_ID AS PENDING21_135_0_,
accountdto2_.CURRENCY_ID AS CURRENCY22_135_0_,
accountdto2_.ACCOUNT_TYPE_ID AS ACCOUNT23_135_0_,
accountdto2_.QUESTION_ID AS QUESTION24_135_0_,
accountdto2_.CREDIT_CARD_ID AS CREDIT25_135_0_,
accountdto2_.PERSON_INFORMATION_ID AS PERSON26_135_0_,
accountfee3_.INVOICE_ID AS INVOICE1_180_1_,
accountfee3_.INVOICE_NUMBER AS INVOICE3_180_1_,
accountfee3_.FEE_TYPE_ID AS FEE2_180_1_,
accountfee3_.INVOICE_TYPE AS INVOICE4_180_1_,
accountfee3_.STATUS AS STATUS180_1_,
accountfee3_.MEMO AS MEMO180_1_,
accountfee3_.DATA_FILENAME AS DATA7_180_1_,
accountfee3_.TEMPLATE_FILENAME AS TEMPLATE8_180_1_,
accountfee3_.CREATION_DATE AS CREATION9_180_1_,
accountfee3_.CONFIRMATION_DATE AS CONFIRM10_180_1_,
accountfee3_.ISSUE_DATE AS ISSUE11_180_1_,
accountfee3_.PAYMENT_ID AS PAYMENT12_180_1_,
accountfee3_.CREDIT_CARD_ID AS CREDIT13_180_1_,
accountfee3_.ACCOUNT_ID AS ACCOUNT14_180_1_,
accountfee3_.LOCATION_REQUEST_ID AS LOCATION15_180_1_,
feetypedto4_.FEE_TYPE_ID AS FEE1_175_2_,
feetypedto4_.FEE_TYPE_NAME AS FEE2_175_2_,
paymentdto5_.PAYMENT_ID AS PAYMENT1_173_3_,
paymentdto5_.VAT AS VAT173_3_,
paymentdto5_.AMOUNT AS AMOUNT173_3_,
paymentdto5_.DISCOUNT AS DISCOUNT173_3_,
paymentdto5_.TAX_GROUP_ID AS TAX5_173_3_,
paymentdto5_.CURRENCY_ID AS CURRENCY6_173_3_,
creditcard6_.CREDIT_CARD_ID AS CREDIT1_160_4_,
creditcard6_.GUWID AS GUWID160_4_,
creditcard6_.AUTH_CODE AS AUTH3_160_4_,
creditcard6_.CC_NUMBER AS CC4_160_4_,
creditcard6_.STATE AS STATE160_4_,
creditcard6_.BSIG AS BSIG160_4_,
creditcard6_.PARENT_ID AS PARENT7_160_4_,
accountdto7_.account_id AS account1_135_5_,
accountdto7_.CONFIRMATION_STATUS AS CONFIRMA2_135_5_,
accountdto7_.LOGIN AS LOGIN135_5_,
accountdto7_.PASSWORD AS PASSWORD135_5_,
accountdto7_.ANSWER AS ANSWER135_5_,
accountdto7_.MAIL_LOCALE AS MAIL6_135_5_,
accountdto7_.DATE_FORMAT AS DATE7_135_5_,
accountdto7_.DISTANCE_FORMAT AS DISTANCE8_135_5_,
accountdto7_.TIME_FORMAT AS TIME9_135_5_,
accountdto7_.TRAVEL_TIME_TYPE AS TRAVEL10_135_5_,
accountdto7_.GPS_FORMAT AS GPS11_135_5_,
accountdto7_.NUMBER_OF_LICENSES AS NUMBER12_135_5_,
accountdto7_.REGISTRATION_PERIOD AS REGISTR13_135_5_,
accountdto7_.REGISTRATION_CHANGED AS REGISTR14_135_5_,
accountdto7_.REGISTRATION_EXPIRATION AS REGISTR15_135_5_,
accountdto7_.CREATION_DATE AS CREATION16_135_5_,
accountdto7_.AUTO_PROLONG AS AUTO17_135_5_,
accountdto7_.KEEP_ME_UPDATED AS KEEP18_135_5_,
accountdto7_.FREE_REQUESTS_AVAILABLE AS FREE19_135_5_,
accountdto7_.LAST_ACCOUNT_INVOICE_ID AS LAST20_135_5_,
accountdto7_.PENDING_ACCOUNT_INVOICE_ID AS PENDING21_135_5_,
accountdto7_.CURRENCY_ID AS CURRENCY22_135_5_,
accountdto7_.ACCOUNT_TYPE_ID AS ACCOUNT23_135_5_,
accountdto7_.QUESTION_ID AS QUESTION24_135_5_,
accountdto7_.CREDIT_CARD_ID AS CREDIT25_135_5_,
accountdto7_.PERSON_INFORMATION_ID AS PERSON26_135_5_,
locationre8_.LOCATION_REQUEST_ID AS LOCATION1_181_6_,
locationre8_.OPTION AS OPTION181_6_,
locationre8_.STATUS AS STATUS181_6_,
locationre8_.TEAM AS TEAM181_6_,
locationre8_.DATE_FROM AS DATE5_181_6_,
locationre8_.DATE_TO AS DATE6_181_6_,
locationre8_.REMINDER_DATE AS REMINDER7_181_6_,
locationre8_.NOTIFICATION_DATE AS NOTIFICA8_181_6_,
locationre8_.ISREMINDERAFTER2WEEK AS ISREMIND9_181_6_,
locationre8_.COMMENTS AS COMMENTS181_6_,
locationre8_.CREATION_DATE AS CREATION11_181_6_,
locationre8_.STATE_CHANGE_DATE AS STATE12_181_6_,
locationre8_.FREE AS FREE181_6_,
locationre8_.BOOKING_DAY_RATE_ID AS BOOKING14_181_6_,
locationre8_.LOCATION_ID AS LOCATION15_181_6_,
locationre8_.ACCOUNT_ID AS ACCOUNT16_181_6_,
accountfee9_.INVOICE_ID AS INVOICE1_180_7_,
accountfee9_.INVOICE_NUMBER AS INVOICE3_180_7_,
accountfee9_.FEE_TYPE_ID AS FEE2_180_7_,
accountfee9_.INVOICE_TYPE AS INVOICE4_180_7_,
accountfee9_.STATUS AS STATUS180_7_,
accountfee9_.MEMO AS MEMO180_7_,
accountfee9_.DATA_FILENAME AS DATA7_180_7_,
accountfee9_.TEMPLATE_FILENAME AS TEMPLATE8_180_7_,
accountfee9_.CREATION_DATE AS CREATION9_180_7_,
accountfee9_.CONFIRMATION_DATE AS CONFIRM10_180_7_,
accountfee9_.ISSUE_DATE AS ISSUE11_180_7_,
accountfee9_.PAYMENT_ID AS PAYMENT12_180_7_,
accountfee9_.CREDIT_CARD_ID AS CREDIT13_180_7_,
accountfee9_.ACCOUNT_ID AS ACCOUNT14_180_7_,
accountfee9_.LOCATION_REQUEST_ID AS LOCATION15_180_7_,
currencydt10_.CURRENCY_ID AS CURRENCY1_149_8_,
currencydt10_.FULL_NAME AS FULL2_149_8_,
currencydt10_.SHORT_NAME AS SHORT3_149_8_,
currencydt10_.SYMBOL AS SYMBOL149_8_,
currencydt10_.CODE AS CODE149_8_,
accounttyp11_.ACCOUNT_TYPE_ID AS ACCOUNT1_138_9_,
accounttyp11_.ACCOUNT_TYPE_NAME AS ACCOUNT2_138_9_,
accounttyp11_.ACCOUNT_TYPE_NUMBER AS ACCOUNT3_138_9_,
accounttyp11_.DISK_SIZE AS DISK4_138_9_,
accounttyp11_.LOCATIONS_AVAILABLE AS LOCATIONS5_138_9_,
accounttyp11_.INFORMATION AS INFORMAT6_138_9_,
accounttyp11_.INTERNAL AS INTERNAL138_9_,
accounttyp11_.FREE AS FREE138_9_,
accounttyp11_.DEFAULT_TYPE AS DEFAULT9_138_9_,
questiondt12_.QUESTION_ID AS QUESTION1_139_10_,
questiondt12_.SECURITY_QUESTION AS SECURITY2_139_10_,
creditcard13_.CREDIT_CARD_ID AS CREDIT1_160_11_,
creditcard13_.GUWID AS GUWID160_11_,
creditcard13_.AUTH_CODE AS AUTH3_160_11_,
creditcard13_.CC_NUMBER AS CC4_160_11_,
creditcard13_.STATE AS STATE160_11_,
creditcard13_.BSIG AS BSIG160_11_,
creditcard13_.PARENT_ID AS PARENT7_160_11_,
creditcard14_.CREDIT_CARD_ID AS CREDIT1_160_12_,
creditcard14_.GUWID AS GUWID160_12_,
creditcard14_.AUTH_CODE AS AUTH3_160_12_,
creditcard14_.CC_NUMBER AS CC4_160_12_,
creditcard14_.STATE AS STATE160_12_,
creditcard14_.BSIG AS BSIG160_12_,
creditcard14_.PARENT_ID AS PARENT7_160_12_,
personinfo15_.PERSON_INFORMATION_ID AS PERSON1_153_13_,
personinfo15_.BUSINESS_TYPE_ID AS BUSINESS2_153_13_,
personinfo15_.COMPANY_NAME AS COMPANY3_153_13_,
personinfo15_.E_MAIL AS E4_153_13_,
personinfo15_.FAX AS FAX153_13_,
personinfo15_.FIRST_NAME AS FIRST6_153_13_,
personinfo15_.LAST_NAME AS LAST7_153_13_,
personinfo15_.MOBILE AS MOBILE153_13_,
personinfo15_.PHONE AS PHONE153_13_,
personinfo15_.PREFIX AS PREFIX153_13_,
personinfo15_.WEB_SITE AS WEB11_153_13_,
personinfo15_.PAYMENT_INFO AS PAYMENT12_153_13_,
personinfo15_.ADDRESS_ID AS ADDRESS13_153_13_,
personinfo15_.VATIN_ID AS VATIN14_153_13_,
businessty16_.BUSINESS_TYPE_ID AS BUSINESS1_148_14_,
businessty16_.BUSINESS_TYPE AS BUSINESS2_148_14_,
businessty16_.BUSINESS AS BUSINESS148_14_,
(SELECT businessty16_.business_type LIKE 'businesstype.production.service'
) AS formula6_14_,
addressdto17_.ADDRESS_ID AS ADDRESS1_154_15_,
addressdto17_.STREET AS STREET154_15_,
addressdto17_.REGION AS REGION154_15_,
addressdto17_.CITY AS CITY154_15_,
addressdto17_.CITY_KEYWORD_ID AS CITY5_154_15_,
addressdto17_.ZIP AS ZIP154_15_,
addressdto17_.COUNTRY_ID AS COUNTRY7_154_15_,
addressdto17_.STATE_ID AS STATE8_154_15_,
vatindto18_.VATIN_ID AS VATIN1_141_16_,
vatindto18_.CODE AS CODE141_16_,
vatindto18_.COUNTRY_ID AS COUNTRY3_141_16_,
representa19_.REPRESENTATIVE_ID AS REPRESEN1_161_17_,
representa19_.PERSON_INFORMATION_ID AS PERSON2_161_17_,
personinfo20_.PERSON_INFORMATION_ID AS PERSON1_153_18_,
personinfo20_.BUSINESS_TYPE_ID AS BUSINESS2_153_18_,
personinfo20_.COMPANY_NAME AS COMPANY3_153_18_,
personinfo20_.E_MAIL AS E4_153_18_,
personinfo20_.FAX AS FAX153_18_,
personinfo20_.FIRST_NAME AS FIRST6_153_18_,
personinfo20_.LAST_NAME AS LAST7_153_18_,
personinfo20_.MOBILE AS MOBILE153_18_,
personinfo20_.PHONE AS PHONE153_18_,
personinfo20_.PREFIX AS PREFIX153_18_,
personinfo20_.WEB_SITE AS WEB11_153_18_,
personinfo20_.PAYMENT_INFO AS PAYMENT12_153_18_,
personinfo20_.ADDRESS_ID AS ADDRESS13_153_18_,
personinfo20_.VATIN_ID AS VATIN14_153_18_,
addressdto21_.ADDRESS_ID AS ADDRESS1_154_19_,
addressdto21_.STREET AS STREET154_19_,
addressdto21_.REGION AS REGION154_19_,
addressdto21_.CITY AS CITY154_19_,
addressdto21_.CITY_KEYWORD_ID AS CITY5_154_19_,
addressdto21_.ZIP AS ZIP154_19_,
addressdto21_.COUNTRY_ID AS COUNTRY7_154_19_,
addressdto21_.STATE_ID AS STATE8_154_19_,
keyworddto22_.KEYWORD_ID AS KEYWORD1_190_20_,
keyworddto22_.KEYWORD_NAME AS KEYWORD2_190_20_,
keyworddto22_.FIXED AS FIXED190_20_,
keyworddto22_.URL AS URL190_20_,
keyworddto22_.PUBLIC_ACCESS AS PUBLIC5_190_20_,
(SELECT COUNT(loc.location_id)
FROM location loc
WHERE loc.online_status='true'
AND loc.status =4
AND loc.location_id IN
(SELECT ltk.location_id
FROM location_to_keyword ltk
WHERE ltk.keyword_id=keyworddto22_.keyword_id
)
) AS formula10_20_,
(SELECT COUNT (ltk.location_id)
FROM location_to_keyword ltk
WHERE ltk.keyword_id = keyworddto22_.keyword_id
) AS formula11_20_,
countrydto23_.COUNTRY_ID AS COUNTRY1_143_21_,
countrydto23_.COUNTRY_NAME AS COUNTRY2_143_21_,
countrydto23_.COUNTRY_CODE AS COUNTRY3_143_21_,
countrydto23_.COUNTRY_GROUP_ID AS COUNTRY4_143_21_,
countrydto23_.VATIN_PREFIX AS VATIN5_143_21_,
countrydto23_.STATE_REQUIRED AS STATE6_143_21_,
countrydto23_.country_keyword_id AS country7_143_21_,
countrygro24_.COUNTRY_GROUP_ID AS COUNTRY1_142_22_,
countrygro24_.COUNTRY_GROUP_NAME AS COUNTRY2_142_22_,
keyworddto25_.KEYWORD_ID AS KEYWORD1_190_23_,
keyworddto25_.KEYWORD_NAME AS KEYWORD2_190_23_,
keyworddto25_.FIXED AS FIXED190_23_,
keyworddto25_.URL AS URL190_23_,
keyworddto25_.PUBLIC_ACCESS AS PUBLIC5_190_23_,
(SELECT COUNT(loc.location_id)
FROM location loc
WHERE loc.online_status='true'
AND loc.status =4
AND loc.location_id IN
(SELECT ltk.location_id
FROM location_to_keyword ltk
WHERE ltk.keyword_id=keyworddto25_.keyword_id
)
) AS formula10_23_,
(SELECT COUNT (ltk.location_id)
FROM location_to_keyword ltk
WHERE ltk.keyword_id = keyworddto25_.keyword_id
) AS formula11_23_,
statedto26_.STATE_ID AS STATE1_140_24_,
statedto26_.STATE_NAME AS STATE2_140_24_,
statedto26_.STATE_CODE AS STATE3_140_24_,
gpsdto27_.GPS_ID AS GPS1_164_25_,
gpsdto27_.EAST_WEST AS EAST2_164_25_,
gpsdto27_.NORTH_SOUTH AS NORTH3_164_25_,
gpsdto27_.DEGREES_LATIDUDE AS DEGREES4_164_25_,
gpsdto27_.MINUTES_LATIDUDE AS MINUTES5_164_25_,
gpsdto27_.SECONDS_LATIDUDE AS SECONDS6_164_25_,
gpsdto27_.DEGREES_LONGITUDE AS DEGREES7_164_25_,
gpsdto27_.MINUTES_LONGITUDE AS MINUTES8_164_25_,
gpsdto27_.SECONDS_LONGITUDE AS SECONDS9_164_25_
FROM user8.LOCATION this_
INNER JOIN user8.ACCOUNT accountdto2_
ON this_.ACCOUNT_ID=accountdto2_.account_id
LEFT OUTER JOIN user8.INVOICE accountfee3_
ON accountdto2_.LAST_ACCOUNT_INVOICE_ID=accountfee3_.INVOICE_ID
LEFT OUTER JOIN user8.FEE_TYPE feetypedto4_
ON accountfee3_.FEE_TYPE_ID=feetypedto4_.FEE_TYPE_ID
LEFT OUTER JOIN user8.PAYMENT paymentdto5_
ON accountfee3_.PAYMENT_ID=paymentdto5_.PAYMENT_ID
LEFT OUTER JOIN user8.CREDIT_CARD creditcard6_
ON accountfee3_.CREDIT_CARD_ID=creditcard6_.CREDIT_CARD_ID
LEFT OUTER JOIN user8.ACCOUNT accountdto7_
ON accountfee3_.ACCOUNT_ID=accountdto7_.account_id
LEFT OUTER JOIN user8.LOCATION_REQUEST locationre8_
ON accountfee3_.LOCATION_REQUEST_ID=locationre8_.LOCATION_REQUEST_ID
LEFT OUTER JOIN user8.INVOICE accountfee9_
ON accountdto2_.PENDING_ACCOUNT_INVOICE_ID=accountfee9_.INVOICE_ID
LEFT OUTER JOIN user8.CURRENCY currencydt10_
ON accountdto2_.CURRENCY_ID=currencydt10_.CURRENCY_ID
LEFT OUTER JOIN user8.ACCOUNT_TYPE accounttyp11_
ON accountdto2_.ACCOUNT_TYPE_ID=accounttyp11_.ACCOUNT_TYPE_ID
LEFT OUTER JOIN user8.QUESTION questiondt12_
ON accountdto2_.QUESTION_ID=questiondt12_.QUESTION_ID
LEFT OUTER JOIN user8.CREDIT_CARD creditcard13_
ON accountdto2_.CREDIT_CARD_ID=creditcard13_.CREDIT_CARD_ID
LEFT OUTER JOIN user8.CREDIT_CARD creditcard14_
ON creditcard13_.PARENT_ID=creditcard14_.CREDIT_CARD_ID
LEFT OUTER JOIN user8.PERSON_INFORMATION personinfo15_
ON accountdto2_.PERSON_INFORMATION_ID=personinfo15_.PERSON_INFORMATION_ID
LEFT OUTER JOIN user8.BUSINESS_TYPE businessty16_
ON personinfo15_.BUSINESS_TYPE_ID=businessty16_.BUSINESS_TYPE_ID
LEFT OUTER JOIN user8.ADDRESS addressdto17_
ON personinfo15_.ADDRESS_ID=addressdto17_.ADDRESS_ID
LEFT OUTER JOIN user8.VATIN vatindto18_
ON personinfo15_.VATIN_ID=vatindto18_.VATIN_ID
LEFT OUTER JOIN user8.REPRESENTATIVE representa19_
ON this_.REPRESENTATIVE_ID=representa19_.REPRESENTATIVE_ID
LEFT OUTER JOIN user8.PERSON_INFORMATION personinfo20_
ON representa19_.PERSON_INFORMATION_ID=personinfo20_.PERSON_INFORMATION_ID
LEFT OUTER JOIN user8.ADDRESS addressdto21_
ON this_.ADDRESS_ID=addressdto21_.ADDRESS_ID
LEFT OUTER JOIN user8.KEYWORD keyworddto22_
ON addressdto21_.CITY_KEYWORD_ID=keyworddto22_.KEYWORD_ID
LEFT OUTER JOIN user8.COUNTRY countrydto23_
ON addressdto21_.COUNTRY_ID=countrydto23_.COUNTRY_ID
LEFT OUTER JOIN user8.COUNTRY_GROUP countrygro24_
ON countrydto23_.COUNTRY_GROUP_ID=countrygro24_.COUNTRY_GROUP_ID
LEFT OUTER JOIN user8.KEYWORD keyworddto25_
ON countrydto23_.country_keyword_id=keyworddto25_.KEYWORD_ID
LEFT OUTER JOIN user8.STATE statedto26_
ON addressdto21_.STATE_ID=statedto26_.STATE_ID
LEFT OUTER JOIN user8.GPS gpsdto27_
ON this_.GPS_ID =gpsdto27_.GPS_ID
WHERE this_.STATUS =?
AND this_.ONLINE_STATUS=?
ORDER BY this_.location_id DESC
|
|
Оптимизация запроса SQL (БД PostgreSQL), Критическое время выполнения запроса. 
Загрузка ...
