Simple sproc executes 240 times slower when called from asp.net thanwhen called from QA

  • Thread starter Thread starter Radu
  • Start date Start date
R

Radu

Hi.

My site's users will upload some "PIN" excel files. On receiving them,
I have to investigate their structure, and if correct, load their
contents into a db table for further processing (inside the db).

The excel file contains, say, 10 records only, and looks like this:
PIN Grouping
0 A
1 A
2 B
3 B
4 V
....................................
9 X

For a specific instance, the ASP code saves the uploaded file as, for
example, "Pin List Sample A1.xls", on a share visible both by me and
by the IIS account, of course.

The sproc named "InputPins_InsertExcelFileIntoRequestedPINS" has to
insert the contents of the excel file into a db table, and is simple.

It contains 4 parameters:
@SessionID varchar(50),
@Filename varchar(200),
@Workbook varchar(200),
@Language varchar(5)

Then I build the following string (simplified):

SELECT [PIN],
a few other fields...,
'Excel' as [Source]
FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="//.../
Pin List
Sample A1.xls";Extended properties=Excel 8.0')...ABC$ WHERE NOT PIN
IS
NULL ORDER BY [Grouping]

Finally the sproc says:
INSERT INTO RequestedPINS EXEC(@SQLString)


All's well. If I run in QueryAnalyzer the following:

exec InputPins_InsertExcelFileIntoRequestedPINS
@SessionID=N'enarct454plep145islfwu55',
@Filename=N'//.../Pin List Sample A1.xls',
@Workbook=N'ABC$',
@Language=N'EN-CA'

the query returns in a few milliseconds, and works fine - I can see
the 10 records in the table RequestedPINS.


Now, asp.net. I run the following simple code:

......................
cnSQL.Open()
cmd = New SqlCommand
("InputPins_InsertExcelFileIntoRequestedPINS", cnSQL)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@SessionID", Session
("SessionID").ToString))
cmd.Parameters.Add(New SqlParameter("@Filename",
strTableName))
cmd.Parameters.Add(New SqlParameter("@Workbook",
strSheetName))
If Session("Language") Is Nothing Then
Session("Language") = "EN-CA"
End If
cmd.Parameters.Add(New SqlParameter("@Language", Session
("Language").ToString))
cmd.ExecuteNonQuery()
cnSQL.Close()

This executes equally well, but requires 12 seconds ! Executing this
sproc from asp.net takes 12
seconds compared to 50 milliseconds in QA - that's a factor of
240 !!!

Since the website is designed as a "wizard", with many data-acquiring
steps/pages, it has to be synchroneous - I cannot go onto the next
page until the excel file is uploaded and its data processed. Note
that all the other sprocs used by this application (and there are
many) execute perfectly - it's only this one which becomes extremely
slow. What can it be ? I have profiled it, but maybe I don't know what
to look for... the fact is that it takes 240 times longer to execute
it from the website than from the QA. What could it be ? Does this
relate solely to SQL Server ?

Thank you very much,
Alex
 
Hi.

My site's users will upload some "PIN" excel files. On receiving them,
I have to investigate their structure, and if correct, load their
contents into a db table for further processing (inside the db).

The excel file contains, say, 10 records only, and looks like this:
PIN            Grouping
0               A
1               A
2               B
3               B
4               V
...................................
9               X

For a specific instance, the ASP code saves the uploaded file as, for
example, "Pin List Sample A1.xls", on a share visible both by me and
by the IIS account, of course.

The sproc named "InputPins_InsertExcelFileIntoRequestedPINS" has to
insert the contents of the excel file into a db table, and is simple.

It contains 4 parameters:
        @SessionID varchar(50),
        @Filename varchar(200),
        @Workbook varchar(200),
        @Language varchar(5)

Then I build the following string (simplified):

SELECT  [PIN],
        a few other fields...,
        'Excel' as [Source]
FROM
        OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="//.../
Pin List
Sample A1.xls";Extended properties=Excel 8.0')...ABC$ WHERE NOT PIN
IS
NULL ORDER BY [Grouping]

Finally the sproc says:
INSERT INTO RequestedPINS EXEC(@SQLString)

All's well. If I run in QueryAnalyzer the following:

exec InputPins_InsertExcelFileIntoRequestedPINS
        @SessionID=N'enarct454plep145islfwu55',
        @Filename=N'//.../Pin List Sample A1.xls',
        @Workbook=N'ABC$',
        @Language=N'EN-CA'

