I have a tsql which is taking more time when it ran on 10c records, is there any chance to optimize this?

Question

0.00/5 (No votes)

See more:

INSERT INTO Mem
	(Ind, CKey
	, ID
	, Date
	, Code
	, Key
	, Cov
	, CovKey)
SELECT 'a', EKey
	, d.ID 
	, d.CEEffDate
	, d.Code 
	, d.Key
	, d.Cov 
	, d.CovKey
FROM DEl d
	WHERE NOT EXISTS
	  (SELECT Mem FROM Mem s 
	    WHERE s.ID = d.ID
	     AND s.PID = d.PID
	     AND s.LID = d.ID
	     AND s.EDate = d.EDate
	    )
	  AND Key1 <> 0
	  AND Key1 between @Binr and @Einr

Posted 11-Nov-11 2:03am

JamesWinter

Updated 11-Nov-11 3:02am

v3

Add a Solution

Comments

Smithers-Jones 11-Nov-11 8:20am

Added code-block.

JamesWinter 11-Nov-11 8:56am

i didnt getu.

Mehdi Gholam 11-Nov-11 8:29am

Have tried indexing?

JamesWinter 11-Nov-11 9:00am

yes, in the select table colums indexes were present, and on insert table no indexes..bt stil taking mre tme.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Answer 1 · 2011-12-20T21:29:00

Solution 2

Hi....

Use JOINS then Check the Performance

Eg....

SQL

INSERT INTO Mem
	(Ind, CKey
	, ID
	, Date
	, Code
	, Key
	, Cov
	, CovKey)
SELECT 'a', EKey
	, d.ID 
	, d.CEEffDate
	, d.Code 
	, d.Key
	, d.Cov 
	, d.CovKey
FROM DEl d
INNER JOIN Mem s ON s.ID <> d.ID AND s.PID <> d.PID AND s.LID <> d.ID AND s.EDate <> d.EDate
WHERE Key1 <> 0 AND Key1 between @Binr and @Einr

Posted 20-Dec-11 21:29pm

gvprabu

Updated 20-Dec-11 21:31pm

v3

Comments

Karthik Harve 21-Dec-11 3:31am

[Edit] pre tags added.

Tanveer A · Answer 2 · 2011-12-20T21:51:00

use this query

INSERT INTO Mem
(Ind, CKey
, ID
, Date
, Code
, Key
, Cov
, CovKey)
SELECT 'a', EKey
, d.ID
, d.CEEffDate
, d.Code
, d.Key
, d.Cov
, d.CovKey
FROM DEl d , mem s
WHERE
s.ID = d.ID
AND s.PID = d.PID
AND s.LID = d.ID
AND s.EDate = d.EDate
AND Key1 <> 0
AND Key1 between @Binr and @Einr
and d.mem not in(select mem from mem)

Amir Mahfoozi · Answer 3 · 2011-12-20T22:21:00

Hi James,
You have compared 4 values to match the rows between Del and Mem. If its possible make some changes in your database design so that a simple s.ID=d.ID works for finding matching rows.

Otherwise have look at other ideas about the comparison between JOIN and IN and EXIST performance :
http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/[^]

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx[^]

http://stackoverflow.com/questions/1001543/in-vs-join-with-large-rowsets[^]

Hope it helps.

I have a tsql which is taking more time when it ran on 10c records, is there any chance to optimize this?

3 solutions

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0