SQL statment speedn - 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)

SQL statment speedn

Author

Topic

clmowers
Starting Member

3 Posts

Posted - 2007-12-08 : 11:26:19

Can anyone tell me how i can speed this code up. im still new to SQL and this is the only way i know how to do it. Right now its taking about 48-62 secs to run and its times out when attached to my web page


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

/*
Name:  usp_delete
Description: After delete computer from the website, It leaves all the information for those computers in the rest of the 
tables. This procedure will delete the rest of the information by comparing it to the computer table. If the computer
is not in the computers table, it will delete that infomration. 
Author:  Chris Mowers
Modification Log: Change

Description                  Date          Changed By
Created procedure            10/29/2007    Chris Mowers
Added statements			 10/30/2007    Chris Mowers
Added Statements             10/31/2007    Chris Mowers
*/

ALTER PROCEDURE [dbo].[usp_delete]

AS
 

-- This will delete from the tblautorun table after it compares it to the computers table
Delete FROM tblautorun 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblautorun.computername = tblcomputers.computername  );

-- This will delete from the tblprocesses table after it compares it to the computers table
Delete FROM tblprocesses 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblprocesses.computername = tblcomputers.computername  );

-- This will delete from the tblsoftware  table after it compares it to the computers table
Delete FROM tblsoftware 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblsoftware.computername = tblcomputers.computername  );

-- This will delete from the tblBattery table after it compares it to the computers table
Delete FROM tblBattery 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblBattery.computername = tblcomputers.computername  );

-- This will delete from the tblBIOS table after it compares it to the computers table
Delete FROM tblBIOS 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblBIOS.computername = tblcomputers.computername  );

-- This will delete from the tblBootConfiguration table after it compares it to the computers table
Delete FROM tblBootConfiguration 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblBootConfiguration.computername = tblcomputers.computername  );

-- This will delete from the tblBus table after it compares it to the computers table
Delete FROM tblBus 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblBus.computername = tblcomputers.computername  );

-- This will delete from the tblCDROMDrive table after it compares it to the computers table
Delete FROM tblCDROMDrive 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblCDROMDrive.computername = tblcomputers.computername  );

-- This will delete from the tblCodecFile table after it compares it to the computers table
Delete FROM tblCodecFile 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblCodecFile.computername = tblcomputers.computername  );

-- This will delete from the tblCOMApplication table after it compares it to the computers table
Delete FROM tblCOMApplication 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblCOMApplication.computername = tblcomputers.computername  );

-- This will delete from the tblCommandLineAccess table after it compares it to the computers table
Delete FROM tblCommandLineAccess 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblCommandLineAccess.computername = tblcomputers.computername  );

-- This will delete from the tblComments table after it compares it to the computers table
Delete FROM tblComments 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblComments.computername = tblcomputers.computername  );

-- This will delete from the tblComponentCategory table after it compares it to the computers table
Delete FROM tblComponentCategory 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblComponentCategory.computername = tblcomputers.computername  );

-- This will delete from the tblComputersystem table after it compares it to the computers table
Delete FROM tblComputersystem 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblComputersystem.computername = tblcomputers.computername  );

-- This will delete from the tblComputerSystemProduct table after it compares it to the computers table
Delete FROM tblComputerSystemProduct 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblComputerSystemProduct.computername = tblcomputers.computername  );

-- This will delete from the tblCPlogoninfo table after it compares it to the computers table
Delete FROM tblCPlogoninfo 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblCPlogoninfo.computername = tblcomputers.computername  );

-- This will delete from the tblDCOMApplication table after it compares it to the computers table
Delete FROM tblDCOMApplication 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblDCOMApplication.computername = tblcomputers.computername  );

-- This will delete from the tblDesktop table after it compares it to the computers table
Delete FROM tblDesktop 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblDesktop.computername = tblcomputers.computername  );

-- This will delete from the tblDesktopMonitor table after it compares it to the computers table
Delete FROM tblDesktopMonitor 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblDesktopMonitor.computername = tblcomputers.computername  );

-- This will delete from the tblDiskdrives table after it compares it to the computers table
Delete FROM tblDiskdrives 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblDiskdrives.computername = tblcomputers.computername  );

-- This will delete from the tblDiskPartition table after it compares it to the computers table
Delete FROM tblDiskPartition 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblDiskPartition.computername = tblcomputers.computername  );

-- This will delete from the tblDisplayConfiguration table after it compares it to the computers table
Delete FROM tblDisplayConfiguration 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblDisplayConfiguration.computername = tblcomputers.computername  );

-- This will delete from the tblDisplayControllerConfiguration table after it compares it to the computers table
Delete FROM tblDisplayControllerConfiguration 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblDisplayControllerConfiguration.computername = tblcomputers.computername  );

