in need of advise to get best performance on a qry

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)

in need of advise to get best performance on a qry

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2011-04-21 : 13:00:07

I have a bunch of tables I need to check to see if items exist before I allow a delete.

Here is the current queries I have.. I am looking for the most efficient way to accomplish this.
Is there a way to thread this process?

Is there a beter way to structure the queries?

the time results are below each query..

thanks guys!




		DECLARE @BIT    BIT
		SET @BIT = 0

		IF @BIT = 0 AND EXISTS ( SELECT 1 AS Error_Ind
					FROM Person_Main 
					WHERE Company_Cd = @cCompany_Cd
					  AND Division_Cd = @cDivision_Cd
					  AND MajorFunction_Cd = @cMajorFunction_Cd
					  AND Department_Cd = @cDepartment_Cd )
			SET @BIT = 1

		PRINT 'Person_Main'
		SELECT dbo.udf_TimeDiff( @NOW )
		SET @NOW = GETDATE()

Person_Main
------------------------------------------------------------
Completed Task in 00:00:00:000 hh:mi:ss:mmm(24h)

		IF EXISTS ( SELECT 1 AS Error_Ind
					FROM Person_PayrollLaborDistribution PPLD
					INNER JOIN Person_Main PM
							ON PM.Employee_Cd = PPLD.Employee_Cd
					WHERE PM.Company_Cd = @cCompany_Cd
					  AND PPLD.Division_Cd = @cDivision_Cd
					  AND PPLD.BusniessUnit_Cd = @cMajorFunction_Cd
					  AND PPLD.Department_Cd = @cDepartment_Cd )
			SET @BIT = 1

		PRINT 'Person_PayrollLaborDistribution'
		SELECT dbo.udf_TimeDiff( @NOW )
		SET @NOW = GETDATE()

Person_PayrollLaborDistribution
------------------------------------------------------------
Completed Task in 00:00:00:000 hh:mi:ss:mmm(24h)


		IF EXISTS ( SELECT 1 AS Error_Ind
					FROM Company_WTCHardwareSetup
					WHERE Company_Cd = @cCompany_Cd
					  AND TerminalDivision_Cd = @cDivision_Cd
					  AND TerminalMajorFunction_Cd = @cMajorFunction_Cd
					  AND TerminalDepartment_Cd = @cDepartment_Cd )
			SET @BIT = 1

		PRINT 'Company_WTCHardwareSetup'
		SELECT dbo.udf_TimeDiff( @NOW )
		SET @NOW = GETDATE()

Company_WTCHardwareSetup
------------------------------------------------------------
Completed Task in 00:00:00:013 hh:mi:ss:mmm(24h)

		IF EXISTS ( SELECT 1 AS Error_Ind
					FROM Person_ChecksToPrint
					WHERE Company_Cd = @cCompany_Cd
					  AND Division_Cd = @cDivision_Cd
					  AND BusinessUnit_Cd = @cMajorFunction_Cd
					  AND Department_Cd = @cDepartment_Cd )
			SET @BIT = 1

		PRINT 'Person_ChecksToPrint'
		SELECT dbo.udf_TimeDiff( @NOW )
		SET @NOW = GETDATE()

Person_ChecksToPrint
------------------------------------------------------------
Completed Task in 00:00:00:000 hh:mi:ss:mmm(24h)


		IF EXISTS ( SELECT 1 AS Error_Ind
					FROM Person_Charges 
					WHERE Company_Cd = @cCompany_Cd
					  AND Division_Cd = @cDivision_Cd
					  AND BusinessUnit_Cd = @cMajorFunction_Cd
					  AND Department_Cd = @cDepartment_Cd )
			SET @BIT = 1

		PRINT 'Person_Charges'
		SELECT dbo.udf_TimeDiff( @NOW )
		SET @NOW = GETDATE()

Person_Charges
------------------------------------------------------------
Completed Task in 00:00:00:017 hh:mi:ss:mmm(24h)

		IF EXISTS ( SELECT 1 AS Error_Ind
					FROM Earnings E WITH ( NOLOCK ) 
					INNER JOIN Person_Main PM
							ON PM.Employee_Cd = E.Employee_Cd
					WHERE PM.Company_Cd = @cCompany_Cd
					  AND E.Division_Cd = @cDivision_Cd
					  AND E.BusinessUnit_Cd = @cMajorFunction_Cd
					  AND E.Department_Cd = @cDepartment_Cd )              
			SET @BIT = 1

		PRINT 'Earnings'
		SELECT dbo.udf_TimeDiff( @NOW )
		SET @NOW = GETDATE()

Earnings
------------------------------------------------------------
Completed Task in 00:00:00:140 hh:mi:ss:mmm(24h)

		IF EXISTS ( SELECT 1 AS Error_Ind
					FROM Person_CheckHistory WITH ( NOLOCK ) 
					WHERE Company_Cd = @cCompany_Cd
					  AND Division_Cd = @cDivision_Cd
					  AND BusinessUnit_Cd = @cMajorFunction_Cd
					  AND Department_Cd = @cDepartment_Cd )              
			SET @BIT = 1

		PRINT 'Person_CheckHistory'
		SELECT dbo.udf_TimeDiff( @NOW )
		SET @NOW = GETDATE()

Person_CheckHistory
------------------------------------------------------------
Completed Task in 00:00:15:830 hh:mi:ss:mmm(24h)

		IF EXISTS ( SELECT 1 AS Error_Ind
					FROM Person_PaymentHistory PPH WITH ( NOLOCK )
					INNER JOIN Person_Main PM
							ON PM.Employee_Cd = PPH.Employee_Cd
					WHERE PM.Company_Cd = @cCompany_Cd
					  AND PPH.Division_Cd = @cDivision_Cd
					  AND PPH.BusinessUnit_Cd = @cMajorFunction_Cd
					  AND PPH.Department_Cd = @cDepartment_Cd )
			SET @BIT = 1

		PRINT 'Person_PaymentHistory'
		SELECT dbo.udf_TimeDiff( @NOW )
		SET @NOW = GETDATE()

Person_PaymentHistory
------------------------------------------------------------
Completed Task in 00:00:30:453 hh:mi:ss:mmm(24h)


		IF EXISTS ( SELECT 1 AS Error_Ind
					FROM TimePunches TP WITH ( NOLOCK )
					INNER JOIN Person_Main PM
							ON PM.Employee_Cd = TP.Employee_Cd
					WHERE PM.Company_Cd = @cCompany_Cd
					  AND TP.Division_Cd = @cDivision_Cd
					  AND TP.MajorFunction_Cd = @cMajorFunction_Cd
					  AND TP.Department_Cd = @cDepartment_Cd )
			SET @BIT = 1

		PRINT 'TimePunches'
		SELECT dbo.udf_TimeDiff( @NOW )
		SET @NOW = GETDATE()
TimePunches
------------------------------------------------------------
Completed Task in 00:00:06:453 hh:mi:ss:mmm(24h)