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

Discussion in 'ASP .Net' started by Radu, Mar 19, 2009.

  1. Radu

    Radu Guest

    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
     
    Radu, Mar 19, 2009
    #1
    1. Advertising

  2. Re: Simple sproc executes 240 times slower when called from asp.netthan when called from QA

    On Mar 19, 3:34 pm, Radu <> wrote:
    > 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


    How did you count the time from ASP.NET? I suppose it is together with
    cnSQL.Open() and Close() statements?
     
    Alexey Smirnov, Mar 19, 2009
    #2
    1. Advertising

  3. Radu

    Lloyd Sheen Guest

    Re: Simple sproc executes 240 times slower when called from asp.net than when called from QA

    "Alexey Smirnov" <> wrote in message
    news:...
    On Mar 19, 3:34 pm, Radu <> wrote:
    > 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


    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
     
    Lloyd Sheen, Mar 19, 2009
    #3
  4. Radu

    Radu Guest

    Re: Simple sproc executes 240 times slower when called from asp.netthan when called from QA

    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
     
    Radu, Mar 19, 2009
    #4
  5. Radu

    Radu Guest

    Re: Simple sproc executes 240 times slower when called from asp.netthan when called from QA

    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
     
    Radu, Mar 19, 2009
    #5
  6. Radu

    Johnny Fiver Guest

    Re: Simple sproc executes 240 times slower when called from asp.netthan when called from QA

    On Mar 19, 9:34 am, Radu <> wrote:
    > 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


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

    - John
     
    Johnny Fiver, Mar 21, 2009
    #6
  7. Radu

    bill Guest

    Re: Simple sproc executes 240 times slower when called from asp.net than when called from QA

    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" <> wrote in message
    news:...
    > 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
     
    bill, Mar 27, 2009
    #7
  8. Radu

    charlie_Stell

    Joined:
    Aug 28, 2009
    Messages:
    1
    Heres a crazy one

    I was having the same performance issue when using INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0',....

    The issue ended up being somehow caused by a large number of files in the datsource directory (where I was creating lots of txt files).

    By changing:
    INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0',\\server\dir\
    [where \\server\dir\ has a few k files]
    to

    INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0',\\server\dir\a\ [where \\server\dir\a has 0 files]

    My time went from 10-15 second to <.00 ms
     
    charlie_Stell, Aug 28, 2009
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andre Charbonneau

    XPath queries getting slower and slower...

    Andre Charbonneau, Feb 15, 2005, in forum: Java
    Replies:
    0
    Views:
    575
    Andre Charbonneau
    Feb 15, 2005
  2. Replies:
    15
    Views:
    538
    Roy Harvey
    Oct 26, 2006
  3. Ludovic
    Replies:
    0
    Views:
    611
    Ludovic
    Dec 8, 2006
  4. Lie
    Replies:
    25
    Views:
    786
    Dafydd Hughes
    Dec 18, 2007
  5. Jack Steven
    Replies:
    2
    Views:
    476
    Chris Rebert
    Mar 9, 2009
Loading...

Share This Page