SQL Server Performance Forum – Threads Archive
SQL Query performance tuning
Hi all, I am using a query which will join more than 10 tables to fetch data. When i analsyed the execution plan i noticed that the query cost is 100%. To be more specific for one where condition the execution plan shows Table Scan (86%). That column contains repetitive values (either 1 or 2 or 3 or 4) since it is a status field. I had created a non-clustered index for this column. But still the query execution plan shows a table scan. I need to select the records where status field is equal to "1" only. (status=1) Please can anybody help me to improve the performance.you are using SELECT query to link up all the 10 tables with condition of status field must be "1"? with left join, union, or inner join?
I am using inner join to link all the tables along with some where condtions which contains like operators and that conditon status=1. i browsed the net to find an interesting behaviour in sql server which is related to this issue. When a column which contains repetitive values and if you use that column for non-clustered index, then the sql server choose to perform a table scan rather than using that non-clustered index. Whether this is true? what will be the solution to my problem. I checked the query execution plan which informs me that for that condition status=1, it performs a table scan with a cost of 86%. please help me to solve this problem
First thing I would try is to force using new created index and compare performance beetween table scan executions and executions when new index is forced. Query optimizer may be wrong about non-clustered index effectivness. If not then we can try other solutions but let’s try this one first. To force an index use index hint:<pre id="code"><font face="courier" size="2" id="code">select …<br />from table t (index = <index name><img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ /><br />…</font id="code"></pre id="code">Another solution you can try is to create a composite index on that table that includes status and another column that appears in where clause.
Hi, First get the required data and store it in hash tables(if the table having huge data) then join then join the tables together and see. here what happens whenver your matching the tables directly it wll take some time to check the data internally. better to split the data in hash tables and join , this wll give better performance compare to join the tables directly. SURYA
]]>