Is there any way to optimize the query given below
I have the following query and I need a query to retrieve data from SomeTable based on the filter criteria present in Someothertable. If there is nothing in SomeOtherTable Query, I have to return all data presented in SomeTable to me
SQL SERVER 2005
SomeOtherTable has no indexes or constraints, all fields are char (50)
The following query works fine for my requirements, but it causes performance problems when I have many parameters.
Due to some Customer requirement, we need to store all Where where data in SomeOtherTable. depending on the subjective data, it will be connected to one of the columns in SomeTable.
For example, the request could 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 guess I might have to explain my problem in detail:
We have developed an ASP.net application that is used to invoke crystal parameterization reports, parameters for crystal reports are not passed using the standard die reporting method.
In an ASP.net application, we have created wizards that are used to pass parameters to reports. These parameters are not directly consumed in the crystal report, but are consumed in a query embedded in the crystal report or in a stored procedure used in a Crystal report.
This is achieved with a table (SomeOtherTable) that contains the parameter data, assuming the report is running, and then the data is deleted, so we can assume that SomeOtherTable has 2 to 3 rows at any given time.
So, if we look at the above query, the initial part of the query can be taken as a report query, and the where clause is used to input the user from the SomeOtherTable.
So I don't think it would be useful to create indexes etc. (maybe I'm wrong).
source to share
SomeOtherTable has no indices or any limitation of all char fields (50)
Okay, there is your problem. There is nothing you can do for such a query that will improve its performance if you create it like this.
-
You need the correct candidate primary or other key listed in all of your tables. That is, you need at least one unique index on the table. You can do this by specifying one or more fields as PK, or you can add a UNIQUE constraint or index.
-
You need to define your fields correctly. Does the field store integers? So an INT field might be better than char (50).
You cannot "optimize" a query based on the wrong schema.
source to share
The following can speed up your work
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')
UNION will effectively split this into two simpler queries that can be optimized separately (highly dependent on the RDBMS, table size, etc., whether it will actually lead to better performance, but always worth a try).
The "EXISTS" keyword is more efficient than "SELECT COUNT (1)" because it will return true as soon as the first row is encountered.
source to share
Or check if the value actually exists in the db AND you can remove the individual keyword in your query, it's useless here.
if EXISTS (select spName from SomeOtherTable Where spName = 'ABC') start SELECT * FROM SomeTable WHERE SomeTable.ID in (SELECT ID FROM SomeOtherTable, where Name = 'ABC') end still start SELECT * FROM SomeTable end
source to share