performance - SQL Server Query running slow when changing constant in where clause -

hello , in advance. have view when queried no clause takes on 0 seconds return ~8600 rows. however, when query clause such as:

select * myview myid = 123 

depending on constant put in place of 123 query execution time changes considerably.

now, "considerably" in case means difference between above 0 seconds , 3 4 seconds. view called , repeatedly tasks makes 3 seconds turn 30 or more seconds.

while cannot give code view itself, can confirm that:

1. the view comprised of joining of 6 standard tables (no special qualities).

2. while there may not records in table link table b, creating null columns in results, have confirmed such instances not consistently resulting in longer or shorter query times.

3. the view has no clauses beyond standard select, from, , left outer join clauses.

4. certain ids result in long query times , others result in short query times

5. i have dropped , created view in between queries on off chance there cached execution plan sub-optimal.

if these known variables not enough reduce possibilities down 2 or 3 possible causes still know theoretical problems might causing issue expand understanding.

thanks again,

protonoob

i assume statistics tables outdated , not match real content of tables. mean optimizer, relying on statistics, e. g. assumes value use in clause not occur in data @ all, hence result set being rather small, while in reality contains many rows. or other way round: relying on statistics, optimizer assume - say- 20% of rows of table have value, , hence better full table scan first access index pages evaluating condition, jump data page each index entry read data, , in end having read pages anyway. or access tables in wrong order, or ... in reality, value not contained in table @ all, leading wrong plan.

one hint pointing outdated statistics if query plan shows huge difference between estimated , actual number of rows.

which dbms using? if sql server, can see current statistics using dbcc show_statistics , refresh statistics selected columns , tables using update statistics statement. there more views , procedures around subject, of them linked 1 of these 2 articles.