Query taking forever in Full/Thin Client; took 2 sec on QA

building reports desktopintelligence
1

Hi all,

I have a query that hardly took 2 seconds on Query Analyzer and is taking forever in Full CLient and thin client.
As a matter of fact the Full client on my machine freezes when i run this query and i have to kill the process everytime.

Any thoughts or ideas on this??

Thanks

kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

2

By the way i forgot to mention that I am on 6.1a (Andreas favourite BO version :lol: ).

kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

3

Which DBMS (MS SQL Server I assume)? Which middleware? Which array fetch size?

Could this be related to this thread (even though you are mentioning Full Client and that thread covers WebI 6.1a)?

Andreas :de: (BOB member since 2002-06-20)

4

Any SQL aggregation? Is it returning rows or sitting in the Anlayzing phase?

Steve Krandel :us: (BOB member since 2002-06-25)

5

I am using SQL Server 2000 on Win XP.
Andy’s post was helpful and I went through that earlier as I had Webi problems as well and I found out that I have to upgrade to 6.1b or 6.5.

I am not worried about Thin CLient for now. I assume if it is taking 2 secs in Qery Analyzer, it should take 3-5 secs in Full CLient. It does not return any rows, just sits in the analyzing phase. The query has some aggregation; just for reference below is the query

SELECT
  Claims.Aid_Category_Code,
  AidCategory.Aid_Category_Description,
  count(distinct(( Claims.re_unique_id ))),
  sum(Claims.Paid_Amount_Dtl),
  Claims.Category_of_Service,
  CategoryService.Category_Of_Service_Description,
  Clients.Residence_Region
FROM
  dbo.HWMF_V_CLAIMS  Claims,
  dbo.HWMF_T_STATE_AID_CATEGORY  AidCategory,
  dbo.HWMF_T_CTG_OF_SVC  CategoryService,
  dbo.HWMF_T_CLIENTS  Clients
WHERE
  ( Claims.re_unique_id=Clients.re_unique_id  )
  AND  ( Claims.Category_of_Service=CategoryService.Category_Of_Service  )
  AND  ( Claims.Aid_Category_Code=AidCategory.Aid_Category_Code  )
  AND  
  (
   ( Claims.Transaction_Status_Code_Dtl IN ('5','6')  AND Claims.Transaction_Type_Code_Dtl  IN ('3','4') AND Claims.Current_IND = 'C'  )
   AND
   Claims.From_Date_of_Service_Dtl  BETWEEN  '01/01/2003 00:0:0' AND   '12/31/2003 00:0:0'
   AND
   Claims.Aid_Category_Code  =  '55'
  )
GROUP BY
  Claims.Aid_Category_Code, 
  AidCategory.Aid_Category_Description, 
  Claims.Category_of_Service, 
  CategoryService.Category_Of_Service_Description, 
  Clients.Residence_Region

[added bbc CODE formatting for better readibility - Andreas]

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

6

One more question.

Is it possible for any of the Claims.Paid_Amount_Dtl values to be NULL. I have had problems with 6.1a and having NULL values in columns that I was summing.

If this is possible, can you try to use whatever function (ISNULL) to substitute 0s in for the NULLs? When I did this, it worked great!

Steve Krandel :us: (BOB member since 2002-06-25)

7

Steve,

Thanks for your reply. At the database level Claims.Paid_Amount_Dtl can be Null, but in this query I have a condition for Paid Claims only. So in this query it cant be Null.

Thanks

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

8

I know, but try it anyway. You might be surprised.

Steve Krandel :us: (BOB member since 2002-06-25)

9

Steve,

Instead of

sum(Claims.Paid_Amount_Dtl)

I tried

sum(IsNull(Claims.Paid_Amount_Dtl, 0))

Still the problem persists. It says analyzing…
Also, the connection thing is out of question as I am able to run other queries on the same universe on Full Client.

Any suggestions??

kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

10

Steve,

It was the most silly thing. I was using old connections that someone else created on my machine and he unchecked

ANSI quoted identifiers
ANSI Null, Padding and Warning

Thanks for all your help and sorry for wasting your and others time for such a silly thing.

kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)