Can some body plesae help me to fine tune this query in a better way
- read http://www.sqlservercentral.com/articles/T-SQL/121906/ about maybe-parameters
- remove the "maybe-parameter" in the subselect in the case when billingtype = 2 then by adding another when @p_Seq_no = -99 with a fitting subselect just before the current when
- be aware, that an subselect in the SELECT part of the query results in NESTED LOOKUPs – maybe you can JOIN the table direct
- the where condition is full of more maybe-parameters as where (tbl.field = @param or @param = 0); this leads to an index scan (instead of seek) on e.g. _Shipments and _Billing_Invoice_Header
- consider the use of dynamic SQL if you can't find another way to remove the maybe-parameters (although the best solution is a separate statement for each combinations)
This will allow SQL Server to embed the values of the variables into the query before optimization begins. This typically allows for significant simplifications, and may enable the query to make better use of existing indexes. If nothing else, capturing the execution plan produced with this hint applied should make it clearer where the indexing might be improved.
If the query is executed very often (tens of times per second at least), it might be worth using dynamic SQL, or, if you have the time to fight your way through the various permutations, write a separate query for each. Naturally, it is possible to combine these approaches, such that different plans are used for the common cases (in separate queries, or dynamically constructed and parameterized), and only the rarer combinations are compiled each time using OPTION (RECOMPILE). Only you can judge if the extra effort involved is worthwhile.
I would echo the advice to work on formatting the code so it is easier to read and maintain. It also a good practice to use table aliases, and use them consistently on all column references, so it clear where each is coming from.
The definitive article on writing effective dynamic search queries is by Erland Sommarskog:
I would also encourage you to read my SQLPerformance.com article: