StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POEnhance performance of large slow dataloading query
text
Body
copied!<p>I'm trying to load data from oracle to sql server (Sorry for not writing this before)</p> <p>I have a table(actually a view which has data from different tables) with 1 million records atleast. I designed my package in such a way that i have functions for business logics and call them in select query directly.</p> <p>Ex:</p> <pre><code>X1(id varchar2) x2(id varchar2, d1 date) x3(id varchar2, d2 date) Select id, x, y, z, decode (.....), x1(id), x2(id), x3(id) FROM Table1 </code></pre> <p>Note: My table has 20 columns and i call 5 different functions on atleast 6-7 columns. And some functions compare the parameters passed with audit table and perform logic</p> <p>How can i improve performance of my query or is there a better way to do this</p> <p>I tried doing it in C# code but initial select of records is large enough for dataset and i get outofmemory exception.</p> <p>my function does selects and then performs logic for example:</p> <pre><code>Function(c_x2, eid) Select col1 into p_x1 from tableP where eid = eid; IF (p_x1 = NULL) THEN ret_var := 'INITIAL'; ELSIF (p_x1 = 'L') AND (c_x2 = 'A') THEN ret_var:= 'RL'; INSERT INTO Audit (old_val, new_val, audit_event, id, pname) VALUES (p_x1, c_x2, 'RL', eid, 'PackageProcName'); ELSIF (p_x1 = 'A') AND (c_x2 = 'L') THEN ret_var := 'GL'; INSERT INTO Audit (old_val, new_val, audit_event, id, pname) VALUES (p_x1, c_x2, 'GL', eid, 'PackgProcName'); END IF; RETURN ret_var; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload