Completely stomped! Please help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Completely stomped! Please help!

Author

Topic

firewall
Starting Member

2 Posts

Posted - 2014-09-23 : 17:53:35

All right, this one has me completely stomped. Hats off to who ever figures that one out.

I have a stored procedure who received a bunch of variables and returns a count (or a result set, same thing happens).

Depending on tiny changes I do, I either get the results back in a split second or in 21 seconds!! Obviously I need the results back right away.

Just to illustrate the changes I am trying:

This returns results in a split second


	DECLARE @company_id int = 10035
	DECLARE @name varchar(50) = ''
	DECLARE @manufacturer_id varchar(50) = '%'
	DECLARE @manufacturer_part_number varchar(50) = '%'
	DECLARE @internal_part_number varchar(50) = ''
	DECLARE @cas_number varchar(50) = ''
	DECLARE @created_by varchar(50) = '%'
	DECLARE @archived varchar(50) = '%'
	DECLARE @language_id int = 1
	DECLARE @site_id INT = 0

			SELECT 
				COUNT(products.product_id) AS product_count
			FROM 
				products 
			LEFT JOIN 
				products_language 
				ON products.product_id = products_language.product_id
				AND products_language.language_id = @language_id			
			WHERE 
				(products_language.name LIKE '%'+@name+'%'
				OR products.main_name LIKE '%'+@name+'%'
				OR products_language.synonyms LIKE '%'+@name+'%') 
				AND products.manufacturer_id LIKE '%%'
				AND products.manufacturer_part_number LIKE '%'
				AND products.part_number LIKE '%'
				AND products.cas_number LIKE '%'
				AND products.created_by LIKE '%%'
				AND products.company_id=@company_id 
				AND products.archived LIKE '%%'

This returns results in 21 seconds!


	DECLARE @company_id int = 10035
	DECLARE @name varchar(50) = ''
	DECLARE @manufacturer_id varchar(50) = '%'
	DECLARE @manufacturer_part_number varchar(50) = '%'
	DECLARE @internal_part_number varchar(50) = ''
	DECLARE @cas_number varchar(50) = ''
	DECLARE @created_by varchar(50) = '%'
	DECLARE @archived varchar(50) = '%'
	DECLARE @language_id int = 1
	DECLARE @site_id INT = 0

			SELECT 
				COUNT(products.product_id) AS product_count
			FROM 
				products 
			LEFT JOIN 
				products_language 
				ON products.product_id = products_language.product_id
				AND products_language.language_id = @language_id			
			WHERE 
				(products_language.name LIKE '%'+@name+'%'
				OR products.main_name LIKE '%'+@name+'%'
				OR products_language.synonyms LIKE '%'+@name+'%') 
				AND products.manufacturer_id LIKE '%%'
				AND products.manufacturer_part_number LIKE @manufacturer_part_number
				AND products.part_number LIKE '%'
				AND products.cas_number LIKE '%'
				AND products.created_by LIKE '%%'
				AND products.company_id=@company_id 
				AND products.archived LIKE '%%'

and again, if I remove any field in the where clause, back to a split second!


	DECLARE @company_id int = 10035
	DECLARE @name varchar(50) = ''
	DECLARE @manufacturer_id varchar(50) = '%'
	DECLARE @manufacturer_part_number varchar(50) = '%'
	DECLARE @internal_part_number varchar(50) = ''
	DECLARE @cas_number varchar(50) = ''
	DECLARE @created_by varchar(50) = '%'
	DECLARE @archived varchar(50) = '%'
	DECLARE @language_id int = 1
	DECLARE @site_id INT = 0

			SELECT 
				COUNT(products.product_id) AS product_count
			FROM 
				products 
			LEFT JOIN 
				products_language 
				ON products.product_id = products_language.product_id
				AND products_language.language_id = @language_id			
			WHERE 
				(products_language.name LIKE '%'+@name+'%'
				OR products.main_name LIKE '%'+@name+'%'
				OR products_language.synonyms LIKE '%'+@name+'%') 
				AND products.manufacturer_id LIKE '%%'
				AND products.manufacturer_part_number LIKE @manufacturer_part_number
'REMOVED PART NUMBER
				AND products.cas_number LIKE '%'
				AND products.created_by LIKE '%%'
				AND products.company_id=@company_id 
				AND products.archived LIKE '%%'

Please help me, not understanding this one at all!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-24 : 15:00:53

Parameter sniffing.
When dealing with this kind of queries, add an

OPTION (RECOMPILE)

to the end of the statement.
Now both should be equally fast.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources