>>Does SQL evaluate both parts of an OR condition or does it stop when one condition is TRUE? For example:

No guarantees what it will do. SQL uses a cost based...

I have seen cases where adding the (WITH FASTFIRSTROW) hint can change the query plan and improve the overall performance.

However, usual disclaimers apply - use hints like this as a...

That's all the indexes ? Just a clustered primary key on the P_K column and no non-clustered indexes ?

It seems that you are joining frequently on columns like N_K_HTXTITL_HTXLINC, so...

I don't see anything obvious that explains the diff between SQL2K and SQL2K5. However, I do see some less than optimal code that could be fixed in both versions:

Select DHT_TITLE_REFRNC_NBR...

Can we see the view definitions ?

Are the views doing anything that isn't supported under SQL2K5 ? Eg: the use of SELECT TOP 100 PERCENT in a view in...

Try placing a SET NOCOUNT ON at the start of the proc to eliminate the row count status messages. Sometimes confuses client side components.

Also, do you need the temp...

>>On the db server side, I've done the execution plan for the queries that time out, and there's nothing resource-intensive going on (no table scans, etc).

Right, but you've done...

SQL is parsed. Then it is executed. The parser is generating the error, and that is happening before the IF statement has executed. The parser simply sees 2 attempts to...

>>However, the application which calls those procedures need both of them explicitly.

By that, are you inferring that you have no control over the application code, and therefore can't make any...

What you have here is a ranking problem, with 2 levels of ranking.

If dt is NULL and there's a fee, that ranks first, otherwise you apply a secondary ranking based...

>>Your approach to solving this solution is suboptimal.

So, for every query in your system where there is a join to a small "lookup" table, you'd replace the join with a...

To add to that ...

I count at least 10 occurrences of

SOME_KEY IN (SELECT * FROM #TBLTEMP12)

Can you convert those IN () sub-queries to EXISTS () or INNER JOINs ?

Also,...

In general:

SELECT

t1.colx,

(SELECT t2.coly FROM table2 t2 WHERE t2.id = t1.id) AS coly

FROM table1 t1

WHERE t1.some_col = 'something'

.. this will be slower. Also, depending on cardinality it does not produce...

Ahh, if it's Crystal passing the parameters, maybe the parameter declarations need to be changed:

CREATE PROCEDURE dbo.gm_P4P_PQRI_MEARSURE_8

(

@STARTDATE VARCHAR(20) = NULL,

@ENDDATE VARCHAR(20) = NULL,

Declare params as varchars, let Crystal pass in...

>>IF ISDATE(@StartDate)=1 AND ISDATE(@EndDate)=1

The variables @StartDate and @EndDate are already true datetime datatypes, so this IF statement is redundant.

>>AND MWAPPTS.ADATE BETWEEN CAST(Convert(varchar(100), @StartDate, 101) as DateTime) AND CAST(Convert(varchar(100), @EndDate, 101)...