Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2006
    Posts
    13

    Unanswered: SP execution time is very different between SQLAdvantage and isql

    Hi all
    I run below sql with SQLAdvantage and isql, but the time " end - start" I got is very different. isql execute the sp within 2 seconds but SQLAdvantage took more than 10 seconds, Ignore the network factor, could you give me any advise about this issue ?Thanks very much

    ------------------------------------------------------------
    declare @time_trace datetime
    select @time_trace = getdate()
    select "###start:",@time_trace,datepart(ss,@time_trace),d atepart(ms,@time_trace)
    go
    // execute a sp
    go
    declare @time_trace datetime
    select @time_trace = getdate()
    select "###end:",@time_trace,datepart(ss,@time_trace),dat epart(ms,@time_trace)
    go

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    There isn't enough information to be able to hazard a guess.

    You should probably run this with SHOWPLAN and STATISTICS IO turned on to see if there is any difference in behavior.

    Michael

  3. #3
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    You should have SET TIME, STATISTICS ON.

    It is possible SQL Adv is running on an local desktop whereas isql is running on the host itself? The difference perhaps is the network?

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Like willy_and_the_ci I guess SQL Advantage is running on the local desktop whereas isql is running on the host itself.

    How many rows and columns are returned to you? I guess a few thousand rows.
    The problem could be your PC that is low on memory when receiving the result set.
    But I suspect it is network related.

    You can improve performance by increasing your network packet size
    e.g. when connecting with SQL Advantage click on options
    Although Network Packet Size is grey, you can change it by clicking on the arrow next to it.

    FYI: My server settings:
    Code:
    Parameter Name                 Default     Memory Used Config Value Run Value   Unit                 Type       
     ------------------------------ ----------- ----------- ------------ ----------- -------------------- ---------- 
     additional network memory                0        5120     5242880      5242880 bytes                dynamic    
     default network packet size            512       #3892        4096         4096 bytes                static     
     max network packet size                512           0       20992        20992 bytes                static
    My test using this code below selects 2081 rows

    Code:
    select 'strt_time'=getdate() into #t1
    select * from sybsystemprocs..syscolumns 
    select 'runtime(ms)'=datediff(ms,strt_time,getdate()) from #t1
    go
    select 'dispay(ms)'=datediff(ms,strt_time,getdate()) from #t1
    go
    drop table #t1
    go
    SQL Advantage connecting at default network packet size 512

    runtime(ms)
    -----------
    120

    dispay(ms)
    -----------
    4636

    SQL Advantage connecting at network packet size 4096

    runtime(ms)
    -----------
    10

    dispay(ms)
    -----------
    1133

    SQL Advantage connecting at network packet size 20992

    runtime(ms)
    -----------
    6

    dispay(ms)
    -----------
    930


    Note: The server setting “default network packet size” is not the default packet size that the client connects at. It is just to pre-allocate memory as if every connection is made at that packet size.

  5. #5
    Join Date
    Jul 2006
    Posts
    13
    Hi Pdreyed

    Could you tell me the version of your sql advantage please ?
    Mine is version 11.5, and I can NOT find the options to set "Network Packet Size"

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    SQL Advantage/12.5.1/EBF 11428/P/NT

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Just checked SQL Advantage/11.5.1.3/P/PC

    It has the same option to change the network packet size

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    See attached picture on where to find it
    Attached Thumbnails Attached Thumbnails pktsize.GIF  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •