Slow SQL 2000 - 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 Administration (2000)

Slow SQL 2000

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2008-01-14 : 09:32:17

Hi Everyone

I have a VB app using SQL 2000 to store data. It's been working fine while we only had about 10 000 records in the main table [indexdata]. Now we're close to 5 million and things are slowing down...

I have 2 tables. Users enter a bacode number and I do a lookup in [autoindex] for the data. When I get it, I fill 7 text boxes with the data. They check it, add more info to it and then I save the record to [indexdata].

[AutoIndex] has 2.5 million records.
[IndexData] has 5 million records.

The first barcode scan queries the table using this query:
select field1, field2, field3, field4, field5, field6, field7, subcoid, companyid from autoindex where subcoid = 123 and companyid = 321 and field1 = [the barcode that was scanned]. Field1 - Field7 are nvarchar(200) fields. The rest are int.

This query takes about 1 second to execute over the LAN and to fill the text boxes.

Then once the user entered all of the data, I do an insert to add the new record to [indexdata].

The insert query looks like this:
CREATE PROCEDURE InsertNewIndexRecord
(@ID int output,
@TotalRecords int output,
@containerid AS int,
@indexfield1 as nvarchar (255),
@indexfield2 as nvarchar (255),
@indexfield3 as nvarchar (255),
@indexfield4 as nvarchar (255),
@indexfield5 as nvarchar (255),
@indexfield6 as nvarchar (255),
@indexfield7 as nvarchar (255),
@dod as datetime,
@fileno as nvarchar (9),
@userid as int,
@indexorigin as int,
@indextype as int,
@companyid as int,
@subcoid as int,
@filestatus as int,
@deptid as int,
@levelid as int,
@indextime as int,
@ObjectTypeID as int,
@ActivityTypeID as int,
@WaitForContainerWHScan AS int, @InsertStatus int Output
)

AS

declare @ExistingIndexID as int
set @ExistingIndexID = 0
set @ExistingIndexID = (select COUNT(*) from indexdata where fileno = @fileno and fileno <> 'F00000000')

if @ExistingIndexID <> 0
BEGIN
set @InsertStatus = 1 --item exists and will not be added
SET @ID = 0
END

IF @ExistingIndexID = 0
begin
set @InsertStatus = 0 --item will be added to database
INSERT INTO indexdata
(containerid, indexfield1, indexfield2, indexfield3, indexfield4, indexfield5,
indexfield6, indexfield7, dod, fileno, userid, indexdate, indexorigin, indextype, companyid, subcoid, filestatus, deptid, levelid, indextimeid, ObjectTypeID, ActivityTypeID, WaitForContainerWHScan, dodapproval)
VALUES
(@containerid, @indexfield1, @indexfield2, @indexfield3, @indexfield4, @indexfield5,
@indexfield6, @indexfield7, @dod, @fileno, @userid, getdate(), @indexorigin, @indextype, @companyid, @subcoid, @filestatus, @deptid, @levelid, @indextime, @ObjectTypeID, @ActivityTypeID, @WaitForContainerWHScan, 0)

set @TotalRecords = (select count(*) from indexdata where containerid = @containerid and filestatus = 1)

SET @ID = SCOPE_IDENTITY()
end
GO

The INSERT takes between 1 and 2 seconds.

Are these times realistic? It used to be much faster when I had 10 000 records instead of 5 million. It used to be instant before.

I don't know how to use indexes and I don't know which columns I should create indexes for. Will Indexes speed things up?

My server has a Duel 2.8GHZ Intel Zeon with 3GB RAM and tons of disk space - should I upgrade the server? SQL is configured to use as much memory as it requires, so my server is always using 3GB of the 3GB - I've decreased SQL's memory usage to 1.5 or 2GB before but it didn't make a diff.

thanks

D.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-14 : 11:48:40

Put an index on fileno in IndexData table and another computed index on ContainerID, filestatus. You should get the value from SCOPE_IDENTITY immediately after the INSERT.
To simplify your code:



CREATE PROCEDURE InsertNewIndexRecord
(@ID int output,
@TotalRecords int output, 
@containerid AS int,
@indexfield1 as nvarchar (255),
@indexfield2 as nvarchar (255),
@indexfield3 as nvarchar (255),
@indexfield4 as nvarchar (255),
@indexfield5 as nvarchar (255),
@indexfield6 as nvarchar (255),
@indexfield7 as nvarchar (255),
@dod as datetime,
@fileno as nvarchar (9),
@userid as int,
@indexorigin as int,
@indextype as int,
@companyid as int,
@subcoid as int,
@filestatus as int,
@deptid as int,
@levelid as int,
@indextime as int,
@ObjectTypeID as int,
@ActivityTypeID as int,
@WaitForContainerWHScan AS int, @InsertStatus int Output
)

AS
BEGIN
	Set @InsertStatus = 0
	IF EXISTS (SELECT * FROM indexdata WHERE fileno = @fileno AND fileno <> 'F00000000')
		SET @InsertStatus = 1 --item exists and will not be added
	ELSE
		BEGIN
			INSERT INTO indexdata
				(containerid, indexfield1, indexfield2, indexfield3, indexfield4, indexfield5,indexfield6, indexfield7, dod, fileno, 
				userid, indexdate, indexorigin, indextype, companyid, subcoid, filestatus, deptid, levelid, indextimeid, 
				ObjectTypeID, ActivityTypeID, WaitForContainerWHScan, dodapproval) 
			VALUES 
				(@containerid, @indexfield1, @indexfield2, @indexfield3, @indexfield4, @indexfield5,@indexfield6, @indexfield7, @dod, @fileno, 
				@userid, getdate(), @indexorigin, @indextype, @companyid, @subcoid, @filestatus, @deptid, @levelid, @indextime, 
				@ObjectTypeID, @ActivityTypeID, @WaitForContainerWHScan, 0)
			SET @ID = SCOPE_IDENTITY()
		END

	SELECT @TotalRecords = count(*) FROM indexdata WHERE containerid = @containerid and filestatus = 1

END
GO

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-14 : 13:22:24

what kind of a superhero are you when a few measly missing indexes bring you down?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

superhero
Yak Posting Veteran

52 Posts

Posted - 2008-01-15 : 06:23:14

Thanks Dinakar, I did what you suggested and now those 2 queries execute instantly - no more 1 or 2 second pauses.

Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources