Query running to slow...

Hi all.

I'm creating a website in Visual Studio using C#.
It takes about 15 seconds for my website to load.
Is there anything in my coding that is causing it to run this slow?
Here is the query.

 string mySelectQuery1 = "SELECT NVL(SUM(FLOATING_HOLIDAY), 0) PERSONAL_DAYS," +
                                       " NVL(SUM(VACATION_HRS), 0) VACATION_HRS" +
                                       " FROM DW_TIME_ANALYSIS" +
                                       " WHERE MONTH_END >= '01-JAN-06' AND" +
                                       " EMPLOYEE_NUMBER ='" + LabelEmpNum.Text + "'";

                 string myConnString1 = "data source=********;user id=******; password=******";
                OracleConnection myDBConnection1 = new OracleConnection(myConnString1);

                OracleDataAdapter myAdapter1 = new OracleDataAdapter(mySelectQuery1, myDBConnection1);

                myDBConnection1.Open();

               OracleDataReader myReader1 = myAdapter1.SelectCommand.ExecuteReader();
                while (myReader1.Read())
                {
                    string strVacUsed = myReader1["VACATION_HRS"].ToString();
                    decimal fltVacUsed = Convert.ToDecimal(strVacUsed);
                    TextVacUsed.Text = Convert.ToString(Math.Round(fltVacUsed / 8));
                   
                    string strPerUsed = myReader1["PERSONAL_DAYS"].ToString();
                    TextPerUsed.Text = strPerUsed;
                }

                myDBConnection1.Close();
JordanBlacklerAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Indeed, I missed it, sorry.

Now, you indeed don't have the index you really need. MONTH_END is only 4th in the index, and employee number is not at all indexed.

CREATE INDEX DTA_IND_2 ON DW_TIME_ANALYSIS ( EMPLOYEE_NUMBER, MONTH_END );

and your query will run subsecond:

"SELECT NVL(SUM(FLOATING_HOLIDAY), 0) PERSONAL_DAYS," +
                                       " NVL(SUM(VACATION_HRS), 0) VACATION_HRS" +
                                       " FROM DW_TIME_ANALYSIS" +
                                       " WHERE MONTH_END >= '01-JAN-06' AND" +
                                       " EMPLOYEE_NUMBER ='" + LabelEmpNum.Text + "'";
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the field MONTH_END of data type date time?
>WHERE MONTH_END >= '01-JAN-06'
should be
WHERE MONTH_END >= TO_DATE('01-JAN-06' , 'DD-MON-YY')

is there and index on either fields Employee_NUmber or Month_end
best would be 1 index on Employee_Number + Month_End
0
 
paquicubaCommented:
Do you have an index for EMPLOYEE_NUMBER?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
JordanBlacklerAuthor Commented:
Everything is indexed.
It took 5 seconds for the query to run using toad
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how many rows are returned?

if toad takes 5 seconds, then either
* not correctly indexed (what ARE the indexes you have / did you try with the to_date )
* too many rows returned
* server is not properly sized
0
 
paquicubaCommented:
Are you comfortable with a 5 seconds response time?

How many rows are in that table?
0
 
JordanBlacklerAuthor Commented:
I'm not sure about the indexes.
I did not create the DB. But i was told everything was indexed.

I added the to_date and it takes just as long.

It's only supposed to return one row.
0
 
paquicubaCommented:
Can you run an explain plan?

In TOAD, click the little ambulance icon

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I did not create the DB. But i was told everything was indexed.
5 seconds for 1 row is FAR TOOO long. for sure there is not THE index you need here.

please post the outcome of this query:
SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'


0
 
JordanBlacklerAuthor Commented:
I don't have access to toad at this moment.

>SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'
It returned 3 rows with about 50 columns.

i ran
SELECT * FROM DW_TIME_ANALYSIS ORDER BY EMPLOYEE
and it's at 150,000 rows returned and it's not done yet.
0
 
paquicubaCommented:
Just run this query for an approximate number of rows:
SELECT NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'

0
 
JordanBlacklerAuthor Commented:
>Just run this query for an approximate number of rows:
>SELECT NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'

693042
0
 
paquicubaCommented:
This is a small table. No reason for such poor performance. I agree with AngelIII that this could be server related or incorrect seeting of parameters in the init.ora file.

Can you run an explain plan?
0
 
JordanBlacklerAuthor Commented:
I'm also running this query. It takes less then a second to run.
SELECT DISTINCT EMPLOYEE_NUMBER,START_DATE
FROM BMDW_EMPLOYEE
WHERE LAST_NAME ='" + lblLastName.Text.ToUpper() + "' AND" + " FIRST_NAME ='" + lblFirstName.Text.ToUpper() + "'";

