The query runs in approx. 700 ms with Maxdop 8 and in about 5 seconds with Maxdop 1.

I need to force the App plan to use Maxdop 8 instead of...

When i use static SQL it is 800% slower, that is why i am using dynamic SQL.

Here is the whole SP.

Thanks

USE [JobPortalIAN]

GO

/****** Object: StoredProcedure [dbo].[GetAllResumesSearchedDynamicQuery] Script Date: 10/2/2012 4:40:05 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ============================================= ...

Thanks

Sean,

The IF statement works fine like this:

if (@Degree <> 0)

Begin

set @QueryCondition = @QueryCondition + ' and p.Degree1id =' + cast(@Degree as varchar(10)) +

' or p.Degree2id ='...

Sean,

Thanks for you help!

A case of 'you can't see the woods, for the trees!'

Ian

Sample Profiles table:

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Profiles](

[Degree1id] [int] NULL,

[Degree2id] [int] NULL,

[Degree3id] [int] NULL,

CONSTRAINT [PK_profiles] PRIMARY KEY CLUSTERED

(

[Idnew] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS...

I got the syntax to work, but the results are incorrect.

if (@Degree <> 0)

Begin

if (' p.Degree1id ' > ' p.Degree2id ' and ' p.Degree1id ' > ' p.Degree3id ') ...

Basically i want to do this, but the syntax is wrong for p.Degree1id, p.Degree2id and p.Degree3id. I keep getting a 'multipart identifier could not be bound' error.

if (@Degree <>...

I want to first test if the @Degree <> 0

then get the highest degree a candidate has obtained.

So i want to use something similar to the Select CASE logic, but...

Thanks 🙂

After creating a column in the Profiles table for DocContent and removing the last Join statement, my query searching 3 years of data now takes 0.6 seconds !

Effectively I...

I update statistics and run DBCC Freeproccache before any testing.

The removal of each Inner join seems to gain about one second in query performance, so i was wondering if removing...

Sean,

The query returns 9 rows, with around 10 fields.

What is frustrating is that the more i follow the correct "rules", the more headaches i get 🙁

Regards

Ian

Sean,

The Joins were slower than the subqueries, and actually adding the recentwages and recentjobtitle fields back to the profiles table, gained 30% in performance! That is just something i did...