Oracle SQL Optimization: SQL Query Takes a Very Long Time
SELECT DISTINCT
'LRS-TECH 1' || rpad(code,7) || rpad('APPTYPE',30) ||
rpad(licensing_no,30) || rpad(' ',300) AS RECORD
FROM APPS
WHERE L_code = '1000' AND licensing_no IS NOT NULL
This seems to be the main culprit for why I am unable to export these entries to a text file in my development environment. Is there any way to make this query run faster. It returns roughly 2000 lines of text.
The solution is simple.
Create an index (code, licensing_no) and an index on (l_code, licensing_no) to fetch records faster. Do the "decoration" later in the app or just in the outer wrapper like this:
SELECT 'LRS-TECH 1'
|| RPAD (code, 7)
|| RPAD ('APPTYPE', 30)
|| RPAD (licensing_no, 30)
|| RPAD (' ', 300) AS RECORD
FROM (SELECT DISTINCT code, licensing_no
FROM apps
WHERE l_code = '1000' AND licensing_no IS NOT NULL)
source to share
You cannot diagnose this problem unless you know how the query is optimized.
Try the following:
explain plan for SELECT DISTINCT
'LRS-TECH 1' || rpad(code,7) || rpad('APPTYPE',30) ||
rpad(licensing_no,30) || rpad(' ',300) AS RECORD
FROM APPS
WHERE L_code = '1000' AND licensing_no IS NOT NULL
/
select * from table(dbms_xplan.display)
/
Now try also ... this will help you spot the statistics problem:
explain plan for SELECT /*+ dynamic_sampling(4) */ DISTINCT
'LRS-TECH 1' || rpad(code,7) || rpad('APPTYPE',30) ||
rpad(licensing_no,30) || rpad(' ',300) AS RECORD
FROM APPS
WHERE L_code = '1000' AND licensing_no IS NOT NULL
/
select * from table(dbms_xplan.display)
/
Please update your original post with the results.
source to share
If there are many entries with L_code = '1000', and the only additional test is for NOT NULL, you probably have a cardinality issue. It is difficult for indexes to choose NULL or not.
The number of rows returned doesn't matter - that's the number of rows that were covered in the question.
What indexes are there?
source to share
Hmmm ... getting rid of DISTINCT can help treat this code as a BASIC KEY. I don't think this is what is causing major processing problems. If you think RPAD etc. Causes most of the request latency.
Indexes are mainly ASCEND - CODE field. These are the only relevant indexes in the table.
You can pre-create a derived RECORD value in a secondary table, view, or column using a trigger and query, instead of building it on the fly if the table is frequently requested.
This can help you find out the size of the table. If you have a large column or many records, it might be IO or cache related.
source to share
I'm really sorry for everyone looking at this SQL, but it's a server issue causing problems. The scenario seems to be looming and I believe there is a data availability issue about where the DB is, but someone can give me some insight.
In my Localhost I run the code, it works instantly. I am exporting the data it gives me from datatable to text file in less than a second ... done.
In our development environment, this same page is in the old ASP. Half of our site is in classic ASP when we convert to .NET. The problem is that on a DEV site, the classic ASP page works fine, fast and without any problem. When I uploaded the newly converted ASPX file, it hung for about 30 seconds in this request.
On Localhost, the old classic ASP hangs for about 30 seconds.
So I have a problem the other way around: Classic ASP does not hang on DEV site, but on my machine, while my own ASPX page hangs on DEV site, but NOT on my machine. The difference is that I believe the data is being fetched in my own code on the DEV site, while the ASP page is fetching the data from the code that is on the old DEV site server that feeds the results to the DEV site. So, technically, the code doesn't run on the same server. The classic ASP code resides on our old site server.
I am guessing that there is a speed issue or a server issue between the two.
As most of the answers pointed out, your question sounds like an optimization question. Your later answer significantly changes the nature of the question. I suggest posting it as a new question, or modifying the original question to find out what you really want to know.
I can't help you with the ASP / ASPX problem, but if it was an optimization question, I would suggest creating a function-based index for the new WHERE clause like this:
SELECT DISTINCT
'LRS-TECH 1' || rpad(code,7) || rpad('APPTYPE',30) ||
rpad(licensing_no,30) || rpad(' ',300) AS RECORD
FROM APPS
WHERE DECODE(L_code,'1000',licensing_no,NULL) IS NOT NULL;
The DECODE functional index (L_code, '1000', licensing_no, NULL) will include all the records you want to return. If you need even more speed, you could create a materialized view from the query results, but that would be more of a last resort.
source to share
I wonder if this is because the oracle is using a different index (or not at all) for the request from the aspx page.
I would suggest updating the statistics in the table to see if it doesn't matter.
See this question for how to do this (and comments that "calculate statistics" are deprecated, replaced by the package instead)
source to share