Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Optimizing query

Author  Topic 

rockingdesi
Starting Member

31 Posts
Posted - 2007-09-18 : 11:59:03
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 - 2007-09-18 : 12:02:17
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
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-09-18 : 12:05:34
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 - 2007-09-18 : 12:07:15
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
Go to Top of Page

rockingdesi
Starting Member

31 Posts
Posted - 2007-09-18 : 12:10:02
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-09-18 : 12:16:09
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 - 2007-09-18 : 12:16:51
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

31 Posts
Posted - 2007-09-18 : 12:22:32
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 - 2007-09-18 : 12:27:44
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

31 Posts
Posted - 2007-09-18 : 12:29:15
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 - 2007-09-18 : 12:29:19
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

31 Posts
Posted - 2007-09-18 : 12:31:18
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

31 Posts
Posted - 2007-09-18 : 12:32:25
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 - 2007-09-18 : 12:33:03
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-09-18 : 12:37:38
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

31 Posts
Posted - 2007-09-18 : 12:37:52
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-09-18 : 12:40:08
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

31 Posts
Posted - 2007-09-18 : 12:40:33
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

31 Posts
Posted - 2007-09-18 : 12:42:33
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 - 2007-09-18 : 12:50:48
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 - 2007-09-18 : 12:52:51
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
  Previous Page&nsp;  Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site