Alternate ways of deleting records - without logging

Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:

> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>

Allow Multiple Parameters form Windows Application

I have been working through a solution to allow multiple parameters to be
passed into a SQL RS report. I have created a windows application that calls
sql reporting services. The problem that I am having now is passing the
multiple values the user selects from the dropdown to sql reporting services.
I am setting: returnValues.Value
When I try to set this parameters to 1;2;3, report fails. However, setting
that value to 1 works.
Is this possbile?RS 2000 does not support multiple selections. For instance,
select * from blah where somefield in (@.Param)
will not work. What you can do is use either an expression or call a stored
procedure that takes the parameter and handles appropriately).
This will work:
= "select * from blah where somefield in (" & Parameters!Paramname.value &
")"
Note that this assume you have dealt with putting in all the proper syntax
like single quotes around charater type parameters, etc and that this will
be a valid query when done.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>I have been working through a solution to allow multiple parameters to be
> passed into a SQL RS report. I have created a windows application that
> calls
> sql reporting services. The problem that I am having now is passing the
> multiple values the user selects from the dropdown to sql reporting
> services.
>
> I am setting: returnValues.Value
> When I try to set this parameters to 1;2;3, report fails. However,
> setting
> that value to 1 works.
> Is this possbile?|||I have been able to get the mulitple selection parameters to work if I call
the SQL RS report from a web page. I basically created dropdown listboxes
and have the form post to the url of the page I want to run. The multiple
selection values are sent to the report and the report works fine. However,
I am trying to call SQL RS report from a Windows Application. I have my
report created in a way that it uses a stored procedure to parse the multiple
values passed to in and joins to those values from a temp table.
"Bruce L-C [MVP]" wrote:
> RS 2000 does not support multiple selections. For instance,
> select * from blah where somefield in (@.Param)
> will not work. What you can do is use either an expression or call a stored
> procedure that takes the parameter and handles appropriately).
> This will work:
> = "select * from blah where somefield in (" & Parameters!Paramname.value &
> ")"
> Note that this assume you have dealt with putting in all the proper syntax
> like single quotes around charater type parameters, etc and that this will
> be a valid query when done.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >I have been working through a solution to allow multiple parameters to be
> > passed into a SQL RS report. I have created a windows application that
> > calls
> > sql reporting services. The problem that I am having now is passing the
> > multiple values the user selects from the dropdown to sql reporting
> > services.
> >
> >
> > I am setting: returnValues.Value
> >
> > When I try to set this parameters to 1;2;3, report fails. However,
> > setting
> > that value to 1 works.
> >
> > Is this possbile?
>
>|||OK, so you are doing the stored procedure method. That a good way to do it.
This whole thing work if from a web page passing in the multiple selections,
the only difference is that you are doing this from a windows app?
How are you integrating your windows app? URL integration or web services?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>I have been able to get the mulitple selection parameters to work if I call
> the SQL RS report from a web page. I basically created dropdown listboxes
> and have the form post to the url of the page I want to run. The multiple
> selection values are sent to the report and the report works fine.
> However,
> I am trying to call SQL RS report from a Windows Application. I have my
> report created in a way that it uses a stored procedure to parse the
> multiple
> values passed to in and joins to those values from a temp table.
> "Bruce L-C [MVP]" wrote:
>> RS 2000 does not support multiple selections. For instance,
>> select * from blah where somefield in (@.Param)
>> will not work. What you can do is use either an expression or call a
>> stored
>> procedure that takes the parameter and handles appropriately).
>> This will work:
>> = "select * from blah where somefield in (" & Parameters!Paramname.value
>> &
>> ")"
>> Note that this assume you have dealt with putting in all the proper
>> syntax
>> like single quotes around charater type parameters, etc and that this
>> will
>> be a valid query when done.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >I have been working through a solution to allow multiple parameters to
>> >be
>> > passed into a SQL RS report. I have created a windows application that
>> > calls
>> > sql reporting services. The problem that I am having now is passing
>> > the
>> > multiple values the user selects from the dropdown to sql reporting
>> > services.
>> >
>> >
>> > I am setting: returnValues.Value
>> >
>> > When I try to set this parameters to 1;2;3, report fails. However,
>> > setting
>> > that value to 1 works.
>> >
>> > Is this possbile?
>>|||I actually need the ability to write the report to a file or display the
report in a web browser on the screen. In both cases I have to set the
Parameter values using an array.
"Bruce L-C [MVP]" wrote:
> OK, so you are doing the stored procedure method. That a good way to do it.
> This whole thing work if from a web page passing in the multiple selections,
> the only difference is that you are doing this from a windows app?
> How are you integrating your windows app? URL integration or web services?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
> >I have been able to get the mulitple selection parameters to work if I call
> > the SQL RS report from a web page. I basically created dropdown listboxes
> > and have the form post to the url of the page I want to run. The multiple
> > selection values are sent to the report and the report works fine.
> > However,
> > I am trying to call SQL RS report from a Windows Application. I have my
> > report created in a way that it uses a stored procedure to parse the
> > multiple
> > values passed to in and joins to those values from a temp table.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> RS 2000 does not support multiple selections. For instance,
> >> select * from blah where somefield in (@.Param)
> >>
> >> will not work. What you can do is use either an expression or call a
> >> stored
> >> procedure that takes the parameter and handles appropriately).
> >>
> >> This will work:
> >>
> >> = "select * from blah where somefield in (" & Parameters!Paramname.value
> >> &
> >> ")"
> >>
> >> Note that this assume you have dealt with putting in all the proper
> >> syntax
> >> like single quotes around charater type parameters, etc and that this
> >> will
> >> be a valid query when done.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
> >> message
> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >> >I have been working through a solution to allow multiple parameters to
> >> >be
> >> > passed into a SQL RS report. I have created a windows application that
> >> > calls
> >> > sql reporting services. The problem that I am having now is passing
> >> > the
> >> > multiple values the user selects from the dropdown to sql reporting
> >> > services.
> >> >
> >> >
> >> > I am setting: returnValues.Value
> >> >
> >> > When I try to set this parameters to 1;2;3, report fails. However,
> >> > setting
> >> > that value to 1 works.
> >> >
> >> > Is this possbile?
> >>
> >>
> >>
>
>|||What I was trying to clarify is what does and does not work.
It sounds like calling the report from a web page using URL integration does
work. But, you are trying to use web services from your windows app (as an
alternative you can embed an IE control and use URL integration). Since you
say that a single value selected work, I wonder if some seperator character
is causing a problem. In your windows app try having a textbox that you key
in the correct value and see if that works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:A07E6166-50E6-44D8-8ED4-3E3C279EAB1E@.microsoft.com...
>I actually need the ability to write the report to a file or display the
> report in a web browser on the screen. In both cases I have to set the
> Parameter values using an array.
> "Bruce L-C [MVP]" wrote:
>> OK, so you are doing the stored procedure method. That a good way to do
>> it.
>> This whole thing work if from a web page passing in the multiple
>> selections,
>> the only difference is that you are doing this from a windows app?
>> How are you integrating your windows app? URL integration or web
>> services?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>> >I have been able to get the mulitple selection parameters to work if I
>> >call
>> > the SQL RS report from a web page. I basically created dropdown
>> > listboxes
>> > and have the form post to the url of the page I want to run. The
>> > multiple
>> > selection values are sent to the report and the report works fine.
>> > However,
>> > I am trying to call SQL RS report from a Windows Application. I have
>> > my
>> > report created in a way that it uses a stored procedure to parse the
>> > multiple
>> > values passed to in and joins to those values from a temp table.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> RS 2000 does not support multiple selections. For instance,
>> >> select * from blah where somefield in (@.Param)
>> >>
>> >> will not work. What you can do is use either an expression or call a
>> >> stored
>> >> procedure that takes the parameter and handles appropriately).
>> >>
>> >> This will work:
>> >>
>> >> = "select * from blah where somefield in (" &
>> >> Parameters!Paramname.value
>> >> &
>> >> ")"
>> >>
>> >> Note that this assume you have dealt with putting in all the proper
>> >> syntax
>> >> like single quotes around charater type parameters, etc and that this
>> >> will
>> >> be a valid query when done.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >>
>> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >> >I have been working through a solution to allow multiple parameters
>> >> >to
>> >> >be
>> >> > passed into a SQL RS report. I have created a windows application
>> >> > that
>> >> > calls
>> >> > sql reporting services. The problem that I am having now is passing
>> >> > the
>> >> > multiple values the user selects from the dropdown to sql reporting
>> >> > services.
>> >> >
>> >> >
>> >> > I am setting: returnValues.Value
>> >> >
>> >> > When I try to set this parameters to 1;2;3, report fails. However,
>> >> > setting
>> >> > that value to 1 works.
>> >> >
>> >> > Is this possbile?
>> >>
>> >>
>> >>
>>