Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2009
    Posts
    13

    Unanswered: Performance Tuning of the query

    Hi All, I have return the following query using Inline view to get output in Comma seperated format. But the problem is it is taking too much time & i have 45 queries in same format. Can someone help me to enhance performance of this query or is there any other way to write this query?


    select 'MyTable'+','+convert(varchar(10), A.Day1)+','+convert(varchar(10), B.Day2)+','+convert(varchar(10), C.Day3)+','+convert(varchar(10), D.Day4)+','+convert(varchar(10), E.Day5)+','+convert(varchar(10), F.Day6)+','+convert(varchar(10), G.Day7)+','+convert(varchar(10), H.Day8)+','+convert(varchar(10), I.Day9)+','+convert(varchar(10), J.Day10) from
    (
    select count(*) 'Day1'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -0, getdate()), 101)
    )A,
    (
    select count(*) 'Day2'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -1, getdate()), 101)
    )B,
    (
    select count(*) 'Day3'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -2, getdate()), 101)
    )C,
    (
    select count(*) 'Day4'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -3, getdate()), 101)
    )D,
    (
    select count(*) 'Day5'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -4, getdate()), 101)
    )E,
    (
    select count(*) 'Day6'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -5, getdate()), 101)
    )F,
    (
    select count(*) 'Day7'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -6, getdate()), 101)
    )G,
    (
    select count(*) 'Day8'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -7, getdate()), 101)
    )H,
    (
    select count(*) 'Day9'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -8, getdate()), 101)
    )I,
    (
    select count(*) 'Day10'
    from MyTable
    where convert(varchar(20), DCount, 101) = convert(varchar(20), dateadd(dd, -9, getdate()), 101)
    )J

  2. #2
    Join Date
    Mar 2009
    Posts
    4
    If there is an index on coloumn DCount of table MyTable then it will not be used as you are using convert function on it, try to rewrite your query without using any function on index column.

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    or is there any other way to write this query?
    I don't have a version of Sybase near me so I can't test this. Also some versions of Sybase don't have the list() function. You'll also need to be careful about the ordering of the group by data (that's why I changed the date format to yyyymmdd). However if this works then it should be a little bit faster (and shorter) :
    Code:
    select 'MyTable,' + list( cnt )
    from (
             select convert(varchar(20), DCount, 112) as dt, 
                    count(*) as cnt
             from   MyTable
             where  DCount >= dateadd( dd, getdate(), -9 )
             group by convert(varchar(20), DCount, 112) 
          ) t

  4. #4
    Join Date
    Mar 2009
    Posts
    13
    hi Sundeep,

    In the table, the value of DCount is like 'May 1 2008 12:00AM' & if i do not convert, it it will not give us desired result.

    Mike,

    list function is not available in Sybase-IQ DB.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    list function is not available in Sybase-IQ DB.
    Is it necessary to have the items in a list, databases work much better when you return sets of data ie:
    Code:
             select convert(varchar(20), DCount, 102) as dt, 
                    count(*) as cnt
             from   MyTable
             where  DCount >= dateadd( dd, getdate(), -9 )
             group by convert(varchar(20), DCount, 102)
    Here's a version without the list function but please remember I don't have a version of Sybase near me so I can't test this. It might need playing with to get it to work but again it will be much faster (I hope) than your original SQL :
    Code:
    create table #totals( diff int, cnt int )
    
    insert #totals ( diff, cnt )
    select datediff( dd, DCount, convert(varchar,getdate(),106) ),
           count(*) as cnt
    from   MyTable
    where  DCount >= dateadd( dd, convert(varchar,getdate(),106) , -9 )
    group by datediff( dd, DCount, convert(varchar,getdate(),106) )
    
    select 'MyTable,' + 
            ( select convert(varchar,cnt) from #totals where diff=0 ) + ',' +
            ( select convert(varchar,cnt) from #totals where diff=1 ) + ',' +
            ( select convert(varchar,cnt) from #totals where diff=2 ) + ',' +
            ( select convert(varchar,cnt) from #totals where diff=3 ) + ',' +
            ( select convert(varchar,cnt) from #totals where diff=4 ) + ',' +
            ( select convert(varchar,cnt) from #totals where diff=5 ) + ',' +
            ( select convert(varchar,cnt) from #totals where diff=6 ) + ',' +
            ( select convert(varchar,cnt) from #totals where diff=7 ) + ',' +
            ( select convert(varchar,cnt) from #totals where diff=8 ) + ',' +
            ( select convert(varchar,cnt) from #totals where diff=9 )
    
    drop table #totals
    Sandeep is quite correct about performance and using functions.

  6. #6
    Join Date
    Mar 2009
    Posts
    4
    If the time portion is always 12:00AM then you can try using DCount=convert(char(12),dateadd(dd, -4, getdate()))

  7. #7
    Join Date
    Mar 2009
    Posts
    13
    no, the time is different... & we are not suppose to create any additional table in Production environment..

  8. #8
    Join Date
    Mar 2009
    Posts
    13
    One more question,

    I am executing following query to get last 10 day's count group by DCount column.
    select count(*) , convert(varchar(20), DCount, 103)
    from MyTable
    where convert(varchar(20), DCount, 102) >= convert(varchar(20),dateadd(dd, -10, getdate()), 102)
    group by convert(varchar(20), DCount, 102)
    order by convert(varchar(20), DCount, 102) desc

    But it is not giving me desired result. It should give result from 28-feb till date. but it is giving different result. I have checked in table, the data is available since 28-feb-09, so can someone tell me where is the problem with this query? DCount column has date with time, that's why i need to convert it.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by ace_friends22
    ... & we are not suppose to create any additional table in Production environment
    The table is created in the temporary database and will contain approx 10 rows. If you aren't allowed to create temporary tables then I suggest you give the option to your users to stay with the existing code (and it's response times) or allow the use of small temporary tables and get nearly instant response times. Must admit I don't honestly feel like rewriting the code a 3rd time.

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    One more question,
    You are converting the date to different formats (102,103). Numerous suggestions have been given to you on how to improve your code but you still seem to insist on not changing it and then asking us to debug whatever you produce.

  11. #11
    Join Date
    Mar 2009
    Posts
    4
    Try this
    select count(*) 'Day1'
    from MyTable
    where DCount>= convert(varchar(20), dateadd(dd, -0, getdate()), 101) and DCount<convert(varchar(20), dateadd(dd, +1, getdate()), 101)

  12. #12
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Like sundeepsharma said don't use a function on a column else an index won't be used and in your case you'll scan the table 10 times.
    You can avoid accessing the table more than once by using a case statement
    Code:
    select 'MyTable'
    +','+convert(varchar(10), sum
     (case when dcount>=dateadd(dd, datediff(dd,'20000101',getdate())-0, '20000101')
            and dcount< dateadd(dd, datediff(dd,'20000101',getdate())+1, '20000101')
      then 1 else 0 end) )
    +','+convert(varchar(10), sum
     (case when dcount>=dateadd(dd, datediff(dd,'20000101',getdate())-1, '20000101')
            and dcount< dateadd(dd, datediff(dd,'20000101',getdate())+0, '20000101')
      then 1 else 0 end) )
    +','+convert(varchar(10), sum
     (case when dcount>=dateadd(dd, datediff(dd,'20000101',getdate())-2, '20000101')
            and dcount< dateadd(dd, datediff(dd,'20000101',getdate())-1, '20000101')
      then 1 else 0 end) )
    +','+convert(varchar(10), sum
     (case when dcount>=dateadd(dd, datediff(dd,'20000101',getdate())-3, '20000101')
            and dcount< dateadd(dd, datediff(dd,'20000101',getdate())-2, '20000101')
      then 1 else 0 end) )
    ...
    from from MyTable 
    where dcount>=dateadd(dd, datediff(dd,'20000101',getdate())-9, '20000101')
      and dcount< dateadd(dd, datediff(dd,'20000101',getdate())+1, '20000101')
    Last edited by pdreyer; 03-11-09 at 11:07.

Posting Permissions

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