Query taking too long to execute

[ishtiaqahmed]

I have a large database of about 2gb. The table that i am quering has more than 200,000 records in it , and also in query there are joins with other tables
here is the stired procedure that i am using to search the database. but ot takes very long time, Please help me make the search stored procedure efficent :
Here is the stored procedure:
===========================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE [dbo].[_testing]

(
@City int=0,
@State int=0,
@Country int=0,
@JobTitle varchar(100)=null,
@CompanyName varchar(100)=null,
@username Varchar(50)=null,
@Keyword Varchar(50)=null,
@ContractType Varchar(50)=null,
@PostedDate DateTime=null,
@Industry varchar(50)=null

)

AS

SELECT JobDetails.JobID, JobDetails.Cat_ID, JobDetails.Title, JobDetails.CompanyName, JobDetails.Source, JobDetails.URL, JobDetails.CountryID,
JobDetails.StateID, JobDetails.CityID, JobDetails.PostDate, JobDetails.ApplyByDate, JobDetails.Description, JobDetails.LongDescription,
JobDetails.SpiderDate, JobDetails.Status, JobDetails.Zip, JobCategory.CategoryName, JobTypes.JobType, Country.CountryName, States.StateName,
City.CityName, JobSalary.MinSalary, JobSalary.MaxSalary, JobSalary.Type, JobSalary.Currency, JobSalary.Text, SavedJobs.username, City.CityID
FROM States INNER JOIN
JobCategory INNER JOIN
Job_Category_Relation ON JobCategory.CategoryId = Job_Category_Relation.Cat_ID INNER JOIN
JobDetails ON Job_Category_Relation.JobID = JobDetails.JobID INNER JOIN
Job_Type_Relation ON JobDetails.JobID = Job_Type_Relation.JobID INNER JOIN
JobSalary ON JobDetails.JobID = JobSalary.JobID INNER JOIN
JobTypes ON Job_Type_Relation.TypeID = JobTypes.ID INNER JOIN
Country ON JobDetails.CountryID = Country.CountryID ON States.StateID = JobDetails.StateID INNER JOIN
City ON JobDetails.CityID = City.CityID

LEFT OUTER JOIN
SavedJobs
ON SavedJobs.jobid = JobDetails.JobID

Where
(
((Title like '%'+@Keyword+'%') OR (@Keyword is null))
AND
((JobDetails.CountryID=@Country) OR (@country =0))
AND
((JobDetails.CityID=@City )OR (@City =0))
AND
((JobDetails.StateID=@State)OR (@State =0))
AND
((JobType =@ContractType AND @ContractType is not null) OR (@ContractType is null))
AND
((CategoryName=@Industry AND @Industry is not null) OR (@Industry is null ))


AND
((Title =@JobTitle AND @JobTitle is not null) OR (@JobTitle is null))
AND
((CompanyName =@CompanyName AND @CompanyName is not null) OR (@CompanyName is null))

)
==================================================
Here is the script of the table that i am quering :
CREATE TABLE [dbo].[JobDetails](
[JobID] [int] IDENTITY(1,1) NOT NULL,
[Cat_ID] [int] NULL,
[Title] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CompanyName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Source] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[URL] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountryID] [int] NULL,
[StateID] [int] NULL,
[CityID] [int] NULL,
[PostDate] [datetime] NULL,
[ApplyByDate] [datetime] NULL,
[Description] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LongDescription] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SpiderDate] [datetime] NULL,
[Status] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_JobDetails] PRIMARY KEY CLUSTERED
(
[JobID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
====================================

[skhanal]

How is states and jobcategory joined, I do not see an ON statement.

FROM States INNER JOIN
JobCategory INNER JOIN

[rmiao]

Did you check execution plan? Is it on sql2k8? Nowadays, 2gb db is small.

[J013]

I think all of the advise so far is really good. I would also check to see if you have indexes on the tables you are querying and that they are not too fragmented. You can try running a trace and playing it in the Database Engine Tuning Advisor to see if it says you should create any additional indexes.