Solved

Query running to slow...

Posted on 2006-06-15
35
648 Views
Last Modified: 2008-01-09
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();
0
Comment
Question by:JordanBlackler
  • 16
  • 10
  • 6
  • +3
35 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16914008
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 16914016
Do you have an index for EMPLOYEE_NUMBER?
0
 

Author Comment

by:JordanBlackler
ID: 16914065
Everything is indexed.
It took 5 seconds for the query to run using toad
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16914108
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 16914146
Are you comfortable with a 5 seconds response time?

How many rows are in that table?
0
 

Author Comment

by:JordanBlackler
ID: 16914164
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 16914204
Can you run an explain plan?

In TOAD, click the little ambulance icon

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16914217
>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
 

Author Comment

by:JordanBlackler
ID: 16914366
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 16914588
Just run this query for an approximate number of rows:
SELECT NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'

0
 

Author Comment

by:JordanBlackler
ID: 16914630
>Just run this query for an approximate number of rows:
>SELECT NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME = 'DW_TIME_ANALYSIS'

693042
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16914907
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
 

Author Comment

by:JordanBlackler
ID: 16915002
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 16915227
Here is a link: http://asktom.oracle.com/~tkyte/article1/autotrace.html

In TOAD, try clicking the button that has an ambulance
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16915939
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
 
LVL 6

Expert Comment

by:JJSmith
ID: 16916783

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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16917737
>>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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:hardikbeit
ID: 16917960

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
 

Author Comment

by:JordanBlackler
ID: 16919592
>>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
 

Author Comment

by:JordanBlackler
ID: 16919599
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
 

Author Comment

by:JordanBlackler
ID: 16919692
----
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
 

Author Comment

by:JordanBlackler
ID: 16919869
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16919876
>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
 

Author Comment

by:JordanBlackler
ID: 16919920
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
 

Author Comment

by:JordanBlackler
ID: 16919960
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16920004
>Table Access Full   34    1020   2301

well, that proves that you have a full table scan and no proper indexes for your query
0
 

Author Comment

by:JordanBlackler
ID: 16920036
>>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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16920059
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
 

Author Comment

by:JordanBlackler
ID: 16920101
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 16920166
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
 

Author Comment

by:JordanBlackler
ID: 16920432
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16920477
>"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
 

Author Comment

by:JordanBlackler
ID: 16920578
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16920620
>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
 

Author Comment

by:JordanBlackler
ID: 16920700
angelIII -

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

Thanks for the help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now