Stored Procedure

Question

Stored Procedure

eslam.amgad

SSC Veteran

Points: 210
More actions
February 7, 2010 at 2:36 am

#218921

Dear all,
My Stored Procedure exec is taking about 45 sec. to insert the data in the table.
Is there a way to improve this??
IF NOT EXISTS(SELECT * FROM Solution where SOL_ArVoc=@ar_voc and SOL_Lemmaid=@lemm_id and SOL_Voc=@voc and SOL_Gloss=@gloss and
SOL_Pr1=@pr1 and SOL_Pr2=@pr2 and SOL_Pr3=@pr3 and SOL_Stem=@stem and
SOL_Suf1=@suf1 and SOL_Suf2=@suf2 and SOL_Gen=@gen and SOL_Num=@num and SOL_Def=@def and
SOL_Case=@case and SOL_ArStem=@ar_stem and SOL_Root=@root and SOL_StmPattern=@sol_stmptrn)
BEGIN
insert into Solution (SOL_ArVoc,SOL_Lemmaid,SOL_Voc,SOL_Gloss,SOL_Pr1,SOL_Pr2,SOL_Pr3,SOL_Stem,
SOL_Suf1,SOL_Suf2,SOL_Gen,SOL_Num,SOL_Def,SOL_Case,SOL_ArStem,SOL_Root,SOL_StmPattern)
values (@ar_voc,@lemm_id,@voc,@gloss,@pr1,@pr2,@pr3,@stem,@suf1,@suf2,@gen,@num,@def,@case,@ar_stem,@root,@sol_stmptrn)
END
Note: The Table contains 2,900,000 records
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Do you really have to check every single columns. Can't you do an exists check just on the primary key?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

eslam.amgad SSC Veteran Points: 210 More actions · Answer 2

Do you really have to check every single columns. Can't you do an exists check just on the primary key?

Yes, I have to check every column :-D, because all the record may vary in only single column.

The table definition:

USE [Corpus]

GO

/****** Object: Table [dbo].[Solution] Script Date: 02/07/2010 13:28:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Solution](

[SOL_ID] [int] IDENTITY(1,1) NOT NULL,

[SOL_ArVoc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Lemmaid] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Voc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Gloss] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Pr1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Pr2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Pr3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Stem] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Suf1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Suf2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Gen] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Num] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Def] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Case] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_ArStem] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_Root] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SOL_WRD_ID] [int] NULL,

[SOL_USR_ID] [int] NULL,

[SOL_StmPattern] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_Solution] PRIMARY KEY CLUSTERED

(

[SOL_ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Index

USE [Corpus]

GO

/****** Object: Index [IX_Solution_Wrd_Id] Script Date: 02/07/2010 13:29:15 ******/

CREATE NONCLUSTERED INDEX [IX_Solution_Wrd_Id] ON [dbo].[Solution]

(

[SOL_WRD_ID] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

The Execution Plan is attached.

Thanks in advance

LutzM SSC Guru Points: 107049 More actions · Answer 3

Is the insert statement the only statement in your proc?

Or are you using a cursor by any chance?

Would you mind sharing the overall concept of your procedure as well as the code around the statement you posted (if it's a cursor or a while loop).

The code performs basically a table scan each time that line of code is called.

So, it's important to know how often that line is called within your proc.

If you need to insert more than one single row I'd probably use a temp table and a left outer join.

It also might help to have an additional index on that table on one or two highly selective columns that also are part of your existence check. This might turn your table scan into an index seek together with a bookmark lookup.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

eslam.amgad (2/7/2010)
Yes, I have to check every column :-D, because all the record may vary in only single column.

So you've actually got no unique column or set of columns that identifies the table? That's generally a sign of poor DB design.

The problem is that to do a not exists check that uses every single column, SQL almost has to do a clustered index (table) scan. Without a column of set of columns that identifies a row, it's going to be hard to get around that. I suspect there's also blocking from other insert queries that are occurring.

You might get some gain if you can index a very selective (almost unique) set of columns, but I won't promise anything.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

Is this part of a data migration / ETL process?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

My recommendation would be to put the rows to be tested for insert into a temp table with an extra "Flag" (TINYINT) column. Do a joined update between the temp table and the final target table to update the FLAG column to indicate whether the row should be inserted or not. Then, use the flag in the WHERE clause of the insert. It should be a bit faster. Just keep in mind that not all of the columns that you're trying to equate are necessarily covered by an index which means that you'll never get "nearly instant" performance out of this process.

Also keep in mind that the more indexes you have, the longer any insert will take especially if the indexes aren't in the same "order" as the inserts. Depending on the FILL FACTOR of each index, the index may have to split off new extents which takes comparatively long.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eslam.amgad SSC Veteran Points: 210 More actions · Answer 7

Thank you all for your great help, I solved the problem from my application.

I compared the new record, which I want to insert in my table, with some selected data(which I want to compare my new record with, that might be simmilar to this new record)

and if the record is new then I will insert it to the table, which will not take more than 1 sec.

Thank you all for your help and have a nice CODING 🙂

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

I wonder if the OP realizes that he/she is open to still putting in a record that already exists without proper transactioning/locking, especially if they do it in a disconnected manner (i.e.getting data, comparing data, inserting if not found).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

TheSQLGuru (2/8/2010)
I wonder if the OP realizes that he/she is open to still putting in a record that already exists without proper transactioning/locking, especially if they do it in a disconnected manner (i.e.getting data, comparing data, inserting if not found).

eslam.amgad (2/8/2010)
Thank you all for your great help, I solved the problem from my application.
I compared the new record, which I want to insert in my table, with some selected data(which I want to compare my new record with, that might be simmilar to this new record)
and if the record is new then I will insert it to the table, which will not take more than 1 sec.
Thank you all for your help and have a nice CODING 🙂

It won't matter.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden