Further to 'slow backups'

I'm backing up to a tape drive
Why does each db take the same time to back up, regardless
of db size...''?Read the page titled "Optimizing Backup and Restore Performance" in SQL
Server 2000 Books Online. It discusses how to optimize backups to tape.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"H jones" <anonymous@.discussions.microsoft.com> wrote in message
news:031201c3db58$a1d52010$a001280a@.phx.gbl...
I'm backing up to a tape drive
Why does each db take the same time to back up, regardless
of db size...''?

Further to 'slow backups'

I'm backing up to a tape drive
Why does each db take the same time to back up, regardless
of db size...''?Read the page titled "Optimizing Backup and Restore Performance" in SQL
Server 2000 Books Online. It discusses how to optimize backups to tape.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"H jones" <anonymous@.discussions.microsoft.com> wrote in message
news:031201c3db58$a1d52010$a001280a@.phx.gbl...
I'm backing up to a tape drive
Why does each db take the same time to back up, regardless
of db size...''?

further to problem with obtaining first/last occurance of contiguous blocks of data

Hi again, I was hoping someone could help me create a sql query to
minimizing the content in my table based on a few rules.
Further to my previous post...
f2b9da63" target="_blank">http://groups.google.com.au/group/m...br />
f2b9da63
... I have decided to use triggers to process my table (see trigger solutio
n
in my previous post). However since triggers are slow at processing, I aim
to search for a solution to minimize my initial table structure so the
triggers have less data to work with (hence process it much quicker). What I
am looking for is a method to perform the following:
This is my initial table (as an example):
[system] [date] [isOn]
A 01 0
A 04 1
A 05 1
A 06 0
A 20 1
A 21 0
A 25 0
A 27 1
A 32 1
A 33 1
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
B 50 1
B 51 1
B 53 1
B 67 0
I want my final table to look like this:
[system] [date] [isOn]
A 01 0
A 04 1
-
A 06 0
A 20 1
A 21 0
-
A 27 1
-
-
A 34 0
A 40 1
B 41 1
B 45 0
B 49 1
-
-
-
B 67 0
... where i have placed a '-' sign to indicate the rows i need deleted
I am deleting rows under the following conditions:
1. for a contiguous block of 1's WITHIN the same client range, delete all
but the first one ( i.e.the one at the earliest date)
2. for a contiguous block of 0's WITHIN the same client range, delete all
but the first one (i.e. the one at the earliest date)
i.e. notice above that even though there is a contiguous block of 1's from
date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
B.
After this table is thus processed, I can use my cursor on it.
Any help would be really appreciated!
many thanks
wileyI have scrapped my cursor implementation for a much faster set-based
approach put forward by Itzik Ben-Gan (as im my previous post). Thanks
everyone!
cheers
wiley
"wiley" <wiley@.nospam.com> wrote in message
news:uK5SI4SFGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi again, I was hoping someone could help me create a sql query to
> minimizing the content in my table based on a few rules.
> Further to my previous post...
> 29f2b9da63" target="_blank">http://groups.google.com.au/group/m... />
29f2b9da63
> ... I have decided to use triggers to process my table (see trigger
> solution in my previous post). However since triggers are slow at
> processing, I aim to search for a solution to minimize my initial table
> structure so the triggers have less data to work with (hence process it
> much quicker). What I am looking for is a method to perform the following:
> This is my initial table (as an example):
> [system] [date] [isOn]
> A 01 0
> A 04 1
> A 05 1
> A 06 0
> A 20 1
> A 21 0
> A 25 0
> A 27 1
> A 32 1
> A 33 1
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> B 50 1
> B 51 1
> B 53 1
> B 67 0
> I want my final table to look like this:
> [system] [date] [isOn]
> A 01 0
> A 04 1
> -
> A 06 0
> A 20 1
> A 21 0
> -
> A 27 1
> -
> -
> A 34 0
> A 40 1
> B 41 1
> B 45 0
> B 49 1
> -
> -
> -
> B 67 0
> ... where i have placed a '-' sign to indicate the rows i need deleted
> I am deleting rows under the following conditions:
> 1. for a contiguous block of 1's WITHIN the same client range, delete all
> but the first one ( i.e.the one at the earliest date)
> 2. for a contiguous block of 0's WITHIN the same client range, delete all
> but the first one (i.e. the one at the earliest date)
> i.e. notice above that even though there is a contiguous block of 1's from
> date = 40 to 41, I have not remove the 2nd '1' as that crosses into client
> B.
> After this table is thus processed, I can use my cursor on it.
> Any help would be really appreciated!
> many thanks
> wiley
>

