Extract data from Secondary server Slow
Hi All,
Is there a way to make my select statement faster? i'm using this in my ETL
but it is running slow..
SELECT CARD.CARDID
,CUST.HHOLDID
,CUST.PHONENUM
,CUST.FIRSTNAME
,CUST.LASTNAME
,CUST.STATUS
,CUST.CUSTTYPE
,CUST.AIR
,CUST.FASTFID
,CARD.CARDC
FROM CARDX CARD
,CUSTOMERS CUST
WHERE CUST.CUSTOMERID = CARD.CUSTOMERID
AND CUST.CUSTOMERID BETWEEN '050000000000' AND '059999999999';
NOTE: I can't use onpload because this is embeeded in my application.
Assuming you have indexes to support this query, statistics have been
updated on the tables and there is sufficient resource (buffers) to handle
this query and everything else that is happening on the server.
I have often found that an explicit 'field' > 'value' and 'field' < 'value'
is more efficient than 'between'. Is customerid a char field as that is
what you are comparing with ? If it is an integer than compare to an
integer. If customerid is a char do you have an option to say
customerid[1,2] = '05' as this will be much quicker.
Keith
On 2 May 2013 09:44, JACK PAPA <informix2009@gmail.com> wrote:
> Hi All,
>
> Is there a way to make my select statement faster? i'm using this in my ETL
> but it is running slow..
>
> SELECT CARD.CARDID>
> ,CUST.HHOLDID
>
> ,CUST.PHONENUM
>
> ,CUST.FIRSTNAME
>
> ,CUST.LASTNAME
>
> ,CUST.STATUS
>
> ,CUST.CUSTTYPE
>
> ,CUST.AIR
>
> ,CUST.FASTFID
>
> ,CARD.CARDC
> FROM CARDX CARD
>
> ,CUSTOMERS CUST
> WHERE CUST.CUSTOMERID = CARD.CUSTOMERID
> AND CUST.CUSTOMERID BETWEEN '050000000000' AND '059999999999';
>
> NOTE: I can't use onpload because this is embeeded in my application.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e01183198487c6904dbb8d229
Table schema? Data distributions for the table? Set explain output? Give
us something to work with here Jack?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, May 2, 2013 at 4:44 AM, JACK PAPA <informix2009@gmail.com> wrote:
> Hi All,
>
> Is there a way to make my select statement faster? i'm using this in my ETL
> but it is running slow..
>
> SELECT CARD.CARDID>
> ,CUST.HHOLDID
>
> ,CUST.PHONENUM
>
> ,CUST.FIRSTNAME
>
> ,CUST.LASTNAME
>
> ,CUST.STATUS
>
> ,CUST.CUSTTYPE
>
> ,CUST.AIR
>
> ,CUST.FASTFID
>
> ,CARD.CARDC
> FROM CARDX CARD
>
> ,CUSTOMERS CUST
> WHERE CUST.CUSTOMERID = CARD.CUSTOMERID
> AND CUST.CUSTOMERID BETWEEN '050000000000' AND '059999999999';
>
> NOTE: I can't use onpload because this is embeeded in my application.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c20c9ac2ad9104dbb93b38