We help IT Professionals succeed at work.

How to increase time to populate data to webpage

titanium0203
titanium0203 asked
on
Medium Priority
514 Views
Last Modified: 2012-06-27
Hi,

Are there any methods that I can use to increase time to populate data to webpage.
we are using php and oci_coonect to connect to oracle db11g.

would indexing solve the problem? or any other ways that we can use to increase the time to process the query?

regards
titanium

Comment
Watch Question

Top Expert 2011

Commented:
- indexing on the right column would definitely helps to speed up a query.
- you also need to tuning the sql statement to further optimize such as limit the number of table join and etc. make use of Oracle Explain Plan to tune your queries.
Top Expert 2011

Commented:
- Oracle Explain Plan basically executes your query, then analyze the way Oracle execute your query. it defines how Oracle finds or writes the data. by examining this plan, you can find out if Oracle is using the right indexes and joining the tables in efficient manner. from the explain plan you can further examine if more indexes is needed, and the need to change the way your sql been written.

Author

Commented:
Hi,

I don't have problem when try to grab the data from toad, but when run the query
via web page then it will slow the process. We use webpage to view the daily report.

regards,
titanium

Author

Commented:
Hi,

Another thing, how can I index where clause below :

where trunc(TXN_DATE) between to_date('01/03/2011','DD/MM/YYYY') and to_date('01/03/2011','DD/MM/YYYY')
and to_char(TXN_DATE,'HH24MISS') between '000000' and '055959'

thanks
Top Expert 2011

Commented:
- what is your table name? can you post the full select statement?

Author

Commented:
Hi,

check below :

select 0,shop_name ,shop_disc,qtyo-qtyd qtyo,sales from
      (
      select shop_id,shop_name,shop_disc from SHOP_INFO
      where SHOP_LOCATION = 'MXT'
      order by shop_name
      ) a
      left join
      (
      select shop_id,sum(ITEM_PRICE) sales,sum(case when ITEM_PRICE >= 0 then QTY_ORDERED else 0 end) qtyo,
      sum(case when ITEM_PRICE < 0 and QTY_ORDERED > 0 then QTY_ORDERED else 0 end) qtyd  from SHOP_TXN
      where trunc(TXN_DATE) between to_date('01/03/2011','DD/MM/YYYY') and to_date('01/03/2011','DD/MM/YYYY')
      and to_char(TXN_DATE,'HH24MISS') between '000000' and '055959'
      group by RETAILER_ID
      ) b
      on a.SHOP_id = b.RETAILER_ID
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi,

If possible I want to focus on the where clause.
Top Expert 2011

Commented:
- the WHERE clause you are referring to only have 1 column which is TXN_DATE. yes you can index TXN_DATE using the syntax i have gave you above. index will help when you provide a date range such as your queries.

- however you must be careful when construct the query. reason being, we always applying functions on date columns such as trunc() or extract(). in those cases the index will not be used.

Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Try this where clause:


-- Etc --
       FROM SHOP_TXN
      WHERE TXN_DATE BETWEEN TO_DATE ('01/03/2011:000000',
                                        'DD/MM/YYYY:HH24MISS')
                         AND TO_DATE ('01/03/2011:055959',
                                        'DD/MM/YYYY:HH24MISS')
-- Etc ...

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.