the query returns in a few milliseconds, and works fine - I can see
the 10 records in the table RequestedPINS.

Now, asp.net. I run the following simple code:

            ......................
            cnSQL.Open()
            cmd = New SqlCommand
("InputPins_InsertExcelFileIntoRequestedPINS", cnSQL)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add(New SqlParameter("@SessionID",Session
("SessionID").ToString))
            cmd.Parameters.Add(New SqlParameter("@Filename",
strTableName))
            cmd.Parameters.Add(New SqlParameter("@Workbook",
strSheetName))
            If Session("Language") Is Nothing Then
                Session("Language") = "EN-CA"
            End If
            cmd.Parameters.Add(New SqlParameter("@Language", Session
("Language").ToString))
            cmd.ExecuteNonQuery()
            cnSQL.Close()

This executes equally well, but requires 12 seconds ! Executing this
sproc from asp.net takes 12
seconds compared to 50 milliseconds in QA - that's a factor of
240 !!!

Since the website is designed as a "wizard", with many data-acquiring
steps/pages, it has to be synchroneous - I cannot go onto the next
page until the excel file is uploaded and its data processed. Note
that all the other sprocs used by this application (and there are
many) execute perfectly - it's only this one which becomes extremely
slow. What can it be ? I have profiled it, but maybe I don't know what
to look for... the fact is that it takes 240 times longer to execute
it from the website than from the QA. What could it be ? Does this
relate solely to SQL Server ?

Thank you very much,
Alex
Click to expand...

How did you count the time from ASP.NET? I suppose it is together with
cnSQL.Open() and Close() statements?
 
Hi.

My site's users will upload some "PIN" excel files. On receiving them,
I have to investigate their structure, and if correct, load their
contents into a db table for further processing (inside the db).

The excel file contains, say, 10 records only, and looks like this:
PIN Grouping
0 A
1 A
2 B
3 B
4 V
...................................
9 X

For a specific instance, the ASP code saves the uploaded file as, for
example, "Pin List Sample A1.xls", on a share visible both by me and
by the IIS account, of course.

The sproc named "InputPins_InsertExcelFileIntoRequestedPINS" has to
insert the contents of the excel file into a db table, and is simple.

It contains 4 parameters:
@SessionID varchar(50),
@Filename varchar(200),
@Workbook varchar(200),
@Language varchar(5)

Then I build the following string (simplified):

SELECT [PIN],
a few other fields...,
'Excel' as [Source]
FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="//.../
Pin List
Sample A1.xls";Extended properties=Excel 8.0')...ABC$ WHERE NOT PIN
IS
NULL ORDER BY [Grouping]

Finally the sproc says:
INSERT INTO RequestedPINS EXEC(@SQLString)

All's well. If I run in QueryAnalyzer the following:

exec InputPins_InsertExcelFileIntoRequestedPINS
@SessionID=N'enarct454plep145islfwu55',
@Filename=N'//.../Pin List Sample A1.xls',
@Workbook=N'ABC$',
@Language=N'EN-CA'

the query returns in a few milliseconds, and works fine - I can see
the 10 records in the table RequestedPINS.

Now, asp.net. I run the following simple code:

......................
cnSQL.Open()
cmd = New SqlCommand
("InputPins_InsertExcelFileIntoRequestedPINS", cnSQL)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@SessionID", Session
("SessionID").ToString))
cmd.Parameters.Add(New SqlParameter("@Filename",
strTableName))
cmd.Parameters.Add(New SqlParameter("@Workbook",
strSheetName))
If Session("Language") Is Nothing Then
Session("Language") = "EN-CA"
End If
cmd.Parameters.Add(New SqlParameter("@Language", Session
("Language").ToString))
cmd.ExecuteNonQuery()
cnSQL.Close()

This executes equally well, but requires 12 seconds ! Executing this
sproc from asp.net takes 12
seconds compared to 50 milliseconds in QA - that's a factor of
240 !!!

Since the website is designed as a "wizard", with many data-acquiring
steps/pages, it has to be synchroneous - I cannot go onto the next
page until the excel file is uploaded and its data processed. Note
that all the other sprocs used by this application (and there are
many) execute perfectly - it's only this one which becomes extremely
slow. What can it be ? I have profiled it, but maybe I don't know what
to look for... the fact is that it takes 240 times longer to execute
it from the website than from the QA. What could it be ? Does this
relate solely to SQL Server ?

Thank you very much,
Alex
Click to expand...

How did you count the time from ASP.NET? I suppose it is together with
cnSQL.Open() and Close() statements?

I think what you need to look at is at Parameter Sniffing. I have seen
other posts about the same problem (or at least the symptoms are the same).

One link is:

http://nflanagan.blogspot.com/2006/07/parameter-sniffing.html

Hope this helps
LS
 
Alexei,

1 - No, Alexei, just the "cmd.ExecuteNonQuery()" command takes
roughly 11 seconds instead of 1/20 sec when the sproc is run from QA
2 - I have tried the trick/recipe from
http://nflanagan.blogspot.com/2006/07/parameter-sniffing.html
yesterday - it made absolutely no change - I still obtain the same
ratio of about 240 between times of execution (for only 5-10 very
short records !)
3 - Running the exact same code against my (mirrored) local database
provides superb reaction time - with all of IIS's and IE's overhead,
the navigation from this page to the next happens in under a second.
Is it the network's speed ? No, because....
4 - I have tried with a larger excel file, 500 PINS this time, instead
of 10. This made NO difference on the local db, and no difference on
the server's db - they both reacted as with 5 PINS

Therefore:
a. it cannot be the network's speed (if it were, I would see some
difference between small and large datasets, and, besides, this is an
action query !)
b. if the network's speed is NOT a factor, it means that the only
difference between running against the local/vs remote db is
b1. some obscure setting on the database server, which is NOT
present on my local machine, or....
b2. the fact that in QA I an running as myself (windows
authentication), whereas the website runs as the IIS account.
Other than that, I can see no reason why this could be. I have
imagined that this could be the webserver (IIS) querying the db, and
the db not easily allowing IIS to execute ad-hoc procedures, as
opposed authorizing me, as dbo.... (maybe the db queries IIS's role
and permissions, and one of these questions times out, so the db
finally allows IIS to invoke that sproc, but with a delay... but this
ONLY on the database server, not on my local SQLExpress server).
Something like that. What else could there be ?...

So, to summarize:

DB server Dataset
Caller Time
-----------------------------------------------------------------------------------------------------------------------------------------
Local
Small QA
~50ms
Local
Large QA
~50-100ms
Local
Small IIS
~50ms
Local
Large IIS
~50-100ms

Remote Small
QA ~50ms
Remote Large
QA ~50-100ms
Remote Small
IIS ~10-11sec
Remote Large
IIS ~10-11sec








Thank you for answering.
Alex
 
The table, fixed:

DB server Dataset Caller Time
---------------------------------------------------------------------------­--------------------------------------------------------------
Local Small QA ~50ms
Local Large QA ~50-100ms
Local Small IIS ~50ms
Local Large IIS ~50-100ms

Remote Small QA ~50ms
Remote Large QA ~50-100ms
Remote Small IIS ~10-11sec
Remote Large IIS ~10-11sec
 
Hi.

My site's users will upload some "PIN" excel files. On receiving them,
I have to investigate their structure, and if correct, load their
contents into a db table for further processing (inside the db).

The excel file contains, say, 10 records only, and looks like this:
PIN            Grouping
0               A
1               A
2               B
3               B
4               V
...................................
9               X

For a specific instance, the ASP code saves the uploaded file as, for
example, "Pin List Sample A1.xls", on a share visible both by me and
by the IIS account, of course.

The sproc named "InputPins_InsertExcelFileIntoRequestedPINS" has to
insert the contents of the excel file into a db table, and is simple.

It contains 4 parameters:
        @SessionID varchar(50),
        @Filename varchar(200),
        @Workbook varchar(200),
        @Language varchar(5)

Then I build the following string (simplified):

SELECT  [PIN],
        a few other fields...,
        'Excel' as [Source]
FROM
        OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="//.../
Pin List
Sample A1.xls";Extended properties=Excel 8.0')...ABC$ WHERE NOT PIN
IS
NULL ORDER BY [Grouping]

Finally the sproc says:
INSERT INTO RequestedPINS EXEC(@SQLString)

All's well. If I run in QueryAnalyzer the following:

exec InputPins_InsertExcelFileIntoRequestedPINS
        @SessionID=N'enarct454plep145islfwu55',
        @Filename=N'//.../Pin List Sample A1.xls',
        @Workbook=N'ABC$',
        @Language=N'EN-CA'

the query returns in a few milliseconds, and works fine - I can see
the 10 records in the table RequestedPINS.

