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.
| Author |
Topic |
|
Tapalotapus
Starting Member
22 Posts |
 Posted - 2006-10-09 : 14:00:07
|
| Procedure in listing 1 if I understand correctly the query plan will change often. Listing two is what I think I should change it to. I have two questions since the tblcourse has a clustered on CourseCode I shouldn't need the order by right? 2nd, does my solution look good or is there another option? the first one seems to run a little faster. --listing 1 -----------------------------------CREATE PROCEDURE dbo.Course_GetByCourseCode @CourseCode varchar(20)IF @CourseCode = '' SELECT tblCourse.* FROM tblCourse ORDER BY CourseCodeELSE SELECT tblCourse.* FROM tblCourse WHERE (CourseCode = @CourseCode)-- listing 2 ----------------------------------CREATE PROCEDURE dbo.Course_GetByCourseCode @CourseCode varchar(20) SELECT @CourseCode =NULLIF(@CourseCode,'') SELECT * FROM tblCourse WHERE ( @CourseCode IS NULL OR CourseCode = @CourseCode )--------------------------------------------------Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-09 : 14:22:45
|
"since the tblcourse has a clustered on CourseCode I shouldn't need the order by right"If you want the records in some specific order you always need the ORDER BY. Imagine that a previous query has just run and the middle section of your table is in-memory; SQL Server may start work on that whilst the disks are getting the rest of the table - and then your answer won't be in any particular order. Its rare, but it does happen, and is very hard to track down when it does, so unless you want potentially-random-order (there's a new buzz word!!) you need the ORDER BY.The problem with your "Listing 1" is that SQL Server will cache a query plan based on the first time the Sproc is run. If 99% of your calls to Course_GetByCourseCode will have a value for @CourseCode bu the very first "run" of it uses blank/NULL then an inefficient query plan will be generated. At least that's the theory!The potential problem with your "listing 2" is that the OR causes a somewhat inefficient query plan in the first place. Having said that I use "listing 2" style all the time where we have a bunch of parameters controlling a WHERE clause and the user may leave some/all of them blank. Try sticking a second parameter in there and you are up to 4 IF clauses to catch all the possible combinations ... clearly unworkable at that point.It might help NOT to use "SELECT *" - putting the column list that you actually want should give SQL Server less work to prep. the query each time it sees it (you should also prefix you tables with the owner - e.g. "dbo." for the same reason).An alternative would be:CREATE PROCEDURE dbo.Course_GetByCourseCode@CourseCode varchar(20)IF @CourseCode = ''BEGIN EXEC dbo.Course_GetByCourseCode_ALLENDELSEBEGIN EXEC dbo.Course_GetByCourseCode_SOME @CourseCodeEND so that the actual SProc that runs the query only handles one of the scenarios, and its query plan is optimised specifically for that job. However, calling a nested SProc may negate any time saving.So personally I would go for "listing 2" on the basis that it is unlikely to only have one parameter for long!Kristen |
 |
|
|
Tapalotapus
Starting Member
22 Posts |
Posted - 2006-10-09 : 20:19:49
|
| Thanks |
|
|
|
|
|
|
query performance?