query performance tuning

Hi All,
I want to tune the following select query:
select
a.TestCaseID , a.Result
from TestResult a , TestCase b
where TestResultId = (select max(TestResultId) from TestResult c where TestCaseID = b.TestCaseID and Result != 'Not Executed' )
and a.TestCaseID = b.TestCaseID
and isnull(a.Product,'') = coalesce('CommandPostWeb',isnull(a.Product,''))
and isnull(a.Flagship,'') = coalesce(null,isnull(a.Flagship,''))
and isnull(a.Feature,'') = coalesce(null,isnull(a.Feature,''))
and isnull(a.protocol,'') = coalesce(null,isnull(a.protocol,''))
and isnull(a.buildTested,'') = coalesce(null,isnull(a.buildTested,''))
and isnull(b.priority,'') = coalesce(null,isnull(b.priority,''))
and isnull(b.configuration,'') = coalesce(null,isnull(b.configuration,''))
and isnull(b.type,'') = coalesce(null,isnull(b.type,''))
and isnull(b.automation,'') = coalesce(null, isnull(b.automation,''))
which takes about 12 to 19 minutes to display the results. But if I query
select
a.TestCaseID , a.Result
from TestResult a , TestCase b
where TestResultId = (select max(TestResultId) from TestResult c where TestCaseID = b.TestCaseID and Result != 'Not Executed' )
and a.TestCaseID = b.TestCaseID
and isnull(a.Product,'') = coalesce('CommandPostWeb',isnull(a.Product,''))
and isnull(a.Flagship,'') = coalesce(null,isnull(a.Flagship,''))
and isnull(a.Feature,'') = coalesce(null,isnull(a.Feature,''))
and isnull(a.protocol,'') = coalesce(null,isnull(a.protocol,''))
and isnull(a.buildTested,'') = coalesce(null,isnull(a.buildTested,''))
--and isnull(b.priority,'') = coalesce(null,isnull(b.priority,''))
--and isnull(b.configuration,'') = coalesce(null,isnull(b.configuration,''))
--and isnull(b.type,'') = coalesce(null,isnull(b.type,''))
--and isnull(b.automation,'') = coalesce(null, isnull(b.automation,''))
the results are returned within a fraction of seconds(de selecting the last four columns). Please suggest something in this regard.

Hi Ashwin,
It seems you have posted same query twice and not the one which returns you result in fraction of second. Also post your index structure on the above tables.

Hi Ranjit,
The query which retrieved results within fraction of seconds is this:
select
a.TestCaseID , a.Result
from TestResult a , TestCase b
where TestResultId = (select max(TestResultId) from TestResult c where TestCaseID = b.TestCaseID and Result != 'Not Executed' )
and a.TestCaseID = b.TestCaseID
and isnull(a.Product,'') = coalesce('CommandPostWeb',isnull(a.Product,''))
and isnull(a.Flagship,'') = coalesce('6.10',isnull(a.Flagship,''))
and isnull(a.Feature,'') = coalesce('Builder',isnull(a.Feature,''))
and isnull(a.protocol,'') = coalesce(null,isnull(a.protocol,''))
and isnull(a.buildTested,'') = coalesce(null,isnull(a.buildTested,''))
and isnull(b.priority,'') = coalesce(null,isnull(b.priority,''))
which does not involve configuration, type and automation columns from TestCase table.
Structure of both tables and the columns indexed are as follows:

Structure of TestCase table

Column Name Data Type Allow Nulls
===========================================
TestCaseID numeric(18, 0) No (primary key) (clustered, unique, primary key located on PRIMARY) TestCaseID, Flagship
StaffID int No
Title nvarchar(1000) Yes
Objective varchar(3000) Yes
Assumptions varchar(4000) Yes
Steps ntext Yes
ExpectedResults ntext Yes
ProdSuiteId bigint Yes
Product nvarchar(50) Yes
DBType nvarchar(25) Yes
Component nvarchar(3000) Yes
Type nvarchar(50) Yes
OperatingSys nvarchar(50) Yes
Server nvarchar(50) Yes
Status nvarchar(50) Yes
Priority nvarchar(50) Yes
Author nvarchar(50) Yes
CreationDate nvarchar(50) Yes
AssignedTo nvarchar(50) Yes
Flagship_A nvarchar(5) Yes
Flagship nvarchar(50) No (primary key)
Webserver nvarchar(20) Yes
Webbrowser nvarchar(20) Yes
AppServer nvarchar(20) Yes
Attachment nvarchar(20) Yes
Feature nvarchar(3000) Yes
Modules nvarchar(3000) Yes
SmokeTest nvarchar(50) Yes
Protocol nvarchar(50) Yes
Configuration nvarchar(50) Yes
Objects nvarchar(50) Yes
ProdReqID nvarchar(50) Yes
UserName char(10) Yes
Result char(14) Yes
HostName nvarchar(30) Yes
automation varchar(70) Yes
Beta nvarchar(10) Yes
GA nvarchar(10) Yes
RequirementId nvarchar(3000) Yes
IsDeleted int Yes
Also iam online on y messenger my id is : ashwin_moreyin@yahoo.co.in,ashwinmorey@gmail.com. Please let me know if you have queries.