>Can you run an explain plan?
The person who is incharge of the oracle DB's is not here now.
what is the syntax to run an explain plan.
0
 
paquicubaCommented:
Here is a link: http://asktom.oracle.com/~tkyte/article1/autotrace.html

In TOAD, try clicking the button that has an ambulance
0
 
ExpertAdminCommented:
Unless there are millions of rows, I don't see why this should even take a second.

Make sure these columns are indexed:

MONTH_END
EMPLOYEE_NUMBER

Are you sure it is the query that is taking so long? Try running it in SQL Plus and see if you have the same execution time.

M@
0
 
JJSmithCommented:

Be advised: the number of rows may not always be a factor. If the query is not indexing 'ie. full scan' and the table had previously extended to a 'high' water mark - then a physically large table containing 1 row can take the same amount of time seen on a table with a million rows!!

Need to check the contents of the table - not a statistic of the table. As paquicuba referred you to Tom Kytes site, look for 'show_space' whilst you are there (last used block being the High Water Mark).


Cheers
JJ
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>>SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'
>It returned 3 rows with about 50 columns.
select INDEX_TYPE, index_name from ALL_INDEXES

select INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM ALL_IND_COLUMN WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'
0
 
hardikbeitCommented:

Rebuild your indexes may be improve your performance. In TOAD there is one button with
ICON like 'Hammer' and tooltip like 'Rebuild this index' for Rebuild your Index.

0
 
JordanBlacklerAuthor Commented:
>>SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'
>>It returned 3 rows with about 50 columns.
>select INDEX_TYPE, index_name from ALL_INDEXES

>select INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM ALL_IND_COLUMN WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'

the first query has 48 rows. Is there something i should look for? Should i post them all?

the 2nd query didn't work.
0
 
JordanBlacklerAuthor Commented:
I don't have access to toad.
I would like to find out if the columns are not indexed before i go to the person who has the access for toad.

I was told that everything was indexed
0
 
JordanBlacklerAuthor Commented:
----
Unless there are millions of rows, I don't see why this should even take a second.
Make sure these columns are indexed:
MONTH_END
EMPLOYEE_NUMBER
Are you sure it is the query that is taking so long? Try running it in SQL Plus and see if you have the same execution time.
M@
----

It takes about 9 secons to run in SQL Plus.
I'm not sure how to check to see what columns are indexed or not.
0
 
JordanBlacklerAuthor Commented:
angelIII--

select INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'

Index_Name                              Column_Name       column_Position
DTA_WE                                    WEEK_END      1
DTA_IND                                      PROJECT_NUMBER      1
DTA_IND                                      PROJECT_NAME      2
DTA_IND                                      CLIENT_NAME      3
DTA_IND                                      MONTH_END      4
DW_TIME_ANALYSIS_I1      PRIMARY_CMP_NAME      1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>the first query has 48 rows. Is there something i should look for? Should i post them all?
this would mean you hav 48 indexes on that table?
or do you have different owners?
please post..


second query: the table is called ALL_IND_COLUMNS, I forgot to put the S:
>select INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'



0
 
JordanBlacklerAuthor Commented:
SELECT INDEX_TYPE, INDEX_NAME
FROM    SYS.ALL_INDEXES

INDEX_TYPE  INDEX_NAME

