Is there a way to optimize the query given below

Question

I have the following Query and i need the query to fetch data from SomeTable based on the filter criteria present in the Someothertable. If there is nothing present in SomeOtherTable Query should return me all the data present in SomeTable

SQL SERVER 2005

SomeOtherTable does not have any indexes or any constraint all fields are char(50)

The Following Query work fine for my requirements but it causes performance problems when i have lots of parameters.

Due to some requirement of Client, We have to keep all the Where clause data in SomeOtherTable. depending on subid data will be joined with one of the columns in SomeTable.

For example the Query can can be

SELECT
    *
FROM
    SomeTable
WHERE
    1=1 
AND
(
SomeTable.ID in (SELECT DISTINCT ID FROM SomeOtherTable  WHERE Name = 'ABC' and subid = 'EF')
OR
0=(SELECT Count(1) FROM SomeOtherTable WHERE spName = 'ABC' and subid = 'EF')
)

AND 
    (
    SomeTable.date =(SELECT date FROM SomeOtherTable  WHERE Name = 'ABC' and subid = 'Date')
    OR
    0=(SELECT Count(1) FROM SomeOtherTable WHERE spName = 'ABC' and subid = 'Date')
    )

EDIT----------------------------------------------

I think i might have to explain my problem in detail:

We have developed an ASP.net application that is used to invoke parametrize crystal reports, parameters to the crystal reports are not passed using the default crystal reports method.

In ASP.net application we have created wizards which are used to pass the parameters to the Reports, These parameters are not directly consumed by the crystal report but are consumed by the Query embedded inside the crystal report or the Stored procedure used in the Crystal report.

This is achieved using a table (SomeOtherTable) which holds parameter data as long as report is running after which the data is deleted, as such we can assume that SomeOtherTable has max 2 to 3 rows at any given point of time.

So if we look at the above query initial part of the Query can be assumed as the Report Query and the where clause is used to get the user input from the SomeOtherTable table.

So i don't think it will be useful to create indexes etc (May be i am wrong).

Answer 1

Try:

SELECT * FROM SomeTable LEFT JOIN SomeOtherTable ON SomeTable.ID=SomeOtherTable.ID AND Name = 'ABC' WHERE 1=1 AND ( SomeOtherTable.ID IS NOT NULL OR 0=(SELECT Count(1) FROM SomeOtherTable WHERE spName = 'ABC') )

Answer 2

also put 'with (nolock)' after each table name to improve performance

Answer 3

The following might speed you up

SELECT * 
FROM SomeTable 
WHERE
   SomeTable.ID in 
        (SELECT DISTINCT ID FROM SomeOtherTable Where Name = 'ABC')
UNION
SELECT * 
FROM SomeTable 
Where 
   NOT EXISTS (Select spName From SomeOtherTable Where spName = 'ABC')

The UNION will effectivly split this into two simpler queries which can be optiomised separately (depends very much on DBMS, table size etc whether this will actually improve performance -- but its always worth a try).

The "EXISTS" key word is more efficient than the "SELECT COUNT(1)" as it will return true as soon as the first row is encountered.

Answer 4

Or check if the value exists in db first And you can remove the distinct keyword in your query, it is useless here.

if EXISTS (Select spName From SomeOtherTable Where spName = 'ABC') begin SELECT * FROM SomeTable WHERE SomeTable.ID in (SELECT ID FROM SomeOtherTable Where Name = 'ABC') end else begin SELECT * FROM SomeTable end

Answer 5

Aloha

Try

select t.* from SomeTable t
    left outer join SomeOtherTable o
     on t.id = o.id
where (not exists (select id from SomeOtherTable where spname = 'adbc')
OR spname = 'adbc')

-Edoode

Answer 6

change all your select statements in the where part to inner jons. the OR conditions should be union all-ed. also make sure your indexing is ok.

sometimes it pays to have an intermediate table for temp results to which you can join to.

Answer 7

Thanks Everyone for your suggestions. I am going to stick to my original Query.

Answer 8

It seems to me that there is no need for the "1=1 AND" in your query. 1=1 will always evaluate to be true, leaving the software to evaluate the next part... why not just skip the 1=1 and evaluate the juicy part?

Answer 9

erm, for some unknown reason, noone has suggested that you profile and create the required indexes?

• Do you have indexes?

• Are your statistics up to date?

• Are you seeing parameter sniffing give you an inappropriate cached query plan? (Can use the OPTIZE FOR hint in that case)...

Answer 10

SomeOtherTable does not have any indexes or any constraint all fields are char(50)

Well, there's your problem. There's nothing you can do to a query like this which will improve its performance if you create it like this.

• You need a proper primary or other candidate key designated on all of your tables. That is to say, you need at least ONE unique index on the table. You can do this by designating one or more fields as the PK, or you can add a UNIQUE constraint or index.

• You need to define your fields properly. Does the field store integers? Well then, an INT field may just be a better bet than a CHAR(50).

You can't "optimize" a query that is based on an unsound schema.