Structure of TestResult table

Column Name Data Type Allow Nulls
===========================================
Flagship nvarchar(9) Yes
TestCaseID numeric(10, 0) No (nonclustered located on PRIMARY) TestCaseID
TestResultId numeric(18, 0) No (primary key) (clustered, unique, primary key located on PRIMARY) TestResultId
TestedBy nvarchar(50) Yes
Result nvarchar(50) Yes
UserName nvarchar(50) Yes
BuildTested nvarchar(25) Yes
Defect nvarchar(50) Yes
Comments ntext Yes
TestEnv nvarchar(50) Yes
CreationDate nvarchar(50) Yes
Feature nvarchar(3000) Yes
Product nvarchar(50) Yes
Protocol nvarchar(50) Yes
StartTime nvarchar(50) Yes
EndTime nvarchar(50) Yes
IsDeleted int No

Hi,
From the query above, it seems like you are using the coalesce function incorrectly. Rather use the "is not null" operator for fields that should not be null and the "=" operator for equality checks. This should also improve performance.

Thanks Ranjit, using IS NOT NULL improved the functionality greatly. But since this query should be dynamic i.e. depending upon user input
So can I write something like this
select
*
into #test
from Test1 a , Test2 b
where

if color IS NOT NULL
isnull(a.color,'') = coalesce(@product,isnull(a.color,'') )
else
IS NOT NULL
id brandname IS NOT NULL
and isnull(a.brandname,'') = coalesce(@Flagship,isnull(a.brandname,'') )
else
IS NOT NULL
if price IS NOT NULL
and isnull(a.price,'') = coalesce(@Feature,isnull(a.price,'') )
else
IS NOT NULL
Please suggest. Thanks in advance!

I would not use the coalesce function at all if I were you. I would write it like this: select <field_list> from <tables> where isnull(color,'') = @product
If the variable @product can be null but it is necessary to have a value (and not blank) then I would add "...and @product is not null"
The coalesce function returns the first non-null value. Do you want to return all records if the variables are all null?

Hi martin,
what if @product has a null value, when user does not provide anything for @product. Can you please create a sample query and post. And also please
explain the difference between 'coalesce' and 'isnull'. Where can I find examples on these two.
thanks
ashwin

Hi,
The best place to find the difference between isnull and coalesce would be in "Books Online", and I would suggest that you go have a look at the examples there. The coalesce function returns the first non-null value from many given expressions, whereas isnull will return what value you specify in the case of a null.
Lets say that @product can be null, and in the case of it being null you want to return all records. Your query should then look something like this:
select *
from table_a a
where isnull(a.product,'') = isnull(@product,isnull(a.product,''))
Using the coalesce function here would do exactly the same and would not be wrong:
select *
from table_a a
where isnull(a.product,'') = coalesce(@product,a.product,'')
It is however not a good function to use when you just want to check if a field is not null, but like you have mentioned you already changed that

The COALESCE around a column in a WHERE clause is a bad idea because even if there is a covering index, the function around the column causes a table scan.
It may look cumbersome, but for SQL Server the direct way of writing criteria is often the best:
SET @product = ISNULL(@product, '')
SELECT ................................
WHERE ((a.product IS NULL OR a.product = '') AND @product = '') OR (a.product = @product)

you mean to say that
and isnull(a.Test,'') = coalesce(@test,isnull(a.Test,'') )
is equivalent to

SET @test = ISNULL(@test, '')
SELECT ................................
WHERE ((a.Test IS NULL OR a.Test = '') AND @test = '') OR (a.Test = @test)
if this is the case then I can replace it in my code. Please suggest.

Your expression is a little bit fuzzy, since you are using an embedded ISNULL. Note that COALESCE can have any number of parameters, and it will return the first one (left-to-right) that is not null. So the expression could be simply COALESCE(@test, a.Test, '')
You did notice that your equation has the same column on both sides, I hope? Try to avoid that sort of confusion.

