Help Fix Slow Query.

I have a query that is taking too long to run. It take 14 seconds to return 6800 rows. However, if I move the query out of a stored proc, it takes 1 second. I want to understand this issue and ideally fix the stored proc case.

I've simplified my actual queries for readability.

-- @.filter is value to filter against or NULL to return all records.
CREATE PROCEDURE queryPlayerStations(@.filter INTEGER)
AS
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

DECLARE @.filter INTEGER
SET @.filter = NULL

-- Takes 14 seconds to return 6800 rows. That's unacceptable performance
EXEC dbo.queryPlayerStations @.filter

When I run the query directly in Query Analyzer, it runs very fast.

DECLARE @.filter INTEGER
SET @.filter = NULL

-- Takes ~1 second to return 6800 rows. That's great performance
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))

When I put the parameters in the stored proc it runs fast.

CREATE PROCEDURE queryPlayerStations
AS
DECLARE @.filter INTEGER
SET @.filter = NULL

SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

-- Takes ~1 second to return 6800 rows. That's great performance
EXEC dbo.queryPlayerStations

Anyone have any ideas what I can do to improve the stored proc case?Just a quick *guess* before I leave office for tonight...

The optimization in SQL Server works differently depending
on where the parameter is defined (as a procedure call argument or inside using DECLARE). In one of the cases,
it doesn't have enough info to optimize in the best way.|||As Coolberg implied, what happens if you do this:

ALTER PROCEDURE queryPlayerStations(@.filterIN INTEGER)
AS
DECLARE @.filter INTEGER
SET @.filter = @.filterIN

SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

DECLARE @.filterIN INTEGER
SET @.filterIN = NULL
EXEC dbo.queryPlayerStations @.filterIN

Help - how to execute an sp using linked server

When I run from Main server which has linked server connection - sp executes but the resultset does not get displayed. When I call the same proc from SSRS - I am getting 'an error occured when retrieving parameters for the query. sp does not exist'

Thanks

you will have a tab called 'Command Type' there you change it to Stored Procedure. Then try to execute also make sure you have declared all the parameters before executing this.

Hope it solves your problem.

Help -- How do I exit a Foreach Loop

I have a foreach loop that is processing files in a directory. One of the tasks is the execute sql task that calls a stored proc. If this stored proc returns a certain value then I need to exit the loop and not process any more files. But I have been unable to figure out how to make this happen.

Any help would be greatly appreciated.

Thanks!

GN

Hi,

I am not sure whether this will work or not. But just try. In the 'Foreach loop' container, select 'Expressions' tab and assign a boolean variable to the propery 'Disable'. Then based on the result, when required, set the value of the variable to 'True'. If it does not work, try this option for 'ForceExectionResult' also.

Let me know if any one of it works.

|||

GN wrote:

I have a foreach loop that is processing files in a directory. One of the tasks is the execute sql task that calls a stored proc. If this stored proc returns a certain value then I need to exit the loop and not process any more files. But I have been unable to figure out how to make this happen.

Any help would be greatly appreciated.

Thanks!

GN

have you considered using the script task instead?|||

Place the workflow in a sequence inside the ForEach Loop.

Place a script task at the beginning of the loop with a precedence constraint going to the sequence.

Place an expression on the precedence constraint to only execute if a variable is true, call the variable, "ExecuteBody". ExecuteBody==true

Set op to expression only

When the loop should terminate, set the ExecuteBody variable to false. The loop will traverse the entire remaining collection, but because there is nothing to execute, it will rip through it extremely fast and terminate.

K

Help (DBCC SHOWCONTIG ( + @tablename + )

Hi everyone, I'm SO glad I found this site.
Ok, here's my problem...

I found this stored proc somewhere to help defrag some tables, well this one line is giving me the hardest time. First I was getting an error about using FAST ('FAST' is not a recognized option.), so I took that out.
Now I'm getting 'ALL_INDEXES' is not a recognized option.

Here's the line giving me the problems.
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS,ALL_INDEXES')

Any ideas?

Thank you!RE: Here's the line giving me the problems.
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS,ALL_INDEXES')

Note: Often the line where the error is 'reported' to be (in query analyzer, etc.) is not where it the issue actually is; frequently that is just where the query parser 'chokes' on it. The line you gave in isolation should work OK. Have you tried it seperatly to test it? For example try:

Use Pubs
GO

Declare @.tablename As NVarChar (256)
Select @.tablename = 'Authors'

Exec ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS, ALL_INDEXES')|||That keyword is not valid. If you include the table id without the index id - all indexes will be processed.|||RE: That keyword is not valid. If you include the table id without the index id - all indexes will be processed.

I'm not sure I understand what you mean, if you mean All_Indexes; as far as I understand it will cause results for "all indexes" to be displayed. In this case for Pubs..authors?

Use Pubs
GO

Declare @.tablename As NVarChar (256)
Select @.tablename = 'Authors'

Exec ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS, ALL_INDEXES')|||I needed to be more specific - ALL_INDEXES is not a valid keyword for sql server 7 - that is why criki is having the problem. Same reason why fast was not working.|||Originally posted by rnealejr
I needed to be more specific - ALL_INDEXES is not a valid keyword for sql server 7 - that is why criki is having the problem. Same reason why fast was not working.

I had assumed criki was on Sql Server 2k; also I thought Sql 2k returns esults for a clustered / pk index (if you leave out index ids...) I guess 7.0 returns results for all inexes if you leave out index ids?|||Whoa! You guys lost me.
I am on 2000. I found the code in help. I would assume FAST and ALL_INDEXES would be accurate. I just figured there's some setting I'm missing.

The full code is in SQL Server Books Online under DBCC SHOWCONTIG|||You say you are running SQL Server 2000, was this an install or an upgrade from SQL Server 7.0. If it was an upgrade your Compatibility Level for the database may still be set to SQL Server 7.

Run this to see the levels
select cmptlevel,name from master.dbo.sysdatabases
If a database comes back with a cmptlevel not equal to 80, then it is not at 2000 level.

You can set a databases Compatibility Level by running sp_dbcmptlevel. However this may have some effect on your stored procedures, so test.|||They're all 70. :-(
Most of my databases don't have stored procedures, luckily in this case.

What type of issues and is there anyway to determine this before running running sp_dbcmptlevel?|||The only difference between levels 70 and 80 is that several reserved keywords introduced in SQL Server 2000 are not supported in level 70.|||When you upgrade from 7 to 2000 the default compatibility is to 2000 (8) so I am surprised that your compatibility it still for 7. Anyway, the short answer to your question is yes. Check out the following ms articles:

ms article 1 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_02wj.asp)

ms article 2 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_5dx1.asp) - This one says version 6.5, but there is some important information in general and specifically to version 7.|||So sorry! I was told Sql 2K but it is in fact 7.0. (that's what I get for not checking myself)

Now, I guess, best thing to do is just rewrite the proc to include the correct fields in the table creation and insert.

I'll have to start a new thread!

Thanks!