Oracle/SQL Query Optimisations
£18-36 GBP / hour
We have an enterprise solution and have recently lost our primary DBA resource. We have a small number of specific database queries/updates that are causing major performance issues for a number of clients.
In addition, we are not currently using BIND VARIABLES in either SQL or Oracle and would seek input/guidance on adoption thereof.
Our application is an [login to view URL] webforms solution.
We are seeking DBA support to help on this.
This is a typical query (in Oracle format):
SELECT * FROM (SELECT JOB_HEADERS_FW.*, COALESCE(STATUS_CODES_FW.DESCRIPTION_FW,JOB_HEADERS_FW.JOB_STATUS_FW) AS JOB_STATUS, COALESCE(JOB_REASONS_FW.DESCRIPTION_FW,JOB_HEADERS_FW.REASON_CODE_FW) AS REASON_CODE, COALESCE(FITTERS_FW.FITTER_NAME_FW,JOB_HEADERS_FW.FITTER_FW) AS FITTER_NAME, (SELECT SUM(COALESCE(STANDARD_HOURS_FW,HOURS_FW)) FROM JOB_DETAILS_fW WHERE JOB_DETAILS_FW.JOB_NUMBER_FW=JOB_HEADERS_FW.JOB_NUMBER_FW AND JOB_DETAILS_FW.JOB_DETAIL_STATUS_FW='FINISH' AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) ) AS STANDARD_COMPLETED, (SELECT SUM(COALESCE(STANDARD_HOURS_FW,HOURS_FW)) FROM JOB_DETAILS_fW WHERE JOB_DETAILS_FW.JOB_NUMBER_FW=JOB_HEADERS_FW.JOB_NUMBER_FW AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) ) AS STANDARD_TOTAL, STATUS_CODES_FW.BACKGROUND_COLOUR_FW FROM JOB_HEADERS_FW LEFT OUTER JOIN JOB_REASONS_FW ON JOB_HEADERS_FW.REASON_CODE_FW=JOB_REASONS_FW.CODE_FW AND JOB_REASONS_FW.ARCHIVE_STATUS_FW='N' LEFT OUTER JOIN FITTERS_FW ON JOB_HEADERS_FW.FITTER_FW=FITTERS_FW.FITTER_FW AND FITTERS_FW.ARCHIVE_STATUS_FW='N' LEFT OUTER JOIN STATUS_CODES_FW ON JOB_HEADERS_FW.JOB_STATUS_FW=STATUS_CODES_FW.STATUS_CODE_FW AND STATUS_CODES_FW.JOB_HEADERS_FW=1 AND STATUS_CODES_FW.ARCHIVE_STATUS_FW='N' WHERE JOB_HEADERS_FW.ARCHIVE_STATUS_FW='N' AND (JOB_HEADERS_FW.DEPOT_ID_FW='MDNK1N' OR (JOB_HEADERS_FW.DEPOT_ID_FW IS NULL OR LENGTH(JOB_HEADERS_FW.DEPOT_ID_FW) = 0)) AND (NOT (STATUS_CODES_FW.CLOSING_FW=1 OR STATUS_CODES_FW.EXCLUDE_WORKSHOP_SCHEDULE_FW =1) OR JOB_HEADERS_FW.JOB_END_DATE_FW=TO_DATE('2018-05-14', 'yyyy-mm-dd')) AND JOB_HEADERS_FW.JOB_STATUS_FW<>'FINISH' AND JOB_HEADERS_FW.JOB_NUMBER_FW IN (SELECT DISTINCT JOB_NUMBER_FW FROM JOB_DETAILS_FW WHERE ARCHIVE_STATUS_FW ='N' AND FITTER_FW ='8226157841' AND (JOB_DETAIL_STATUS_FW IN ('','WND','WIP','AWP','FINISH') OR JOB_DETAIL_STATUS_FW IS NULL ) AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) UNION ALL (SELECT DISTINCT JOB_NUMBER_FW FROM JOB_DETAILS_FW WHERE ARCHIVE_STATUS_FW ='N' AND FITTER_FW ='8226157841' AND JOB_DETAIL_STATUS_FW IN ('COMPLETED','DFT','CHO') AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) AND JOB_NUMBER_FW NOT IN ( (SELECT DISTINCT JOB_NUMBER_FW FROM JOB_DETAILS_FW WHERE FITTER_FW = '8226157841' AND ARCHIVE_STATUS_FW = 'N' ) INTERSECT (SELECT DISTINCT JOB_NUMBER_FW FROM JOB_DETAILS_FW WHERE ARCHIVE_STATUS_FW ='N' AND FITTER_FW <>'8226157841' AND (JOB_DETAIL_STATUS_FW IN('','WND','WIP','AWP') OR JOB_DETAIL_STATUS_FW IS NULL ) AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) )) )) AND JOB_STATUS_FW<>'CLOSED' AND JOB_STATUS_FW<>'COMPLETE' AND JOB_STATUS_FW<>'WFS_COMPLETE_FW' AND JOB_STATUS_FW<>'DFT' AND JOB_STATUS_FW<>'DEFERRED' AND JOB_STATUS_FW<>'WFS_C_FW' AND JOB_STATUS_FW<>'A' AND JOB_STATUS_FW<>'WFS_DFT_FW' AND STATUS_CODES_FW.CLOSING_FW=0) tmpsqltop WHERE ROWNUM <= 50 ORDER BY FITTER_NAME,PRIORITY_FW,JOB_DATE_FW,JOB_NUMBER_FW
Project ID: #17081358
About the project
49 freelancers are bidding on average £31/hour for this job
Hello Sir, I am an expert with query optimization and would like to discuss this project with you. Requesting you to open chat for discussion. Thanks Aanvik
I am having more than 9 years of experience in IT industry.I have worked on .net(c#.net,vb.net,asp,asp.net,mvc) with sql server 2005/2008/2012/2014/2016 and oracle(pl/sql). I can provide you better solution by utili More
Hi, Good Day. I have read your post and would like to discuss things further as I am an expert database developer and administrator as I am having advanced knowledge of sql and query optimizations.I have worked i More
I am a professional with a decade of experience working with Oracle currently serving in a US based NASDAQ registered company. Have just implemented Complete Oracle setup in the company. Can help you with any related i More
As an Oracle Database Administrator have performance tuned SQL's using SQL hints, Tuning Advisor,Segment Advisor, ADDM reports,ASH reports,AWR reports. Could we please have an discussion? Thanks,
Hi, I have good knowlege of sql.i can make your query faster but for that i need to check once database design and query s which you are using for fetch data. Please let me know for more contact. Thanks, Darshan
Hi, I will help you in your SQL query optimization. I have 6+ years of professional experience as an Oracle DBA in Infosys Limited. Looking forward to hear from you. Thanks, Rinku Gautam
Complexed Query, from first look I can see some changes are required to make it better. Although modifications on Table(s) are most probably required. I will be glad to check and fix it with you in a minimum time.
I would be able to deliver your solution within the timeline with better quality I have 9 years of experience in SQL Server design, development and optimization. I have used Erwin tool for data modelling and i am we More
i want to do this project because i am having good skills in sql and you people want to solve complicated queries. so iam expert in it