-- This will delete from the tblDriverForDevice table after it compares it to the computers table
Delete FROM tblDriverForDevice 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblDriverForDevice.computername = tblcomputers.computername  );

-- This will delete from the tblEnvironment table after it compares it to the computers table
Delete FROM tblEnvironment 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblEnvironment.computername = tblcomputers.computername  );

-- This will delete from the tblExtensionInfoAction table after it compares it to the computers table
Delete FROM tblExtensionInfoAction 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblExtensionInfoAction.computername = tblcomputers.computername  );



-- This will delete from the tblFloppy table after it compares it to the computers table
Delete FROM tblFloppy 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblFloppy.computername = tblcomputers.computername  );

-- This will delete from the tblgroups table after it compares it to the computers table
Delete FROM tblgroups 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblgroups.computername = tblcomputers.computername  );

-- This will delete from the tblIDEController table after it compares it to the computers table
Delete FROM tblIDEController 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblIDEController.computername = tblcomputers.computername  );

-- This will delete from the tblIEActiveX table after it compares it to the computers table
Delete FROM tblIEActiveX 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where tblIEActiveX.computername = tblcomputers.computername  );

-- This will delete from the tblIEBars table after it compares it to the computers table
Delete FROM dbo.tblIEBars 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblIEBars.computername = tblcomputers.computername  );

-- This will delete from the tblIEBHO table after it compares it to the computers table
Delete FROM dbo.tblIEBHO 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblIEBHO.computername = tblcomputers.computername  );

-- This will delete from the tblIEExtensions table after it compares it to the computers table
Delete FROM dbo.tblIEExtensions 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblIEExtensions.computername = tblcomputers.computername  );

-- This will delete from the tblInfraredDevice table after it compares it to the computers table
Delete FROM dbo.tblInfraredDevice 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblInfraredDevice.computername = tblcomputers.computername  );

-- This will delete from the tblKeyboard table after it compares it to the computers table
Delete FROM dbo.tblKeyboard 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblKeyboard.computername = tblcomputers.computername  );

-- This will delete from the tblLogicalProgramGroup table after it compares it to the computers table
Delete FROM dbo.tblLogicalProgramGroup 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblLogicalProgramGroup.computername = tblcomputers.computername  );

-- This will delete from the tblNetwork table after it compares it to the computers table
Delete FROM dbo.tblNetwork 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblNetwork.computername = tblcomputers.computername  );

-- This will delete from the tblNetworkClient table after it compares it to the computers table
delete FROM dbo.tblNetworkClient 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblNetworkClient.computername = tblcomputers.computername  );

-- This will delete from the tblOnBoardDevice table after it compares it to the computers table
Delete FROM dbo.tblOnBoardDevice 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblOnBoardDevice.computername = tblcomputers.computername  );

-- This will delete from the tblOperatingsystem table after it compares it to the computers table
Delete FROM dbo.tblOperatingsystem 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblOperatingsystem.computername = tblcomputers.computername  );

-- This will delete from the tblOSRecoveryConfiguration table after it compares it to the computers table
Delete FROM dbo.tblOSRecoveryConfiguration 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblOSRecoveryConfiguration.computername = tblcomputers.computername  );

-- This will delete from the tblPageFile table after it compares it to the computers table
Delete FROM dbo.tblPageFile 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblPageFile.computername = tblcomputers.computername  );

-- This will delete from the tblParallelPort table after it compares it to the computers table
Delete FROM dbo.tblParallelPort 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblParallelPort.computername = tblcomputers.computername  );

-- This will delete from the tblPCMCIAController table after it compares it to the computers table
Delete FROM dbo.tblPCMCIAController 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblPCMCIAController.computername = tblcomputers.computername  );

-- This will delete from the tblPointingDevice table after it compares it to the computers table
Delete FROM dbo.tblPointingDevice 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblPointingDevice.computername = tblcomputers.computername  );

-- This will delete from the tblPortableBattery table after it compares it to the computers table
Delete FROM dbo.tblPortableBattery 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblPortableBattery.computername = tblcomputers.computername  );

-- This will delete from the tblPortConnector table after it compares it to the computers table
Delete FROM dbo.tblPortConnector 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblPortConnector.computername = tblcomputers.computername  );

-- This will delete from the tblPOTSModem table after it compares it to the computers table
Delete FROM dbo.tblPOTSModem 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblPOTSModem.computername = tblcomputers.computername  );

-- This will delete from the tblPrinters table after it compares it to the computers table
Delete FROM dbo.tblPrinters 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblPrinters.computername = tblcomputers.computername  );

-- This will delete from the tblPROCESSOR table after it compares it to the computers table
Delete FROM dbo.tblPROCESSOR 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblPROCESSOR.computername = tblcomputers.computername  );

-- This will delete from the tblProxy table after it compares it to the computers table
Delete FROM dbo.tblProxy 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblProxy.computername = tblcomputers.computername  );

-- This will delete from the tblQuickFixEngineering table after it compares it to the computers table
Delete FROM dbo.tblQuickFixEngineering 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblQuickFixEngineering.computername = tblcomputers.computername  );
-- This will delete from the tblScsicontroller table after it compares it to the computers table
Delete FROM dbo.tblScsicontroller 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblScsicontroller.computername = tblcomputers.computername  );

-- This will delete from the tblRegistry table after it compares it to the computers table
Delete FROM dbo.tblRegistry 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblRegistry.computername = tblcomputers.computername  );

-- This will delete from the tblSerialPort table after it compares it to the computers table
Delete FROM dbo.tblSerialPort 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblSerialPort.computername = tblcomputers.computername  );

-- This will delete from the tblServices table after it compares it to the computers table
Delete FROM dbo.tblServices 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblServices.computername = tblcomputers.computername  );

-- This will delete from the tblShares table after it compares it to the computers table
Delete FROM dbo.tblShares 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblShares.computername = tblcomputers.computername  );

-- This will delete from the tblSoundDevice table after it compares it to the computers table
Delete FROM dbo.tblSoundDevice 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblSoundDevice.computername = tblcomputers.computername  );

-- This will delete from the tblTapeDrive table after it compares it to the computers table
Delete FROM dbo.tblTapeDrive 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblTapeDrive.computername = tblcomputers.computername  );

-- This will delete from the tblTerminal table after it compares it to the computers table
Delete FROM dbo.tblTerminal 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblTerminal.computername = tblcomputers.computername  );

-- This will delete from the tblUSBController table after it compares it to the computers table
Delete FROM dbo.tblUSBController 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblUSBController.computername = tblcomputers.computername  );

-- This will delete from the tblUsers table after it compares it to the computers table
Delete FROM dbo.tblUsers 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblUsers.computername = tblcomputers.computername  );

-- This will delete from the tblUsersInGroup table after it compares it to the computers table
Delete FROM dbo.tblUsersInGroup 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblUsersInGroup.computername = tblcomputers.computername  );

-- This will delete from the tblVideoController table after it compares it to the computers table
Delete FROM dbo.tblVideoController 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.tblVideoController.computername = tblcomputers.computername  );

-- This will delete from the TsysErrors table after it compares it to the computers table
Delete FROM dbo.TsysErrors 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.TsysErrors.computername = tblcomputers.computername  );

-- This will delete from the TsysLastscan table after it compares it to the computers table
Delete FROM dbo.TsysLastscan 
WHERE  NOT EXISTS
  ( select *
     from tblcomputers
     where dbo.TsysLastscan.computername = tblcomputers.computername  );

Thanks for all your help

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-08 : 13:17:18

Looks like you have too many deletes in the proc and if the tables are big it will take a few seconds for each delete.

One way around this is to mark the records as deleted. This can be done in a few ways. You can add a Type/Status column or insert the PK of this table into another "RecordstoDelete" table and have a nightly job run through the table and do the delete then when the load is minimal.

You can do this for bigger tables.

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-08 : 13:47:48

are these tables indexed by the computername column? if not, you are doing scans on each, which can be slow.

also my instinct would be to use a left join rather than a correlated subquery. example:


Delete t
from dbo.TsysLastscan t
left join tblcomputers c on c.computername=t.computername 
where c.computername is null

also consider setting up FKs from each of these tables to tblcomputers with ON DELETE CASCADE. then you woudln't need this proc at all: http://msdn2.microsoft.com/en-us/library/ms186712.aspx

elsasoft.org

clmowers
Starting Member

3 Posts

Posted - 2007-12-10 : 07:56:17

Cool, Thanks allot, I will try each one out and see which performs better. I'm still new to the whole SQL thing, so it get frustrating not know all the odd and ends to SQL
dinakar- How would I get it to run nightly. Does MS SQL Server have a something i can schedule

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-10 : 08:54:46

yes, you could create an agent job and schedule that.

elsasoft.org

clmowers
Starting Member

3 Posts

Posted - 2007-12-10 : 11:53:26

hey, I change the code so it will use


Delete t
from dbo.TsysLastscan t
left join tblcomputers c on c.computername=t.computername 
where c.computername is null

and what do you know, the SP ran in 12 secs. Huge improvement over what i have. Thanks alot, Im going to change the indexing on the tables so that all the computername are now a index. I will let you guys know how it turns out

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-10 : 12:02:10

I love it when a plan comes together.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources