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 |
Dave_Fergy
Starting Member
5 Posts |
Posted - 2006-11-03 : 11:30:20
|
I have a report in which the user can input information into one or both fields and then the SQL will return information based on this input. I have created the SQL and made three different versions using the if statment to run one of the three versions based on the users input. I beleive that there should be a better way to execute this. Any input would be appreciated. Currently I am doing the following:Declare X char(5), Y char(10)IF (not X = '') and (Y = '')beginversion 1 SQL where columnX = XendIF (X = '') and (not Y = '')beginversion 2 SQL where columnY = YendIF (not X = '') and (not Y = '')beginversion 3 SQL where columnX = Xand columnY = Yend |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-03 : 12:26:09
|
Are you using a stored proc? If so, do something likecreate proc dbo.Dave_Report @x char(5) = null, @y char(10) = nullasset no count onselect <column list>from <table>where columnX = isnull(@x, columnX) and columnY = isnull(@y, columnY) and --what is your business rule here? I'm going to assume that --no X and no Y gives you no results (@x is not null or @y is not null) --leaving out this predicate will result in all rows returned --when there is no X and no Y exec dbo.Dave_Report @x ='string'exec dbo.Dave_Report @y = 'longerstrg'exec dbo.Dave_Report @x = 'string', @y = 'longerstrg'Jay White |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-03 : 12:32:41
|
Check if this style performs better (which I think I've read somewhere ... but!)WHERE (@x IS NULL OR columnX = @x) AND (@y IS NULL OR columnY = @Y)... Whether checking performance again when columnX and/or columnY is indexedEDIT: Fixed typos Kristen |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-03 : 12:44:05
|
Kristen, I think you are missing some predicate to handle the cases where @x and/or @y are NOT NULL...In my experience, so long as columnX stands on it's own on the left side of a predicate the optimizer will take advantage of any indexes it can.Jay White |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-03 : 13:57:41
|
Sorry Jay, finger trouble I've fixed the typos in my previous postI was looking for the Optimiser to ignore a step when @parameter is NULLcolumnX = isnull(@x, columnX) requires that ColumnX is compared to itself under certain circumstances. That calculation might induce a SCAN (or even [when @parameter IS NULL] an INDEX traversal selecting all rows, except where it Barfs on rows where columnX IS NULL), whereas what I was trying to achieve was to get that statement optimised to "TRUE" so that there is no effort, and the next statement can then be examined to see if there is any "work to do"But I'm hypothesizing rather than actually knowing, or having tested!, that this method actually performs well ...Kristen |
|
|
|
|
|
|
|