Help optimizing query - 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

Transact-SQL (2000)

Help optimizing query

Author

Topic

elmonty
Starting Member

4 Posts

Posted - 2006-12-01 : 10:41:17

I have a Users table:

CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[LoginID] [varchar] (10) NOT NULL ,
[OldLmsID] [int] NULL ,
[NewLmsID] [int] NULL ,
[FirstName] [varchar] (35) NOT NULL ,
[LastName] [varchar] (35) NOT NULL ,
[DealerCode] [char] (9) NULL ,
[Status] [char] (1) NOT NULL
) ON [PRIMARY]

I am importing new records into this table. The new records are in a table called ImportStudents:

CREATE TABLE [dbo].[ImportStudents] (
[OldLmsID] [int] NULL ,
[NewLmsID] [int] NULL ,
[LoginID] [varchar] (10) NOT NULL ,
[FirstName] [varchar] (35) NOT NULL ,
[LastName] [varchar] (35) NOT NULL ,
[DealerCode] [varchar] (9) NULL ,
[Status] [char] (1) NULL
) ON [PRIMARY]

Here's how the import works. I need to add rows from ImportStudents where neither the OldLmsID or the NewLmsID are already in the Users table. Here's what I cam up with:

INSERT INTO Users (OldLmsID, NewLmsID, LoginID, FirstName,LastName,DealerCode,Status)
SELECT DISTINCT i.OldLmsID, i.NewLmsID, i.LoginID, i.FirstName, i.LastName, i.DealerCode, i.Status
FROM ImportStudents i
LEFT JOIN Users u
ON (i.OldLmsID=u.OldLmsID)
OR (i.NewLmsID=u.NewLmsID)
WHERE u.UserID IS NULL;

The ImportStudents table has 15,000 rows. This query takes so long that I have to abort it. I have run the Index tuning Wizard, but it hasn't helped. I have indexes on OldLmsID and NewLmsID in both tables, but SQL Server is still scanning the tables to do the join. There are a lot of NULLs in the OldLmsID and NewLmsID columns; I don't know if that's part of the problem.

Any suggestions?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-01 : 10:47:22

Either this:

INSERT INTO Users (OldLmsID, NewLmsID, LoginID, FirstName,LastName,DealerCode,Status)
SELECT DISTINCT i.OldLmsID, i.NewLmsID, i.LoginID, i.FirstName, i.LastName, i.DealerCode, i.Status
FROM ImportStudents i
Where not exists (Select * from Users u where i.OldLmsID=u.OldLmsID or i.NewLmsID=u.NewLmsID)

create table #temp
(
	OldLmsID int, 	
	NewLmsID int
)

insert into #temp
select OldLmsID, NewLmsID
from users

INSERT INTO Users (OldLmsID, NewLmsID, LoginID, FirstName,LastName,DealerCode,Status)
SELECT DISTINCT i.OldLmsID, i.NewLmsID, i.LoginID, i.FirstName, i.LastName, i.DealerCode, i.Status
FROM ImportStudents i
LEFT JOIN #temp t
on i.OldLmsID = t.OldLmsID
or i.NewLmsID = t.NewLmsID
where t.OldLmsID is Null or t.NewLmsID is Null

Harsh Athalye
India.
"Nothing is Impossible"

elmonty
Starting Member

4 Posts

Posted - 2006-12-01 : 10:52:59

Wow, much better, thanks.

elmonty
Starting Member

4 Posts

Posted - 2006-12-01 : 11:10:46

Oops, now I have the same problem with another step: updating rows in Users from matching rows in ImportStudents:

UPDATE Users
SET NewLmsID=i.NewLmsID,
LoginID=i.LoginID,
FirstName=i.FirstName,
LastName=i.LastName,
DealerCode=i.DealerCode,
Status=i.Status
FROM Users,ImportStudents i
WHERE Users.OldLmsID=i.OldLmsID
OR Users.NewLmsID=i.NewLmsID;

This step is actually performed before the INSERT, but I didn't have a problem with it until I added 15000 rows to the Users table.

Kristen
Test

22859 Posts

Posted - 2006-12-01 : 12:31:45

"I didn't have a problem with it until I added 15000 rows to the Users table"

Try updating the statistics?

Or use the temporary table style from Harsh's example above.

Kristen

elmonty
Starting Member

4 Posts

Posted - 2006-12-01 : 16:10:23

I got somewhat better performance by splitting the update into two:

UPDATE Users
SET LoginID=i.LoginID,
FirstName=i.FirstName,
LastName=i.LastName,
DealerCode=i.DealerCode,
Status=i.Status
FROM Users,ImportStudents i
WHERE Users.NewLmsID IS NOT NULL
AND Users.NewLmsID=i.NewLmsID;

UPDATE Users
SET NewLmsID=i.NewLmsID,
LoginID=i.LoginID,
FirstName=i.FirstName,
LastName=i.LastName,
DealerCode=i.DealerCode,
Status=i.Status
FROM Users,ImportStudents i
WHERE Users.OldLmsID=i.OldLmsID;

This can potentially update some records twice, but for some reason it's still faster than the original single query. With 15336 rows, this took about 5 seconds, where I had to abort the original after 2 minutes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources