I have a stored procedure that contains a series of bcp calls to export data
.
The bcp calls are executed by xp_cmdshell and are all the same (other than
the table name of course).
I have SET NOCOUNT ON as the first line in the stored procedure and it is
not returning any data. The bcp calls are in the format of:
bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber' queryout
SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
exec @.RC = master..xp_cmdshell @.Query
On one of the bcp shells I'm receiving the following errors but not on
others. The errors are:
SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL
Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
[Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation failed.
Failed to get the call stack!
ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQ
L
Server is terminating this process.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
Server: Msg 11, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation.
@.RETURN_VALUE = N/A
The stored procedure is being called from a .Net class library and is
executing on SQL Server 2000 with following version info:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
Any ideas would be appreciated. Thanks.AV normally means a bug in sqlserver code. I would suggest you contact PSS
regarding this.
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>I have a stored procedure that contains a series of bcp calls to export
>data.
> The bcp calls are executed by xp_cmdshell and are all the same (other than
> the table name of course).
> I have SET NOCOUNT ON as the first line in the stored procedure and it is
> not returning any data. The bcp calls are in the format of:
> bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber'
> queryout
> SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
> exec @.RC = master..xp_cmdshell @.Query
>
> On one of the bcp shells I'm receiving the following errors but not on
> others. The errors are:
> SQLState = S1010, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
> NULL
> Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
> [Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation
> failed.
> Failed to get the call stack!
>
> ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
> SQL
> Server is terminating this process.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
> Server: Msg 11, Level 16, State 1, Line 0
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
> your network documentation.
> @.RETURN_VALUE = N/A
> The stored procedure is being called from a .Net class library and is
> executing on SQL Server 2000 with following version info:
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> Any ideas would be appreciated. Thanks.
>|||AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you elaborat
e?
"oj" wrote:
> AV normally means a bug in sqlserver code. I would suggest you contact PSS
> regarding this.
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>
>|||Jacob,
Sorry. I mean Access Violation and Product Support Services.
http://support.microsoft.com/oas/de...aspx?gprid=2852
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
> AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you
> elaborate?
> "oj" wrote:
>|||Thanks, I'll look into that.
"oj" wrote:
> Jacob,
> Sorry. I mean Access Violation and Product Support Services.
> http://support.microsoft.com/oas/de...aspx?gprid=2852
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
>
>
Showing posts with label executed. Show all posts
Showing posts with label executed. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
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
Subscribe to:
Posts (Atom)