Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

How to increase time to populate data to webpage

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

0
titanium0203
Asked:
titanium0203
3 Solutions
 
OP_ZaharinCommented:
- 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.
0
 
OP_ZaharinCommented:
- 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.
0
 
titanium0203Author 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
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
titanium0203Author 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
0
 
OP_ZaharinCommented:
- what is your table name? can you post the full select statement?
0
 
titanium0203Author 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
0
 
OP_ZaharinCommented:
- my first recommendation on indexing would be:
i- SHOP_id
ii- RETAILER_ID

- the syntax as follows:

CREATE INDEX index_name ON tablename (column)

Open in new window

0
 
titanium0203Author Commented:
Hi,

If possible I want to focus on the where clause.
0
 
OP_ZaharinCommented:
- 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.

0
 
OP_ZaharinCommented:
- you can use this syntax:

CREATE INDEX TNX_DATE_IDX ON SHOP_TXN (TXN_DATE)
0
 
slightwv (䄆 Netminder) Commented:
I believe Toad buffers the returned rows so it 'appears' to be quick.  The web report needs ALL the data.

Guessing on proper indexes is just that, a guess.

I suggest you go back to the suggestion in http:#a35445878 and provide us with the execution plans.  First make sure the statistics on the tables are up to date.

I also suggest you provide the plans of the individual selects to start with.  Tune those first, then the joined query.
0
 
MikeOM_DBACommented:

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

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now