Help optimizing Query with parameters

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help optimizing Query with parameters

Author

Topic

nrpueschel
Starting Member

3 Posts

Posted - 2015-02-25 : 14:49:02

Hi,

I've completely changed my original post. I know I did have some issues, but I figured out what my culprit is (also going to delete my response and make the question simpler) I have a query, shorthanded to this. BigTable has over 3 million rows.

SELECT A,B,C
FROM BigTable
WHERE 1 = @paramCanSearch
AND [OtherCriteria]

If I execute this as

SELECT A,B,C
FROM BigTable
WHERE 1 = 1
AND [OtherCriteria]

The execution time is about 3 seconds. However, If I execute it with @paramCanSearch = 1 (or even 0) The execution speed is still around 30 seconds.

Do you know a better way to “not search” if @paramCanSearch is not set to 1? It seems like it is comparing each row and then returns an empty set.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-26 : 04:55:03

If you expect less than 2 billion rows in return, try this

SELECT	TOP(CASE WHEN @paramCanSearch = 1 THEN 2147483647 ELSE 0 END)
	A,
	B,
	C
FROM	dbo.BigTable
WHERE	1 = @paramCanSearch
	AND [OtherCriteria];

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

nrpueschel
Starting Member

3 Posts

Posted - 2015-02-26 : 10:17:23

Thanks SwePeso,

However, the 1 = @paramCanSearch in the WHERE clause is causing a Cartesian join on the result set and thus checks that for every record. Yours would work if i removed the 1 = @paramCanSearch in the Where clause. There were two options that I could take, the first being

IF(@paramCanSearch = 1)
BEGIN
SELECT A,B,C
FROM BigTable
WHERE [OtherCriteria]
END

and the other solution was

SELECT A,B,C
FROM BigTable
WHERE ID <= @parameter
AND [Criteria]

Assuming ID is an integer. I would then pass in a 0 if criteria was not filled in, or pass in the maximum value for an integer, thus pulling back all records.

Thanks everyone.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-26 : 14:40:12

I am not what you mean by "cartesian join", when there is only one table.

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

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-26 : 15:13:19

Add:
OPTION (RECOMPILE)
to the query to remove the effect of the variable.

Also, are there indexes or statistics on the:
[OtherCriteria]
columns in the query? That will help SQL a lot.

In particular, if you (almost) always specify = a certain column(s), cluster the table on that column(s) if it's reasonable to do so.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources