May 12, 2008 at 11:10 pm
I have a query which has 8 conditions in the where clause. It is terribly slow.
Should i introduce a non - clustered index on of the columns used in the where clause.
May 12, 2008 at 11:16 pm
I believe I'd start wtih the Index Tuning Advisor and then tweek from there.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 11:31 pm
Tried the Index Tuning Wizard. Just recommends an existing index. Not much help that.
May 12, 2008 at 11:36 pm
Then you probably don't have columns in your WHERE clause.
_____________
Code for TallyGenerator
May 13, 2008 at 12:27 am
Can you post query, table schema and index definitions please. Can't say anything for sure without seeing those.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2008 at 12:54 am
Its a search query ..........so we need not get all the inputs. or we might get all....
if it is a numeric data and not to be searched then we send -1 from the app, if alphanumeric then ''
I dont know if this helps...but I dont think I can post the schema of the tables.
DECLARE @col1 CHAR(3)
DECLARE @col2_id INT
DECLARE @col3 INT
DECLARE @col4 VARCHAR(30)
DECLARE @col5 VARCHAR(30)
DECLARE @col6 VARCHAR(30)
SET @col1 = 'ABC'
SET @col2 = -1
SET @col3 = -1
SET @col4 = ''
SET @col5 = '54957495495'
SET @col6 = ''
SELECT TOP 1000 TP.col1, TP.prof_id,TP.prof_nm, TP.col2,
TP.col3, TP.col4,C.col5
FP.fst_nm + ' ' + FP.lst_nm AS comp
FROM table1 TP
INNER JOIN table2 CT
ON TP.id = CT.id
INNER JOIN table3 C
ON C.CKey = CT.CKey
LEFT OUTER JOIN dbo.table4 CP
ON TP.id = CP.id
LEFT OUTER JOIN table1 FP
ON FP.id = CP.psngr_id
WHERE TP.col1 = CASE @col1 WHEN '' THEN TP.col1 ELSE @col1 END
AND TP.col2 = CASE @col2 WHEN -1 THEN TP.col2 ELSE @col2 END
AND TP.col3 = CASE @col3 WHEN -1 THEN TP.col3 ELSE @col3 END
AND ISNULL(C.col5,'') = CASE @col5 WHEN '' THEN ISNULL(C.col5,'') ELSE @col5 END
AND ISNULL(TP.col5,'') = CASE @col6 WHEN '' THEN ISNULL(TP.col6,'') ELSE @col6 END
May 13, 2008 at 1:07 am
The index definitions please?
This is going to prevent index seeks on these two columns
AND ISNULL(C.col5,'')
AND ISNULL(TP.col5,'')
In addition, the kind of 'search-all' query that you have is quite well known to give bad performance. The optimiser can't accuratly sniff the values of the CASE and tends to generate very bad cardinality estimates.
Quite often, dynamic SQL works much better for this kind of construct. Though, be aware of the downsides and pitfalls.
http://www.sommarskog.se/dynamic_sql.html
If there are common patterns to how you call this, then you could try splitting it into multiple subprocs based on the values passed in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2008 at 1:22 am
"If there are common patterns to how you call this, then you could try splitting it into multiple subprocs based on the values passed in."
This is what I am looking for, I have been shouting hoarse that indexes will not solve the problem we need to split the query but because of the demand of the search we were sitting pretty with this query.
Index are the usual ,Primary Key - Clustered index on the join columns.
May 15, 2008 at 2:13 am
Obiviously CASE statments in WHERE clause will improve performance issues. i think you can use temp table to extract records for the CASE conditional records and finally JOIN THE TABLES with TEMP TABLES
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply