Sql Select Query Timeout

Question

Hello guys i am working on an query and every thing is working fine except when i run a query more than 2-3 times this return timeout is there is something wrong in my query or this is server error kindly suggest me here is my query

SELECT   
    Category_Feature_Mapping.Feature_ID,
    Category_Feature_Mapping.Category_ID, 
    Option_Table.Option_ID,  
    Option_Table.Title, 
    Feature_Table.Title AS featuretitle, 
    Feature_Table.Type
FROM 
    Category_Feature_Mapping 
    INNER JOIN Feature_Table 
        ON Category_Feature_Mapping.Feature_ID = Feature_Table.Feature_ID
    INNER JOIN Option_Table 
        ON Feature_Table.Feature_ID = Option_Table.Feature_ID 
WHERE 
    Category_Feature_Mapping.Category_ID = @catid
    AND Feature_Table.Feature_ID=@feid
    AND Feature_Table.Feature_ID not in (SELECT
                                             Feature_ID 
                                         FROM
                                             Vendor_Value_Table 
                                         WHERE
                                             Vendor_ID=@venid)                       
GROUP BY
    Category_Feature_Mapping.Feature_ID,
    Category_Feature_Mapping.Category_ID, 
    Option_Table.Option_ID, 
    Option_Table.Title, 
    Feature_Table.Title,
    Feature_Table.Type

The connection timeout default is 30 seconds. http://stackoverflow.com/questions/4308417/connection-timeout-for-sql-server — David דודו Markovitz, Nov 05 '16 at 20:14
Look at the query plan and see what is holding it up. You could be having lock problems. — paparazzo, Nov 06 '16 at 10:28
@dudu markvoitz why should I increase timeout the query is returning not more than 50 items wo basically increasing timeout is bad habit I think kindly suggest something else. — user1870546, Nov 06 '16 at 12:38
@user1870546, can you add the number of rows in each table and the execution plan? — David דודו Markovitz, Nov 06 '16 at 12:47
@DuduMarkovitz dear as i say there are not more than 50 items — user1870546, Nov 07 '16 at 17:08
@user1870546, you are referring to the result set or to the base tables (which I'm interesting in)? — David דודו Markovitz, Nov 07 '16 at 17:11
Maybe you have a parameter sniffing issue... Add `OPTION(RECOMPILE)` at the end of the query. See [Query Hints](https://msdn.microsoft.com/en-us/library/ms181714.aspx). — TT., Nov 07 '16 at 17:57
@DuduMarkovitz yes the query which is after not in clause that gives hardly 50 data rest the base table has more than 300-400 data but i don't think so my db size is more it will increase in future — user1870546, Nov 09 '16 at 05:01

score 0 · Answer 1 · answered Nov 05 '16 at 22:04

0

may by a question of performance, try to replace "not in" by "not exists" like this

 AND not exists (
 SELECT * FROM Vendor_Value_Table vvt
 WHERE Feature_Table.Feature_ID =vvt.Feature_ID and vvt.Vendor_ID=@venid
 )

answered Nov 05 '16 at 22:04

Esperento57

16,521
3
39
45

The problem exist after this amendment – user1870546 Nov 06 '16 at 12:39
your tables are indexed on id? – Esperento57 Nov 06 '16 at 12:47
Can you put your connectionstring – Esperento57 Nov 07 '16 at 17:39

score 0 · Accepted Answer · answered Nov 07 '16 at 17:45

0

try to append ";Connection Timeout=100" to you connectionstring for up your timeout

answered Nov 07 '16 at 17:45

Esperento57

16,521
3
39
45

but don't you think its bad habit to increase connection timeout while there are not more than 50 data? – user1870546 Nov 09 '16 at 04:58
may be one table is loked sometime, try wrkobjlck if you are on iseries – Esperento57 Nov 09 '16 at 06:36
i think table is locked during updation here i am just selecting data set – user1870546 Nov 09 '16 at 19:09

Sql Select Query Timeout

2 Answers2