Now, asp.net. I run the following simple code:

            ......................
            cnSQL.Open()
            cmd = New SqlCommand
("InputPins_InsertExcelFileIntoRequestedPINS", cnSQL)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add(New SqlParameter("@SessionID",Session
("SessionID").ToString))
            cmd.Parameters.Add(New SqlParameter("@Filename",
strTableName))
            cmd.Parameters.Add(New SqlParameter("@Workbook",
strSheetName))
            If Session("Language") Is Nothing Then
                Session("Language") = "EN-CA"
            End If
            cmd.Parameters.Add(New SqlParameter("@Language", Session
("Language").ToString))
            cmd.ExecuteNonQuery()
            cnSQL.Close()

This executes equally well, but requires 12 seconds ! Executing this
sproc from asp.net takes 12
seconds compared to 50 milliseconds in QA - that's a factor of
240 !!!

Since the website is designed as a "wizard", with many data-acquiring
steps/pages, it has to be synchroneous - I cannot go onto the next
page until the excel file is uploaded and its data processed. Note
that all the other sprocs used by this application (and there are
many) execute perfectly - it's only this one which becomes extremely
slow. What can it be ? I have profiled it, but maybe I don't know what
to look for... the fact is that it takes 240 times longer to execute
it from the website than from the QA. What could it be ? Does this
relate solely to SQL Server ?

Thank you very much,
Alex
Click to expand...

If your db is SQL Server 2008, take a look at the "optimize for
unknown" hint.

- John
 
try setting the parameter types and sizes in your asp.net code. I have found
that if you don't specify the types sometimes SQL server will ignore
indeces.

Radu said:
Hi.

My site's users will upload some "PIN" excel files. On receiving them,
I have to investigate their structure, and if correct, load their
contents into a db table for further processing (inside the db).

The excel file contains, say, 10 records only, and looks like this:
PIN Grouping
0 A
1 A
2 B
3 B
4 V
...................................
9 X

For a specific instance, the ASP code saves the uploaded file as, for
example, "Pin List Sample A1.xls", on a share visible both by me and
by the IIS account, of course.

The sproc named "InputPins_InsertExcelFileIntoRequestedPINS" has to
insert the contents of the excel file into a db table, and is simple.

It contains 4 parameters:
@SessionID varchar(50),
@Filename varchar(200),
@Workbook varchar(200),
@Language varchar(5)

Then I build the following string (simplified):

SELECT [PIN],
a few other fields...,
'Excel' as [Source]
FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="//.../
Pin List
Sample A1.xls";Extended properties=Excel 8.0')...ABC$ WHERE NOT PIN
IS
NULL ORDER BY [Grouping]

Finally the sproc says:
INSERT INTO RequestedPINS EXEC(@SQLString)


All's well. If I run in QueryAnalyzer the following:

exec InputPins_InsertExcelFileIntoRequestedPINS
@SessionID=N'enarct454plep145islfwu55',
@Filename=N'//.../Pin List Sample A1.xls',
@Workbook=N'ABC$',
@Language=N'EN-CA'

the query returns in a few milliseconds, and works fine - I can see
the 10 records in the table RequestedPINS.


Now, asp.net. I run the following simple code:

......................
cnSQL.Open()
cmd = New SqlCommand
("InputPins_InsertExcelFileIntoRequestedPINS", cnSQL)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@SessionID", Session
("SessionID").ToString))
cmd.Parameters.Add(New SqlParameter("@Filename",
strTableName))
cmd.Parameters.Add(New SqlParameter("@Workbook",
strSheetName))
If Session("Language") Is Nothing Then
Session("Language") = "EN-CA"
End If
cmd.Parameters.Add(New SqlParameter("@Language", Session
("Language").ToString))
cmd.ExecuteNonQuery()
cnSQL.Close()

This executes equally well, but requires 12 seconds ! Executing this
sproc from asp.net takes 12
seconds compared to 50 milliseconds in QA - that's a factor of
240 !!!

Since the website is designed as a "wizard", with many data-acquiring
steps/pages, it has to be synchroneous - I cannot go onto the next
page until the excel file is uploaded and its data processed. Note
that all the other sprocs used by this application (and there are
many) execute perfectly - it's only this one which becomes extremely
slow. What can it be ? I have profiled it, but maybe I don't know what
to look for... the fact is that it takes 240 times longer to execute
it from the website than from the QA. What could it be ? Does this
relate solely to SQL Server ?

Thank you very much,
Alex
Click to expand...
 
Back
Top