performance tuning for a query

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 2008 Forums

Transact-SQL (2008)

performance tuning for a query

Author

Topic

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2014-08-05 : 16:25:51

Is there anyway to improve performance of below linked query

Delete from [Myserver].db.dbo.z2t where UniqueKey in (Select UniqueKey from #DeleteKeys where CAST(ZipCode AS INT) < 9999 )

This takes more than 2 hrs to execute

ZipCode is a varchar so need to convert to int- this actually doesnot take too much time

#DeleteKeys is a temp tables

the query is a linked query to another server

I have already created a non clustered index on UniqueKey column and after that this takes 2hrs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-05 : 16:28:19

Is ZipCode indexed? Please remove the CAST: Select UniqueKey from #DeleteKeys where ZipCode < '9999'

How many rows match the IN? How many rows are being deleted?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2014-08-05 : 16:36:57

no i have not indexed zipcode in the temporary table
also when i run just the sub query i get the results in 2 - 3 seconds

Select UniqueKey from #DeleteKeys where CAST(ZipCode AS INT) < 9999

there are around 50,000 rows returened from the query and around 15 K will really be deleted.
The problem is that this is a linked query. but the query really should not be taking so long... any idea how i can improve the performance. thanks for helping me out:)

one more update:
UniqueKey is not an int.. its a varchar.. looks like
V110566207
V110566208
V110566209

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-05 : 16:45:06

Well that's the issue with linked servers.

Show us the execution plan and add an index to the temp table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2014-08-05 : 17:02:06

Thanks Tara for helping me out

I dont know how to post an image here but the execution plan still shows a table scan

Delete Cost:0% <- Remote delete Cost:0% <- Table spool(eager spool) Cost:0% <- Hash match (right semi join)Cost:2% <- Table scan(#deleteKeys) Cost:0% <- Remote scan(#deleteKeys) Cost:98%

All arrors are liner excep that the last 2 ( <- Table scan(#deleteKeys) Cost:0% <- Remote scan(#deleteKeys) Cost:98%) both join Hash match (right semi join)Cost:2%

Index that i have created is
CREATE NONCLUSTERED INDEX [z2t_Zip4G0_UniqueKey] ON [dbo].[z2t_Zip4G0]
(
[UniqueKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Also i think u missed my update.. uniquekey is a varchar not int

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-05 : 19:18:42

Try adding an index to the temp table:

create clustered index cdx_UniqueKey on #DeleteKeys(UniqueKey)

If that doesn't help, you may need to import #DeleteKeys into the remote server and run the delete on the remote server instead of remotely.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-08-06 : 15:49:36

[code]

EXEC('IF OBJECT_ID(''tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse'') IS NULL CREATE TABLE tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse ( UniqueKey varchar(20) PRIMARY KEY ) ') AT [Myserver]

EXEC('TRUNCATE TABLE tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse') AT [Myserver]

INSERT INTO [Myserver].tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse
SELECT UniqueKey
FROM #DeleteKeys
WHERE
CAST(ZipCode AS int) < 9999

EXEC('Delete from userdb.dbo.z2t where UniqueKey in (Select UniqueKey from tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse)') AT [Myserver]

EXEC('DROP TABLE tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse') AT [Myserver]

[/code]

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2014-08-12 : 13:34:26

Thanks for this solution.. I have not tried it but looks like THE thing i just needed...

quote:

Originally posted by ScottPletcher



EXEC('IF OBJECT_ID(''tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse'') IS NULL CREATE TABLE tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse ( UniqueKey varchar(20) PRIMARY KEY ) ') AT [Myserver]

EXEC('TRUNCATE TABLE tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse') AT [Myserver]

INSERT INTO [Myserver].tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse
SELECT UniqueKey 
FROM #DeleteKeys
WHERE
    CAST(ZipCode AS int) < 9999
    
EXEC('Delete from userdb.dbo.z2t where UniqueKey in (Select UniqueKey from tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse)') AT [Myserver]

EXEC('DROP TABLE tempdb.dbo.DeleteKeys_UniqueNameThatOnlyIUse') AT [Myserver]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources