June 25, 2010 at 6:12 am
Apologies , my bad
June 25, 2010 at 10:02 am
I have to pass 12 parameters; out of which 11 are single value and one is multi value. I pass '%' while 'All' are selected for 11 parameters but the 12th parameters I dont want to pass all the options in the string.
I have applied covering index and now the query takes only 2 seconds to pull over 95000 records in SSMS.
So basically, I can not modify my SQL any further.
I am going to try Dynamic SQL now.
Else I have to make that 12th parameter as single value parameter which limits the usage of the report drastically.
Any suggestions whether to use Dynamic SQL or not?
August 24, 2010 at 1:44 pm
I am having the same problem except using a simple query such as
select top 10
FormattheName(client_table3.last_name, client_table3.first_name, client_table3.middle_name) as [Client Name],
client_table3.table3_id,
logTable_table5.actual_date,
logTable_table5.end_date,
table4.program_name,
table6.profile_name,
convert(char(10), client_table3.dob, 101) as DOB,
client_table3.last_name,
client_table3.first_name
from
logTable logTable_table5
join table2 table2
on table2.logTable_id = logTable_table5.logTable_id
--and is_initial = 1 -- limit to initial table5
inner join table3 client_table3
on client_table3.table3_id = logTable_table5.table3_id
JOIN table4 AS table4
ON logTable_table5.program_providing_service = table4.table4_id
join logTable logTable_faciltity_placement With (NoLock)
on logTable_faciltity_placement.table3_id = client_table3.table3_id
and logTable_table5.table2_event_id = logTable_faciltity_placement.table2_event_id
and logTable_table5.end_date is null
JOIN table5 table5 With (NoLock)
ON table5.logTable_id = logTable_faciltity_placement.logTable_id
JOIN table5_type table5_type With (NoLock)
ON table5.table5_type_id = table5_type.table5_type_id
and table5_type.type_code = 'FACIL_PLACEMENT'
JOIN table6 table6 With (NoLock)
ON table5.table6_id = table6.table6_id
where
table6.profile_name like 'somevalue%'
When running in Management Studio the time to execute is to short to register. In SSRS it takes over 5 minutes. Very frustrating.
February 13, 2011 at 12:49 am
Nice one ash, thanks for posting that as it helped me out alot!. Going from 10+ minutes to < 30 seconds was a great result. Seems to me like it's a bit of a bug in SSRS.
Thanks again,
Ryan
February 22, 2011 at 12:33 pm
Thanks, this post helped me resolve an issue that had us all scratching our heads. in SSMS my SQL ran in 30 seconds, in SSRS, it never stopped running. The DBAs were puzzled over the different execution plans they were seeing. That led me here and we switched the SQL to a PROC and used local params and voila! SSRS can run the report now.
Many thanks!
June 8, 2011 at 12:46 pm
Can I bring this topic back to life? I'm trying to figure out if any of these suggestions will work for me since I'm having the same problem, but it kind of seems like I'm already doing these things.
I created a report in SSRS. It has 6 parameters, and 5 of them are dropdowns that are populated via datasets in SSRS. I thought the report was running fine, but then a user advised me when she plugged in certain criteria the report wouldn't finish running. One of the parameters is a multi-select with 8 values. This seems to be where the report has a problem. I can select 4 of the 8 values, and it can only be those same 4 values! If I choose any of the 4 values the report doesn't run.
The field is just a string of integers. I am passing all the parameter values into my stored procedure, which is what populates the report itself. For this particular parameter, I am parsing the values into a table variable, and then am joining the table variable to the 'body' of my procedure to get back the records I want.
When I execute the procedure in SSMS, the results come back in less than a minute. When I run the report in SSRS (either locally or via our website), the report doesn't finish running. But here's the odd thing...if I select all 8 of the values in that parameter and change the value of one of the other parameters, it works fine!
Please, please, please, if anyone has any input I would greatly appreciate it. I need to get this report running but am out of ideas.
Thanks in advance!
June 8, 2011 at 1:33 pm
sounds like exactly what I went through. SQL Server will stage an execution plan better running through SSMS than it does receiving values from SSRS. if you change your SQL to a stored proc and use local variables then sql server should create a more generic plan and you should see a vast improvement.
Example:
create procedure yourProc
@yourVar varchar(20)
AS
declare @localVar varchar(20)
BEGIN
select @localVar = @yourVar
select *
from your table
where yourField = @localVar
END
the part of "select @localVar = @yourVar" is where you hand what you've sent in to your local variable. the existing query plan is based on your local variable.
it's been a few months since I looked at this and I am at a different job now so can't review the exact methodology, but give this a try, I'm 98% sure this was the exact resolution
June 9, 2011 at 6:38 am
Thank you so much for your feedback. But I think I'm confused. I was trying to make sense of this in your previous posting, but I'm not sure it applies to my problem. I'm not sure what it is that I need to turn into a procedure. Below is the jist of what I am using to populate my parameter's dropdown:
SELECT t.FieldId, t.FieldDescription
FROM dbo.Table AS t WITH (NOLOCK)
WHERE t.Active = -1
ORDER BY t.FieldDescription
Now, I could turn that into a stored proc, but I'm not sure what good it would do. Once I get the values back from the user's selection, I do pass those values into a stored procedure. I'm letting the proc do all the work.
Is this still similar to what you had to do, or did you not have a stored procedure to begin with?
Thanks again!
June 9, 2011 at 7:12 am
First I would look carefully at your data to make sure the problem is not there.
Second, take all the input parameters to your SP and convert them to local variables, and then use the local variables in the body of your SP. SQL Server and SSRS are prone to an issue called parameter sniffing where SQL Server frequently develops a very bad execution plan because of what it expects the input parameters to be.
Also, make sure that your input parameters and local variables are long enough to take your multi select input. I usually use VARCHAR(MAX) in both instances.
June 9, 2011 at 7:18 am
it sounds like the first problem to address is the parameter problem.
SELECT t.FieldId, t.FieldDescription
FROM dbo.Table AS t WITH (NOLOCK)
WHERE t.Active = -1
ORDER BY t.FieldD
could be bringin back dupes. check for that first.
June 9, 2011 at 7:37 am
Skott Shores (6/9/2011)
it sounds like the first problem to address is the parameter problem.SELECT t.FieldId, t.FieldDescription
FROM dbo.Table AS t WITH (NOLOCK)
WHERE t.Active = -1
ORDER BY t.FieldD
could be bringin back dupes. check for that first.
Thanks. Definitely not a problem with the parameter values. There are only 8 valid values in the table, and the ID field is a primary key. I am passing in the IDs (which are 2, 3, 4, 5, 6, 7, 9, and 10) as a comma-delimited string. And again, if I change the value of one of my other parameters, there is not problem with the data returning even if I select all of the values available. Perhaps it is the parameter sniffing problem.
June 9, 2011 at 7:39 am
try switching your main proc to using local variables as mentioned
June 9, 2011 at 7:53 am
Thanks so much for the input everyone. As it turns out, it didn't like the table variable I was putting my parameter values into. I decided that before I declared a local variable I would change the table variable to a #temp table and create a primary key. It worked like a charm! Guess it was the parameter sniffing! Thanks again!
August 18, 2011 at 9:52 am
Thank you so much, I name my parameters in SSRS reports the same as I do within the SP - makes this fix a breeze. As with others I couldn't understand why a fairly simple SP which tool 1-2 seconds in SSMS took over 5 mins (before I and the user cancelled it) to run. It now takes seconds as it does in SSMS.
Going to see what other reports are slow now and give this a go!
May 1, 2012 at 5:06 am
I would like to add my thanks to this also.
This saved me possibly hours of frustration.
Makes you wonder why SSRS engine does not simply convert the variables for the optimiser.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply