I had a question from the client the other day that confused me at first so I thought it would be good to post here.

The client had a query that looked like this:

SELECT

WH_PROJECT_LITI.PROJECT_NAME AS MNAME

, WH_PROJECT_LITI.PROJECT_NUMBER AS MNUMBER

, WH_PROJECT_LITI.MAIN_ASSIGNEE_NAME

, WH_CF_LITI.LIDISPOSITIONTYPE AS DISPOSITION

, WH_CF_LITI.LIDISPDATE AS DISPDATE

, WH_CF_LITI.LISETTLEAMT AS SETTLEAMNT

, WH_CF_LITI.LIMATTDESC AS MDESC

, WH_CF_LITI.LIDISPDESC AS "Disp Desc"

FROM WH_PROJECT_LITI, WH_CF_LITI

WHERE

WH_PROJECT_LITI.PROJECT_PRIMARY_KEY = WH_CF_LITI.PROJECT_ID

AND (WH_CF_LITI.LIDISPOSITIONTYPE = 'Settlement')

AND (WH_CF_LITI.LIDISPDATE >= TO_DATE('01/01/2010', 'mm/dd/yyyy'))

In TOAD on the clients machine this query finished in 109ms.  He complained that in BIDS Query Builder it finished in over 25 seconds.  The client thought that BIDS was much slower.

I ran the same query from my machine and it finish in TOAD at about the same speed as in BIDS.  I did not actual measure it because it seems almost instantaneous.

Realizing that the client was accessing a database that was remote to his location and my test was local to my location and after taking a closer look at the query results in TOAD I realized what was going on.

The results from TOAD look like this…

The results from Query Designer look like this…

Notice that last two fields in TOAD are reported as (CLOB) Character Large Objects and that the data is not actually returned.  In query designer it actually retrieved that data.

Because they are CLOB data types what comes back from the initial query is pointers to the data and additional calls from the client must be made to get the data.  Remember the client with the problem was going over the WAN which means the latency was much longer for him than me.

I profiled the two columns and neither has a length greater than 2000 characters.  So I told him to replace the query with:

SELECT

WH_PROJECT_LITI.PROJECT_NAME AS MNAME

, WH_PROJECT_LITI.PROJECT_NUMBER AS MNUMBER

, WH_PROJECT_LITI.MAIN_ASSIGNEE_NAME

, WH_CF_LITI.LIDISPOSITIONTYPE AS DISPOSITION

, WH_CF_LITI.LIDISPDATE AS DISPDATE

, WH_CF_LITI.LISETTLEAMT AS SETTLEAMNT

, CAST (SUBSTR(WH_CF_LITI.LIMATTDESC,1,4000) AS VARCHAR2(4000)) AS MDESC

, CAST(SUBSTR(WH_CF_LITI.LIDISPDESC,1,4000) AS VARCHAR2(4000)) AS "Disp Desc"

FROM WH_PROJECT_LITI, WH_CF_LITI

   WHERE

WH_PROJECT_LITI.PROJECT_PRIMARY_KEY = WH_CF_LITI.PROJECT_ID

AND (WH_CF_LITI.LIDISPOSITIONTYPE = 'Settlement')

AND (WH_CF_LITI.LIDISPDATE >= TO_DATE('01/01/2010', 'mm/dd/yyyy'))

I actually measured the difference on my laptop the original query ran 441ms and the modified one ran in 10ms (97% improvement).  

 I guess the moral of the story is everything isn’t what it seems and replace CLOB objects with VARCHAR2 (if you don’t really need the size) so you don’t have these hidden calls on the network.