Results 1 to 2 of 2

Thread: Performance Tuning of the query

  1. #1
    Join Date
    Mar 2009
    Posts
    3

    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

    My Original query is :

    select count(*) 'MyTable', DCount from MyTable where DCount >= dateadd(dd, -10, getdate()) group by DCount order by DCount desc

    I have found the List function which gives comma seperated values, but SYBASE-IQ does not support it. Can someone help me?

  2. #2
    Join Date
    Dec 2007
    Posts
    7

Posting Permissions

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