Statement Hangs - 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)

Statement Hangs

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-24 : 10:28:26

Whenever I run this sql it jacks up my memory. Why?


			SELECT @SQL = 
			'SELECT     dbo.CJQ.CJQID, dbo.CJQ.JobTitle, dbo.CJQ.Status, dbo.CJQ.Published, dbo.CJQ.PreparedBy, dbo.Users.FirstName, dbo.CJQ.DateLastEdit, dbo.CJQ.ApprovedBy, dbo.Users.UserID
			FROM        dbo.CJQ INNER JOIN dbo.Users ON dbo.CJQ.PreparedBy = dbo.Users.UserID 
			WHERE     dbo.CJQ.PreparedBy  = ' +  @UserID + ' AND dbo.CJQ.FormID = ' + @FormNum + ' AND dbo.CJQ.NotToBeViewed = 0
			ORDER BY dbo.Users.FirstName , dbo.CJQ.JobTitle'
			Exec(@SQL)

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-24 : 13:28:33

SELECT @SQL =
'SELECT dbo.C.CJQID '
+', dbo.C.JobTitle '
+', dbo.C.Status '
+', dbo.C.Published '
+', dbo.C.PreparedBy '
+', dbo.U.FirstName '
+', dbo..DateLastEdit '
+', dbo.C.ApprovedBy '
+', dbo.U.UserID '
+'FROM dbo.C C'
+'INNER JOIN dbo.U U'
+'ON dbo.C.PreparedBy = dbo.Users.UserID '
+'WHERE dbo.C.PreparedBy = ' + @UserID + ' '
+'AND dbo.C.FormID = ' + @FormNum + ' '
+'AND dbo.C.NotToBeViewed = 0'
+'ORDER BY dbo.U.FirstName , dbo.C.JobTitle '

That's assuming @UserId and @FormNum are both numeric.
If for example UserId is charachter data you would use something like:

+'WHERE dbo.CJQ.PreparedBy = ' + '''' + @UserID + '''' + ' '

Also, how many rows do you have. I would think Users would be small, and therefore scanned and placed in memory.
Do you have an index on the columns that have the highest cardinality? My first guess
would be to make sure FormId has an index.

Just some thoughts. See if this runs for you.

Thanks

Brett

8-)

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-24 : 13:29:41

thanks I'll give a look

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources