Any tips for using large parameter lists

Our parts table has 5k records. I want to use part number as a parameter for one of my reports. Is there a way to do this and have the report generate in a reasonable amount of time?

Thanks

Create a new dataset on the Data tab with the query as "SELECT part_number from Parts" and name the dataset as, say DataSet2.

Go to Report (menu) -> Report Parameters

and add a new parameter and give the name, type and prompt.

Then under Available Values, select "From Query" radio button

select DataSet2 under Dataset, part_number under Value field

Shyam

|||Thats the problem.
It takes five minutes for the param list to render.|||

Maybe you can reduce the query time on SQL server by adding index (clustered preferably) to part_number column. This is the only solution to reduce the load time.

Shyam

Any ideas on this one ?

I have a Datetime parameter from a dataset. It also shows the time but I
only need the date, is it possible somehow to change how it displays the
parameter. As what I can see it is only possible to choose datetime format.
I tried to convert it like this convert(datetime,period,105) as dateonly and
then use this field but it shows exactly the same !
JackI ended up writing a sql function to do this.
Basically the function subtracts the hours , minutes and seconds using
the DateAdd statement. There vb equivalents to this to - look up date
functions in BOL.
Chris
Jack Nielsen wrote:
> I have a Datetime parameter from a dataset. It also shows the time
> but I only need the date, is it possible somehow to change how it
> displays the parameter. As what I can see it is only possible to
> choose datetime format. I tried to convert it like this
> convert(datetime,period,105) as dateonly and then use this field but
> it shows exactly the same !
> Jack|||Could this be used somehow, and if how do I show this new field in the
Parameter and still use the datetime field in the sql statement ?
USE Northwind
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id =object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
DROP FUNCTION [dbo].[udf_MyDate]
GO
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
This is the statement where i include the parameter
HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART >
@.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
And it now shows like this 12/01/2005 00:00:00 in the report parameter,
would like it only to show 12/01/2005 but still using a real datetime format
so that the statement still works.
Jack
> I ended up writing a sql function to do this.
> Basically the function subtracts the hours , minutes and seconds using
> the DateAdd statement. There vb equivalents to this to - look up date
> functions in BOL.
> Chris
> Jack Nielsen wrote:
> > I have a Datetime parameter from a dataset. It also shows the time
> > but I only need the date, is it possible somehow to change how it
> > displays the parameter. As what I can see it is only possible to
> > choose datetime format. I tried to convert it like this
> > convert(datetime,period,105) as dateonly and then use this field but
> > it shows exactly the same !
> >
> > Jack
>|||Can you not just return the datetime from the dataset and format it in the
layout of the report using an expression (=Format(Fields!dateTimeField.Value,
"MMM dd, yyyy"))? Am I missing something in your requirements?
MKD
"Jack Nielsen" wrote:
> Could this be used somehow, and if how do I show this new field in the
> Parameter and still use the datetime field in the sql statement ?
> USE Northwind
> GO
> IF EXISTS (SELECT * FROM sysobjects WHERE id => object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
> DROP FUNCTION [dbo].[udf_MyDate]
> GO
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> This is the statement where i include the parameter
> HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART >
> @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
> And it now shows like this 12/01/2005 00:00:00 in the report parameter,
> would like it only to show 12/01/2005 but still using a real datetime format
> so that the statement still works.
> Jack
> > I ended up writing a sql function to do this.
> > Basically the function subtracts the hours , minutes and seconds using
> > the DateAdd statement. There vb equivalents to this to - look up date
> > functions in BOL.
> >
> > Chris
> >
> > Jack Nielsen wrote:
> >
> > > I have a Datetime parameter from a dataset. It also shows the time
> > > but I only need the date, is it possible somehow to change how it
> > > displays the parameter. As what I can see it is only possible to
> > > choose datetime format. I tried to convert it like this
> > > convert(datetime,period,105) as dateonly and then use this field but
> > > it shows exactly the same !
> > >
> > > Jack
> >
>
>|||Ducky,
This is a date from a dataset, being used to populate a parameter list.
This is why we have to be a bit more convaluted.
Jack,
In your dataset do something like this;
Select MyDate, dbo.udf_MyDate(MyDate, '/') As Label From etc ...
In your parameter set MyDate as the Value and Label as the Label.
Chris
ducky wrote:
> Can you not just return the datetime from the dataset and format it
> in the layout of the report using an expression
> (=Format(Fields!dateTimeField.Value, "MMM dd, yyyy"))? Am I missing
> something in your requirements?
> MKD
>
> "Jack Nielsen" wrote:
> > Could this be used somehow, and if how do I show this new field in
> > the Parameter and still use the datetime field in the sql statement
> > ?
> >
> > USE Northwind
> > GO
> > IF EXISTS (SELECT * FROM sysobjects WHERE id => > object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
> > DROP FUNCTION [dbo].[udf_MyDate]
> > GO
> > CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> > RETURNS Nchar(20)
> > AS
> > BEGIN
> > RETURN
> > CONVERT(Nvarchar(20), datepart(mm,@.indate))
> > + @.separator
> > + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> > + @.separator
> > + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> > END
> > GO
> >
> > This is the statement where i include the parameter
> > HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART
> > > @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
> >
> > And it now shows like this 12/01/2005 00:00:00 in the report
> > parameter, would like it only to show 12/01/2005 but still using a
> > real datetime format so that the statement still works.
> >
> > Jack
> >
> > > I ended up writing a sql function to do this.
> > > Basically the function subtracts the hours , minutes and seconds
> > > using the DateAdd statement. There vb equivalents to this to -
> > > look up date functions in BOL.
> > >
> > > Chris
> > >
> > > Jack Nielsen wrote:
> > >
> > > > I have a Datetime parameter from a dataset. It also shows the
> > > > time but I only need the date, is it possible somehow to change
> > > > how it displays the parameter. As what I can see it is only
> > > > possible to choose datetime format. I tried to convert it like
> > > > this convert(datetime,period,105) as dateonly and then use this
> > > > field but it shows exactly the same !
> > > >
> > > > Jack
> > >
> >
> >
> >|||If your RS parameter is set to a string, the original
"convert(datetime,period,105)" should give you the listing you desire. When
this parameter is then passed to SQL it "should" automatically be recognized
as a date, but if not, you could pass the parameter as as string, and then
declare and set a new SQL parameter to the cast(@.param as datetime).
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:eP03NtUiFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Ducky,
> This is a date from a dataset, being used to populate a parameter list.
> This is why we have to be a bit more convaluted.
> Jack,
> In your dataset do something like this;
> Select MyDate, dbo.udf_MyDate(MyDate, '/') As Label From etc ...
> In your parameter set MyDate as the Value and Label as the Label.
> Chris
>
> ducky wrote:
>> Can you not just return the datetime from the dataset and format it
>> in the layout of the report using an expression
>> (=Format(Fields!dateTimeField.Value, "MMM dd, yyyy"))? Am I missing
>> something in your requirements?
>> MKD
>>
>> "Jack Nielsen" wrote:
>> > Could this be used somehow, and if how do I show this new field in
>> > the Parameter and still use the datetime field in the sql statement
>> > ?
>> >
>> > USE Northwind
>> > GO
>> > IF EXISTS (SELECT * FROM sysobjects WHERE id =>> > object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
>> > DROP FUNCTION [dbo].[udf_MyDate]
>> > GO
>> > CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
>> > RETURNS Nchar(20)
>> > AS
>> > BEGIN
>> > RETURN
>> > CONVERT(Nvarchar(20), datepart(mm,@.indate))
>> > + @.separator
>> > + CONVERT(Nvarchar(20), datepart(dd, @.indate))
>> > + @.separator
>> > + CONVERT(Nvarchar(20), datepart(yy, @.indate))
>> > END
>> > GO
>> >
>> > This is the statement where i include the parameter
>> > HAVING (DEBSTAT.DATASET = @.Regnskab) AND (DEBSTAT.PERIODESTART
>> > > @.periodestartparm) AND (DEBSTAT.PERIODESTART < @.periodeslutparm)
>> >
>> > And it now shows like this 12/01/2005 00:00:00 in the report
>> > parameter, would like it only to show 12/01/2005 but still using a
>> > real datetime format so that the statement still works.
>> >
>> > Jack
>> >
>> > > I ended up writing a sql function to do this.
>> > > Basically the function subtracts the hours , minutes and seconds
>> > > using the DateAdd statement. There vb equivalents to this to -
>> > > look up date functions in BOL.
>> > >
>> > > Chris
>> > >
>> > > Jack Nielsen wrote:
>> > >
>> > > > I have a Datetime parameter from a dataset. It also shows the
>> > > > time but I only need the date, is it possible somehow to change
>> > > > how it displays the parameter. As what I can see it is only
>> > > > possible to choose datetime format. I tried to convert it like
>> > > > this convert(datetime,period,105) as dateonly and then use this
>> > > > field but it shows exactly the same !
>> > > >
>> > > > Jack
>> > >
>> >
>> >
>> >
>|||> If your RS parameter is set to a string, the original
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it still
shows the time.
I'm trying to do it the other way around but get syntax error, what could be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
Jack

Any ideas on this one ?

If your RS parameter is set to a string, the original
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it still
shows the time.
I'm trying to do it the other way around but get syntax error, what could be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
JackJack, recommend you check Books on Line. Here's what it says:"Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )"
Style is very useful here; you may need to experiment with it until you get
the output you want. By using this, you won't need a function. You can use
the convert statement in your query.
I hope this helps you.
Henry Robinette
"Jack Nielsen" wrote:
> If your RS parameter is set to a string, the original
> > "convert(datetime,period,105)" should give you the listing you desire.
> When
> > this parameter is then passed to SQL it "should" automatically be
> recognized
> > as a date, but if not, you could pass the parameter as as string, and then
> > declare and set a new SQL parameter to the cast(@.param as datetime).
> This doesn't seem to work, if I set it to string and do the convert it still
> shows the time.
> I'm trying to do it the other way around but get syntax error, what could be
> wrong here:
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
> (periodestart,'/') AS pstart
> FROM DEBSTAT
> WHERE (DAY(PERIODESTART) <> '31')
> ORDER BY PERIODESTART
> DROP FUNCTION [dbo].[udf_MyDate]
> Jack
>
>

any advantage using an xml string as parameter

Hi,
I just wanna know the pros and cons while using XML string as argument to the procedure. Which one will be better in terms of security and performance.
regards
Aneesh R.
That depends on what you want to do with the parameter. Could you please be a bit more specific?
Best regards
Michael
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:%23TD84aoiEHA.1356@.TK2MSFTNGP09.phx.gbl...
Hi,
I just wanna know the pros and cons while using XML string as argument to the procedure. Which one will be better in terms of security and performance.
regards
Aneesh R.
|||Hi,
Actually the application is E-Com. Is it better to use XML parameters for Password verification, UserRegistration etc. I need to consider both Performance and security
regards
Aneesh R.
That depends on what you want to do with the parameter. Could you please be a bit more specific?
Best regards
Michael
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:%23TD84aoiEHA.1356@.TK2MSFTNGP09.phx.gbl...
Hi,
I just wanna know the pros and cons while using XML string as argument to the procedure. Which one will be better in terms of security and performance.
regards
Aneesh R.
|||Some items are dependent on your perf and scaling requirements:
- Are you planning on using OpenXML to unshred it? You may find that large XML documents may be problematic from a scaling point of view (>100kB). Small ones should be fine.
- Are you planning on using a T-SQL statement instead to parse a CSV format? That may be less efficient, but performance tests should be done.
Some items are general security items:
- Do you expose the stored proc parameter to arbitrary users? In that case there are some DoS scenarios that you may need to prepare for by checking for them on the mid-tier. If you have full control over the XML format that you send, then there is no more or less security than on any other data value that you send.
HTH
Michael
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:O0iDzmyiEHA.3564@.TK2MSFTNGP10.phx.gbl...
Hi,
Actually the application is E-Com. Is it better to use XML parameters for Password verification, UserRegistration etc. I need to consider both Performance and security
regards
Aneesh R.
That depends on what you want to do with the parameter. Could you please be a bit more specific?
Best regards
Michael
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:%23TD84aoiEHA.1356@.TK2MSFTNGP09.phx.gbl...
Hi,
I just wanna know the pros and cons while using XML string as argument to the procedure. Which one will be better in terms of security and performance.
regards
Aneesh R.