very slow insertion - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

very slow insertion

Author

Topic

raniait
Starting Member

14 Posts

Posted - 2003-11-09 : 06:48:12

CREATE TABLE [dbo].[Article_Index] (
[Doc_ID] [int] NULL ,
[TermID] [int] NULL ,
[tf] [int] NULL ,
[Seq] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Article_Info] (
[Doc_ID] [int] NULL ,
[Doc_ParentID] [int] NULL ,
[Doc_bpath] [varbinary] (50) NULL ,
[Doc_Title] [varchar] (200) COLLATE Arabic_CI_AS NULL ,
[Doc_Link] [varchar] (100) COLLATE Arabic_CI_AS NULL ,
[Doc_Author] [int] NULL ,
[Doc_Desc] [varchar] (500) COLLATE Arabic_CI_AS NULL ,
[Doc_Date] [datetime] NULL ,
[Doc_Depth] [int] NULL ,
[Doc_Path] [varchar] (300) COLLATE Arabic_CI_AS NULL ,
[Doc_IsLeaf] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Article_Terms] (
[TermID] [int] NULL ,
[Term] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[idf] [float] NULL
) ON [PRIMARY]
GO

I have 6000 articles ,I made a tool to insert every word in every article in a record

The tool runs fast but when the table grows large it becomes very slow

I am thinking of the indexing but i don't know what type can suit my processes which are

1-Selection to know if the word exist or not
2-Insertion if it doesn't exist
3-Update the counter of the word if it exists

and on which filed I put them to obtain maxuimum performance

The tables I used are listed above

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-09 : 10:57:18

You need to post the queries you are using - hopefully stored procedures for best performance and easiest analysis.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-09 : 11:29:57

rania, looks like you are building a search engine of sorts for a document management system. This sort of functionality is already present with Full Text Indexing in SQL Server or linked server queries to MS Index Server. You might want to investigate these two, they just might suit your purpose, and save you the headache of having to re-invent the wheel.

Also, indexing is double-edged sword. While it can substantially speed up SELECT operations, it can slow down INSERT, UPDATE and DELETE operations. Which is why you really need to weigh the trade-off, and ensure that you are actually getting a positive impact from adding the indexes. Like nr suggests, post the relevant bit of your code, and we might be able to optimise the code and suggest some appropriate indexes.

Owais

Where there's a will, I want to be in it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources