How Can I improve following stored procedure

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)

How Can I improve following stored procedure

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-02-15 : 02:26:00

My sp hangs my system

What can i do to improve this procedure

SELECT dbo.Job.JobNumber, dbo.Job.Email, dbo.Job.Title, dbo.Job.SalaryLow, dbo.Job.NumberOfOpenings, dbo.Job.SalaryHigh, dbo.Job.JobCode,
dbo.Job.Experience, dbo.Job.Description, dbo.Job.KeyWords, dbo.Job.ZipCode, dbo.Job.EmploymentTypeId,
CASE Job.EducationID WHEN 1 THEN '12' WHEN 2 THEN '18' WHEN 3 THEN '16' ELSE '' END AS Education, dbo.Job.CompanyDescription,
dbo.Job.CompanyName, dbo.Job.ContactName, dbo.Job.HomePageURL, dbo.Job.HideContactName, dbo.Job.HideContactPhone,
dbo.Job.HideContactFax, dbo.Job.HideAddress, dbo.Job.ContactAddr1, dbo.Job.ContactAddr2, dbo.Job.ContactCity, dbo.Job.ContactState,
dbo.Job.ContactPostal, dbo.Job.ContactPhone, dbo.Job.ContactFax, dbo.ZipCode.State AS JobState, dbo.ZipCode.City AS JobCity, dbo.ZipCode.Country,
dbo.JobBoardCategoryXref.JobBoardCategory, dbo.JobBoardSalaryUnitXref.JobBoardSalaryUnit,
dbo.JobBoardEmpTypeXref.JobBoardEmploymentType, dbo.Job.HideApplyOnline, dbo.Job.HideCompany, dbo.Job.ModifiedDate AS JobModifiedDate,
dbo.Job.CreatedDate AS JobCreatedDate, dbo.Branch.BranchStatusId, dbo.Job.CompanyId, dbo.Job.SourceId, dbo.JobDist.ofID, dbo.Job.BranchId
FROM dbo.Job INNER JOIN
dbo.JobBoardCategoryXref ON dbo.Job.CategoryId = dbo.JobBoardCategoryXref.CategoryId INNER JOIN
dbo.Branch ON dbo.Job.BranchId = dbo.Branch.BranchId INNER JOIN
dbo.JobBoardSalaryUnitXref ON dbo.Job.SalaryUnitId = dbo.JobBoardSalaryUnitXref.SalaryUnitId INNER JOIN
dbo.JobBoardEmpTypeXref ON dbo.Job.EmploymentTypeId = dbo.JobBoardEmpTypeXref.EmploymentTypeId INNER JOIN
dbo.JobDist ON dbo.Job.JobNumber = dbo.JobDist.JobNumber INNER JOIN
dbo.ZipCode ON dbo.Job.ZipCode = dbo.ZipCode.ZipCode
WHERE (dbo.JobDist.JobBoardId = 6) AND (dbo.Job.JobStatusId = 1) AND (dbo.JobBoardCategoryXref.JobBoardId = 6) AND
(dbo.JobBoardSalaryUnitXref.JobBoardId = 6) AND (dbo.JobBoardEmpTypeXref.JobBoardId = 6) AND (dbo.Branch.BranchStatusId IN (1, 2, 3)) AND
(dbo.Job.CompanyId <> 20765) AND (dbo.Job.SourceId <> '44') AND (NOT (dbo.Job.BranchId IN (23611, 23548, 23372, 23379, 23473, 23489, 23542,
23581)))
ORDER BY dbo.Job.ModifiedDate DESC, dbo.Branch.BranchStatusId, dbo.Job.JobNumber DESC
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DefaultView', @value=0x02 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'xpAJB'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1[50] 4[25] 3) )"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1[50] 2[25] 3) )"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1 [56] 4 [18] 2))"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1[75] 4) )"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 9
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "Job"
Begin Extent =
Top = 6
Left = 38
Bottom = 483
Right = 253
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "JobBoardCategoryXref"
Begin Extent =
Top = 125
Left = 756
Bottom = 218
Right = 926
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "Branch"
Begin Extent =
Top = 72
Left = 569
Bottom = 473
Right = 752
End
DisplayFlags = 280
TopColumn = 21
End
Begin Table = "JobBoardSalaryUnitXref"
Begin Extent =
Top = 388
Left = 750
Bottom = 481
Right = 924
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "JobBoardEmpTypeXref"
Begin Extent =
Top = 274
Left = 518
Bottom = 367
Right = 725
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "JobDist"
Begin Extent =
Top = 4
Left = 415
Bottom = 120
Right = 566
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "ZipCode"
Begin Extent =
Top = 197
Left = 333
Bottom = 305
Right = 511
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'xpAJB'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
PaneHidden =
End
Begin DataPane =
PaneHidden =
Begin ParameterDefaults = ""
End
RowHeights = 220
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 2280
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 2220
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'xpAJB'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'xpAJB'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Filter', @value=N'((xpAJB.SourceId="33"))' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'xpAJB'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderBy', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'xpAJB'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOn', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'xpAJB'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'xpAJB'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TableMaxRecords', @value=10000 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'xpAJB'

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-15 : 02:36:41

first step would be to format it properly. 2nd thing you could do is tell us a little about what it is supposed to do too. 3rd you could tell us what problems you are having with this current solution.

-ec

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-15 : 02:47:41

You have to help us understand your problem here.
1. use the [ code ] your query here [ /code ] tag (without the space) to format your query here.
2. Explain what is the query doing
3. Provide sample data & expected result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources