[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select count takes too long

Posted on 2009-12-16
5
Medium Priority
?
1,045 Views
Last Modified: 2012-06-21
Hi experts,
I wrote stored proc that compares card from systables to actual row count. It runs fine on all tables, but stucks on table with 19 mil rows .
 FETCH C2 INTO vTableCount;  gets stuck and never comes back


Sample
DECLARE c1 CURSOR FOR
    SELECT tabname,card from syscat.tables where  Type='T' and tabschema=IN_SCHEMA;
    DECLARE C2 CURSOR FOR S2;
      
     DECLARE GLOBAL TEMPORARY TABLE SESSION.#row_counter
       (tableName varchar(150),
        card double,
        tableCount double)
            ON COMMIT PRESERVE ROWS
      ;

     
   OPEN c1;

   getRows:
   LOOP
FETCH c1 INTO vTableName,vTableCard;
     IF SQLCODE = 0 THEN
       SET stmt ='SELECT count(1) FROM ' || IN_SCHEMA || '.' ||  vTableName || ' with ur';
       PREPARE S2 FROM stmt;
       OPEN C2;
       SET vTableCount = 0;
       FETCH C2 INTO vTableCount;
       INSERT INTO SESSION.#row_counter (tableName, card,tableCount)
              VALUES (vTableName, vTableCard,vTableCount);

Any advise?

thanks
Julia
0
Comment
Question by:juliakir
  • 2
  • 2
5 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26069308
If that table has a primary key, then try doing count(pk) instead of count(1) to make it work faster..
If you don't have a primary key, just use Indexed columns for better performance..
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 26069355
count(1) is better than count(pk) since count(1) will use the smallest index on the table, so this is a good approch

if you just perform
select count(1) from  tab_with_19mil_rows
with ur

in the command editor, how much time does it takes?
what is the chosen access path ?
0
 

Author Comment

by:juliakir
ID: 26069382
it runs fast in the command editor . It seems that depends on the environment. It runs fast in Dev/QA (stored proc and command editor) but stucks on the personal server that we have which only has 2G of Ram.
IS there any server config I can check that may affect it ?

thank you
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1000 total points
ID: 26069392
sorry for the dumb question, but do you have the same indexes defined? did you run runstats on the personal server?
0
 

Author Comment

by:juliakir
ID: 26069462
that was not dumb question . I overlooked the fact that somebody else was working on that server and stats were not up todate . strange  that it run reasonable time in command editor but not in stored proc. I did runstats and it looks good.

Thank you !!!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Loops Section Overview

872 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