But I think COALESCE does make a whole tablescan so it takes more time than ISNULL function. am I right and is this the only performance consideration?

Hi all,
now Iam using the following query:
select
*
from Test1 a , Test2 b
where
isnull(b.color,'') = coalesce(null,b.color,'')
and isnull(b.brandname,'') = coalesce(null,b.brandname,'')
and isnull(b.tag,'') = coalesce(null,b.tag,'')
and isnull(b.price,'') = coalesce(null,b.price,'')
which makes proper use of coalesce function, but still there is a performance degradation. Let me tell you that
there are lot of null values in columns like 'brandname'(4560), 'tag'(123) and 'price'(101256) which might be adding to the performance degradation. Please suggest.
thanks

Hi Ashwin,
Your usage of the COALESCE function is still wrong. The COALESCE function will return the first non-null expression (in the list of expressions you supply). The following therefore does not make sense:
and isnull(b.brandname,'') = coalesce(null,b.brandname,'')
The "null" will never be returned, and therefore not needed. That line should rather be written as:
and isnull(b.brandname,'') = isnull(b.brandname,'')
It seems like you are very intent to use the COALESCE function, but it is not necessary in the above query. Only use the COALESCE function when you want to return another field when a field is null, for instance lets say if the brandname was null you wanted to return the product name, and if the product name was null you wanted to return the value 'Unknown'. You could do this either with a CASE statement, nested isnull statement or a COALESCE function:
and isnull(b.brandname,'') = coalesce(b.brandname,b.productname,'Unkown')

But in your query above the isnull function will do because you only want to return '' in the case of the brandname being null. The fact that you are passing a null as the first expression of your COALESCE function is already an indicator that you should not be using that function.
Hope this helps.

thanks martin,
I modified my query to
select
*
from Test1 a , Test2 b
where
isnull(b.color,'') = isnull(b.color,'')
and isnull(b.brandname,'') = isnull(b.brandname,'')
and isnull(b.tag,'') = isnull(b.tag,'')
and isnull(b.price,'') = isnull(b.price,'')
but the performance issue is still there, in the previous message i told you that there are lot of null values for columns like
brandname(4560), tag(123), price(101256) in the database. Please suggest.

I am still confused as to what you are trying to do with the above query. You are comparing columns in Test2 with columns in Test2...they will always be equal. Like I mentioned in my previous post, with this query you are returning the cartesian product of the 2 tables.
Shouldn't your query read:
select * from Test1 a, Test2 b where isnull(a.color,'') = isnull(b.color,'')...

Just another comment:
The query that you posted will return all the records and is really nothing different from"
select * from Test1, Test2
The where-clause will not filter any data and you have no join criteria, which means it performs a cross join. That will explain bad performance.

thanks martin,
let me explain you in detail, the original query is something like
select
*
from Test1 a , Test2 b
where
isnull(a.user,'') = isnull('ashwin',a.user)
and isnull(a.city,'') = isnull('california',a.city)
and isnull(b.color,'') = isnull(null,b.color)
and isnull(b.brandname,'') = isnull(null,b.brandname)
and isnull(b.tag,'') = isnull(null,b.tag)
and isnull(b.price,'') = isnull(null,b.price)
the first two columns are from Test1 table and the remaining four are from Test2 table, the first two filter criteria are almost always filled by the user
but the remaining ones are not (rarely filled) by the user while searching. So if we use
select
*
from Test1 a , Test2 b
where
isnull(a.user,'') = isnull('ashwin',a.user)
and isnull(a.city,'') = isnull('california',a.city)
the results are retrieved within 4 seconds, but if we include the whole thing

select
*
from Test1 a , Test2 b
where
isnull(a.user,'') = isnull('ashwin',a.user)
and isnull(a.city,'') = isnull('california',a.city)
and isnull(b.color,'') = isnull(null,b.color)
and isnull(b.brandname,'') = isnull(null,b.brandname)
and isnull(b.tag,'') = isnull(null,b.tag)
and isnull(b.price,'') = isnull(null,b.price)
same results are retrieved but now takes 35 seconds, thanks for your comments on the issue (which was taking 12 to 19 mins previously).
but I still need to improve the performance. I think the left part 'and isnull(b.price,'')' is incorrect now, also there are null values in these columns.
Please suggest. Thanks in advance.

Firstly you need some join criteria between the two tables. You cannot do anything about the fact that there might be nulls in the table, so you should try and improve performance with the correct indexes.
How big are these 2 tables and are there any indexes?