Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Question Unanswered: PGPLSQL / Postgres / Insertion

    I've written stored procedure for selecting counts on particular column value by equaling Urls (ex. http://dbforums.com/editpost.php?act...postid=3169001).

    I'm doing the same above for different column thrise. after that i'm trying to insert in to some other table.. those 3 counts, even i've value in variable.. but its not inserting..

    if the table has minimum rec like 1000 rec..its working fine..Actual table contains 4 million records.

    I jus need a optimized query....and Reason for insertions are not made. i've pasted my code bellow this..

    ***** In SP Code part ***

    For rcColinkeeHost In SELECT sites FROM COLINKEES_sites
    LOOP

    select count(*) into pagesDownloaded from crawled_urls where source_host=rcColinkeeHost.sites;

    select count(*) into timeOuts from link_error where host_name = rcColinkeeHost.sites and letype=error;

    select count(*) into otherErrors from link_error where host_name= rcColinkeeHost.sites and letype <> error;

    insert into site_statistics values(rcColinkeeHost.sites,pagesDownloaded,timeOu ts,otherErrors);
    End LOOP;


    ***sp/***

    Thanks,
    Sundar
    Last edited by rkssundaram; 07-31-03 at 07:08.

  2. #2
    Join Date
    Jul 2003
    Location
    Portugal (EU)
    Posts
    6

    Question

    Hello,

    I think you have an optimization/performance problem.

    can you please tell us the specific version of Postgres and operating system you are using?


    If you can also post your postgresql.conf ( usually inside $PGDATA) it may help

  3. #3
    Join Date
    Jul 2003
    Location
    Portugal (EU)
    Posts
    6
    Originally posted by legolas
    Hello,

    I think you have an optimization/performance problem.

    can you please tell us the specific version of Postgres and operating system you are using?


    If you can also post your postgresql.conf ( usually inside $PGDATA) it may help
    Can you evaluate the execution time of the 3 separate Select Into statements using psql interactively?

    Also try to PREPARE the query before actual execution.

    If they execute interactively in a timely manner, you have some other problem..

    If they don't, then verify that you have table indexes for source_host in table crawled_urls, and for host_name and letype in table rcColinkeeHost.
    If those fields are not indexed create indices for them.

    Finally, if you are running this on a Pentium-200, just do a hardware upgrade ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •