SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Optimizing query
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  11:59:03  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
Hi,

I have the below query written so that i do not insert entries that is already existing in the table. I am trying to put in 70000 entries at a single shot and it breaks down. Can anybody help me optimize the below query so that it doesnt break? Is there any other way I can write this query?

Please do help me with this. Thanks in advance. The table in which i am inserting the entries has a composite key composed of ACCT_NUM_MIN and ACCT_NUM_MAX. I am getting this from a table which doesnt have a primary key(CORE)


INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
SELECT UID , LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), BIN, BUS_ID, BUS_NM, ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE o
WHERE NOT EXISTS (SELECT * FROM CRF i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN
AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:02:17  Show Profile  Reply with Quote
What do you mean by Breaking down?

Please post the DDL for the 2 tables including all of the indexes and constraints

Read the hint link in my sig



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5560 Posts

Posted - 09/18/2007 :  12:05:34  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Try to rewrite query using LEFT JOIN instead of NOT EXISTS.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:07:15  Show Profile  Reply with Quote
quote:
Originally posted by harsh_athalye

Try to rewrite query using LEFT JOIN instead of NOT EXISTS.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"



Why?

Do you think it would make a differece to the Optimizer?

Besides, you left off the null check




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam




Edited by - X002548 on 09/18/2007 12:07:54
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:10:02  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
This is for the CRF table

CREATE TABLE [CRF] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[CORE_UID] [int] NULL ,
[BUS_ID] [int] NULL ,
[BIN] [float] NULL ,
[ISO_CTRY_CD] [smallint] NULL ,
[ACCT_NUM_MIN] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NUM_MAX] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BUS_NM] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAST_NM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REST_OF_NM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMAIL_ADDR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACS30_URL1_UID] [int] NULL ,
[ACS30_URL2_UID] [int] NULL ,
[AACS30_URL1_UID] [int] NULL ,
[AACS30_URL2_UID] [int] NULL ,
[PROCESSOR_ID] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROCESSOR_RULE_ID] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACS_TIMEOUT] [int] NULL ,
[FLG_CHLD_EXIST] [bit] NULL CONSTRAINT [DF_DPT_CRF_FLG_CHLD_EXIST] DEFAULT (0),
[FLG_APPROVAL_PENDING] [bit] NULL CONSTRAINT [DF_DPT_CRF_FLG_APPROVAL_PENDING] DEFAULT (0),
[PARENT_UID] [int] NULL ,
[REGN_CD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FRM_CORE] [bit] NULL ,
[FLG_DELETED] [bit] NULL CONSTRAINT [DF_DPT_CRF_FLG_DELETED] DEFAULT (0),
[DELETED_BY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROD_TYPE_CD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HLDNG_CMPNY_ID] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENROLL_URL] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CARD_TYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INCLUSIVE_DEVICES] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [UNIQUE_CARDRANGE_DPT] PRIMARY KEY CLUSTERED
(
[ACCT_NUM_MIN],
[ACCT_NUM_MAX]
) ON [PRIMARY]
) ON [PRIMARY]
GO


This is for the CORE table

CREATE TABLE [CORE] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[BIN] [numeric](7, 0) NOT NULL ,
[BUS_ID] [int] NOT NULL ,
[BUS_NM] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ISO_CTRY_CD] [numeric](3, 0) NOT NULL ,
[ACCT_NUM_MIN] [char] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NUM_MAX] [char] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PROD_TYPE_CD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PROD_LONG_NM] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[B1_PROC_RULE_ID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[B2_PROC_RULE_ID] [numeric](10, 0) NULL ,
[DB_PROC_RULE_ID] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[B1_PCR] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[B2_CIB] [numeric](7, 0) NULL ,
[DB_PCR] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REGN_CD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_RNG_LEN_GRP] [smallint] NOT NULL ,
[CARD_TYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

An additional information is that another table has already inserted some entries in CRf which the CORE is trying to insert. So it is giving me an error saying
"Violation of PRIMARY KEY constraint 'UNIQUE_CARDRANGE_DPT'. Cannot insert duplicate key in object 'CRF'"


Please help me out Brett

Edited by - rockingdesi on 09/18/2007 12:16:03
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:16:09  Show Profile  Reply with Quote
UID is an IDENTITY Column. Have you SET IDENTITY_INSERT ON????

Also are there are no Unique indexes or PK's on Core?

What is the error you are getting. We can only guess unless you tell us what is "breaking down"

If it is the UID, either remove it, or if you need to retain the IDENTITY you need to do SET IDENTITY_INSERT CORE ON

Problem is, what if the UID Exists already



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:16:51  Show Profile  Reply with Quote
And change your profile so I can email you



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:22:32  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
Hi Brett,

I have changed my profile so you can mail me and the error I have already posted in the same post where I posted the DDL.

The CORE table doesnt have a PK.

CORE table has a UID of its own
CRF table has a UID of its own. Addition to that CRF has the CORE_UID too. Do let me know in the email if you need anything else.

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:27:44  Show Profile  Reply with Quote
OH, OK, you eliminate where the PK's exists, but what if they don't exits and Core has dup's like your other post

What if there are dups in Core. What do you want to do about that



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:29:15  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
quote:
Originally posted by X002548

UID is an IDENTITY Column. Have you SET IDENTITY_INSERT ON????

Also are there are no Unique indexes or PK's on Core?
NO THERE ARE NO PKs

What is the error you are getting. We can only guess unless you tell us what is "breaking down"

Violation of PRIMARY KEY constraint 'UNIQUE_CARDRANGE'. Cannot insert duplicate key in object 'CRF'


If it is the UID, either remove it, or if you need to retain the IDENTITY you need to do SET IDENTITY_INSERT CORE ON

I AM ABLE TO ADD ENTRIES IN IT BUT WHEN IT IS TRYING TO INSERT DUPLICATE ONES IT GIVES ME AN ERROR...THERE IS NOTHING WRONG WITH THE TABLE STRUCTURE


Problem is, what if the UID Exists already

I DO NOT WANT TO INSERT IF THE UID ALREADY EXISTS.....



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:29:19  Show Profile  Reply with Quote
Is CRF empty before this operation?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:31:18  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
quote:
Originally posted by X002548

Is CRF empty before this operation?

YES THE CRF IS EMPTY BEFORE RUNNING THIS OPERATION

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:32:25  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
quote:
Originally posted by X002548

OH, OK, you eliminate where the PK's exists, but what if they don't exits and Core has dup's like your other post

What if there are dups in Core. What do you want to do about that

I DONT CARE ABOUT DUPLICATES IN THE CORE TABLE.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:33:03  Show Profile  Reply with Quote
Try this


SET IDENTITY_INSERT CRF ON
INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE) 
SELECT UID 
	, LEFT(ACCT_NUM_MIN,16)
	, LEFT(ACCT_NUM_MAX,16)
	, BIN
	, BUS_ID
	, BUS_NM
	, ISO_CTRY_CD
	, REGN_CD
	, PROD_TYPE_CD
	, CARD_TYPE 
  FROM CORE o 
 WHERE NOT EXISTS (SELECT * FROM CRF i1
		    WHERE o.ACCT_NUM_MIN = i1.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i1.ACCT_NUM_MAX)
       AND EXISTS (SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF i2
		    WHERE o.ACCT_NUM_MIN = i2.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i2.ACCT_NUM_MAX 
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) = 1)
  WHERE NOT EXISTS (SELECT * FROM CRF i3
		    WHERE o.UID = i3.UID)


SET IDENTITY_INSERT CRF OFF



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam




Edited by - X002548 on 09/18/2007 12:35:42
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:37:38  Show Profile  Reply with Quote
quote:
Originally posted by rockingdesi

quote:
Originally posted by X002548

OH, OK, you eliminate where the PK's exists, but what if they don't exits and Core has dup's like your other post

What if there are dups in Core. What do you want to do about that

I DONT CARE ABOUT DUPLICATES IN THE CORE TABLE.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam









You have to care about dups

What does this produce


                   SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF i2
		    WHERE o.ACCT_NUM_MIN = i2.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i2.ACCT_NUM_MAX 
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) > 1



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:37:52  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
Hi Brett,

It is giving me an error

Explicit value must be specified for identity column in table 'CRF' when IDENTITY_INSERT is set to ON.


quote:
Originally posted by X002548

Try this


SET IDENTITY_INSERT CRF ON
INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE) 
SELECT UID 
	, LEFT(ACCT_NUM_MIN,16)
	, LEFT(ACCT_NUM_MAX,16)
	, BIN
	, BUS_ID
	, BUS_NM
	, ISO_CTRY_CD
	, REGN_CD
	, PROD_TYPE_CD
	, CARD_TYPE 
  FROM CORE o 
 WHERE NOT EXISTS (SELECT * FROM CRF i1
		    WHERE o.ACCT_NUM_MIN = i1.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i1.ACCT_NUM_MAX)
       AND EXISTS (SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF i2
		    WHERE o.ACCT_NUM_MIN = i2.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i2.ACCT_NUM_MAX 
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) = 1)
  WHERE NOT EXISTS (SELECT * FROM CRF i3
		    WHERE o.UID = i3.UID)


SET IDENTITY_INSERT CRF OFF



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam






Edited by - rockingdesi on 09/18/2007 12:43:35
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:40:08  Show Profile  Reply with Quote
What is table DPT_CRF?

Is there a trigger on CRF?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:40:33  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
Hi Brett,

What is o here?




quote:
Originally posted by X002548

quote:
Originally posted by rockingdesi

quote:
Originally posted by X002548

OH, OK, you eliminate where the PK's exists, but what if they don't exits and Core has dup's like your other post

What if there are dups in Core. What do you want to do about that

I DONT CARE ABOUT DUPLICATES IN THE CORE TABLE.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam









You have to care about dups

What does this produce


                   SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF i2
		    WHERE o.ACCT_NUM_MIN = i2.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i2.ACCT_NUM_MAX 
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) > 1



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:42:33  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
DPT_CRF is same as the table CRF....i was referring it as CRF instead of DPT_CRF....

DPT_CRF = CRF...both are same....just the names are different


quote:
Originally posted by X002548

What is table DPT_CRF?

Is there a trigger on CRF?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:50:48  Show Profile  Reply with Quote
I'm wondering how that's possible

http://support.microsoft.com/kb/908711

Do you have replication?

I mean CORE has values UID right?

What does SELECT * FROM CORE WHERE UID IS NULL return

should be

(0) rows returned



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  12:52:51  Show Profile  Reply with Quote
quote:
Originally posted by rockingdesi

DPT_CRF is same as the table CRF....i was referring it as CRF instead of DPT_CRF....

DPT_CRF = CRF...both are same....just the names are different





Confusing things doesn't help us much

Are you changing the code



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000