Historical tables, partitioning or what?

I have about 45000 records in a CSV file, which I am using as HTTP request parameters to query a website and store some results in a database. This is the kind of application which runs 24/7, so database grows really quickly. Every insert fires up a trigger, which has to look for some old records based on some criteria and modify the last inserted record. My client is crazy about performance on this one and suggested to move the old records into another table, which has exactly the same structure, but would serve as a historical table only (used to generate reports, statistics, etc.), whilst the original table would store only the latest rows (so no more than 45k at a given time, whereas the historical table may grow to millions of records). Is this a good idea? Having the performance in mind and the fact that there's that trigger - it has to run as quickly as possible - I might second that idea. Is it good or bad? What do you think?

I read a similar post here, which mentioned SQL Server 2005 partitioning, I might as well try this, although I never used it before.

I think you should use the archive table and partition it with 2005s new capabilities. This will allow you to easily administer the table(s). Heres a good link on 2005 partitioning. http://msdn2.microsoft.com/en-us/library/ms345146.aspx If you use a seperate archive table, you can create good indexes that will really speed queries. The only bad thing is that if you insert new records into it often, it could be slow. So, you might want to only insert new archive records during off-peak time if possible.
Tim|||I would caution to have the absolute minimal indexes on the archive table. In some situations, it may be efficient to create an index just for a report, and then remove the index as soon as the report is complete.|||Thanks for your comments. I read about SQL Server 2005's partitioning feature and find it very interesting (and useful, I think), but the application in question runs on the Express Edition currently with possible switch to a higher version, but doubtfuly the Enterprise one, so partitioning is out of the question, unfortunately. I will hopefuly use it when I will work on some enterprise level application in the future.

Hiding Other options from Format Drop down in Report Viewer Control

Hi All
We have requirement where we need to hide some of the options which we have in Format drop down i.e we need only CSV,PDF and excel .
How can we do this?
Thanks in advance
Rehan Mustafa Khan
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.To disable export to a particular rendering format, add the string
Visible="false" to the end of the rendering extension entry in
%PROGRAMFILES%\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config file. For example, to disable
Excel from showing up in the Export dropdown in report manager, change
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering"/>
to
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering" Visible="false"/>
Note that disabling a rendering extension does not prevent users from
rendering to those extensions using SOAP API or use URL access to reports
like so:
http://localhost/reportserver?%2fSampleReports%2fCompany+Sales&rs:Command=Render&rs:Format=EXCEL
If you want to remove the rendering extension completely, just delete the
corresponding line from the <Render> section of the config file (in which
case URL access like shown above will throw rsRenderingExtensionNotFound
exception.)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:uDqFX7%23YEHA.1264@.TK2MSFTNGP11.phx.gbl...
> Hi All
> We have requirement where we need to hide some of the options which we
have in Format drop down i.e we need only CSV,PDF and excel .
> How can we do this?
> Thanks in advance
> Rehan Mustafa Khan
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||Hi Ravi,
I tried ur Suggestion.Not Sure Y its not working for me'
Can u Plz Guide where im going wrong'
My Code is
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering" Visible="false"//>
<Extension Name="NULL"
Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering"Visible="false"/ >
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering" Visible="false"/>
<Extension Name="IMAGE"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport,Microsoft.ReportingServices.ImageRendering"Visible="false"/>
<Extension Name="PDF"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering"Visible="false"/>
<Extension Name="HTML4.0"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering"Visible="false"/>
<Extension Name="HTML3.2"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html32RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
<Extension Name="MHTML"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering"Visible="false"/>
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"Visible="false"/>
<Extension Name="HTMLOWC"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.HtmlOWCRenderingExtension,Microsoft.ReportingServices.HtmlRendering"Visible="false"/>
Im waiting for ur response,
Thanks in Advance,
"Ravi Mumulla (Microsoft)" wrote:
> To disable export to a particular rendering format, add the string
> Visible="false" to the end of the rendering extension entry in
> %PROGRAMFILES%\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config file. For example, to disable
> Excel from showing up in the Export dropdown in report manager, change
> <Extension Name="EXCEL"
> Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> osoft.ReportingServices.ExcelRendering"/>
> to
> <Extension Name="EXCEL"
> Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> osoft.ReportingServices.ExcelRendering" Visible="false"/>
> Note that disabling a rendering extension does not prevent users from
> rendering to those extensions using SOAP API or use URL access to reports
> like so:
> http://localhost/reportserver?%2fSampleReports%2fCompany+Sales&rs:Command=Render&rs:Format=EXCEL
> If you want to remove the rendering extension completely, just delete the
> corresponding line from the <Render> section of the config file (in which
> case URL access like shown above will throw rsRenderingExtensionNotFound
> exception.)
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
> news:uDqFX7%23YEHA.1264@.TK2MSFTNGP11.phx.gbl...
> > Hi All
> >
> > We have requirement where we need to hide some of the options which we
> have in Format drop down i.e we need only CSV,PDF and excel .
> >
> > How can we do this?
> >
> > Thanks in advance
> >
> > Rehan Mustafa Khan
> >
> > --
> > Posted using Wimdows.net NntpNews Component -
> >
> > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
>
>