GROUP BY A.BusinessUnit , A.RetailerCode , A.ExecutiveCode) AS VisitCount ON VisitInfo.BusinessUnit=VisitCount.BusinessUnit AND VisitInfo.RetailerCode=VisitCount.RetailerCode AND VisitInfo.ExecutiveCode=VisitCount.ExecutiveCode

GROUP BY A.BusinessUnit , A.RetailerCode , A.ExecutiveCode) AS VisitCount ON VisitInfo.BusinessUnit=VisitCount.BusinessUnit AND VisitInfo.RetailerCode=VisitCount.RetailerCode AND VisitInfo.ExecutiveCode=VisitCount.ExecutiveCode

1)was this initially taking less time = If data is less , report generating . But with large data in tables it took more time ,

2) and now you are facing the issue with sp or it's the first run ?? = When i call to this sp from code behind it working . but getting time. If table data is less , then report generating( sp running quickly). But i don't no any other way to code without join table like this.

Pick a chunk of the query at the bottom of your stored procedure, run it in a query window with a couple of sensible parameters, and have a look at the execution plan. Since there are a few joins involving five or six different columns, and similarly complex WHERE clauses, it would seem likely that the existing indexing strategy isn't helping this query. Not much else springs to mind except that the derived table SalesInvoiceHeader appears to be aggregated to the same level of granularity as the salesorderheader part of visitinfo. If you were to run the results of derived table SalesInvoiceHeader into a #temp table, then extracting the rows for the salesorderheader part of visitinfo from this #temp table would be a lot cheaper than reading the base tables. Performance (and readability=maintainability) can be significantly improved by breaking up a large query into smaller chunks using indexed #temp tables

Here's what looks to me to be a suitable chunk, reformatted for readability (please read up on table aliases and use them). It's from @RepType = '2', and it's the first query in the UNIONed query set.

Including the White Space, you have 918 lines of code and none of it is documented.

Steps 1 and 2 would be to document the code and add print statements to each section of the code to identify the section and how long that section of code actually took to run. "Divide'n'Conquer" is one of the main keys here because as the old saying goes, "Correct identification of the problem is 90% of the solution".

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013