Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2002
    Posts
    272

    Unanswered: Stop preparing my SQL!

    I have a performance issue with a Cognos report against SQL Server 2005.
    The total running time of the report is 1 minute 15, and using SQL Profiler I found out that 1m13 is spent preparing SQL. Execution and generating the report takes up 2 seconds; no problem there.
    The SQL is the same every time I run the report, yet SQL Server spends 1m13 preparing it every single time! I'm no DBA, but as far as I understand that's not what's supposed to happen; once prepared, the SQL should execute quickly every time.

    Is there a way to stop SQL from preparing the statement every time?

    (Cognos 8 against SQL Server 2005 through OLEDB. Oh, and this query takes about a second when run in EM.)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is it identical? White space, capitalisation, values you use for filtering - everything?

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Yes, it is. Filtering of the results is done client side (Cognos).

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Urgh. Really? All filtering client side?

    Ok - well it doesn't help you now. I don't know if the OLEDB has anything to do with it - I'd have to read around.

    Are you able to influence the SQL sent to the server in any way or does cognos generate it for you under the covers?* Could you post one of these queries please?

    * Might sound like I am going nowhere but SS2005 has a few options for forcing plans.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also have you got a dev\ test box you can play on with this?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Finally..... when you use em (SSMS surely?) you are using it on the same client as the cognos tool? Not on the server or anything?

  7. #7
    Join Date
    Nov 2002
    Posts
    272
    * Urgh. Really? All filtering client side?
    - Yes... I didn't create the report. On the other hand, there's only about 30,000 rows returned.

    * Ok - well it doesn't help you now. I don't know if the OLEDB has anything to do with it - I'd have to read around.
    - Oh, I forgot about that one. I've only been rooting around in SQL2k5 and Cognos docs

    * Are you able to influence the SQL sent to the server in any way or does cognos generate it for you under the covers?
    - Cognos generates it. I can view it, but not change it.

    * Could you post one of these queries please?
    - I could, but it'll have to be Monday. My working day officially ended half an hour ago.

    * Also have you got a dev\ test box you can play on with this?
    - Yes I do. I may have to copy some data to it, but that shouldn't be too hard.

    * Finally..... when you use em (SSMS surely?) you are using it on the same client as the cognos tool? Not on the server or anything?
    - Yes, SSMS of course; I also maintain some older apps which have SQL2k back ends. I get them mixed up sometimes.
    SSMS runs on my desktop client. I don't have priviliges to do that kind of thing on the Congnos production server (and I don't want to).
    Last edited by ivon; 12-03-07 at 03:51.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Super. What I was getting at was that there is defo no bottleneck moving data across the network clouding the issue.

  9. #9
    Join Date
    Nov 2002
    Posts
    272
    Here's the SQL:
    Code:
    select "Indicator_prijs"."Instrument_ID", 
           "Indicator_prijs"."Indicator_prijs_laatste_versie", 
           "Indicator_prijs_batch_feed"."c4", 
           convert(datetime, convert( char(8), current_timestamp, 112 ), 112),
           "dbo".AddWorkingDays(convert(datetime, convert( char(8), current_timestamp, 112 ), 112), -1), 
           "Indicator_prijs"."c24", 
           "dbo".AddWorkingDays(convert(datetime, convert( char(8), current_timestamp, 112 ), 112), -2), 
           "Indicator_prijs"."Indicator_prijs_aangemaakt_op", 
           "Indicator_prijs"."Indicator_prijs_future_datum", 
           case datepart(month,"Indicator_prijs"."Indicator_prijs_future_datum") 
                when 1 then 'JAN ' 
                when 2 then 'FEB ' 
                when 3 then 'MAR ' 
                when 4 then 'APR ' 
                when 5 then 'MAY ' 
                when 6 then 'JUN ' 
                when 7 then 'JUL ' 
                when 8 then 'AUG ' 
                when 9 then 'SEP ' 
                when 10 then 'OCT ' 
                when 11 then 'NOV ' 
                when 12 then 'DEC ' 
           end +CAST( datepart(year,"Indicator_prijs"."Indicator_prijs_future_datum") AS VARCHAR( 200 ) ), 
           "Indicator_prijs"."Indicator_prijs", 
           "Indicator_prijs"."Indicator_batch_feed_ID"
     from (
           select "pcv_instrument_price_incl_arc"."pmip_i_instrument_id" AS "Instrument_ID", 
                  "pcv_instrument_price_incl_arc"."pmip_business_date" AS "Indicator_prijs_future_datum", 
                  "pcv_instrument_price_incl_arc"."pmip_value" AS "Indicator_prijs", 
                  "pcv_instrument_price_incl_arc"."pmip_latest_flag" AS "Indicator_prijs_laatste_versie", 
                  "pcv_instrument_price_incl_arc"."pmip_input_time" AS "Indicator_prijs_aangemaakt_op", 
                  "pcv_instrument_price_incl_arc"."pmip_auth_status" AS "c24", 
                  "pcv_instrument_price_incl_arc"."pmip_batch_id" AS "Indicator_batch_feed_ID"
           from (("PRINS_Mic_ODS"."dbo"."pcv_instrument_price_incl_arc" "pcv_instrument_price_incl_arc" 
                  INNER JOIN "dbo"."pm_currency" "pm_currency" on "pm_currency"."pmcu_currency_id" = "pcv_instrument_price_incl_arc"."pmip_cu_currency_id") 
                  INNER JOIN "dbo"."pm_instrument" "pm_instrument" on "pm_instrument"."pmi_instrument_id" = "pcv_instrument_price_incl_arc"."pmip_i_instrument_id") INNER JOIN "dbo"."pm_price_type" "pm_price_type" on "pm_price_type"."pmprt_price_type_id" = "pcv_instrument_price_incl_arc"."pmip_prt_price_type_id") "Indicator_prijs" 
                  LEFT OUTER JOIN (select "pm_feed_received_or_sent"."pmfrs_batch_id" AS "Indicator_batch_feed_ID", 
                                          "pm_feed_received_or_sent"."pmfrs_business_date" AS "c4"
                                     from "dbo"."pm_feed_received_or_sent" "pm_feed_received_or_sent") "Indicator_prijs_batch_feed" on "Indicator_prijs"."Indicator_batch_feed_ID" = "Indicator_prijs_batch_feed"."Indicator_batch_feed_ID"
    I tried to make it a bit more readable by adding spaces and line breaks.

    The SQL that Cognos generates is exactly the same as what shows up in SQL Profiler.

    I ran it again this morning:
    9:41:42 AM - Prepare SQL
    9:42:57 AM - Execute prepared SQL (The report appears a few seconds after that)
    9:44:34 AM - Unprepare SQL

  10. #10
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Is there any way you can bring the query server side in a stored procedure?

  11. #11
    Join Date
    Nov 2002
    Posts
    272
    I'm pretty sure I could do that, but I won't like it. Doing that takes away some of the flexibility, as changes in the report would mean changing the SP as well.
    On the other hand, if the performance improves drastically, it would make a bunch of people very happy. The query above is part of a larger report with a total running time of 10 minutes. If I can bring that down to, say, 20 or 30 seconds...

    So yeah, I'm going to do that, but only as a workaround. I really want to get this query to run fast.

  12. #12
    Join Date
    Nov 2002
    Posts
    272
    Well, okay.

    It seems to have been the filtering after all. I changed the filter definition in the report so the filter refers to the actual query items and not the underlying 'tables' (in Cognos, they are more like views on the actual tables), and the report came up in about 5 seconds.

    I'm still not sure why, but this seems to work well. I'm going to change the entire 10 minute report and see what the performance gain will be.
    Last edited by ivon; 12-04-07 at 06:17.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just checked in to see where we were. Does this change what you are reading in profiler?

  14. #14
    Join Date
    Nov 2002
    Posts
    272
    It does. There is different SQL, and the time difference between Prepare and Execute is down to 2 seconds.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No unprepare event? Please could you post your new SQL? I'm not quite sure what you mean by actual query items etc.. I know this is all academic now but....

Posting Permissions

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