NORMAL      UNIQUE_ANGLE_UNITS
NORMAL      UNIQUE_AREA_UNITS
NORMAL      UNIQUE_DIST_UNITS
NORMAL      SYS_C008989
NORMAL      SYS_C008983
NORMAL      SYS_C008980
NORMAL      I_AUDIT_ACTIONS
NORMAL      I_AUDIT_ACTIONS
NORMAL      KEY_C
NORMAL      HELP_TOPIC_SEQ
NORMAL      PK_SRID
NORMAL      I_STMT_AUDIT_OPTION_MAP
NORMAL      I_TABLE_PRIVILEGE_MAP
NORMAL      I_SYSTEM_PRIVILEGE_MAP
NORMAL      CLI_NUM_I
NORMAL      PY_MONTH_END_I
NORMAL      DW_MONTH_INC_PROJ_RCL
NORMAL      DW_MONTH_INC_PROJ_U2
NORMAL      DW_CLI_PLAN_ACT_I3
NORMAL      DW_CLI_PLAN_ACT_I2
NORMAL      DW_CLI_PLAN_ACT_I1
NORMAL      BMDW_CLIENT_U4
NORMAL      BMDW_CLIENT_U3
NORMAL      BMDW_PROJECT_N1
NORMAL      TPTBL_IDX
NORMAL      TPSQL_IDX
NORMAL      BMDW_TASK_N1
NORMAL      PK_TASK
NORMAL      PK_PROJECT
NORMAL      DW_TIME_ANALYSIS_I1
NORMAL      BMDW_CLIENT_FCST_PLAN_IDX1
NORMAL      DW_MONTH_INC_PROJ_ENT
NORMAL      BMDW_CLIENT_U2
NORMAL      BMDW_PROJECT_U2
NORMAL      BMDW_EMPLOYEE_ENUM
NORMAL      EMP_LE1
NORMAL      EMP_EN1
NORMAL      EMP_OF1
NORMAL      EMP_SE1
NORMAL      EMP_PR1
NORMAL      EMP_SP1
NORMAL      BMDW_EMPLOYEE_N1
NORMAL      DW_MONTH_INC_PROJ_U1
NORMAL      BMDW_CLIENT_U1
NORMAL      BMDW_TASK_U1
NORMAL      BMDW_PROJECT_U1
NORMAL      DTA_IND
NORMAL      DTA_WE
NULL      NULL
0
 
JordanBlacklerAuthor Commented:
I asked someone to run an explain plan.  This is what i got back.
Explain plan:

                                                                 Rows    Bytes    Cost
Select Statement Optimizer Mode = choose     1       2301
Sort Aggregate     1       30  
Table Access Full   34    1020   2301
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Table Access Full   34    1020   2301

well, that proves that you have a full table scan and no proper indexes for your query
0
 
JordanBlacklerAuthor Commented:
>>Table Access Full   34    1020   2301
>well, that proves that you have a full table scan and no proper indexes for your query

Just because i don't know and i would like to..
How does that prove that there are no proper indexes for my query
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the good indexes where there , it would say soemthing with index...

please run this query:

select INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM ALL_IND_COLUMNS
WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'
0
 
JordanBlacklerAuthor Commented:
angelIII--
i already did. I guess you missed it.

select INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'

Index_Name                              Column_Name       column_Position
DTA_WE                                    WEEK_END               1
DTA_IND                                     PROJECT_NUMBER     1
DTA_IND                                     PROJECT_NAME        2
DTA_IND                                     CLIENT_NAME        3
DTA_IND                                     MONTH_END        4
DW_TIME_ANALYSIS_I1     PRIMARY_CMP_NAME     1
0
 
JordanBlacklerAuthor Commented:
angelIII -

CREATE INDEX DTA_IND_2 ON DW_TIME_ANALYSIS ( EMPLOYEE_NUMBER, MONTH_END );

I think they are having a problem with creating this. As in they are not sure if they will do it.
"It already has 6 indexes (month_end included) and adding a concatenated one will probably slow down an already slow refresh."

what about just creating EMPLOYEE_NUMBER as an index alone?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>"It already has 6 indexes (month_end included) and adding a concatenated one will probably slow down an already slow refresh."

well, this statement is WRONG. the table has only 2 indexes, of which 1 is concatenated.
And, having the month_end as 4th in the index does NOT help at all for your query.

>I think they are having a problem with creating this. As in they are not sure if they will do it.
if you want your query to run in less than 15 seconds, they have to (or use a even heavier method than materialized view
0
 
JordanBlacklerAuthor Commented:
angelIII -

>"It already has 6 indexes (month_end included) and adding a concatenated one will probably slow down an already slow refresh."

well, this statement is WRONG. the table has only 2 indexes, of which 1 is concatenated.
And, having the month_end as 4th in the index does NOT help at all for your query.

>I think they are having a problem with creating this. As in they are not sure if they will do it.
if you want your query to run in less than 15 seconds, they have to (or use a even heavier method than materialized view

Just so it looks like i know what i'm talking about. How can you tell it only has 2 indexes.
Didn't it list 6 indexes from that query i did.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Didn't it list 6 indexes from that query i did.
3 indexes:

index 1 (1 column)
DTA_WE                                    WEEK_END               1

index 2 ( 4 columns)
DTA_IND                                     PROJECT_NUMBER     1
DTA_IND                                     PROJECT_NAME        2
DTA_IND                                     CLIENT_NAME        3
DTA_IND                                     MONTH_END        4

index 3 (1 column)
DW_TIME_ANALYSIS_I1     PRIMARY_CMP_NAME     1

0
 
JordanBlacklerAuthor Commented:
angelIII -

I'm trying to see what is going to happen.
I will post back shortly.

Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.