Add this before your query and post the results of the ShowPlan:

Set Showplan_Text On

go

Set NoExec on

go

Also, you can make your query more concise and easier to read...

>>The sproc as a whole is showing significantly more reads than I would expect in Profiler,

Do all of your tables have a clustered index to prevent the tables from...

>>consists of some setup, and then 30 or so subqueries, each just doing selects from the child data tables

Are these sub-queries actually sub-SELECTs within the main SELECT, or are they...

>>AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP

You may have a less than optimal execution plan due to "parameter sniffing" and use of the @strZIP variable. Searchthis site for...

>>Indexes cant help here can they?

If selecting every row and every column, an index can still help if the SELECT statement has an ORDER BY and the index definition...

In SQL 2005, this is done with RANK()/OVER:

SELECT

 Sno, Name, marks, RANK() OVER (

And if you really are a sql_2005_fan, you should not be using this and should be using the RANK/OVER functions in  SQL 2005 as a more efficient alternative.

>>If I run the query without the clause, the query returns the rows in the expected (correct) order. 

So, the only problem is the ORDERING of the resultset ? Are you...

Patindex() will only locate the position of the numerics within the string.

In order to strip the numbers out, you need to use 1 of the functions that actually modifies the...

Select @NewItemIdAsInt =

  IsNull(Max( Cast(Right(ItemID, 4) As Int)  ), 0) + 1 As NextNum

From dbo.tblInItem

Where ItemID Like '[0-9][0-9][0-9][0-9]%'

What happens when a concurrent transaction adds a new item while you're...

>>There are more fields than I have showed and some have subquery in on the Union statement.

Wouldn't it have been better to state that right from the outset,...

Don't use old-style non ANSI joins.

Get rid of the "*=" joins in the WHERE clause, and replace with LEFT JOIN in the FROM.

>>I get an error when trying to retrieve data with the view.

Doubtful anyone can help until you:

- Provide details on the error and

- Show us the SQL of the view definition

You do not need dynamic SQL for this. Nor do you need a UNION:

Insert into #Profit( Items, Amt)

Select   Items,  Amt

From Sales

Where State IN ( 'SC', 'TX' )

AND    ((@Mode = 'R'...

If you are new to SQL, and find yourself wanting to write a cursor to sum things, its time to take a step back from the SQL code editor and...