June 21, 2010 at 1:06 am
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,
[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]
====================================
June 21, 2010 at 1:52 am
Hi
Just glancing at your query ... i find two things ... usage of so many "OR" statements and the way "like" is used. Both probably result in proper indexes not being used. Using like operator in this manner "'%'+@Keyword+'%'" does not generate a good execution plan and so is the case with "OR".
Can this query be re-written?
"Keep Trying"
June 21, 2010 at 1:53 am
Hi,
Please see this link http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
June 21, 2010 at 2:29 am
Thank you..
What is the better alternative of Like statement???
I will remove the Or statments , as in the Dave Ballantyne article..
June 21, 2010 at 2:43 am
If you *Have* to search for a title that contains the text contained in @keyword within in , then full text searching may be a viable alternative.
June 21, 2010 at 3:14 am
Just created a new full text index on "Title" and replaced like '%' with Contains keyword..
But now its taking even longer to execute...
June 21, 2010 at 3:23 am
Please post the execution plan.
June 21, 2010 at 3:54 am
execution plan attached....
Here is the new Stored procedure after changes you have suggested
=====================================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[_testing]
(
@City varchar(50)=null,
@State varchar(50)=null,
@Country varchar(50)=null,
@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
(
((Contains(Title,@Keyword)) OR (@Keyword is null))
AND
(CountryName = CASE WHEN @Country IS NULL THEN CountryName ELSE @Country END)
AND
(CityName = CASE WHEN @City IS NULL THEN CityName ELSE @City END)
AND
(StateName = CASE WHEN @State IS NULL THEN StateName ELSE @State END)
AND
(JobType = CASE WHEN @ContractType IS NULL THEN JobType ELSE @ContractType END)
AND
(CategoryName = CASE WHEN @Industry IS NULL THEN CategoryName ELSE @Industry END)
AND
(Title = CASE WHEN @JobTitle IS NULL THEN Title ELSE @JobTitle END)
AND
(CompanyName = CASE WHEN @CompanyName IS NULL THEN CompanyName ELSE @CompanyName END)
)
June 21, 2010 at 4:04 am
Im not to surprised by that.
You need to follow the advice give in the link i gave earlier , it still doing table scans which are making any possible gain of using CONTAINS irrelevant.
June 21, 2010 at 4:23 am
How much time it will take to scam the table..
it is still very slow....
June 21, 2010 at 4:45 am
ahmed_sag4f5 (6/21/2010)
How much time it will take to scam the table..it is still very slow....
Depends on the size of the table. One a large table it will be slow. Take a look at the link Dave posted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply