It depends on how often you use the views and how much of data you need from all the 10 tables. If majority of your usage is only couple of columns from couple of tables then smaller view with only required tables will be more efficient as opposed to one giant view that is called every time even if most of the info the view returns is not required.
Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts
Friday, March 9, 2012
Large views vs multiple small views
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
Friday, February 24, 2012
Large selection in multiple parameters
I have a problem and was wondering if someone might have some good
suggestions. I've created a report that shows the number of loans that
have been given to students at various colleges by various vendors.
The report has parameters for vendor and for college, and I created the
parameters as multi-value drop down boxes. The source for the schools
box is a query against my data table for the distinct Schools that
appear.
The problem I'm having is this: There are approximately 4000 schools
in the table, and when the user does a Select All on the web site, the
report chugs away for a while and then returns nothing. It works fine
in Visual Studio (slowly, but it returns everything) but not on the
website. I don't get any error messages.
The WHERE clause in my main script is simply:
Where SchoolName in (@.schoolName)
My guess is that there are simply too much being shoved into the
parameter when I try to select all. Just wondering if anyone has a
reasonable way to get around this. I've managed a rather kludgy method
where I have an additional boolean parameter and then logic to bypass
the WHERE if it's true, but I'd rather just have the thing work
normally (via the drop-down) if that's possible.
The drop down for the Vendors works just fine, whether I select one,
many, or all of the vendors. But there are only 30 of those, so the
cause seems to be the number of choices. Is there a set limit to the
number of choices in a drop down, or a set size that can be passed
perhaps?
Using Reporting Services 2005.How about doing a bit of processing before the query gets run. Something
along the lines of
IIF parameter!School.Item(0) = true, select data regardless of school (no
whereclause on Schoolname) else select data with whereclause.
I'm not quite sure how to check if the Select All parameter was selected or
not, but it might be food for thought anyway.
Kaisa M. Lindahl Lervik
<cphite@.gmail.com> wrote in message
news:1162419349.183710.148290@.h48g2000cwc.googlegroups.com...
>I have a problem and was wondering if someone might have some good
> suggestions. I've created a report that shows the number of loans that
> have been given to students at various colleges by various vendors.
> The report has parameters for vendor and for college, and I created the
> parameters as multi-value drop down boxes. The source for the schools
> box is a query against my data table for the distinct Schools that
> appear.
> The problem I'm having is this: There are approximately 4000 schools
> in the table, and when the user does a Select All on the web site, the
> report chugs away for a while and then returns nothing. It works fine
> in Visual Studio (slowly, but it returns everything) but not on the
> website. I don't get any error messages.
> The WHERE clause in my main script is simply:
> Where SchoolName in (@.schoolName)
> My guess is that there are simply too much being shoved into the
> parameter when I try to select all. Just wondering if anyone has a
> reasonable way to get around this. I've managed a rather kludgy method
> where I have an additional boolean parameter and then logic to bypass
> the WHERE if it's true, but I'd rather just have the thing work
> normally (via the drop-down) if that's possible.
> The drop down for the Vendors works just fine, whether I select one,
> many, or all of the vendors. But there are only 30 of those, so the
> cause seems to be the number of choices. Is there a set limit to the
> number of choices in a drop down, or a set size that can be passed
> perhaps?
> Using Reporting Services 2005.
>|||What I have done in the case is as follows. I found this function
CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=',')
RETURNS @.VALUES TABLE (Param nvarchar(max))AS
BEGIN
DECLARE @.chrind INT
DECLARE @.Piece nvarchar(max)
SELECT @.chrind = 1
WHILE @.chrind > 0
BEGIN
SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
IF @.chrind > 0
SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
ELSE
SELECT @.Piece = @.RepParam
INSERT @.VALUES(Param) VALUES(@.Piece)
SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
IF LEN(@.RepParam) = 0 BREAK
END
RETURN
END
This will break the comma separated string passed into your stored proc into
a table. Thow the results of this function into a temp table, and filter you
results with a join, instead of an in.
Ken
"Kaisa M. Lindahl Lervik" wrote:
> How about doing a bit of processing before the query gets run. Something
> along the lines of
> IIF parameter!School.Item(0) = true, select data regardless of school (no
> whereclause on Schoolname) else select data with whereclause.
> I'm not quite sure how to check if the Select All parameter was selected or
> not, but it might be food for thought anyway.
> Kaisa M. Lindahl Lervik
> <cphite@.gmail.com> wrote in message
> news:1162419349.183710.148290@.h48g2000cwc.googlegroups.com...
> >I have a problem and was wondering if someone might have some good
> > suggestions. I've created a report that shows the number of loans that
> > have been given to students at various colleges by various vendors.
> > The report has parameters for vendor and for college, and I created the
> > parameters as multi-value drop down boxes. The source for the schools
> > box is a query against my data table for the distinct Schools that
> > appear.
> >
> > The problem I'm having is this: There are approximately 4000 schools
> > in the table, and when the user does a Select All on the web site, the
> > report chugs away for a while and then returns nothing. It works fine
> > in Visual Studio (slowly, but it returns everything) but not on the
> > website. I don't get any error messages.
> >
> > The WHERE clause in my main script is simply:
> > Where SchoolName in (@.schoolName)
> >
> > My guess is that there are simply too much being shoved into the
> > parameter when I try to select all. Just wondering if anyone has a
> > reasonable way to get around this. I've managed a rather kludgy method
> > where I have an additional boolean parameter and then logic to bypass
> > the WHERE if it's true, but I'd rather just have the thing work
> > normally (via the drop-down) if that's possible.
> >
> > The drop down for the Vendors works just fine, whether I select one,
> > many, or all of the vendors. But there are only 30 of those, so the
> > cause seems to be the number of choices. Is there a set limit to the
> > number of choices in a drop down, or a set size that can be passed
> > perhaps?
> >
> > Using Reporting Services 2005.
> >
>
>|||Ken Reitmeyer wrote:
> What I have done in the case is as follows. I found this function
> CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=> ',')
> RETURNS @.VALUES TABLE (Param nvarchar(max))AS
> BEGIN
> DECLARE @.chrind INT
> DECLARE @.Piece nvarchar(max)
> SELECT @.chrind = 1
> WHILE @.chrind > 0
> BEGIN
> SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
> IF @.chrind > 0
> SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
> ELSE
> SELECT @.Piece = @.RepParam
> INSERT @.VALUES(Param) VALUES(@.Piece)
> SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
> IF LEN(@.RepParam) = 0 BREAK
> END
> RETURN
> END
> This will break the comma separated string passed into your stored proc into
> a table. Thow the results of this function into a temp table, and filter you
> results with a join, instead of an in.
Ken,
When I use this function it works for one selection, but when I select
more than one school it tells me I have too many parameters.|||This is how I am using the function to pass a long parameter list
--temp table to hold parameters
declare @.tbl_WorkCenters table
(
work_center int
)
declare @.sql varchar(max)
--@.work_centers is the comma separated parameter list passed in from the
reprot
set @.sql = 'select ltrim(param) from fn_MVParam(''' + @.work_centers + ''',
'','')'
insert into @.tbl_WorkCenters
exec (@.sql)
Not sure if that answers your question or not.
"cphite@.gmail.com" wrote:
> Ken Reitmeyer wrote:
> > What I have done in the case is as follows. I found this function
> >
> > CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=> > ',')
> > RETURNS @.VALUES TABLE (Param nvarchar(max))AS
> > BEGIN
> > DECLARE @.chrind INT
> > DECLARE @.Piece nvarchar(max)
> > SELECT @.chrind = 1
> > WHILE @.chrind > 0
> > BEGIN
> > SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
> > IF @.chrind > 0
> > SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
> > ELSE
> > SELECT @.Piece = @.RepParam
> > INSERT @.VALUES(Param) VALUES(@.Piece)
> > SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
> > IF LEN(@.RepParam) = 0 BREAK
> > END
> > RETURN
> > END
> >
> > This will break the comma separated string passed into your stored proc into
> > a table. Thow the results of this function into a temp table, and filter you
> > results with a join, instead of an in.
> Ken,
> When I use this function it works for one selection, but when I select
> more than one school it tells me I have too many parameters.
>
suggestions. I've created a report that shows the number of loans that
have been given to students at various colleges by various vendors.
The report has parameters for vendor and for college, and I created the
parameters as multi-value drop down boxes. The source for the schools
box is a query against my data table for the distinct Schools that
appear.
The problem I'm having is this: There are approximately 4000 schools
in the table, and when the user does a Select All on the web site, the
report chugs away for a while and then returns nothing. It works fine
in Visual Studio (slowly, but it returns everything) but not on the
website. I don't get any error messages.
The WHERE clause in my main script is simply:
Where SchoolName in (@.schoolName)
My guess is that there are simply too much being shoved into the
parameter when I try to select all. Just wondering if anyone has a
reasonable way to get around this. I've managed a rather kludgy method
where I have an additional boolean parameter and then logic to bypass
the WHERE if it's true, but I'd rather just have the thing work
normally (via the drop-down) if that's possible.
The drop down for the Vendors works just fine, whether I select one,
many, or all of the vendors. But there are only 30 of those, so the
cause seems to be the number of choices. Is there a set limit to the
number of choices in a drop down, or a set size that can be passed
perhaps?
Using Reporting Services 2005.How about doing a bit of processing before the query gets run. Something
along the lines of
IIF parameter!School.Item(0) = true, select data regardless of school (no
whereclause on Schoolname) else select data with whereclause.
I'm not quite sure how to check if the Select All parameter was selected or
not, but it might be food for thought anyway.
Kaisa M. Lindahl Lervik
<cphite@.gmail.com> wrote in message
news:1162419349.183710.148290@.h48g2000cwc.googlegroups.com...
>I have a problem and was wondering if someone might have some good
> suggestions. I've created a report that shows the number of loans that
> have been given to students at various colleges by various vendors.
> The report has parameters for vendor and for college, and I created the
> parameters as multi-value drop down boxes. The source for the schools
> box is a query against my data table for the distinct Schools that
> appear.
> The problem I'm having is this: There are approximately 4000 schools
> in the table, and when the user does a Select All on the web site, the
> report chugs away for a while and then returns nothing. It works fine
> in Visual Studio (slowly, but it returns everything) but not on the
> website. I don't get any error messages.
> The WHERE clause in my main script is simply:
> Where SchoolName in (@.schoolName)
> My guess is that there are simply too much being shoved into the
> parameter when I try to select all. Just wondering if anyone has a
> reasonable way to get around this. I've managed a rather kludgy method
> where I have an additional boolean parameter and then logic to bypass
> the WHERE if it's true, but I'd rather just have the thing work
> normally (via the drop-down) if that's possible.
> The drop down for the Vendors works just fine, whether I select one,
> many, or all of the vendors. But there are only 30 of those, so the
> cause seems to be the number of choices. Is there a set limit to the
> number of choices in a drop down, or a set size that can be passed
> perhaps?
> Using Reporting Services 2005.
>|||What I have done in the case is as follows. I found this function
CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=',')
RETURNS @.VALUES TABLE (Param nvarchar(max))AS
BEGIN
DECLARE @.chrind INT
DECLARE @.Piece nvarchar(max)
SELECT @.chrind = 1
WHILE @.chrind > 0
BEGIN
SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
IF @.chrind > 0
SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
ELSE
SELECT @.Piece = @.RepParam
INSERT @.VALUES(Param) VALUES(@.Piece)
SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
IF LEN(@.RepParam) = 0 BREAK
END
RETURN
END
This will break the comma separated string passed into your stored proc into
a table. Thow the results of this function into a temp table, and filter you
results with a join, instead of an in.
Ken
"Kaisa M. Lindahl Lervik" wrote:
> How about doing a bit of processing before the query gets run. Something
> along the lines of
> IIF parameter!School.Item(0) = true, select data regardless of school (no
> whereclause on Schoolname) else select data with whereclause.
> I'm not quite sure how to check if the Select All parameter was selected or
> not, but it might be food for thought anyway.
> Kaisa M. Lindahl Lervik
> <cphite@.gmail.com> wrote in message
> news:1162419349.183710.148290@.h48g2000cwc.googlegroups.com...
> >I have a problem and was wondering if someone might have some good
> > suggestions. I've created a report that shows the number of loans that
> > have been given to students at various colleges by various vendors.
> > The report has parameters for vendor and for college, and I created the
> > parameters as multi-value drop down boxes. The source for the schools
> > box is a query against my data table for the distinct Schools that
> > appear.
> >
> > The problem I'm having is this: There are approximately 4000 schools
> > in the table, and when the user does a Select All on the web site, the
> > report chugs away for a while and then returns nothing. It works fine
> > in Visual Studio (slowly, but it returns everything) but not on the
> > website. I don't get any error messages.
> >
> > The WHERE clause in my main script is simply:
> > Where SchoolName in (@.schoolName)
> >
> > My guess is that there are simply too much being shoved into the
> > parameter when I try to select all. Just wondering if anyone has a
> > reasonable way to get around this. I've managed a rather kludgy method
> > where I have an additional boolean parameter and then logic to bypass
> > the WHERE if it's true, but I'd rather just have the thing work
> > normally (via the drop-down) if that's possible.
> >
> > The drop down for the Vendors works just fine, whether I select one,
> > many, or all of the vendors. But there are only 30 of those, so the
> > cause seems to be the number of choices. Is there a set limit to the
> > number of choices in a drop down, or a set size that can be passed
> > perhaps?
> >
> > Using Reporting Services 2005.
> >
>
>|||Ken Reitmeyer wrote:
> What I have done in the case is as follows. I found this function
> CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=> ',')
> RETURNS @.VALUES TABLE (Param nvarchar(max))AS
> BEGIN
> DECLARE @.chrind INT
> DECLARE @.Piece nvarchar(max)
> SELECT @.chrind = 1
> WHILE @.chrind > 0
> BEGIN
> SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
> IF @.chrind > 0
> SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
> ELSE
> SELECT @.Piece = @.RepParam
> INSERT @.VALUES(Param) VALUES(@.Piece)
> SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
> IF LEN(@.RepParam) = 0 BREAK
> END
> RETURN
> END
> This will break the comma separated string passed into your stored proc into
> a table. Thow the results of this function into a temp table, and filter you
> results with a join, instead of an in.
Ken,
When I use this function it works for one selection, but when I select
more than one school it tells me I have too many parameters.|||This is how I am using the function to pass a long parameter list
--temp table to hold parameters
declare @.tbl_WorkCenters table
(
work_center int
)
declare @.sql varchar(max)
--@.work_centers is the comma separated parameter list passed in from the
reprot
set @.sql = 'select ltrim(param) from fn_MVParam(''' + @.work_centers + ''',
'','')'
insert into @.tbl_WorkCenters
exec (@.sql)
Not sure if that answers your question or not.
"cphite@.gmail.com" wrote:
> Ken Reitmeyer wrote:
> > What I have done in the case is as follows. I found this function
> >
> > CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=> > ',')
> > RETURNS @.VALUES TABLE (Param nvarchar(max))AS
> > BEGIN
> > DECLARE @.chrind INT
> > DECLARE @.Piece nvarchar(max)
> > SELECT @.chrind = 1
> > WHILE @.chrind > 0
> > BEGIN
> > SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
> > IF @.chrind > 0
> > SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
> > ELSE
> > SELECT @.Piece = @.RepParam
> > INSERT @.VALUES(Param) VALUES(@.Piece)
> > SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
> > IF LEN(@.RepParam) = 0 BREAK
> > END
> > RETURN
> > END
> >
> > This will break the comma separated string passed into your stored proc into
> > a table. Thow the results of this function into a temp table, and filter you
> > results with a join, instead of an in.
> Ken,
> When I use this function it works for one selection, but when I select
> more than one school it tells me I have too many parameters.
>
Large report export to pdf = corruption error
Hi,
I have a report which can produce 220 pages if the user select "all" in my
multiple choice list.
if the user try to export this report the PDF file is corrupted.
the same report with only a small amount of item selected instead-of the
"all" (so around 1 to 20 pages) is correct.
The export works fine.
any idea why?
Thanks.
Jerome.On Mar 7, 10:54 am, "Jeje" <willg...@.hotmail.com> wrote:
> Hi,
> I have a report which can produce 220 pages if the user select "all" in my
> multiple choice list.
> if the user try to export this report the PDF file is corrupted.
> the same report with only a small amount of item selected instead-of the
> "all" (so around 1 to 20 pages) is correct.
> The export works fine.
> any idea why?
> Thanks.
> Jerome.
I've never experience this issue; however, it sounds like a rendering
problem w/the quantity of data being exported to PDF. Have you tried
splitting up the results via the report query/stored procedure to
obtain a manageable amount of exported data? This might be a viable
solution.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I'm trying to create another report with less pages and a better query.
if this doesn't works, we'll change the prompt options to restrict the user
to 1 value only.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1173361141.038474.105280@.n33g2000cwc.googlegroups.com...
> On Mar 7, 10:54 am, "Jeje" <willg...@.hotmail.com> wrote:
>> Hi,
>> I have a report which can produce 220 pages if the user select "all" in
>> my
>> multiple choice list.
>> if the user try to export this report the PDF file is corrupted.
>> the same report with only a small amount of item selected instead-of the
>> "all" (so around 1 to 20 pages) is correct.
>> The export works fine.
>> any idea why?
>> Thanks.
>> Jerome.
>
> I've never experience this issue; however, it sounds like a rendering
> problem w/the quantity of data being exported to PDF. Have you tried
> splitting up the results via the report query/stored procedure to
> obtain a manageable amount of exported data? This might be a viable
> solution.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>
I have a report which can produce 220 pages if the user select "all" in my
multiple choice list.
if the user try to export this report the PDF file is corrupted.
the same report with only a small amount of item selected instead-of the
"all" (so around 1 to 20 pages) is correct.
The export works fine.
any idea why?
Thanks.
Jerome.On Mar 7, 10:54 am, "Jeje" <willg...@.hotmail.com> wrote:
> Hi,
> I have a report which can produce 220 pages if the user select "all" in my
> multiple choice list.
> if the user try to export this report the PDF file is corrupted.
> the same report with only a small amount of item selected instead-of the
> "all" (so around 1 to 20 pages) is correct.
> The export works fine.
> any idea why?
> Thanks.
> Jerome.
I've never experience this issue; however, it sounds like a rendering
problem w/the quantity of data being exported to PDF. Have you tried
splitting up the results via the report query/stored procedure to
obtain a manageable amount of exported data? This might be a viable
solution.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I'm trying to create another report with less pages and a better query.
if this doesn't works, we'll change the prompt options to restrict the user
to 1 value only.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1173361141.038474.105280@.n33g2000cwc.googlegroups.com...
> On Mar 7, 10:54 am, "Jeje" <willg...@.hotmail.com> wrote:
>> Hi,
>> I have a report which can produce 220 pages if the user select "all" in
>> my
>> multiple choice list.
>> if the user try to export this report the PDF file is corrupted.
>> the same report with only a small amount of item selected instead-of the
>> "all" (so around 1 to 20 pages) is correct.
>> The export works fine.
>> any idea why?
>> Thanks.
>> Jerome.
>
> I've never experience this issue; however, it sounds like a rendering
> problem w/the quantity of data being exported to PDF. Have you tried
> splitting up the results via the report query/stored procedure to
> obtain a manageable amount of exported data? This might be a viable
> solution.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>
Subscribe to:
Posts (Atom)