Ok here we go,
MySQL 5
MyODBC 3.51
Crystal Reports.Net
and
ASP.Net using VB.Net
Set up the connection to the DB in Crystal using MyODBC. I used the Database expert to call my Stored Procedure which has one Parameter and the code is real simple going like this:
call SuccessLetter (AppNo)
From the wizard i can see and place all the fields from the SP onto the report as long as i hard code an application number into my parameter. But as soon as i change it to a variable (as above) i get the following error from Crystal: Unknown Fields AppNo in order clause MySQL ODBC 3.51 error 42S21
On the VB code side i used
Success.SetParameterValue("AppNo", GeneralFunctions.ApplicationNo)
to set up the parameter and this does get passed thru so the error is comming from the database expert - almost as if it dont recognise that my stored procedure has a parameter. Which it does and it does work if i use the SP outside of Crystal.
Any ideas - please help me before i go bald from tearing the hair out.
ThanksAnd another weird thing i;ve noticed is that Crystal puts the stored procedure in the list of tables when i use the database expert when i go to choose my tables
Showing posts with label net. Show all posts
Showing posts with label net. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
parameter collection
greetings -
I am trying to set up a custom parameters page in an ASP .NET application -
I am in the process of choosing between keeping the values (information I
need to list the parameters for the reports, as well as the available, valid
and default values for any given report) in a database or access all I need
through reporting services (web service).
Does anyone have any advice for setting up a Web App to run rs reports - can
you get all you need from the rs web services to create a custom parameter
page, or is it better to retrieve the values you need from a database?
I can dynamically build the custom parm page using values from a database
(dynamically list the parms for the report, fire off the sp to return valid
values for the parm and set the default value) - I would like to get this
going using the web services but am stuck at the point of trying to fill the
valid parm values - I do have the name of the stored procedure to run to
retrieve the valid values for the parm, but can't seem to figure out how to
either a)get the name of the sp to run, or b)just do a databind some how to
my dynamic control (what ever that may be - a textbox, a list box, a drop
down list, etc.)
Any ideas - ?
ThanksI have done exactly this, but the code is at work and it is a (very) long
weekend.
You can certainly use the web services to retrieve everything - the trick to
getting the values you have set up from datasets that populate the possible
values etc is to that when you run the GetReportParameters method, make sure
you set the ForRendering option to true - then everything you need comes
back.
Have a go and I'll post my code on Tuesday
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> greetings -
> I am trying to set up a custom parameters page in an ASP .NET
> application -
> I am in the process of choosing between keeping the values (information I
> need to list the parameters for the reports, as well as the available,
> valid
> and default values for any given report) in a database or access all I
> need
> through reporting services (web service).
> Does anyone have any advice for setting up a Web App to run rs reports -
> can
> you get all you need from the rs web services to create a custom parameter
> page, or is it better to retrieve the values you need from a database?
> I can dynamically build the custom parm page using values from a database
> (dynamically list the parms for the report, fire off the sp to return
> valid
> values for the parm and set the default value) - I would like to get this
> going using the web services but am stuck at the point of trying to fill
> the
> valid parm values - I do have the name of the stored procedure to run to
> retrieve the valid values for the parm, but can't seem to figure out how
> to
> either a)get the name of the sp to run, or b)just do a databind some how
> to
> my dynamic control (what ever that may be - a textbox, a list box, a drop
> down list, etc.)
> Any ideas - ?
>
> Thanks
>
>|||Thank you for the reply Mary - I may need a little help yet,
I went back and read my question and I was so involved I think I mis-stated
my question! Do the web services expose the names of the stored procedures
used to fill the valid values for the parameters - from your response it
sounds as if you just use the GetReportParameters method and that returns
everything (all of the datasets for all of the parameters) - if that's the
case how do you separate or sort through it all...HELP!
It was a very long weekend - and I still didn't get anything done!
Thanks,
"Mary Bray [MVP]" wrote:
> I have done exactly this, but the code is at work and it is a (very) long
> weekend.
> You can certainly use the web services to retrieve everything - the trick to
> getting the values you have set up from datasets that populate the possible
> values etc is to that when you run the GetReportParameters method, make sure
> you set the ForRendering option to true - then everything you need comes
> back.
> Have a go and I'll post my code on Tuesday
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > greetings -
> >
> > I am trying to set up a custom parameters page in an ASP .NET
> > application -
> > I am in the process of choosing between keeping the values (information I
> > need to list the parameters for the reports, as well as the available,
> > valid
> > and default values for any given report) in a database or access all I
> > need
> > through reporting services (web service).
> >
> > Does anyone have any advice for setting up a Web App to run rs reports -
> > can
> > you get all you need from the rs web services to create a custom parameter
> > page, or is it better to retrieve the values you need from a database?
> >
> > I can dynamically build the custom parm page using values from a database
> > (dynamically list the parms for the report, fire off the sp to return
> > valid
> > values for the parm and set the default value) - I would like to get this
> > going using the web services but am stuck at the point of trying to fill
> > the
> > valid parm values - I do have the name of the stored procedure to run to
> > retrieve the valid values for the parm, but can't seem to figure out how
> > to
> > either a)get the name of the sp to run, or b)just do a databind some how
> > to
> > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > down list, etc.)
> >
> > Any ideas - ?
> >
> >
> > Thanks
> >
> >
> >
> >
>
>|||OK - now I'm at work so here are some code snippets that may help (in C#):
They are used to build drop down lists in a web page with the parameter
values. This way RS takes care of the security for the parameter data. To
find out the names of the stored procs you need to get the DataSetDefinition
object and query it. I haven't yet worked out how to get it back though...
sorry. I think i tried this first and gave up, so used the parameters
collection to build the lookup data.
ReportingService rs=new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
bool forRendering = true;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(ReportPath, historyID, forRendering,
values, credentials);
if (parameters != null)
{
foreach (ReportParameter rp in parameters)
if((rp.ValidValues!=null)||(rp.ValidValuesQueryBased))
{
DropDownList lst=new DropDownList();
foreach(ValidValue vv in rp.ValidValues)
{
ListItem item=new ListItem();
item.Text=vv.Label;
item.Value=vv.Value;
lst.Items.Add(item);
}
"Myles" wrote:
> Thank you for the reply Mary - I may need a little help yet,
> I went back and read my question and I was so involved I think I mis-stated
> my question! Do the web services expose the names of the stored procedures
> used to fill the valid values for the parameters - from your response it
> sounds as if you just use the GetReportParameters method and that returns
> everything (all of the datasets for all of the parameters) - if that's the
> case how do you separate or sort through it all...HELP!
> It was a very long weekend - and I still didn't get anything done!
>
> Thanks,
>
> "Mary Bray [MVP]" wrote:
> > I have done exactly this, but the code is at work and it is a (very) long
> > weekend.
> > You can certainly use the web services to retrieve everything - the trick to
> > getting the values you have set up from datasets that populate the possible
> > values etc is to that when you run the GetReportParameters method, make sure
> > you set the ForRendering option to true - then everything you need comes
> > back.
> >
> > Have a go and I'll post my code on Tuesday
> > --
> >
> > Mary Bray [SQL Server MVP]
> > Please reply only to newsgroups
> >
> > "Myles" <Myles@.discussions.microsoft.com> wrote in message
> > news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > > greetings -
> > >
> > > I am trying to set up a custom parameters page in an ASP .NET
> > > application -
> > > I am in the process of choosing between keeping the values (information I
> > > need to list the parameters for the reports, as well as the available,
> > > valid
> > > and default values for any given report) in a database or access all I
> > > need
> > > through reporting services (web service).
> > >
> > > Does anyone have any advice for setting up a Web App to run rs reports -
> > > can
> > > you get all you need from the rs web services to create a custom parameter
> > > page, or is it better to retrieve the values you need from a database?
> > >
> > > I can dynamically build the custom parm page using values from a database
> > > (dynamically list the parms for the report, fire off the sp to return
> > > valid
> > > values for the parm and set the default value) - I would like to get this
> > > going using the web services but am stuck at the point of trying to fill
> > > the
> > > valid parm values - I do have the name of the stored procedure to run to
> > > retrieve the valid values for the parm, but can't seem to figure out how
> > > to
> > > either a)get the name of the sp to run, or b)just do a databind some how
> > > to
> > > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > > down list, etc.)
> > >
> > > Any ideas - ?
> > >
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> >
> >
> >|||beautiful - thank you very much Mary - this will be a tremendous help. If I
figure out how to actually get the sp names I will post back ~
Hope you had a great weekend!
Thanks,
Pete
"Mary Bray [SQL Server MVP]" wrote:
> OK - now I'm at work so here are some code snippets that may help (in C#):
> They are used to build drop down lists in a web page with the parameter
> values. This way RS takes care of the security for the parameter data. To
> find out the names of the stored procs you need to get the DataSetDefinition
> object and query it. I haven't yet worked out how to get it back though...
> sorry. I think i tried this first and gave up, so used the parameters
> collection to build the lookup data.
> ReportingService rs=new ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> bool forRendering = true;
> string historyID = null;
> ParameterValue[] values = null;
> DataSourceCredentials[] credentials = null;
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(ReportPath, historyID, forRendering,
> values, credentials);
> if (parameters != null)
> {
> foreach (ReportParameter rp in parameters)
> if((rp.ValidValues!=null)||(rp.ValidValuesQueryBased))
> {
> DropDownList lst=new DropDownList();
> foreach(ValidValue vv in rp.ValidValues)
> {
> ListItem item=new ListItem();
> item.Text=vv.Label;
> item.Value=vv.Value;
> lst.Items.Add(item);
> }
>
>
> "Myles" wrote:
> > Thank you for the reply Mary - I may need a little help yet,
> >
> > I went back and read my question and I was so involved I think I mis-stated
> > my question! Do the web services expose the names of the stored procedures
> > used to fill the valid values for the parameters - from your response it
> > sounds as if you just use the GetReportParameters method and that returns
> > everything (all of the datasets for all of the parameters) - if that's the
> > case how do you separate or sort through it all...HELP!
> >
> > It was a very long weekend - and I still didn't get anything done!
> >
> >
> > Thanks,
> >
> >
> >
> > "Mary Bray [MVP]" wrote:
> >
> > > I have done exactly this, but the code is at work and it is a (very) long
> > > weekend.
> > > You can certainly use the web services to retrieve everything - the trick to
> > > getting the values you have set up from datasets that populate the possible
> > > values etc is to that when you run the GetReportParameters method, make sure
> > > you set the ForRendering option to true - then everything you need comes
> > > back.
> > >
> > > Have a go and I'll post my code on Tuesday
> > > --
> > >
> > > Mary Bray [SQL Server MVP]
> > > Please reply only to newsgroups
> > >
> > > "Myles" <Myles@.discussions.microsoft.com> wrote in message
> > > news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > > > greetings -
> > > >
> > > > I am trying to set up a custom parameters page in an ASP .NET
> > > > application -
> > > > I am in the process of choosing between keeping the values (information I
> > > > need to list the parameters for the reports, as well as the available,
> > > > valid
> > > > and default values for any given report) in a database or access all I
> > > > need
> > > > through reporting services (web service).
> > > >
> > > > Does anyone have any advice for setting up a Web App to run rs reports -
> > > > can
> > > > you get all you need from the rs web services to create a custom parameter
> > > > page, or is it better to retrieve the values you need from a database?
> > > >
> > > > I can dynamically build the custom parm page using values from a database
> > > > (dynamically list the parms for the report, fire off the sp to return
> > > > valid
> > > > values for the parm and set the default value) - I would like to get this
> > > > going using the web services but am stuck at the point of trying to fill
> > > > the
> > > > valid parm values - I do have the name of the stored procedure to run to
> > > > retrieve the valid values for the parm, but can't seem to figure out how
> > > > to
> > > > either a)get the name of the sp to run, or b)just do a databind some how
> > > > to
> > > > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > > > down list, etc.)
> > > >
> > > > Any ideas - ?
> > > >
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
I am trying to set up a custom parameters page in an ASP .NET application -
I am in the process of choosing between keeping the values (information I
need to list the parameters for the reports, as well as the available, valid
and default values for any given report) in a database or access all I need
through reporting services (web service).
Does anyone have any advice for setting up a Web App to run rs reports - can
you get all you need from the rs web services to create a custom parameter
page, or is it better to retrieve the values you need from a database?
I can dynamically build the custom parm page using values from a database
(dynamically list the parms for the report, fire off the sp to return valid
values for the parm and set the default value) - I would like to get this
going using the web services but am stuck at the point of trying to fill the
valid parm values - I do have the name of the stored procedure to run to
retrieve the valid values for the parm, but can't seem to figure out how to
either a)get the name of the sp to run, or b)just do a databind some how to
my dynamic control (what ever that may be - a textbox, a list box, a drop
down list, etc.)
Any ideas - ?
ThanksI have done exactly this, but the code is at work and it is a (very) long
weekend.
You can certainly use the web services to retrieve everything - the trick to
getting the values you have set up from datasets that populate the possible
values etc is to that when you run the GetReportParameters method, make sure
you set the ForRendering option to true - then everything you need comes
back.
Have a go and I'll post my code on Tuesday
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> greetings -
> I am trying to set up a custom parameters page in an ASP .NET
> application -
> I am in the process of choosing between keeping the values (information I
> need to list the parameters for the reports, as well as the available,
> valid
> and default values for any given report) in a database or access all I
> need
> through reporting services (web service).
> Does anyone have any advice for setting up a Web App to run rs reports -
> can
> you get all you need from the rs web services to create a custom parameter
> page, or is it better to retrieve the values you need from a database?
> I can dynamically build the custom parm page using values from a database
> (dynamically list the parms for the report, fire off the sp to return
> valid
> values for the parm and set the default value) - I would like to get this
> going using the web services but am stuck at the point of trying to fill
> the
> valid parm values - I do have the name of the stored procedure to run to
> retrieve the valid values for the parm, but can't seem to figure out how
> to
> either a)get the name of the sp to run, or b)just do a databind some how
> to
> my dynamic control (what ever that may be - a textbox, a list box, a drop
> down list, etc.)
> Any ideas - ?
>
> Thanks
>
>|||Thank you for the reply Mary - I may need a little help yet,
I went back and read my question and I was so involved I think I mis-stated
my question! Do the web services expose the names of the stored procedures
used to fill the valid values for the parameters - from your response it
sounds as if you just use the GetReportParameters method and that returns
everything (all of the datasets for all of the parameters) - if that's the
case how do you separate or sort through it all...HELP!
It was a very long weekend - and I still didn't get anything done!
Thanks,
"Mary Bray [MVP]" wrote:
> I have done exactly this, but the code is at work and it is a (very) long
> weekend.
> You can certainly use the web services to retrieve everything - the trick to
> getting the values you have set up from datasets that populate the possible
> values etc is to that when you run the GetReportParameters method, make sure
> you set the ForRendering option to true - then everything you need comes
> back.
> Have a go and I'll post my code on Tuesday
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > greetings -
> >
> > I am trying to set up a custom parameters page in an ASP .NET
> > application -
> > I am in the process of choosing between keeping the values (information I
> > need to list the parameters for the reports, as well as the available,
> > valid
> > and default values for any given report) in a database or access all I
> > need
> > through reporting services (web service).
> >
> > Does anyone have any advice for setting up a Web App to run rs reports -
> > can
> > you get all you need from the rs web services to create a custom parameter
> > page, or is it better to retrieve the values you need from a database?
> >
> > I can dynamically build the custom parm page using values from a database
> > (dynamically list the parms for the report, fire off the sp to return
> > valid
> > values for the parm and set the default value) - I would like to get this
> > going using the web services but am stuck at the point of trying to fill
> > the
> > valid parm values - I do have the name of the stored procedure to run to
> > retrieve the valid values for the parm, but can't seem to figure out how
> > to
> > either a)get the name of the sp to run, or b)just do a databind some how
> > to
> > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > down list, etc.)
> >
> > Any ideas - ?
> >
> >
> > Thanks
> >
> >
> >
> >
>
>|||OK - now I'm at work so here are some code snippets that may help (in C#):
They are used to build drop down lists in a web page with the parameter
values. This way RS takes care of the security for the parameter data. To
find out the names of the stored procs you need to get the DataSetDefinition
object and query it. I haven't yet worked out how to get it back though...
sorry. I think i tried this first and gave up, so used the parameters
collection to build the lookup data.
ReportingService rs=new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
bool forRendering = true;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(ReportPath, historyID, forRendering,
values, credentials);
if (parameters != null)
{
foreach (ReportParameter rp in parameters)
if((rp.ValidValues!=null)||(rp.ValidValuesQueryBased))
{
DropDownList lst=new DropDownList();
foreach(ValidValue vv in rp.ValidValues)
{
ListItem item=new ListItem();
item.Text=vv.Label;
item.Value=vv.Value;
lst.Items.Add(item);
}
"Myles" wrote:
> Thank you for the reply Mary - I may need a little help yet,
> I went back and read my question and I was so involved I think I mis-stated
> my question! Do the web services expose the names of the stored procedures
> used to fill the valid values for the parameters - from your response it
> sounds as if you just use the GetReportParameters method and that returns
> everything (all of the datasets for all of the parameters) - if that's the
> case how do you separate or sort through it all...HELP!
> It was a very long weekend - and I still didn't get anything done!
>
> Thanks,
>
> "Mary Bray [MVP]" wrote:
> > I have done exactly this, but the code is at work and it is a (very) long
> > weekend.
> > You can certainly use the web services to retrieve everything - the trick to
> > getting the values you have set up from datasets that populate the possible
> > values etc is to that when you run the GetReportParameters method, make sure
> > you set the ForRendering option to true - then everything you need comes
> > back.
> >
> > Have a go and I'll post my code on Tuesday
> > --
> >
> > Mary Bray [SQL Server MVP]
> > Please reply only to newsgroups
> >
> > "Myles" <Myles@.discussions.microsoft.com> wrote in message
> > news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > > greetings -
> > >
> > > I am trying to set up a custom parameters page in an ASP .NET
> > > application -
> > > I am in the process of choosing between keeping the values (information I
> > > need to list the parameters for the reports, as well as the available,
> > > valid
> > > and default values for any given report) in a database or access all I
> > > need
> > > through reporting services (web service).
> > >
> > > Does anyone have any advice for setting up a Web App to run rs reports -
> > > can
> > > you get all you need from the rs web services to create a custom parameter
> > > page, or is it better to retrieve the values you need from a database?
> > >
> > > I can dynamically build the custom parm page using values from a database
> > > (dynamically list the parms for the report, fire off the sp to return
> > > valid
> > > values for the parm and set the default value) - I would like to get this
> > > going using the web services but am stuck at the point of trying to fill
> > > the
> > > valid parm values - I do have the name of the stored procedure to run to
> > > retrieve the valid values for the parm, but can't seem to figure out how
> > > to
> > > either a)get the name of the sp to run, or b)just do a databind some how
> > > to
> > > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > > down list, etc.)
> > >
> > > Any ideas - ?
> > >
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> >
> >
> >|||beautiful - thank you very much Mary - this will be a tremendous help. If I
figure out how to actually get the sp names I will post back ~
Hope you had a great weekend!
Thanks,
Pete
"Mary Bray [SQL Server MVP]" wrote:
> OK - now I'm at work so here are some code snippets that may help (in C#):
> They are used to build drop down lists in a web page with the parameter
> values. This way RS takes care of the security for the parameter data. To
> find out the names of the stored procs you need to get the DataSetDefinition
> object and query it. I haven't yet worked out how to get it back though...
> sorry. I think i tried this first and gave up, so used the parameters
> collection to build the lookup data.
> ReportingService rs=new ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> bool forRendering = true;
> string historyID = null;
> ParameterValue[] values = null;
> DataSourceCredentials[] credentials = null;
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(ReportPath, historyID, forRendering,
> values, credentials);
> if (parameters != null)
> {
> foreach (ReportParameter rp in parameters)
> if((rp.ValidValues!=null)||(rp.ValidValuesQueryBased))
> {
> DropDownList lst=new DropDownList();
> foreach(ValidValue vv in rp.ValidValues)
> {
> ListItem item=new ListItem();
> item.Text=vv.Label;
> item.Value=vv.Value;
> lst.Items.Add(item);
> }
>
>
> "Myles" wrote:
> > Thank you for the reply Mary - I may need a little help yet,
> >
> > I went back and read my question and I was so involved I think I mis-stated
> > my question! Do the web services expose the names of the stored procedures
> > used to fill the valid values for the parameters - from your response it
> > sounds as if you just use the GetReportParameters method and that returns
> > everything (all of the datasets for all of the parameters) - if that's the
> > case how do you separate or sort through it all...HELP!
> >
> > It was a very long weekend - and I still didn't get anything done!
> >
> >
> > Thanks,
> >
> >
> >
> > "Mary Bray [MVP]" wrote:
> >
> > > I have done exactly this, but the code is at work and it is a (very) long
> > > weekend.
> > > You can certainly use the web services to retrieve everything - the trick to
> > > getting the values you have set up from datasets that populate the possible
> > > values etc is to that when you run the GetReportParameters method, make sure
> > > you set the ForRendering option to true - then everything you need comes
> > > back.
> > >
> > > Have a go and I'll post my code on Tuesday
> > > --
> > >
> > > Mary Bray [SQL Server MVP]
> > > Please reply only to newsgroups
> > >
> > > "Myles" <Myles@.discussions.microsoft.com> wrote in message
> > > news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > > > greetings -
> > > >
> > > > I am trying to set up a custom parameters page in an ASP .NET
> > > > application -
> > > > I am in the process of choosing between keeping the values (information I
> > > > need to list the parameters for the reports, as well as the available,
> > > > valid
> > > > and default values for any given report) in a database or access all I
> > > > need
> > > > through reporting services (web service).
> > > >
> > > > Does anyone have any advice for setting up a Web App to run rs reports -
> > > > can
> > > > you get all you need from the rs web services to create a custom parameter
> > > > page, or is it better to retrieve the values you need from a database?
> > > >
> > > > I can dynamically build the custom parm page using values from a database
> > > > (dynamically list the parms for the report, fire off the sp to return
> > > > valid
> > > > values for the parm and set the default value) - I would like to get this
> > > > going using the web services but am stuck at the point of trying to fill
> > > > the
> > > > valid parm values - I do have the name of the stored procedure to run to
> > > > retrieve the valid values for the parm, but can't seem to figure out how
> > > > to
> > > > either a)get the name of the sp to run, or b)just do a databind some how
> > > > to
> > > > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > > > down list, etc.)
> > > >
> > > > Any ideas - ?
> > > >
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
Friday, March 23, 2012
Paramaters
Hi, we run an ASP.NET portal that has fairly extensive reporting needs (it's
a government portal...) We're thinking of switching to reporting services
from a custom xml/xsl solution. My questions are:
1. interfacing with RS from our existing .net web app. We have screens set
up to let the user do things like select paramaters and stuff. Can this be
integrated? Currently for things that require multiple selections (for
instance, selecting a set of cities to report on), we build a string of city
ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which uses
the string to select info on each city (using IN(1,3,45,60,61)). Is this
possible in RS, and can we use our existing frontend? If we use our existing
frontend, can we still have access to the toolbar, for things like exporting
to different formats?
2. I know dynamic fields aren't possible. But can fields be left out /
included based on the presence of data to fill it?
3. Is it possible to use XML as a datasource to a report?
If anyone has any insight into these, I'd really appreciate it. Thanks in
advance
Peter LRS has two ways to integrate an existing app. You can either use web
services or you can use URL integration. To be able to have the toolbar you
need to use the URL integration. What you want to do with #1 is very easy to
do using URL integration. For question number 2, whether something is
visible can be set with an expression. The expression can decide not to show
it based on whatever you choose. #3 is possible but a little more difficult.
It is possible to create your own data extension. I suggest first seeing if
you really need to do this or if you can just use the stored procedures
and/or queries instead.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:CF96A1DF-84CA-4136-BCF4-9AF3BBC345CF@.microsoft.com...
> Hi, we run an ASP.NET portal that has fairly extensive reporting needs
> (it's
> a government portal...) We're thinking of switching to reporting services
> from a custom xml/xsl solution. My questions are:
> 1. interfacing with RS from our existing .net web app. We have screens set
> up to let the user do things like select paramaters and stuff. Can this be
> integrated? Currently for things that require multiple selections (for
> instance, selecting a set of cities to report on), we build a string of
> city
> ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which
> uses
> the string to select info on each city (using IN(1,3,45,60,61)). Is this
> possible in RS, and can we use our existing frontend? If we use our
> existing
> frontend, can we still have access to the toolbar, for things like
> exporting
> to different formats?
> 2. I know dynamic fields aren't possible. But can fields be left out /
> included based on the presence of data to fill it?
> 3. Is it possible to use XML as a datasource to a report?
>
> If anyone has any insight into these, I'd really appreciate it. Thanks in
> advance
> Peter L
a government portal...) We're thinking of switching to reporting services
from a custom xml/xsl solution. My questions are:
1. interfacing with RS from our existing .net web app. We have screens set
up to let the user do things like select paramaters and stuff. Can this be
integrated? Currently for things that require multiple selections (for
instance, selecting a set of cities to report on), we build a string of city
ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which uses
the string to select info on each city (using IN(1,3,45,60,61)). Is this
possible in RS, and can we use our existing frontend? If we use our existing
frontend, can we still have access to the toolbar, for things like exporting
to different formats?
2. I know dynamic fields aren't possible. But can fields be left out /
included based on the presence of data to fill it?
3. Is it possible to use XML as a datasource to a report?
If anyone has any insight into these, I'd really appreciate it. Thanks in
advance
Peter LRS has two ways to integrate an existing app. You can either use web
services or you can use URL integration. To be able to have the toolbar you
need to use the URL integration. What you want to do with #1 is very easy to
do using URL integration. For question number 2, whether something is
visible can be set with an expression. The expression can decide not to show
it based on whatever you choose. #3 is possible but a little more difficult.
It is possible to create your own data extension. I suggest first seeing if
you really need to do this or if you can just use the stored procedures
and/or queries instead.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:CF96A1DF-84CA-4136-BCF4-9AF3BBC345CF@.microsoft.com...
> Hi, we run an ASP.NET portal that has fairly extensive reporting needs
> (it's
> a government portal...) We're thinking of switching to reporting services
> from a custom xml/xsl solution. My questions are:
> 1. interfacing with RS from our existing .net web app. We have screens set
> up to let the user do things like select paramaters and stuff. Can this be
> integrated? Currently for things that require multiple selections (for
> instance, selecting a set of cities to report on), we build a string of
> city
> ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which
> uses
> the string to select info on each city (using IN(1,3,45,60,61)). Is this
> possible in RS, and can we use our existing frontend? If we use our
> existing
> frontend, can we still have access to the toolbar, for things like
> exporting
> to different formats?
> 2. I know dynamic fields aren't possible. But can fields be left out /
> included based on the presence of data to fill it?
> 3. Is it possible to use XML as a datasource to a report?
>
> If anyone has any insight into these, I'd really appreciate it. Thanks in
> advance
> Peter L
Tuesday, March 20, 2012
Painful installation of RS 2005 on Win2K
Ok I'm am going through a rather painful experience of installing Reporting Services on:
Windows 2000 SP4
MDAC 2.8
.Net Framework 2.0
IIS 5.0
SQL Server 2005 Developer
I have installed RS 2005 on Windows 2003, and seen it installed on XP no problems. However this is a nightmare.
RS goes ahead and installs - note that there is no Network/Local service option on the service accounts just Local system or the domain account option. I have tried using the Local system and tried using the NT AUTHORITY/SYSTEM.
After the installation I go to the Reporting Services Configuration Tool, where I have crosses on:
- Server Status
- Report Server Virtual Directory
- Report Manager Virtual Directory
- Web Service Identity
- Database Setup
I- nitialization
So I go to report server/manager and click on "New.." on each to create the virtual directories and check "apply default settings" and click "Apply", all goes well
Web Service Identity I cannot change so I go to:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config
and change the web service account to:
<WebServiceAccount><<MACHINE NAME>>\ASPNET</WebServiceAccount>
When I restart the reporting services service I no longer see a warning in the reporting services log file about the WebServiceAccount
I go back to the Reporting Services Config Tool, where I have crosses on the following:
Web Service Identity
Database Setup
Initialization
The Web Service Identity stays greyed out...
I go to Database Setup:
connect to the server
select the reportingserver database
leave it set as service credentials
Click "Apply"
and I get the most vague of all error messages:
"There was a failure applying your change
A virtual directory must first be created before performing this operation"
What virtual directory, where?
I look in the log files..nothing. I look in the event viewer...and...nothing.
I look on here...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=628231&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1682492&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=493194&SiteID=1
Either unanswered or there is the one answer but it is not relevant in this case.
Can someone please provide some advice before I lose my sanity?
Thanks in advance
Ok since I got no responses, after trial and error I found that the following process worked....
1. run SQLSERVER2005 setup from the command line with setup SKUUPGRADE=1
2. Install RS
3. open the RS config tool, create the virtual directories for server/manager
4. run rsconfig -c -s <SQLSERVERNAME> -d <<RS DB Name>> -a Windows from the command line (for a service account) see http://msdn2.microsoft.com/en-us/library/ms162837.aspx for more.
5. Edit the rsreportserver.config file add the WebServiceAccount <WebServiceAccount><<MACHINE NAME>>\ASPNET</WebServiceAccount>
6. Add ASPNET to the group SQLServer2005ReportingServicesWebServiceUser$<<INSTANCE_NAME>>
7. Give ASPNET or the above group acess to the RS DB
8. Add administrators as Content Managers via either the browser localhost/reports or in SQL Server Management Studio
9. Reboot
I have no idea why the RS configuration tool does not perform in the same way as the command line rsconfig? But running it from the command line fixes the problem
Paging, Performance and ADODB
I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
Paging, Performance and ADODB
I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
Paging, Performance and ADODB
I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?
Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.
|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?
Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.
|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
Monday, March 12, 2012
Paging using Web Service
To get the UI we required, we built a custom .NET front end that
communicates directly with the MSRS web service. Paging (e.g. a break in the
data at a certain point with some indicator for the "next" page) does not
seem to work once you take this approach. Anyone figured this out?Hi Sean,
> To get the UI we required, we built a custom .NET front end that
> communicates directly with the MSRS web service. Paging (e.g. a break in the
[...]
>does not seem to work once
What doesn't work?
I used the same approch. My user control calls RS webservice with parameter "Section" of device settings so I can _read_ report section by section.
There is only a little problem... there is no way... perhaps is better to say I don't find any way to known How many sections I must paging :o
HTH M.rkino
--
Marco Barzaghi - [MVP - MCP]
http://mvp.support.microsoft.com - http://italy.mvps.org
UGIDotNet - User Group Italiano .NET, http://www.ugidotnet.org
Read my WebLog: http://www.ugidotnet.org/436.blog
communicates directly with the MSRS web service. Paging (e.g. a break in the
data at a certain point with some indicator for the "next" page) does not
seem to work once you take this approach. Anyone figured this out?Hi Sean,
> To get the UI we required, we built a custom .NET front end that
> communicates directly with the MSRS web service. Paging (e.g. a break in the
[...]
>does not seem to work once
What doesn't work?
I used the same approch. My user control calls RS webservice with parameter "Section" of device settings so I can _read_ report section by section.
There is only a little problem... there is no way... perhaps is better to say I don't find any way to known How many sections I must paging :o
HTH M.rkino
--
Marco Barzaghi - [MVP - MCP]
http://mvp.support.microsoft.com - http://italy.mvps.org
UGIDotNet - User Group Italiano .NET, http://www.ugidotnet.org
Read my WebLog: http://www.ugidotnet.org/436.blog
Paging of Large Results Using Server Cursors
I'm writing an ASP.NET application that uses a SQL Server 2000 database. The application searches in large tables with 500, 000+ Records and then displays the search results, the search results could be easily 20,000 or 30,000 results. Ofcourse i need to use paging to show like 10 or 20 results per page. Unfortunetly ADO.NET doesn't support the paging functions that were found in ADO (like PageSize or AbsolutePosition) so i have to implement the paging myself.
I've read many articles that talk about how paging could be implemented. Most of them suggest doing the paging through SQL Server using Server Cursors. I know that cursors are resource intensive and should be avoided whenever possible but it seems that this is the only solution that fits. I just want you to notice that the cursor will just loop through 20 or 30 entries no more (Page Size) So is this a problem?
I will be using code that looks similar to this:
--
DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
Another method was suggested also that uses RowCount but it doesn't work for some technical reasons discussed in the article above.
So what do you think should i move on or what?
Regards,
Mohamed Salah
Please take a look at Aaron's article on this:http://aspfaq.com/show.asp?id=2120
Paging of Large Results Using Server Cursors
I'm writing an ASP.NET application that uses a SQL Server 2000 database. The
application searches in large tables with 500, 000+ Records and then display
s
the search results, the search results could be easily 20,000 or 30,000
results. Ofcourse i need to use paging to show like 10 or 20 results per
page. Unfortunetly ADO.NET doesn't support the paging functions that were
found in ADO (like PageSize or AbsolutePosition) so i have to implement the
paging myself.
I've read many articles that talk about how paging could be implemented.
Most of them suggest doing the paging through SQL Server using Server
Cursors. I know that cursors are resource intensive and should be avoided
whenever possible but it seems that this is the only solution that fits. I
just want you to notice that the cursor will just loop through 20 or 30
entries no more (Page Size) So is this a problem?
I will be using code that looks similar to this:
---
DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
----
I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
Another method was suggested also that uses RowCount but it doesn't work for
some technical reasons discussed in the article above.
So what do you think should i move on or what?
Regards,
Mohamed SalahLet's assume you are using a stored procedure call to page through a
Customer table and sorting by LastName. All you need to do is maintain in
session state the last offset value of LastName and CustomerID. This should
be fast resource efficeint. For example:
select top 20
LastName,
FirstName,
PhoneNumber
from
Customer
where
LastName > @.PrevLastName and
CustomerID > @.PrevCustomerID
order by
LastName,
CustomerID
"Mohamed Salah" <MohamedSalah@.discussions.microsoft.com> wrote in message
news:1D026C27-4BA2-43F8-B1CF-053D10B127D5@.microsoft.com...
> I'm writing an ASP.NET application that uses a SQL Server 2000 database.
> The
> application searches in large tables with 500, 000+ Records and then
> displays
> the search results, the search results could be easily 20,000 or 30,000
> results. Ofcourse i need to use paging to show like 10 or 20 results per
> page. Unfortunetly ADO.NET doesn't support the paging functions that were
> found in ADO (like PageSize or AbsolutePosition) so i have to implement
> the
> paging myself.
> I've read many articles that talk about how paging could be implemented.
> Most of them suggest doing the paging through SQL Server using Server
> Cursors. I know that cursors are resource intensive and should be avoided
> whenever possible but it seems that this is the only solution that fits. I
> just want you to notice that the cursor will just loop through 20 or 30
> entries no more (Page Size) So is this a problem?
> I will be using code that looks similar to this:
> ---
> DECLARE @.PK /* PK Type */
> DECLARE @.tblPK TABLE (
> PK /* PK Type */ NOT NULL PRIMARY KEY
> )
> DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
> SELECT @.PK FROM Table ORDER BY SortColumn
> OPEN PagingCursor
> FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
> WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
> BEGIN
> INSERT @.tblPK(PK) VALUES(@.PK)
> FETCH NEXT FROM PagingCursor INTO @.PK
> SET @.PageSize = @.PageSize - 1
> END
> CLOSE PagingCursor
> DEALLOCATE PagingCursor
> SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
> ORDER BY SortColumn
> ----
> I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
> Another method was suggested also that uses RowCount but it doesn't work
> for
> some technical reasons discussed in the article above.
> So what do you think should i move on or what?
> Regards,
> Mohamed Salah
>
application searches in large tables with 500, 000+ Records and then display
s
the search results, the search results could be easily 20,000 or 30,000
results. Ofcourse i need to use paging to show like 10 or 20 results per
page. Unfortunetly ADO.NET doesn't support the paging functions that were
found in ADO (like PageSize or AbsolutePosition) so i have to implement the
paging myself.
I've read many articles that talk about how paging could be implemented.
Most of them suggest doing the paging through SQL Server using Server
Cursors. I know that cursors are resource intensive and should be avoided
whenever possible but it seems that this is the only solution that fits. I
just want you to notice that the cursor will just loop through 20 or 30
entries no more (Page Size) So is this a problem?
I will be using code that looks similar to this:
---
DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
----
I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
Another method was suggested also that uses RowCount but it doesn't work for
some technical reasons discussed in the article above.
So what do you think should i move on or what?
Regards,
Mohamed SalahLet's assume you are using a stored procedure call to page through a
Customer table and sorting by LastName. All you need to do is maintain in
session state the last offset value of LastName and CustomerID. This should
be fast resource efficeint. For example:
select top 20
LastName,
FirstName,
PhoneNumber
from
Customer
where
LastName > @.PrevLastName and
CustomerID > @.PrevCustomerID
order by
LastName,
CustomerID
"Mohamed Salah" <MohamedSalah@.discussions.microsoft.com> wrote in message
news:1D026C27-4BA2-43F8-B1CF-053D10B127D5@.microsoft.com...
> I'm writing an ASP.NET application that uses a SQL Server 2000 database.
> The
> application searches in large tables with 500, 000+ Records and then
> displays
> the search results, the search results could be easily 20,000 or 30,000
> results. Ofcourse i need to use paging to show like 10 or 20 results per
> page. Unfortunetly ADO.NET doesn't support the paging functions that were
> found in ADO (like PageSize or AbsolutePosition) so i have to implement
> the
> paging myself.
> I've read many articles that talk about how paging could be implemented.
> Most of them suggest doing the paging through SQL Server using Server
> Cursors. I know that cursors are resource intensive and should be avoided
> whenever possible but it seems that this is the only solution that fits. I
> just want you to notice that the cursor will just loop through 20 or 30
> entries no more (Page Size) So is this a problem?
> I will be using code that looks similar to this:
> ---
> DECLARE @.PK /* PK Type */
> DECLARE @.tblPK TABLE (
> PK /* PK Type */ NOT NULL PRIMARY KEY
> )
> DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
> SELECT @.PK FROM Table ORDER BY SortColumn
> OPEN PagingCursor
> FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
> WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
> BEGIN
> INSERT @.tblPK(PK) VALUES(@.PK)
> FETCH NEXT FROM PagingCursor INTO @.PK
> SET @.PageSize = @.PageSize - 1
> END
> CLOSE PagingCursor
> DEALLOCATE PagingCursor
> SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
> ORDER BY SortColumn
> ----
> I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
> Another method was suggested also that uses RowCount but it doesn't work
> for
> some technical reasons discussed in the article above.
> So what do you think should i move on or what?
> Regards,
> Mohamed Salah
>
Paging of Large Results Using Server Cursors
I'm writing an ASP.NET application that uses a SQL Server 2000 database. The application searches in large tables with 500, 000+ Records and then displays the search results, the search results could be easily 20,000 or 30,000 results. Ofcourse i need to use paging to show like 10 or 20 results per page. Unfortunetly ADO.NET doesn't support the paging functions that were found in ADO (like PageSize or AbsolutePosition) so i have to implement the paging myself.
I've read many articles that talk about how paging could be implemented. Most of them suggest doing the paging through SQL Server using Server Cursors. I know that cursors are resource intensive and should be avoided whenever possible but it seems that this is the only solution that fits. I just want you to notice that the cursor will just loop through 20 or 30 entries no more (Page Size) So is this a problem?
I will be using code that looks similar to this:
--
DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
Another method was suggested also that uses RowCount but it doesn't work for some technical reasons discussed in the article above.
So what do you think should i move on or what?
Regards,
Mohamed Salah
Please take a look at Aaron's article on this:http://aspfaq.com/show.asp?id=2120
Friday, March 9, 2012
Paging at the end of a large data set
I want to do paging with my VB.NET app using SQL Server. I have a
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?
Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegro ups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>
|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegro ups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>
|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?
|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?
|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.
|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.
|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegro ups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>
|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegro ups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks
|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegro ups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?
Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegro ups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>
|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegro ups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>
|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?
|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?
|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.
|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.
|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegro ups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>
|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegro ups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks
|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegro ups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>
Paging at the end of a large data set
I want to do paging with my VB.NET app using SQL Server. I have a
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>
Paging at the end of a large data set
I want to do paging with my VB.NET app using SQL Server. I have a
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>> BTW, are you narrowing down your result set before you allow the user to
>> page through them, or is every paging operation performed on all 5
>> million
>> rows every time?
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>|||Thanks to all who responded! I appreciate it.
First, I could use the ORDER BY DESC *IF* I only wanted to get the
last 10. But I will need to page backwards from the last page. To
get the second to last page I need to do something else. So this
solution is not acceptable.
Second, we will provide for the ability to filter. However, I want to
be able to figure this out. Microsoft Access can do it. If I open
the same table with the 5 million records, I can move to the bottom of
the data set and page backward. There is a lag going to the bottom,
but once there it pages with virtually no delay.
Third, I've used the ROW_NUMBER and results are not much different. I
tried every example that I could find. Nothing beats using the ADODB
recordset object. This doesn't make sense to me. I have to believe
that SQL Server can do this.
Ultimately what I am trying to do is create my own DataGridView and
use it in Virtual mode to be able to display tables with this many
records. So far I have not found anything that comes even close to
the performance Microsoft Access provides.|||One more thing...
Another option that I'm considering is the use of threading. Like MS
Access there would be a delay in getting the bottom 3-5 pages.
However as the person is paging through, a new thread is generated to
capture additional pages. I was trying to see if SQL Server could
work with better performance without having to use threading.
FYI...I have pasted below the SQL code that I used with the
ROW_NUMBER:
WITH A AS ( SELECT TrustID, ClientID, [DATE], Amount, ROW_NUMBER()
OVER (order by TrustID) AS RowNumber FROM tbl_TrustData )
SELECT *
FROM A
WHERE RowNumber between 5000001 and 5000010
TrustID is the PK and is a clustered index.|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171986970.993291.134530@.k78g2000cwa.googlegroups.com...
> Thanks to all who responded! I appreciate it.
> First, I could use the ORDER BY DESC *IF* I only wanted to get the
> last 10. But I will need to page backwards from the last page. To
> get the second to last page I need to do something else. So this
> solution is not acceptable.
I think the idea there was to retrieve pages from the bottom up. For
instance, any pages in the back half (last 50% of the rows in your table)
might get some benefit from retrieving them from the back - although you'd
have to play with it to see if it helps your situation.
> Second, we will provide for the ability to filter. However, I want to
> be able to figure this out. Microsoft Access can do it. If I open
> the same table with the 5 million records, I can move to the bottom of
> the data set and page backward. There is a lag going to the bottom,
> but once there it pages with virtually no delay.
Microsoft Access is opening a cursor, iterating every single row (hence the
lag time you feel), and caching every single row in memory. If that's what
you want to replicate, then just retrieve every single row from SQL Server
into your client application and page client-side...
> Third, I've used the ROW_NUMBER and results are not much different. I
> tried every example that I could find. Nothing beats using the ADODB
> recordset object. This doesn't make sense to me. I have to believe
> that SQL Server can do this.
> Ultimately what I am trying to do is create my own DataGridView and
> use it in Virtual mode to be able to display tables with this many
> records. So far I have not found anything that comes even close to
> the performance Microsoft Access provides.
That "performance" comes from caching the entire dataset in memory
client-side, which you can easily replicate yourself if you really want to
read the entire dataset into memory client-side just to page it...|||Access cannot be loading every record into memory. When I view the
Process from the Task Manager, Access never goes over 50K (under the
"Mem Usage" column). Also, I've tried loading every record in memory
just to see what would happen and get an "Out of Memory" exception.
Is Access loading every record to some temporary file? I would not
think that would improve performance. I would guess that would make
it worse.
It sounds like I cannot get the performance that I want from SQL
Server retrieving any page, whether top or bottom, from a large
DataSet.|||It's using a fast-forward cursor to rip through the dataset (one of the
methods described on Aaron Bertrand's article on the subject at ASPFAQ). I
was exaggerating Access' caching mechanism (for effect), but Jet does cache
a lot of data client-side which, while it is one way to try to improve
performance, introduces a lot of complexity since you need to keep track of
who's updating what and all that good crap. If you want to try to emulate
Access, use a cursor.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1172098426.310438.17840@.j27g2000cwj.googlegroups.com...
> Access cannot be loading every record into memory. When I view the
> Process from the Task Manager, Access never goes over 50K (under the
> "Mem Usage" column). Also, I've tried loading every record in memory
> just to see what would happen and get an "Out of Memory" exception.
> Is Access loading every record to some temporary file? I would not
> think that would improve performance. I would guess that would make
> it worse.
> It sounds like I cannot get the performance that I want from SQL
> Server retrieving any page, whether top or bottom, from a large
> DataSet.
>|||Thanks Mike. That's the information that I was looking for. The
ASPFAQ article does not mention cursors. Is the Recordset object
using the cursor?|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
> Thanks Mike. That's the information that I was looking for. The
> ASPFAQ article does not mention cursors. Is the Recordset object
> using the cursor?
I must have been drinking that night. I thought ASPFAQ had an article
comparing performance of different paging techniques; apparently not. I'll
keep looking for that article (maybe someone else remembers seeing it and
can provide a link?)
Depending on which technique you use, you may get a client-side cursor
automatically, and it may even be backed up by a server-side cursor. (See
SqlDataReader). Old ADO was famous for its use of cursors to get at the
data, although its been a while so I'd have to look up the Recordset object
specs to find out for sure in that code. I would suspect that it is using a
client-side cursor, at least.
Here's a couple more links for you:
http://www.4guysfromrolla.com/webtech/042606-1.shtml
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
Google up some "SQL Server paging speed" or some such... This problem has
been attacked by a lot of people all over the place through the years, so
there's a lot of good info. out there on it.|||Thanks Mike! You've been very helpful. I will look through these.|||> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques;
It does;
http://www.aspfaq.com/2120
A|||Not sure if this is relevant but I did a blog post a while ago about paging
that had some references:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/20/580353.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike C#" <xyz@.xyz.com> wrote in message
news:O04M$i3VHHA.488@.TK2MSFTNGP06.phx.gbl...
> "Paul" <pwh777@.hotmail.com> wrote in message
> news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
>> Thanks Mike. That's the information that I was looking for. The
>> ASPFAQ article does not mention cursors. Is the Recordset object
>> using the cursor?
> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques; apparently not.
> I'll keep looking for that article (maybe someone else remembers seeing it
> and can provide a link?)
> Depending on which technique you use, you may get a client-side cursor
> automatically, and it may even be backed up by a server-side cursor. (See
> SqlDataReader). Old ADO was famous for its use of cursors to get at the
> data, although its been a while so I'd have to look up the Recordset
> object specs to find out for sure in that code. I would suspect that it
> is using a client-side cursor, at least.
> Here's a couple more links for you:
> http://www.4guysfromrolla.com/webtech/042606-1.shtml
> http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
> Google up some "SQL Server paging speed" or some such... This problem has
> been attacked by a lot of people all over the place through the years, so
> there's a lot of good info. out there on it.
>|||For some reason, the data at the end of the article (including the important
summary of performance comparisons) shows up blank. You can get it if you
download the PDF of all articles from http://www.aspfaq.com/downloads.asp
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
> A
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
>
I thought your article also showed performance differences between
server-side paging techniques like using dynamic SQL, cursors, temp tables,
etc. in a side-by-side format. I'm probably confusing your article with
another one I've seen somewhere, but I'll be darned if I'm able to find it
now. Or it might have just been a heavy night of drinking :)|||>> It does;
>> http://www.aspfaq.com/2120
> I thought your article also showed performance differences between
> server-side paging techniques like using dynamic SQL, cursors, temp
> tables, etc. in a side-by-side format.
It did (see my follow-up).
I talked to the site owners and they were supposed to have fixed it today,
but haven't yet.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OlVBKX9VHHA.392@.TK2MSFTNGP06.phx.gbl...
> It did (see my follow-up).
> I talked to the site owners and they were supposed to have fixed it today,
> but haven't yet.
I thought you owned it? What's going on 'round here? :)|||>> I talked to the site owners and they were supposed to have fixed it
>> today, but haven't yet.
> I thought you owned it? What's going on 'round here? :)
No, I handed it over to new owners last year.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23gwTXeOWHHA.3568@.TK2MSFTNGP06.phx.gbl...
> No, I handed it over to new owners last year.
Well that's a kick in the pants! Hope you made a killin' :)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23A7bClqWHHA.3332@.TK2MSFTNGP04.phx.gbl...
> Sure, but I'll be paying dearly for it on April 15th. :-)
Sounds like it's time for a trip to Vegas... They'll help you if you have
too much money :)
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>> BTW, are you narrowing down your result set before you allow the user to
>> page through them, or is every paging operation performed on all 5
>> million
>> rows every time?
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>|||Thanks to all who responded! I appreciate it.
First, I could use the ORDER BY DESC *IF* I only wanted to get the
last 10. But I will need to page backwards from the last page. To
get the second to last page I need to do something else. So this
solution is not acceptable.
Second, we will provide for the ability to filter. However, I want to
be able to figure this out. Microsoft Access can do it. If I open
the same table with the 5 million records, I can move to the bottom of
the data set and page backward. There is a lag going to the bottom,
but once there it pages with virtually no delay.
Third, I've used the ROW_NUMBER and results are not much different. I
tried every example that I could find. Nothing beats using the ADODB
recordset object. This doesn't make sense to me. I have to believe
that SQL Server can do this.
Ultimately what I am trying to do is create my own DataGridView and
use it in Virtual mode to be able to display tables with this many
records. So far I have not found anything that comes even close to
the performance Microsoft Access provides.|||One more thing...
Another option that I'm considering is the use of threading. Like MS
Access there would be a delay in getting the bottom 3-5 pages.
However as the person is paging through, a new thread is generated to
capture additional pages. I was trying to see if SQL Server could
work with better performance without having to use threading.
FYI...I have pasted below the SQL code that I used with the
ROW_NUMBER:
WITH A AS ( SELECT TrustID, ClientID, [DATE], Amount, ROW_NUMBER()
OVER (order by TrustID) AS RowNumber FROM tbl_TrustData )
SELECT *
FROM A
WHERE RowNumber between 5000001 and 5000010
TrustID is the PK and is a clustered index.|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171986970.993291.134530@.k78g2000cwa.googlegroups.com...
> Thanks to all who responded! I appreciate it.
> First, I could use the ORDER BY DESC *IF* I only wanted to get the
> last 10. But I will need to page backwards from the last page. To
> get the second to last page I need to do something else. So this
> solution is not acceptable.
I think the idea there was to retrieve pages from the bottom up. For
instance, any pages in the back half (last 50% of the rows in your table)
might get some benefit from retrieving them from the back - although you'd
have to play with it to see if it helps your situation.
> Second, we will provide for the ability to filter. However, I want to
> be able to figure this out. Microsoft Access can do it. If I open
> the same table with the 5 million records, I can move to the bottom of
> the data set and page backward. There is a lag going to the bottom,
> but once there it pages with virtually no delay.
Microsoft Access is opening a cursor, iterating every single row (hence the
lag time you feel), and caching every single row in memory. If that's what
you want to replicate, then just retrieve every single row from SQL Server
into your client application and page client-side...
> Third, I've used the ROW_NUMBER and results are not much different. I
> tried every example that I could find. Nothing beats using the ADODB
> recordset object. This doesn't make sense to me. I have to believe
> that SQL Server can do this.
> Ultimately what I am trying to do is create my own DataGridView and
> use it in Virtual mode to be able to display tables with this many
> records. So far I have not found anything that comes even close to
> the performance Microsoft Access provides.
That "performance" comes from caching the entire dataset in memory
client-side, which you can easily replicate yourself if you really want to
read the entire dataset into memory client-side just to page it...|||Access cannot be loading every record into memory. When I view the
Process from the Task Manager, Access never goes over 50K (under the
"Mem Usage" column). Also, I've tried loading every record in memory
just to see what would happen and get an "Out of Memory" exception.
Is Access loading every record to some temporary file? I would not
think that would improve performance. I would guess that would make
it worse.
It sounds like I cannot get the performance that I want from SQL
Server retrieving any page, whether top or bottom, from a large
DataSet.|||It's using a fast-forward cursor to rip through the dataset (one of the
methods described on Aaron Bertrand's article on the subject at ASPFAQ). I
was exaggerating Access' caching mechanism (for effect), but Jet does cache
a lot of data client-side which, while it is one way to try to improve
performance, introduces a lot of complexity since you need to keep track of
who's updating what and all that good crap. If you want to try to emulate
Access, use a cursor.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1172098426.310438.17840@.j27g2000cwj.googlegroups.com...
> Access cannot be loading every record into memory. When I view the
> Process from the Task Manager, Access never goes over 50K (under the
> "Mem Usage" column). Also, I've tried loading every record in memory
> just to see what would happen and get an "Out of Memory" exception.
> Is Access loading every record to some temporary file? I would not
> think that would improve performance. I would guess that would make
> it worse.
> It sounds like I cannot get the performance that I want from SQL
> Server retrieving any page, whether top or bottom, from a large
> DataSet.
>|||Thanks Mike. That's the information that I was looking for. The
ASPFAQ article does not mention cursors. Is the Recordset object
using the cursor?|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
> Thanks Mike. That's the information that I was looking for. The
> ASPFAQ article does not mention cursors. Is the Recordset object
> using the cursor?
I must have been drinking that night. I thought ASPFAQ had an article
comparing performance of different paging techniques; apparently not. I'll
keep looking for that article (maybe someone else remembers seeing it and
can provide a link?)
Depending on which technique you use, you may get a client-side cursor
automatically, and it may even be backed up by a server-side cursor. (See
SqlDataReader). Old ADO was famous for its use of cursors to get at the
data, although its been a while so I'd have to look up the Recordset object
specs to find out for sure in that code. I would suspect that it is using a
client-side cursor, at least.
Here's a couple more links for you:
http://www.4guysfromrolla.com/webtech/042606-1.shtml
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
Google up some "SQL Server paging speed" or some such... This problem has
been attacked by a lot of people all over the place through the years, so
there's a lot of good info. out there on it.|||Thanks Mike! You've been very helpful. I will look through these.|||> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques;
It does;
http://www.aspfaq.com/2120
A|||Not sure if this is relevant but I did a blog post a while ago about paging
that had some references:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/20/580353.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike C#" <xyz@.xyz.com> wrote in message
news:O04M$i3VHHA.488@.TK2MSFTNGP06.phx.gbl...
> "Paul" <pwh777@.hotmail.com> wrote in message
> news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
>> Thanks Mike. That's the information that I was looking for. The
>> ASPFAQ article does not mention cursors. Is the Recordset object
>> using the cursor?
> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques; apparently not.
> I'll keep looking for that article (maybe someone else remembers seeing it
> and can provide a link?)
> Depending on which technique you use, you may get a client-side cursor
> automatically, and it may even be backed up by a server-side cursor. (See
> SqlDataReader). Old ADO was famous for its use of cursors to get at the
> data, although its been a while so I'd have to look up the Recordset
> object specs to find out for sure in that code. I would suspect that it
> is using a client-side cursor, at least.
> Here's a couple more links for you:
> http://www.4guysfromrolla.com/webtech/042606-1.shtml
> http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
> Google up some "SQL Server paging speed" or some such... This problem has
> been attacked by a lot of people all over the place through the years, so
> there's a lot of good info. out there on it.
>|||For some reason, the data at the end of the article (including the important
summary of performance comparisons) shows up blank. You can get it if you
download the PDF of all articles from http://www.aspfaq.com/downloads.asp
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
> A
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
>
I thought your article also showed performance differences between
server-side paging techniques like using dynamic SQL, cursors, temp tables,
etc. in a side-by-side format. I'm probably confusing your article with
another one I've seen somewhere, but I'll be darned if I'm able to find it
now. Or it might have just been a heavy night of drinking :)|||>> It does;
>> http://www.aspfaq.com/2120
> I thought your article also showed performance differences between
> server-side paging techniques like using dynamic SQL, cursors, temp
> tables, etc. in a side-by-side format.
It did (see my follow-up).
I talked to the site owners and they were supposed to have fixed it today,
but haven't yet.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OlVBKX9VHHA.392@.TK2MSFTNGP06.phx.gbl...
> It did (see my follow-up).
> I talked to the site owners and they were supposed to have fixed it today,
> but haven't yet.
I thought you owned it? What's going on 'round here? :)|||>> I talked to the site owners and they were supposed to have fixed it
>> today, but haven't yet.
> I thought you owned it? What's going on 'round here? :)
No, I handed it over to new owners last year.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23gwTXeOWHHA.3568@.TK2MSFTNGP06.phx.gbl...
> No, I handed it over to new owners last year.
Well that's a kick in the pants! Hope you made a killin' :)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23A7bClqWHHA.3332@.TK2MSFTNGP04.phx.gbl...
> Sure, but I'll be paying dearly for it on April 15th. :-)
Sounds like it's time for a trip to Vegas... They'll help you if you have
too much money :)
Paging and printing without the toolbar
Is there a way to issue the report print command or paging command from code.
I would like to hide the toolbar and use another button in our ASP.NET
application to kick off printing or tell the reportviewer to go to another
page.
ThanksPerhaps you could use the findstring to search for the literal in a report
ie
http://server/Reportserver?/SampleReports/Product
Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
Search for URL Access in Books on line and you will get all of the URL
parameters, etc... There is also code which shows you how to put and IE
browswer instance in a windows form, and put a report into it...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"NeedToKnow" wrote:
> Is there a way to issue the report print command or paging command from code.
> I would like to hide the toolbar and use another button in our ASP.NET
> application to kick off printing or tell the reportviewer to go to another
> page.
> Thanks|||I do not understand. I have looked through URL Access and why would I want to
find a literal. I want to send the command to reportviewer through a URL
that tells the report viewer to print the current report to the printer.
"Wayne Snyder" wrote:
> Perhaps you could use the findstring to search for the literal in a report
> ie
> http://server/Reportserver?/SampleReports/Product
> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>
> Search for URL Access in Books on line and you will get all of the URL
> parameters, etc... There is also code which shows you how to put and IE
> browswer instance in a windows form, and put a report into it...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NeedToKnow" wrote:
> > Is there a way to issue the report print command or paging command from code.
> > I would like to hide the toolbar and use another button in our ASP.NET
> > application to kick off printing or tell the reportviewer to go to another
> > page.
> >
> > Thanks|||You asked two question. He answered one of them (the one about going to
another page).
As far as printing, I don't think what you want is possible with the report
viewer control. Are you using the one from VS 2005?
I wanted to do something similar with the winform report viewer control but
found out that until it is rendered I can't request for it to print. So for
Windows I am setting a property to print and then responding to the
rendering event and if their is a request to print, I print and then close
the form. However, I am not familiar with the webform control from VS 2005.
If you are doing this in RS 2000 then I have even less to offer as a
suggestion.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
>I do not understand. I have looked through URL Access and why would I want
>to
> find a literal. I want to send the command to reportviewer through a URL
> that tells the report viewer to print the current report to the printer.
> "Wayne Snyder" wrote:
>> Perhaps you could use the findstring to search for the literal in a
>> report
>> ie
>> http://server/Reportserver?/SampleReports/Product
>> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>>
>> Search for URL Access in Books on line and you will get all of the URL
>> parameters, etc... There is also code which shows you how to put and IE
>> browswer instance in a windows form, and put a report into it...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "NeedToKnow" wrote:
>> > Is there a way to issue the report print command or paging command from
>> > code.
>> > I would like to hide the toolbar and use another button in our ASP.NET
>> > application to kick off printing or tell the reportviewer to go to
>> > another
>> > page.
>> >
>> > Thanks|||It is vs 2005.
I don't want to print the form, since my report is several pages long. Is
there a way to do this?
"Bruce L-C [MVP]" wrote:
> You asked two question. He answered one of them (the one about going to
> another page).
> As far as printing, I don't think what you want is possible with the report
> viewer control. Are you using the one from VS 2005?
> I wanted to do something similar with the winform report viewer control but
> found out that until it is rendered I can't request for it to print. So for
> Windows I am setting a property to print and then responding to the
> rendering event and if their is a request to print, I print and then close
> the form. However, I am not familiar with the webform control from VS 2005.
> If you are doing this in RS 2000 then I have even less to offer as a
> suggestion.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
> news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
> >I do not understand. I have looked through URL Access and why would I want
> >to
> > find a literal. I want to send the command to reportviewer through a URL
> > that tells the report viewer to print the current report to the printer.
> >
> > "Wayne Snyder" wrote:
> >
> >> Perhaps you could use the findstring to search for the literal in a
> >> report
> >>
> >> ie
> >> http://server/Reportserver?/SampleReports/Product
> >> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
> >>
> >>
> >> Search for URL Access in Books on line and you will get all of the URL
> >> parameters, etc... There is also code which shows you how to put and IE
> >> browswer instance in a windows form, and put a report into it...
> >> --
> >> Wayne Snyder MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >>
> >> I support the Professional Association for SQL Server ( PASS) and it''s
> >> community of SQL Professionals.
> >>
> >>
> >> "NeedToKnow" wrote:
> >>
> >> > Is there a way to issue the report print command or paging command from
> >> > code.
> >> > I would like to hide the toolbar and use another button in our ASP.NET
> >> > application to kick off printing or tell the reportviewer to go to
> >> > another
> >> > page.
> >> >
> >> > Thanks
>
>
I would like to hide the toolbar and use another button in our ASP.NET
application to kick off printing or tell the reportviewer to go to another
page.
ThanksPerhaps you could use the findstring to search for the literal in a report
ie
http://server/Reportserver?/SampleReports/Product
Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
Search for URL Access in Books on line and you will get all of the URL
parameters, etc... There is also code which shows you how to put and IE
browswer instance in a windows form, and put a report into it...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"NeedToKnow" wrote:
> Is there a way to issue the report print command or paging command from code.
> I would like to hide the toolbar and use another button in our ASP.NET
> application to kick off printing or tell the reportviewer to go to another
> page.
> Thanks|||I do not understand. I have looked through URL Access and why would I want to
find a literal. I want to send the command to reportviewer through a URL
that tells the report viewer to print the current report to the printer.
"Wayne Snyder" wrote:
> Perhaps you could use the findstring to search for the literal in a report
> ie
> http://server/Reportserver?/SampleReports/Product
> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>
> Search for URL Access in Books on line and you will get all of the URL
> parameters, etc... There is also code which shows you how to put and IE
> browswer instance in a windows form, and put a report into it...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NeedToKnow" wrote:
> > Is there a way to issue the report print command or paging command from code.
> > I would like to hide the toolbar and use another button in our ASP.NET
> > application to kick off printing or tell the reportviewer to go to another
> > page.
> >
> > Thanks|||You asked two question. He answered one of them (the one about going to
another page).
As far as printing, I don't think what you want is possible with the report
viewer control. Are you using the one from VS 2005?
I wanted to do something similar with the winform report viewer control but
found out that until it is rendered I can't request for it to print. So for
Windows I am setting a property to print and then responding to the
rendering event and if their is a request to print, I print and then close
the form. However, I am not familiar with the webform control from VS 2005.
If you are doing this in RS 2000 then I have even less to offer as a
suggestion.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
>I do not understand. I have looked through URL Access and why would I want
>to
> find a literal. I want to send the command to reportviewer through a URL
> that tells the report viewer to print the current report to the printer.
> "Wayne Snyder" wrote:
>> Perhaps you could use the findstring to search for the literal in a
>> report
>> ie
>> http://server/Reportserver?/SampleReports/Product
>> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
>>
>> Search for URL Access in Books on line and you will get all of the URL
>> parameters, etc... There is also code which shows you how to put and IE
>> browswer instance in a windows form, and put a report into it...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "NeedToKnow" wrote:
>> > Is there a way to issue the report print command or paging command from
>> > code.
>> > I would like to hide the toolbar and use another button in our ASP.NET
>> > application to kick off printing or tell the reportviewer to go to
>> > another
>> > page.
>> >
>> > Thanks|||It is vs 2005.
I don't want to print the form, since my report is several pages long. Is
there a way to do this?
"Bruce L-C [MVP]" wrote:
> You asked two question. He answered one of them (the one about going to
> another page).
> As far as printing, I don't think what you want is possible with the report
> viewer control. Are you using the one from VS 2005?
> I wanted to do something similar with the winform report viewer control but
> found out that until it is rendered I can't request for it to print. So for
> Windows I am setting a property to print and then responding to the
> rendering event and if their is a request to print, I print and then close
> the form. However, I am not familiar with the webform control from VS 2005.
> If you are doing this in RS 2000 then I have even less to offer as a
> suggestion.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NeedToKnow" <NeedToKnow@.newsgroups.nospam> wrote in message
> news:ECC2AC4D-1252-4A19-A22D-B284920EC5FC@.microsoft.com...
> >I do not understand. I have looked through URL Access and why would I want
> >to
> > find a literal. I want to send the command to reportviewer through a URL
> > that tells the report viewer to print the current report to the printer.
> >
> > "Wayne Snyder" wrote:
> >
> >> Perhaps you could use the findstring to search for the literal in a
> >> report
> >>
> >> ie
> >> http://server/Reportserver?/SampleReports/Product
> >> Catalog&rs:Command=Render&rc:StartFind=1&rc:EndFind=5&rc:FindString=Mountain-400
> >>
> >>
> >> Search for URL Access in Books on line and you will get all of the URL
> >> parameters, etc... There is also code which shows you how to put and IE
> >> browswer instance in a windows form, and put a report into it...
> >> --
> >> Wayne Snyder MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >>
> >> I support the Professional Association for SQL Server ( PASS) and it''s
> >> community of SQL Professionals.
> >>
> >>
> >> "NeedToKnow" wrote:
> >>
> >> > Is there a way to issue the report print command or paging command from
> >> > code.
> >> > I would like to hide the toolbar and use another button in our ASP.NET
> >> > application to kick off printing or tell the reportviewer to go to
> >> > another
> >> > page.
> >> >
> >> > Thanks
>
>
Paging (Performance)
Hello, I have incorporated a paging query in my software. I got the query from here:
http://rosca.net/writing/articles/serverside_paging.asp
My web software ususlly responded in .005 - .02 seconds with about a 100 rows of data. When I put simulated data on my database I added about 2 million rows. when I did this -- every page that did not execute the paging query responded lightning fast. But the webpages that executed the paging query took over 5 seconds. I dont understand why this paging query brought my web application to its knees.
Does anyone know of a more efficient way to do paging. I have SQL server 2000. If it may be easier I can upgrade to SQL 2005. PLZ Let me know. Thankshave a look at these two blog posts (focus on the more recent post):
http://weblogs.sqlteam.com/jeffs/category/162.aspx
theres a lot of info in the blog comments as well, and also links to other methods (for example, http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html)
http://rosca.net/writing/articles/serverside_paging.asp
My web software ususlly responded in .005 - .02 seconds with about a 100 rows of data. When I put simulated data on my database I added about 2 million rows. when I did this -- every page that did not execute the paging query responded lightning fast. But the webpages that executed the paging query took over 5 seconds. I dont understand why this paging query brought my web application to its knees.
Does anyone know of a more efficient way to do paging. I have SQL server 2000. If it may be easier I can upgrade to SQL 2005. PLZ Let me know. Thankshave a look at these two blog posts (focus on the more recent post):
http://weblogs.sqlteam.com/jeffs/category/162.aspx
theres a lot of info in the blog comments as well, and also links to other methods (for example, http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html)
Paging
Is there any way to implement a paging scheme such that only the required records are transferred from SQL Server to the asp.net app?
The only support I can find such as the DataAdaptor.Fill will bring all the records back from SQL Server and then create the page...
This obviously still takes time and memory based on the entire query, not the page size.
Any ideas?Hi,
You can use a temporary table via stored proc in SQL Server. Seehere andhere.
Another approach is to use SELECT TOP, seehere.
A.|||Thanks... Very useful...
Having a fully dynamic query built up in asp.net, without necessarily being from a single table and having unknown (in advance) columns, without always having a primary key I'm not sure that I will be able to adapt one of these without doing some major work on my system.
Pagination without datagrid
I'm working on a website where we're using .Net web services to feed data to a Flash front-end. The site will have a comments section and we want to display 15 or so comments per page with 'back' and 'next' functionalitiy. We'd also like to show the number of pages of comments and highlight the page they're on. The standard stuff that datagrids do so well.
How can this be accomplished without a datagrid?
There's a good page that explains a number of ways to do this using classic ASP. Some of the solutions they implement I can most likely use with .Net.
http://www.aspfaq.com/show.asp?id=2120
But I wanted to ask the community. How to paginate without a recordset? Sql Server 2000 back-end, ASP.NET 1.x
Thanks.Why don't you want to use a datagrid?|||We aren't using a DataGrid because our 'front-end' isn't an aspx page it's a Flash application that we're feeding the data to via a webservice.
Correct me if I'm wrong but I don't think a DataGrid is an option with this situation.|||
I see. Sorry, I missed that part. Well, if you are using web services to a flash front end, I would assume that your data isn't going to change very often. I would see about getting your data into a dataset, formatting the output into a cache object with a sqldependancy.
Beyond that, I would have to know a lot more about how your web service interacts with your flash front end.
Wednesday, March 7, 2012
Paginate VB options
I have a VB.NET 2005 program that on a click of a button exports my report to a text file. This works fine apart from one thing, i get a strange charactor every 60 lines. So i opened Crystal Deveolper and tried exporting from there and it gave me and option set number of lines per page and setting this to 0 switched off paginate, which removed the charactors.
My question is this how do i add the options for Charactors/Inch and Number of Lines per Page (setting this to 0 switches off paginate). Here is my code so far:-
Private Sub btnGenFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenFile.Click Dim
Exportpath As String Exportpath = "n:\Regatta Order Files\"
CrDiskfileDestinationoption = New DiskFileDestinationOptions
CrExportOption = CrReport.ExportOptions
CrDiskfileDestinationoption.DiskFileName = Exportpath + ("CHAN1" & sDate & ".txt")
With CrExportOption
.DestinationOptions = CrDiskfileDestinationoption
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.Text
End With
Try CrReport.Export()
'MessageBox.Show("Exported to " & CrReport.ExportOptions.DestinationOptions.DiskFileName)
txtStatus.Text = "Text File has been created, you can now tranfer to Regatta"
Catch ex As Exception
MessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
I have XP Pro SP2
Visual Studio 2005
Crystal Reports Developer XI R2dump
My question is this how do i add the options for Charactors/Inch and Number of Lines per Page (setting this to 0 switches off paginate). Here is my code so far:-
Private Sub btnGenFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenFile.Click Dim
Exportpath As String Exportpath = "n:\Regatta Order Files\"
CrDiskfileDestinationoption = New DiskFileDestinationOptions
CrExportOption = CrReport.ExportOptions
CrDiskfileDestinationoption.DiskFileName = Exportpath + ("CHAN1" & sDate & ".txt")
With CrExportOption
.DestinationOptions = CrDiskfileDestinationoption
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.Text
End With
Try CrReport.Export()
'MessageBox.Show("Exported to " & CrReport.ExportOptions.DestinationOptions.DiskFileName)
txtStatus.Text = "Text File has been created, you can now tranfer to Regatta"
Catch ex As Exception
MessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
I have XP Pro SP2
Visual Studio 2005
Crystal Reports Developer XI R2dump
Subscribe to:
Posts (Atom)