From all my search to speed up the queries in sql server,sources said to reduce the logical reads by using the proper where clause. Actually i need to know how stored procedures in sql server work flow when it got request from front end and some tips to avoid in stored procedures and which is not.
Logical reads means records you are reading from the database. Let's take a small, stupid example:
select * from ( select * from orders where client = 1234 ) where item = 9876;
Here you select all orders from client 1234. Then later you only take those for item 9876. So (provided the optimizer doesn't see through this and optimizes your query internally) you select many more records in the first step than needed. Reduce logical reads (and the according large intermediate result) by applying both criteria in one step:
select * from orders where client = 1234 and item = 9876;
(This may also effect physical reads, but doesn't necessarily have to. For instance the first query may access 100 records and then reduce that to 10, whereas the second only reads those 10. But all 100 records may be in one disk block, so both statements read one disk block, i.e. make one physical read. It can even be zero physical reads, by the way, in case the data happens to be already in the dbms cache, i.e. in memory. This also tells us that physical reads can vary for a query, while logical reads remain the same as long as the query and the data are not altered.)