Further Question on American Date Format

Thanks Scott and Aron for the info on American date formatting. However I'm
still slightly confused.
recap of problem:
One of my table's in SQL Server contains a field of a field type 'datetime'
and recording of dates are stored in American format and not Irish/Uk
format. As sugessted by previous posting that the client application control
this, I used Enterprise Manager to insert a record into the table and the
date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
not as "16/08/2004 4:34:52PM"
This is causing me major proiblems as I'm trying to run simple reporting
form a web base client base on dates. The only way the reporting will work if
I change the date format on my machine to american format.
Any ideas ?
Thanks,
Liam
Hi
SQL does not store the date in an human readable format and does not persist
the formatting you used to enter it. It is up to you, when you select the
data, to format it to what you want.
Look at CAST or CONVERT in BOL for formatting information.
Regards
Mike
"Liam Mac" wrote:

> Thanks Scott and Aron for the info on American date formatting. However I'm
> still slightly confused.
> recap of problem:
> One of my table's in SQL Server contains a field of a field type 'datetime'
> and recording of dates are stored in American format and not Irish/Uk
> format. As sugessted by previous posting that the client application control
> this, I used Enterprise Manager to insert a record into the table and the
> date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
> not as "16/08/2004 4:34:52PM"
> This is causing me major proiblems as I'm trying to run simple reporting
> form a web base client base on dates. The only way the reporting will work if
> I change the date format on my machine to american format.
> Any ideas ?
> Thanks,
> Liam
sql

Further Question on American Date Format

Thanks Scott and Aron for the info on American date formatting. However I'm
still slightly confused.
recap of problem:
One of my table's in SQL Server contains a field of a field type 'datetime'
and recording of dates are stored in American format and not Irish/Uk
format. As sugessted by previous posting that the client application control
this, I used Enterprise Manager to insert a record into the table and the
date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
not as "16/08/2004 4:34:52PM"
This is causing me major proiblems as I'm trying to run simple reporting
form a web base client base on dates. The only way the reporting will work if
I change the date format on my machine to american format.
Any ideas '
Thanks,
LiamHi
SQL does not store the date in an human readable format and does not persist
the formatting you used to enter it. It is up to you, when you select the
data, to format it to what you want.
Look at CAST or CONVERT in BOL for formatting information.
Regards
Mike
"Liam Mac" wrote:
> Thanks Scott and Aron for the info on American date formatting. However I'm
> still slightly confused.
> recap of problem:
> One of my table's in SQL Server contains a field of a field type 'datetime'
> and recording of dates are stored in American format and not Irish/Uk
> format. As sugessted by previous posting that the client application control
> this, I used Enterprise Manager to insert a record into the table and the
> date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
> not as "16/08/2004 4:34:52PM"
> This is causing me major proiblems as I'm trying to run simple reporting
> form a web base client base on dates. The only way the reporting will work if
> I change the date format on my machine to american format.
> Any ideas '
> Thanks,
> Liam

further informations

Just for the record:
- the statements are viewed exactly before execution with an unicode aware
application and they contain the right unicodes.
- the db columns supposed to store the unicodes are defined as nvarchar
- the db viewer (SQL Query Analizer) is also unicode aware. It shows right
unicodes for values inserted with "preparedStatements" but ''?...' for
those inserted with plain statements.
?Hint?
In statements we don't use the N prefix for the unicode values. F.ex.:
we do:
UPDATE Table1 SET Col1 = 'New Text' WHERE id = 1
not:
UPDATE Table1 SET Col1 = N'New Text' WHERE id = 1
Is there a way to eliminate the necessity of using N by means of DB/SQL
Server settings?
Many thanks in advance.I think the '? show up because you insert non-unicode strings, and
there's a mismatch between the code pages of the process that changes the
data and the process that reads the data.
The ideal way to fix this would be to stop generating UPDATE statements, if
you can't change your code like that then you need to insert the values as
Unicode.
As a side note, you'll want to pay attention to injection attacks.
In the statement below:
UPDATE Table1 SET Col1 = N'New Text' WHERE id = 1
If the value of 'Nex Text' comes from the user then you need to escape it
with QUOTENAME
UPDATE Table1 SET Col1 = QUOTENAME(N'New Text','''') WHERE id = 1
Otherwise it can be injected with >>>>New Text' do_a_bad_thing_here --<<<<
UPDATE Table1 SET Col1 = N'New Text' do_a_bad_thing_here -- WHERE id = 1
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cristian Senchiu" <Cristian Senchiu@.discussions.microsoft.com> wrote in
message news:88F7BF72-A378-4A4D-9D42-E2F9F98C697A@.microsoft.com...
> Just for the record:
> - the statements are viewed exactly before execution with an unicode aware
> application and they contain the right unicodes.
> - the db columns supposed to store the unicodes are defined as nvarchar
> - the db viewer (SQL Query Analizer) is also unicode aware. It shows right
> unicodes for values inserted with "preparedStatements" but ''?...' for
> those inserted with plain statements.
> ?Hint?
> In statements we don't use the N prefix for the unicode values. F.ex.:
> we do:
> UPDATE Table1 SET Col1 = 'New Text' WHERE id = 1
> not:
> UPDATE Table1 SET Col1 = N'New Text' WHERE id = 1
> Is there a way to eliminate the necessity of using N by means of DB/SQL
> Server settings?
> Many thanks in advance.

Further Information

If you require further information, I will be able to provide.
Sorry,
it is meant to be a reply to my previous message in
Error 14053 and Push Replication.
I would be very greatful if someone may be able to provide assistance,
Regards,
Betty.
"Betty Smith" wrote:

> If you require further information, I will be able to provide.
|||Betty,
please see my reply below.
Rgds,
Paul Ibison
"Betty Smith" <BettySmith@.discussions.microsoft.com> wrote in message
news:60120143-FDA9-404D-B79E-3EFC2C6AF8AE@.microsoft.com...[vbcol=seagreen]
> Sorry,
> it is meant to be a reply to my previous message in
> Error 14053 and Push Replication.
> I would be very greatful if someone may be able to provide assistance,
> Regards,
> Betty.
> "Betty Smith" wrote:

function executed as part of a stored procedure?

Hi there!

I've a stored procedure who gets a lot of data sets (50.000 - 200.000). For each of this data set i've to make further (recursiv) calculations what i do in an user defined function. The problem is that the functions needs to make a heavy query (everytime the same) and calculates something based on this query resultset. But the query requieres ~ 600ms, what results in a great amount of time (i.e. 6 hours)by executing this function 50.000 or more times.

I am wondering if it is possible to create the function as part of the stored procedure, so that the heavy query is beeing executed only one time and the function run in the scope of the stored procedure.

Is there any idea?

Cheers, Torsten

Torsten:

What exactly are you meaning by 50,000 - 200,000 result sets? Do you mean records or do you mean 50,000 -200,000 multi-record datasets? To me it sounds like you are talking about a record-based process instead of a set-based process.

If so, then yes, you should by all means convert the stored procedure to a set-based process and call your function only one time.

It would help if you could provide the main part of the process you are doing and important elements of your data schema.

|||

Hi Kent!

My procedure makes something like

select a,b,c,d,e,f,dbo.fct_my_funktion(a,b,c,d,e,f) amount from somewhere. This are > 50.000 sets, so the function is called > 50.000 times.

The function dbo.fct_my_funktion makes something like that:

Insert into a table var (...) (Select <a hierarchy left outer join to somethiong> from x,y.) <<<- That is the bottleneck

Aggregate recursiv on the table var <<<- That is very fast (3 to 20 ms, that quiet okay)

return the amount

Torsten

|||

Torsten:

A few things. First, you have done a good job of identifying your bottleneck. If this insert statement is taking about 600 ms per iteration then yes, this is what is killing you. Therefore, it would be good if you could give details that relate to this particular insert statement. Also, is it possible for you to run of the SELECT statements for that INSERT in isolation and get the QUERY PLAN for that particular SELECT.

Also, a couple of other issues. Are you running SQL Server 2005? A secondary issue here is that you are using a SCALAR function. It would be best if we could retrieve this information by some other means because scalar functions tend to be inefficient.

But FIRST: we need to look at the SELECT that statement.

|||

I did it!

I'm running SQL Server 2005. The solution ist to create a cursor (over the very heavy query - is only executed once) and then calculate the staff for each row in the cursor loop. So i dont need to use another function - i have gone from ~ 600ms per row to 13-17ms. That's quite good.

Thank you for your comments, may be it has openend my mind...

Greetings from germany,

Torsten

function executed as part of a stored procedure?

Hi there!

I've a stored procedure who gets a lot of data sets (50.000 - 200.000). For each of this data set i've to make further (recursiv) calculations what i do in an user defined function. The problem is that the functions needs to make a heavy query (everytime the same) and calculates something based on this query resultset. But the query requieres ~ 600ms, what results in a great amount of time (i.e. 6 hours)by executing this function 50.000 or more times.

I am wondering if it is possible to create the function as part of the stored procedure, so that the heavy query is beeing executed only one time and the function run in the scope of the stored procedure.

Is there any idea?

Cheers, Torsten

Torsten:

What exactly are you meaning by 50,000 - 200,000 result sets? Do you mean records or do you mean 50,000 -200,000 multi-record datasets? To me it sounds like you are talking about a record-based process instead of a set-based process.

If so, then yes, you should by all means convert the stored procedure to a set-based process and call your function only one time.

It would help if you could provide the main part of the process you are doing and important elements of your data schema.

|||

Hi Kent!

My procedure makes something like

select a,b,c,d,e,f,dbo.fct_my_funktion(a,b,c,d,e,f) amount from somewhere. This are > 50.000 sets, so the function is called > 50.000 times.

The function dbo.fct_my_funktion makes something like that:

Insert into a table var (...) (Select <a hierarchy left outer join to somethiong> from x,y.) <<<- That is the bottleneck

Aggregate recursiv on the table var <<<- That is very fast (3 to 20 ms, that quiet okay)

return the amount

Torsten

|||

Torsten:

A few things. First, you have done a good job of identifying your bottleneck. If this insert statement is taking about 600 ms per iteration then yes, this is what is killing you. Therefore, it would be good if you could give details that relate to this particular insert statement. Also, is it possible for you to run of the SELECT statements for that INSERT in isolation and get the QUERY PLAN for that particular SELECT.

Also, a couple of other issues. Are you running SQL Server 2005? A secondary issue here is that you are using a SCALAR function. It would be best if we could retrieve this information by some other means because scalar functions tend to be inefficient.

But FIRST: we need to look at the SELECT that statement.

|||

I did it!

I'm running SQL Server 2005. The solution ist to create a cursor (over the very heavy query - is only executed once) and then calculate the staff for each row in the cursor loop. So i dont need to use another function - i have gone from ~ 600ms per row to 13-17ms. That's quite good.

Thank you for your comments, may be it has openend my mind...

Greetings from germany,

Torsten