Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    8

    Question Unanswered: How to improve performance

    Could someone please guide me how to improve performance. I have some Perl script getting data from Sybase through DBi/DBD. My summary report takes about 30 seconds or so. I need to make it faster but not sure how. The program fragment below really slows down the process, I think. I need to call the routine twice to get the complete data. Thanks for any input.

    Code:
    sub getPrevMonths { 
         my ($parentID, $startDate, $endDate, $seq) = @_; 
        my $sth = ''; 
        my @arrs = (); 
    
         for (my $i = -1; $i >= -3; $i-- ) { 
             $sth = $dbh->prepare(qq(  
                                    select distinct  
                                   (select jobID 
                                    from feedXref c, feed d 
                                    where a.parentID = c.parentID 
                                    and c.feedID = d.feedID 
                                    and c.sequence = '$seq'), 
                                   (select distinct datename(month, endTime) 
                                    from transmission c, feedXref d 
                                    where a.parentID = d.parentID 
                                    and c.feedID = d.feedID 
                                    and datepart(mm, endTime) = datepart(mm, dateadd(mm, $i, getdate()))), 
                                    (select avg(datediff(ss, convert(char(8), startTime, 112), dateadd(hh, 0, startTime))) 
                                     from transmission c, feedXref d 
                                     where a.parentID = d.parentID 
                                     and c.feedID = d.feedID 
                                     and d.sequence = '$seq' 
                                     and datepart(mm, startTime) = datepart(mm, dateadd(mm, $i, getdate())) 
                                     and convert(char(8), startTime, 112) not in 
                                                (select convert(char(8), date, 112)  
                                                 from holiday e, feed f 
                                                 where datepart(mm, date) = datepart(mm, dateadd(mm, $i, getdate())) 
                                                 and e.ctryCode = f.ctryCode 
                                                 and c.feedID = f.feedID)), 
                                   (select avg(datediff(ss, convert(char(8), endTime, 112), dateadd(hh, 0, endTime))) 
                                    from transmission c, feedXref d 
                                    where a.parentID = d.parentID 
                                    and c.feedID = d.feedID 
                                    and d.sequence = '$seq' 
                                    and datepart(mm, endTime) = datepart(mm, dateadd(mm, $i, getdate())) 
                                    and convert(char(8), endTime, 112) not in 
                                                (select convert(char(8), date, 112)  
                                                 from holiday e, feed f 
                                                 where datepart(mm, date) = datepart(mm, dateadd(mm, $i, getdate())) 
                                                 and e.ctryCode = f.ctryCode 
                                                 and c.feedID = f.feedID)), 
                                   (select count(transID) 
                                    from transmission c, feedXref d 
                                    where a.parentID = d.parentID 
                                    and c.feedID = d.feedID 
                                    and d.sequence = '$seq' 
                                    and datepart(mm, endTime) = datepart(mm, dateadd(mm, $i, getdate())) 
                                    and convert(char(8), endTime, 112) not in 
                                                (select convert(char(8), date, 112)  
                                                 from holiday e, feed f 
                                                 where datepart(mm, date) = datepart(mm, dateadd(mm, $i, getdate())) 
                                                 and e.ctryCode = f.ctryCode 
                                                 and c.feedID = f.feedID)), 
                                   (select count(transID) 
                                    from transmission c, feedXref d 
                                    where a.parentID = d.parentID 
                                    and c.feedID = d.feedID 
                                    and d.sequence = '$seq' 
                                    and datepart(mm, endTime) = datepart(mm, dateadd(mm, $i, getdate())) 
                                    and convert(char(8), endTime, 112) not in 
                                                (select convert(char(8), date, 112)  
                                                 from holiday e, feed f 
                                                 where datepart(mm, date) = datepart(mm, dateadd(mm, $i, getdate())) 
                                                 and e.ctryCode = f.ctryCode 
                                                 and c.feedID = f.feedID) 
                                                 and c.feedID = f.feedID) 
                                    and transID in (select transID 
                                                    from transmission e, feed f 
                                                    where c.feedID = f.feedID 
                                                    and e.feedID = f.feedID 
                                                    having convert(int, substring(stdTime, 1, 2)) * 3600 + convert(int, substring(stdTime, 4, 2)) * 60 < datediff(ss, convert(char(8), endTime, 112), dateadd(hh, 0, endTime)))), 
     
                                   (select avg(datediff(ss, convert(char(8), c.endTime, 112), dateadd(hh, 0, c.endTime))) 
                                    from gdb c, transmission d, feedXref e 
                                    where c.transID = d.transID 
                                    and d.feedID = e.feedID 
                                    and a.parentID = e.parentID 
                                    and e.sequence = '$seq' 
                                    and loadType = 'rel' 
                                    and datepart(mm, c.endTime) = datepart(mm, dateadd(mm, $i, getdate()))), 
                                   (select count(c.transID) 
                                    from gdb c, transmission d, feedXref e 
                                    where c.transID = d.transID 
                                    and d.feedID = e.feedID 
                                    and a.parentID = e.parentID 
                                    and e.sequence = '$seq' 
                                    and loadType = 'rel' 
                                    and datepart(mm, c.endTime) = datepart(mm, dateadd(mm, $i, getdate()))), 
                                   (select count(*) 
                                    from gdb c, transmission d, feedXref e 
                                    where c.transID = d.transID 
                                    and d.feedID = e.feedID 
                                    and a.parentID = e.parentID 
                                    and e.sequence = '$seq' 
                                    and loadType = 'rel' 
                                    and datepart(mm, c.endTime) = datepart(mm, dateadd(mm, $i, getdate())) 
                                    and c.transID in (select x.transID 
                                                    from gdb x, transmission y, feed z 
                                                    where y.feedID = z.feedID 
                                                    and x.transID = y.transID 
                                                    and loadType = 'rel' 
                                                    and datepart(mm, x.endTime) = datepart(mm, dateadd(mm, $i, getdate())) 
                                                    having convert(int, substring(stdTime, 1, 2)) * 3600 + convert
    (int, substring(stdTime, 4, 2)) * 60 < datediff(ss, convert(char(8), x.endTime, 112), dateadd(hh, 0, x.endTime)))) 
                                    from feedXref a 
                                    where parentID = $parentID   )) 
                or die "could not prepare: " . $dbh->errstr(); 
     
            $sth->execute() or die "could not execute " . $dbh->errstr(); 
     
            my ($jobID, $month, $start, $ldnEnd, $ldnTotal, $ldnLate, $gdbEnd, $gdbTotal, $gdbLate, $transEnd) = $sth->fetchrow_array; 
     
            if ($jobID =~ /JFE/) { 
                push @arrs, [ $month, $start, $ldnEnd, $ldnTotal, $ldnLate, $transEnd ]; 
            } else { 
                push @arrs, [ $month, $start, $gdbEnd, $gdbTotal, $gdbLate, $transEnd ]; 
            } 
     
            $sth->finish(); 
     
        } 
     
        return @arrs; 
    }
    MattR edit: added CODE tags to make code a bit more clear

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Well, the things that jump out right off of the bat:
    1) The SQL is huge. You are using many subqueries which, from the looks of the WHERE clauses, could perhaps be reduced into several smaller ones with liberal use of the CASE statement.
    2) datepart(mm, endTime) = datepart(mm, dateadd(mm, $i, getdate()))
    This part of the where clause, cannot take advantage of an index, so it must scan. This is not necessarily a Bad Thing, but it is certainly not the Best Thing.

    Can you provide SET SHOWPLAN ON output? Also maybe SET STATISTICS IO ON.
    Thanks,

    Matt

  3. #3
    Join Date
    Apr 2003
    Posts
    8
    I reran my Perl script through the browser and it works just fine now. It takes about 5-10 secs, which is close to what I got before I added this long sql statements.

    I am not sure whether the Sybase server or internet/intranet connection was really slow at the time. I should have isolated the problem.

    Do you or anyone knows how to measure the performance of the Sybase server? What is the good way to test that server starts to get slow ? I am still new to Sybase.

    Thanks.

    PS. In fact I have another sql statement that takes about a minute to run and want to reduce by at least by half. I will try with showplan and set statistics and might post the question later.

  4. #4
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    1) sp_sysmon (provide us an aoutput and we will help you to analyse it)
    2) other stored procedures like sp_who, sp_lock, others like sp__cpu, sp__who)
    3) Sybase monitor server
    4) Sybase historical server
    5) external products like SQL Expert, SQL Supervision, Cyrano workbench, etc

  5. #5
    Join Date
    Apr 2003
    Posts
    8

    Question

    Here is another query that I have problem with the timing. It currently takes 20-30 secs and I like to get the results faster. I also attach the
    showplan and statistics IO results. I still don't know much how to read these results

    I would appreciate any input. Thanks a lot.

    <pre>
    select distinct a.parentID,
    (select ctryDesc
    from country c, feed d, feedXref e
    where c.ctryCode = d.ctryCode
    and d.feedID = e.feedID
    and a.parentID = e.parentID
    and e.sequence = '1') as ctry,
    (select jobID
    from feed c, feedXref d
    where a.parentID = d.parentID
    and c.feedID = d.feedID
    and d.sequence = '1'),
    (select stdTime
    from feed c, feedXref d
    where c.feedID = d.feedID
    and a.parentID = d.parentID
    and d.sequence = '1'),
    (select avg(datediff(ss, convert(char(8), startTime, 112), dateadd(hh, 0, startTime)))
    from transmission c, feedXref d
    where a.parentID = d.parentID
    and c.feedID = d.feedID
    and d.sequence = '1'
    and startTime between '20030601 00:00' and '20030630 23:59'
    and convert(char(8), startTime, 112) not in
    (select convert(char(8), date, 112)
    from holiday e, feed f
    where date between '20030601 00:00' and '20030630 23:59'
    and e.ctryCode = f.ctryCode
    and c.feedID = f.feedID)),
    (select avg(datediff(ss, convert(char(8), endTime, 112), dateadd(hh, 0, endTime)))
    from transmission c, feedXref d
    where a.parentID = d.parentID
    and c.feedID = d.feedID
    and d.sequence = '1'
    and endTime between '20030601 00:00' and '20030630 23:59'
    and convert(char(8), endTime, 112) not in
    (select convert(char(8), date, 112)
    from holiday e, feed f
    where date between '20030601 00:00' and '20030630 23:59'
    and e.ctryCode = f.ctryCode
    and c.feedID = f.feedID)),
    (select count(transID)
    from transmission c, feedXref d
    where a.parentID = d.parentID
    and c.feedID = d.feedID
    and d.sequence = '1'
    and endTime between '20030601 00:00' and '20030630 23:59'
    and convert(char(8), endTime, 112) not in
    (select convert(char(8), date, 112)
    from holiday e, feed f
    where date between '20030601 00:00' and '20030630 23:59'
    and e.ctryCode = f.ctryCode
    and c.feedID = f.feedID)),
    (select count(transID)
    from transmission c, feedXref d
    where a.parentID = d.parentID
    and c.feedID = d.feedID
    and d.sequence = '1'
    and endTime between '20030601 00:00' and '20030630 23:59'
    and convert(char(8), endTime, 112) not in
    (select convert(char(8), date, 112)
    from holiday e, feed f
    where date between '20030601 00:00' and '20030630 23:59'
    and e.ctryCode = f.ctryCode
    and c.feedID = f.feedID)
    and transID in (select transID
    from transmission e, feed f
    where c.feedID = f.feedID
    and e.feedID = f.feedID
    having convert(int, substring(stdTime, 1, 2)) * 3600 + convert(int, substring(stdTime, 4, 2)) * 60 >= datediff(ss, convert(char(8), endTime, 112), dateadd(hh, 0, endTime)))\
    ),

    (select avg(datediff(ss, convert(char(8), c.endTime, 112), dateadd(hh, 0, c.end\
    Time)))
    from gdb c, transmission d, feedXref e
    where c.transID = d.transID
    and d.feedID = e.feedID
    and a.parentID = e.parentID
    and e.sequence = '1'
    and loadType = 'rel'
    and c.endTime between '20030601 00:00' and '20030630 23:59'),
    (select count(c.transID)
    from gdb c, transmission d, feedXref e
    where c.transID = d.transID
    and d.feedID = e.feedID
    and a.parentID = e.parentID
    and e.sequence = '1'
    and loadType = 'rel'
    and c.endTime between '20030601 00:00' and '20030630 23:59'),
    (select count(c.transID)
    from gdb c, transmission d, feedXref e
    where c.transID = d.transID
    and d.feedID = e.feedID
    and a.parentID = e.parentID
    and e.sequence = '1'
    and loadType = 'rel'
    and c.endTime between '20030601 00:00' and '20030630 23:59'
    and c.transID in (select f.transID
    from gdb f, transmission g, feed h
    where c.transID = f.transID
    and f.transID = g.transID
    and g.feedID = h.feedID
    and loadType = 'rel'
    having convert(int, substring(stdTime, 1, 2)) * 3600 + convert(int, substring(stdTime, 4, 2)) * 60 >= datediff(ss, convert(char(8), f.endTime, 112), dateadd(hh, 0, f.endTim\
    e))))

    from feedXref a
    order by ctry
    </pre>
    Last edited by bedford; 07-14-03 at 13:33.

  6. #6
    Join Date
    Mar 2002
    Location
    indore
    Posts
    6
    1. You are using too many distinct clause if you have clusterd index try to avoid it.
    2. Use the derived coulmns rather then performing the mathematical calculation in the where clause as it does not use the index and makes the system heavy in case table is large.
    3. If possible break the queries into smaller one.

    use set showplan, set statistics io & statistics time.

    try the above ...

  7. #7
    Join Date
    Dec 2002
    Posts
    104
    some more points,

    u can even make use of temp table for example: u r selecting data in subquery on certain table multiple times ..so try to extract common data in a temp table once and make use of that temp table.
    avoid using between use >= and <= instead of it
    try to avoid negation situation.(not)

    Pooja

Posting Permissions

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